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.

We have two types of restore points. Those are:

1. Normal restore point
2. Guaranteed restore point

Guaranteed restore points, it use a mechanism similar to flashback logging, and as with flashback logging, Oracle must store the required logs in the flash recovery area.
Oracle database server periodically logs before images of data blocks in the Flashback Database logs. The data block images are used to quickly back out changes to the database during Flashback Database.

Flashback Database reduces the time required to recover the database to a point in time.

The flashback logs are created in an area known as the Flash Recovery Area.

Prerequisites for restore points:

To support the use of guaranteed restore points, the database must satisfy the following requirements:

– The COMPATIBLE initialization parameter must be set to 10.2 or greater.

– The database must be running in ARCHIVELOG mode. The FLASHBACK DATABASE operation used to return your database to a guaranteed restore point requires the use of archived redo logs from around the time of the restore point.

– A flash recovery area must be configured, as described in “Setting Up a Flash Recovery Area for RMAN”. Guaranteed restore points use a mechanism similar to flashback logging, and as with flashback logging, Oracle must store the required logs in the flash recovery area.

If flashback database is not enabled, then the database must be mounted, not open, when creating the first guaranteed restore point (or if all previously created guaranteed restore points have been dropped).

So here is the my demo. Let us see what will happens:

STEP A:

1. Check db_recovery_file_dest&db_recovery_file_dest_size existing parameters.

SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE
 ------------------------------------
 db_recovery_file_dest string +ORADATA
 db_recovery_file_dest_size big integer 300G

If related parameters have not been set than you need to set them by using below syntax:

ALTER SYSTEM SET db_recovery_file_dest_size = 300G SID='*' scope='BOTH';
 ALTER SYSTEM SET db_recovery_file_dest = "+ORAFRA" SID='*' scope=BOTH;

Recheck parameters:

SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE
 ------------------------------------ ----------- --------------------------
 db_recovery_file_dest string +ORAFRA
 db_recovery_file_dest_size big integer 300G

2. Check DB_FLASHBACK_RETENTION_TARGET parameter.

The DB_FLASHBACK_RETENTION_TARGET parameter should be at least 24 hours.

Also you need enough space for flashback logs to can keep 1 day archivelogs size

SQL> show parameter DB_FLASHBACK_RETENTION_TARGET

NAME TYPE VALUE
 ------------------------------------ ----------- --------------------------
 db_flashback_retention_target integer 1440

3. Check LOG_BUFFER parameter.

Set LOG_BUFFER to at least 8 MB to give flashback database more buffer space in memory.

For large 11.1.0.7+ databases with more than a 4GB SGA, you may consider setting LOG_BUFFER to values in the range of 32-64 MB.

SQL> show parameter LOG_BUFFER

NAME TYPE VALUE
------------------------------------ ----------- --------------------------
log_buffer integer 7028736

4. Check PARALLEL_EXECUTION_MESSAGE_SIZE parameter.

The PARALLEL_EXECUTION_MESSAGE_SIZE to at least 8192. This will improve the media recovery phase of any flashback database operation.

SQL> show parameter PARALLEL_EXECUTION_MESSAGE_SIZE

NAME TYPE VALUE
 ------------------------------------ ----------- --------------------------
 parallel_execution_message_size integer 16384

5. Enable flashback

SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON
 ------------------
 NO

Enable parameter

SQL> shutdown immediate;
SQL> startup mount
SQL> alter database flashback on;

Database altered.

SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON
 ------------------
 YES

STEP B:

Before start process, We need to create Guarenteed Restore Point,so that when its neccessary we can revert back to existing point with Guarenteed restore point feauture.

DO NOT FORGET DB mode is MOUNT MODE!!

 SQL> CREATE RESTORE POINT before_endofday GUARANTEE FLASHBACK DATABASE;
 Restore point created.
 
 SQL> Alter database open; 
 Database Altered
 
 SQL> select name, time,guarantee_flashback_database from v$restore_point;

After that creation now I am going to create table for test purpose:

SQL> create table test1(a char);
 SQL> insert into test1 values (1);

1 row created.

SQL> commit;

Commit complete.

Than try to my db to move restore point:

SQL>  flashback database to restore point before_endofday;
 flashback database to restore point before_endofday
 *
 ERROR at line 1:
 ORA-38757: Database must be mounted and not open to FLASHBACK.

To can use this future your database status should be at mount mode.

SQL> shutdown immediate;
SQL> startup nomunt;

Than try to my db to move restore point again:

SQL> flashback database to restore point before_endofday;
Flashback complete.

SQL> alter database open resetlogs;
Database altered.

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

As you can see We can not see table which We have been created. Our database revert back to retore point.

For can see Restore point which may can create previously:

SELECT NAME, SCN, TIME, DATABASE_INCARNATION# DI,GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE
 FROM V$RESTORE_POINT
 WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

Source:
http://docs.oracle.com/cd/B19306_01/backup.102/b14192/rpfbdb002.htm

Advertisements