1. Requirements for Guaranteed Restore Points
The COMPATIBLE initialization parameter must be set to 10.2 or greater.
The database must be running in ARCHIVELOG mode.
A flash recovery area must be configured Guaranteed restore points use a mechanism similar to flashback logging. Oracle must store the required logs in the flash recovery area.
Oracle 10.2
If flashback database is not enabled, then the database must be mounted, not open, when creating the first guaranteed restore point
SQL> ALTER DATABASE FLASHBACK ON;
Oracle 11.x
There is no need to mount the database. Flashback can be tunred on at open state.
SQL> ALTER DATABASE FLASHBACK ON;
2. Creating Restore points [CREATE RESTORE POINT]
1 2 3 4 5 6 7 | # Create Normal restore points SQL> CREATE RESTORE POINT before_upgrade; #Create guaranteed restore points SQL> CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE; |
3. Listing restore points [V$RESTORE_POINT]
1 2 3 4 5 6 7 | # To see a list of the currently defined restore points SQL>SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE FROM V$RESTORE_POINT; #To view only the guaranteed restore points: SQL>SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES'; |
For normal restore points, STORAGE_SIZE is zero. For guaranteed restore points, STORAGE_SIZE indicates the amount of disk space in the flash recovery area used to retain logs required to guarantee FLASHBACK DATABASE to that restore point.
4. Dropping restore points [DROP RESTORE POINT]
1 2 | #Same statement is used to drop both normal and guaranteed restore points. SQL> DROP RESTORE POINT before_app_upgrade; |
5. Turn of Flashback
SQL> ALTER DATABASE FLASHBACK OFF;
Hope this helps! Regards Rupam
