|Oracle® Database SQL Language Reference
11g Release 2 (11.2)
Part Number E17118-03
POINT statement to create a restore point, which is a name associated with a timestamp or an SCN of the database. A restore point can be used to flash back a table or the database to the time specified by the restore point without the need to determine the SCN or timestamp. Restore points are also useful in various RMAN operations, including backups and database duplication. You can use RMAN to create restore points in the process of implementing an archival backup.
Oracle Database Backup and Recovery User's Guide for more information on creating and using restore points and guaranteed restore points, for information on database duplication, and for information on archival backups
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 can create a 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 consistently before being mounted unless it is a physical standby database.
You must have created a fast 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.
Specify the name of the restore point. The name is a character value of up to 128 characters.
The database can retain at least 2048 normal restore points. Normal 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.
If you specify neither
GUARANTEE FLASHBACK DATABASE, then the resulting 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 such restore points. When the maximum number of restore points is reached, according to the rules described in
restore_point above, the database automatically drops the oldest restore point. Under some circumstances the restore points will be retained in the RMAN recovery catalog for use in restoring long-term backups. You can explicitly drop a restore point using the
AS OF Clause
Use this clause to create a restore point at a specified datetime or SCN in the past. If you specify
expr must be a valid datetime expression resolving to a time in the past. If you specify SCN, then
expr must be a valid SCN in the database in the past. In either case,
expr must refer to a datetime or SCN in the current incarnation of the database.
PRESERVE to indicate that the restore point must be explicitly deleted. Such restore points are useful for preserving a flashback database.
GUARANTEE FLASHBACK DATABASE
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 fast 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 are always preserved. They 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 fast recovery area. Therefore, Oracle recommends that you create guaranteed restore points only after careful consideration.
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