5.2 Using Normal and Guaranteed Restore Points

This section describes the commands used to manage normal and guaranteed restore points. This section contains the following topics:

5.2.1 Requirements for Using Guaranteed Restore Points

To support the use of guaranteed restore points, the database must satisfy the following requirements:

  • The COMPATIBLE initialization parameter must be set to 10.2 or greater.

  • The database must be running in ARCHIVELOG mode. The FLASHBACK DATABASE operation used to return your database to a guaranteed restore point requires the use of archived redo logs from around the time of the restore point.

  • A flash recovery area must be configured, as described in "Setting Up a Flash Recovery Area for RMAN". Guaranteed restore points use a mechanism similar to flashback logging, and as with 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 (or if all previously created guaranteed restore points have been dropped).


There are no special requirements for using normal restore points.

5.2.2 Creating Normal and Guaranteed Restore Points

To create normal or guaranteed restore points, use the CREATE RESTORE POINT statement in SQL*Plus, providing a name for the restore point and specifying whether it is to be a guaranteed restore point or a normal one (the default).

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

This example shows how to create a normal restore point:

Restore point created.

This example shows how to create a guaranteed restore point:

Restore point created.

See also:

Oracle Database SQL Reference for reference information about the SQL CREATE RESTORE POINT statement

5.2.3 Listing Restore Points

To see a list of the currently defined restore points, use the V$RESTORE_POINT control file view, by means of the following query:


You can view the name of each restore point, the SCN, wall-clock time and database incarnation number at which the restore points were created, whether each restore point is a guaranteed restore point, and how much space in the flash recovery area is being used to support information needed for Flashback Database operations to that restore point.

You can also use the following query to view only the guaranteed restore points:


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.

Full information about the columns of V$RESTORE_POINT can be found in the Oracle Database Reference.

5.2.4 Dropping Restore Points

When you are satisfied that you do not need an existing restore point, or when you want to create a new restore point with the name of an existing restore point, you can drop the restore point, using the DROP RESTORE POINT SQL*Plus statement. For example:

SQL> DROP RESTORE POINT before_app_upgrade;
Restore point dropped.

The same statement is used to drop both normal and guaranteed restore points.


Normal restore points eventually age out of the control file, even if not explicitly dropped. The rules governing retention of restore points in the control file are:
  • The most recent 2048 restore points are always kept in the control file, regardless of their age.

  • Any restore point more recent than the value of CONTROL_FILE_RECORD_KEEP_TIME is retained, regardless of how many restore points are defined.

Normal restore points that do not meet either of these conditions may age out of the control file.

Guaranteed restore points never age out of the control file. They remain until they are explicitly dropped.

See also:

Oracle Database SQL Reference for reference information about the SQL DROP RESTORE POINT statement

5.2.5 Monitoring Space Usage For Guaranteed Restore Points

When guaranteed restore points are defined on your database, you should monitor the amount of space used in your flash recovery area for files required to meet the guarantee. Use the query for viewing guaranteed restore points in "Listing Restore Points" and refer to the STORAGE_SIZE column to determine the space required for files related to each guaranteed restore point. To see the total usage of your flash recovery area, use the query provided in "Using V$RECOVERY_FILE_DEST and V$FLASH_RECOVERY_AREA_USAGE".