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 to the restore point regardless of the DB_FLASHBACK_RETENTION_TARGET initialization parameter setting.
Guaranteed restore points must be dropped explicitly by the user using the DROP RESTORE POINT statement. They do not age out.
• 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. You can explicitly drop a normal restore point using the DROP RESTORE POINT statement.
Prerequisites:
To create a restore point, you must have either SELECT ANY DICTIONARY or FLASHBACK ANY TABLE privilege.
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 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 points. 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 and preserved restore points are retained in the database until explicitly dropped by the user.
If you specify neither PRESERVE nor 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.
When the maximum number of restore points is reached, the database automatically drops the oldest restore point.
PRESERVE
Specify PRESERVE to indicate that the restore point must be explicitly deleted. Such restore points are useful when created for use with the flashback history feature.
Example of how to use a restore point:
Enable archivelog
SQL> alter database archivelog;
Set Flash Recovery Area
SQL> alter system set db_recovery_file_dest='/u01/home/oracle/oradata/clone/flash' scope=spfile;
System altered.
SQL> alter system set db_recovery_file_dest_size=2g scope=spfile;
System altered.
Enable flashback
ALTER DATABASE FLASHBACK ON;
Database altered.
A user “abc” has been created with a table “t1”.
SQL> grant select any dictionary to abc;
Grant succeeded.
SQL> conn abc/abc
Connected.
Create a restore point named “before_update”
SQL> create restore point before_update;
Restore point created.
SQL> select scn,name from v$restore_point;
SCN NAME
------------------------------------------------------------------------
1925632 BEFORE_UPDATE
Current data in table t1.
SQL> select * from t1;
ID NAME
---------- ---------------
1 shakeeb
2 petkar
Modify a record to revert back using the restore point.
SQL> update t1 set id=10 where name='shakeeb';
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from t1;
ID NAME
---------- ---------------
10 shakeeb
2 petkar
SQL> flashback table t1 to restore point before_update;
flashback table t1 to restore point before_update
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled
Enable row movement on the created table.
SQL> alter table t1 enable row movement;
Table altered.
SQL> flashback table t1 to restore point before_update;
Flashback complete.
Check the data in the table to see if the data is same as it was before creating the restore point.
SQL> select * from t1;
ID NAME
---------- ---------------
1 shakeeb
2 petkar
Do the same thing with a GUARANTEED RESTORE POINT
Create GUARANTEED RESTORE POINT named “before_update_guaranteed”.
SQL> create restore point before_update_guaranteed guarantee flashback database;
Restore point created.
SQL> select * from t1;
ID NAME
---------- ---------------
1 shakeeb
2 petkar
SQL> update t1 set id=10 where name='shakeeb';
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from t1;
ID NAME
---------- ---------------
10 shakeeb
2 petkar
Check all the restore points from RMAN
RMAN> list restore point all;
using target database control file instead of recovery catalog
SCN RSP Time Type Time Name
---------------- --------- ---------- --------- ----
1926574 30-APR-15 BEFORE_UPDATE
1926721 30-APR-15 BEFORE_UPDATE_GUARANTEED
To flashback database to a restore point
SHUTDOWN IMMEDIATE
STARTUP MOUNT
FLASHBACK DATABASE TO RESTORE POINT BEFORE_UPDATE_GUARANTEED
ALTER DATABASE OPEN READ ONLY
-- check the flashed back data
SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER DATABASE OPEN RESETLOGS;
SQL> startup mount
SQL> flashback database to restore point before_update_guaranteed;
Flashback complete.
SQL> alter database open read only;
Database altered.
SQL> conn abc/abc
Connected.
SQL> select * from t1;
ID NAME
---------- ---------------
1 shakeeb
2 petkar
SQL> shutdown immediate
SQL> startup mount
SQL> alter database open resetlogs;
Database altered.
Dropping restore points
SQL> drop restore point before_update;
Restore point dropped.
SQL> drop restore point before_update_guaranteed;
Restore point dropped.
Once FLASHBACK has been enabled we cannot disable archivelog.
SQL> alter database noarchivelog;
alter database noarchivelog
*
ERROR at line 1:
ORA-38774: cannot disable media recovery - flashback database is enabled
SQL> alter database flashback off;
Database altered.