The database can be open or mounted when creating restore points. If it is mounted, then it must have been shut down cleanly (unless this is a physical standby database).

 Use the CREATE RESTORE POINT statement in SQL*Plus

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.
• If flashback database is not enabled, then the database must be mounted, not open, when creating the first guaranteed restore point

2. Requirements for Normal Restore Points

There are no special requirements for using normal restore points.

3. 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;
 
 

4. 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.

5. 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; 

Related Articles

1. FlashBack Database Technology [Part-1] [Introduction]
2. FlashBack Database Technology [Part-2] [Set up and Maintain flashback database]
3. FlashBack Database Technology [Part-3] [Set up and Maintain Restore points]
4. FlashBack Database Technology [Part-4] [Flashback Database]
5. FlashBack Database Technology [Part-5] [Flashback and Database Point-in-Time Recovery (Coming Soon)]