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
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
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.
To create a normal restore point, you must have either
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
TABLE system privilege or the
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.
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.
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