Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
|
|
View PDF |
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.
See Also:
Oracle Database Backup and Recovery Basics for more information on creating and using restore points and guaranteed restore points
FLASHBACK DATABASE, FLASHBACK TABLE, and DROP RESTORE POINT for information on using and dropping restore points
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::=
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