GUARANTEED RESTORE POINTS IN ORACLE

Guaranteed Restore Points came with oracle 10gR2. It can ensure that you can rewind the database to a time without tradional point in time recovery. It uses db_file_recovery_dest to keep the changed blocks, even if flashback logging is not enabled for your database. It never age out of the control file and must be explicitly dropped.

A normal restore point assigns a restore point name to an SCN or specific point in time. The control file can maintain a record of thousands of normal restore points with no significant effect on database performance. Normal restore points eventually age out of the control file if not manually deleted, so they require no ongoing maintenance.

SQL>CREATE RESTORE POINT before_upgrade;

Creating a normal restore point eliminates manually recording an SCN in advance or determines the correct SCN after the fact by using features such as Flashback Query. Like a normal restore point, a guaranteed restore point serves as an alias for an SCN in recovery operations.

A guaranteed restore point ensures that you can use Flashback Database to rewind a database to its state at the restore point SCN, even if the generation of flashback logs is disabled. If flashback logging is enabled, then a guaranteed restore point enforces the retention of flashback logs required for Flashback Database to any SCN after the earliest guaranteed restore point.

Caution: If we are using Guaranteed Restore Points with Flashback logging turned on, we need to exercise care that we drop restore points that are not in use or that we allocate sufficient space for the flashback logs as specified by the init.ora parameter db_recovery_file_dest_size. This is because the flashback logs will be retained and not overwritten due to space constraints when we create guaranteed restore points.

To create first guaranteed restore point when flashback off, you first start the database in mount state after a consistent shut down. After opening in mount state you can create your first guaranteed restore point like below.

SQL> CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;

You can use the LIST command to list either a specific restore point or all restore points known to the RMAN repository

LIST RESTORE POINT restore_point_name;
LIST RESTORE POINT ALL;

SCN              RSP Time  Type       Time      Name
—————-           ——— ———-  ———        —---      ----
431843           24-NOV-12            24-NOV-12 NORMAL_RS
448619           24-NOV-12 GUARANTEED 24-NOV-12 GUARANTEED_RS

RMAN indicates the SCN and time of the restore point, the type of restore point, and the name of the restore point. You can useV$RESTORE_POINT control file view to check a list of all currently defined restore points (normal and guaranteed).

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE
FROM V$RESTORE_POINT;

You can use the below query to find only Guaranteed restore points:

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE
FROM V$RESTORE_POINT

WHERE GUARANTEE_FLASHBACK_DATABASE = ‘YES’;

Note: For normal restore points, STORAGE_SIZE is zero. For guaranteed restore points, STORAGE_SIZE indicates the amount of disk space in the flash recovery area used to retain logs required to guarantee FLASHBACK DATABASE to that restore point.

Dropping Restore Points:

When you are satisfied that you do not need an existing restore point, or when you want to create a new restore point with the name of an existing restore point, you can drop the restore point.

SQL>DROP RESTORE POINT before_app_upgrade;Note: The same statement is used to drop both normal and guaranteed restore points. Normal restore points eventually age out of the control file, even if not explicitly dropped where as guaranteed restore points never age out of the control file. They remain until they are explicitly dropped.

How to Flashback database till Restore Points:
Login to RMAN and bring the database to mount mode.

FLASHBACK DATABASE TO RESTORE POINT ‘before_upgrade’;

FLASHBACK DATABASE TO SCN 448619;

ALTER DATABASE OPEN;

Requirement for using Guaranteed Restore Points:

§         The compatible restore point must be set to 10.2 or greater.

§         The database must be running in archivelog mode.

§         A flashback recovery area must be configured.

§         If flashback database is not enabled, then the database must be mounted (not open) when creating the first guaranteed restore point.

Checking Existing Parameter before using Guaranteed Restore Points:

Check the existing database version. You can use the guaranteed restore point facility since the oracle database version 10.2 or higher.

SQL> Select * from V$Version;

Check the database mode. If it is not in archivelog then you need to convert it into archive log mode. Check the separate post to convert archivelog mode: Convert Noarchive to Archive

SQL> select log_mode from v$database;

SQL> show parameter db_recovery_file_dest;

Check the separate post to set the recovery size and destination: Set Flash Recovery Destination

The DB_FLASHBACK_RETENTION_TARGET parameter should be at least 1 day (24 hours). You need enough space for flashback logs to keep 1 day archivelogs size.

SQL> show parameter DB_FLASHBACK_RETENTION_TARGET;

If required set LOG_BUFFER to at least 8 MB to give flashback database more buffer space in memory and setPARALLEL_EXECUTION_MESSAGE_SIZE to at least 8192. This will improve the media recovery phase of any flashback database operation.

SQL> show parameter LOG_BUFFER;

SQL> show parameter PARALLEL_EXECUTION_MESSAGE_SIZE;

Enable Flashback database. If flashback database is not enabled, then the database must be mounted (not open) when creating the first guaranteed restore point.

SQL> select FLASHBACK_ON from v$database;

SQL> Alter database flashback on;

SQL> Select FLASHBACK_ON from v$database;

SQL> CREATE RESTORE POINT before_date_20121202 GUARANTEE FLASHBACK DATABASE;

Now create or update some object for the test purpose before applying the restore point.

SQL> Create table pay_payment_master_demo as select * from pay_payment_master;

SQL> commit;

SQL> flashback database to restore point before_date_20121202;
ERROR at line 1:
ORA-38757: Database must be mounted and not open to FLASHBACK.
Thus to use this future your database status should be at mount mode.

Shutdown immediate;
Startup mount;

SQL> flashback database to restore point before_date_20121202;

Flashback complete.
SQL> alter database open resetlogs;

Database altered.

SQL> select * from pay_payment_master_demo;
ERROR at line 1:
ORA-00942: table or view does not exist

We can not see that table which we have been created before restore. Thus our database revert back to restore point.