CREATE RESTORE POINT

Purpose

Use the CREATE RESTORE POINT statement to create a restore point, which is a name associated with an SCN of the database corresponding to the time of the creation of the restore point. A restore point can be used to flash a table or the database back to the time of creation of the restore point without the need to determine the SCN or timestamp.

There are two types of restore point:

• Guaranteed restore points: A guaranteed restore point enables you to flash the database back deterministically to the restore point regardless of the DB_FLASHBACK_RETENTION_TARGET initialization parameter setting. The guaranteed ability to flash back depends on sufficient space being available in the flash recovery area.

  Guaranteed restore points guarantee only that the database will maintain enough flashback logs to flashback the database to the guaranteed restore point. It does not guarantee that the database will have enough undo to flashback any table to the same restore point.

  Guaranteed restore points must be dropped explicitly by the user using the DROP RESTORE POINT statement. They do not age out. Guaranteed restore points can use considerable space in the flash recovery area. Therefore, Oracle recommends that you create guaranteed restore points only after careful consideration.

• Normal restore points: A normal restore point enables you to flash the database back to a restore point within the time period determined by the DB_FLASHBACK_RETENTION_TARGET initialization parameter. The database automatically manages normal restore points. When the maximum number of restore points is reached, according to the rules described restore_point, the database automatically drops the oldest restore point. However, you can explicitly drop a normal restore point using the DROP RESTORE POINT statement.

You can create either type of restore point on a primary or standby database. The database can be open or mounted but not open. If the database is mounted, then it must have been shut down cleanly before being mounted unless it is a physical standby database.

Prerequisites

To create a normal restore point, you must have either SELECT ANY DICTIONARY or FLASHBACK ANY TABLE privilege. To create a guaranteed restore point, you must have the SYSDBA system privileges.To view or use a restore point, you must have the SELECT ANY DICTIONARY or FLASHBACK ANY TABLE system privilege or the SELECT_CATALOG_ROLE role.

You must have created a flash recovery area before creating a guaranteed restore point. You need not enable flashback database before you create the restore point. However, if flashback database is not enabled, then the first guaranteed restore point you create on this database must be created when the database is mounted. The database must be in ARCHIVELOG mode if you are creating a guaranteed restore point.

Syntax

create_restore_point::=

Description of the illustration create_restore_point.gif

Semantics

restore_point Specify the name of the restore point. The name is a character value of up to 128 characters.

The database can retain up to 2048 restore point. Restore points are retained in the database for at least the number of days specified for the CONTROL_FILE_RECORD_KEEP_TIME initialization parameter. The default value of that parameter is 7 days. Guaranteed restore points are retained in the database until explicitly dropped by the user.

GUARANTEE FLASHBACK DATABASE Specify this clause to create a guaranteed restore point. Please refer to the "Purpose" section above for information on guaranteed restore points.

Examples

Creating and Using a Restore Point: Example The following example creates a normal restore point, updates a table, and then flashes back the altered table to the restore point. The example assumes the user hr has the appropriate system privileges to use each of the statements.

CREATE RESTORE POINT good_data;

SELECT salary FROM employees WHERE employee_id = 108;

    SALARY
----------
     12000

UPDATE employees SET salary = salary*10
   WHERE employee_id = 108;

SELECT salary FROM employees
   WHERE employee_id = 108;

    SALARY
----------
    120000

COMMIT;

FLASHBACK TABLE employees TO RESTORE POINT good_data;

SELECT salary FROM employees
   WHERE employee_id = 108;

    SALARY
----------
     12000