Retore point in oracle

Restore point:

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.

Two types:

1. Normal restore point
2. Guaranteed restore point

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.
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:

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.(In which situation,we create restore point in standby database, I will tell in another article)

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.
• 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).

Privileges required

• Select any dictionary
• Select catalog role
• Flashback any table

Steps: (Enable the Flashback recovery area)

1.Configure the Flashback recovery area.

SQL>Alter system set db_recovery_file_dest=’ D:\oracle\product\10.2.0\flash_recovery_area’ scope=spfile;

SQL>Alter system set db_recovery_file_dest _size=2G scope=spfile;

2.Shutdown the database.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

3. Startup the database in Mount stage & Enable the flashback feature.

SQL> startup mount
ORACLE instance started.

Total System Global Area 293601280 bytes
Fixed Size 1289064 bytes
Variable Size 92275864 bytes
Database Buffers 192937984 bytes
Redo Buffers 7098368 bytes
Database mounted.

SQL> Alter database flashback on;
Database altered.

SQL>Select flashback_on from v$database;
(Verify the flashback on or not)

SQL> alter database open;
Database altered.

Small testing about restore point

SQL> create user raja identified by raja;

User created.

SQL> grant sysdba, connect, resource, dba to raja;

Grant succeeded.

--- Create new table test_raja with data

SQL> create table test_raja as select * from dba_objects;

Table created.

SQL> select count(*) from test_raja;

COUNT(*)
----------
68693

---- Check flashback enable or not

SQL> select flashback_on from v$database;

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

--- Create new table raja.restore_raja with data

SQL> create table raja.restore_raja as select * from dba_objects nologging;

Table created.

SQL> delete from raja.restore_raja where rownum<10000; 9999 rows deleted. SQL> commit;

Commit complete.

SQL> select count(*) from raja.restore_raja;

COUNT(*)
----------
58695

--- Create Normal restore point

SQL> create restore point test_april21;

Restore point

SQL> SELECT scn, time, database_incarnation#, guarantee_flashback_database from
v$restore_point;

SCN TIME DATABASE_INCARNATION# GUA
---------- -------------------------------- --------------------- ---
905359 21-APR-10 09.18.21.000000000 AM 2 NO

-----Delete some records & create another table for testing.

SQL> delete from raja.restore_raja where rownum<10000; 9999 rows deleted. SQL> commit;

Commit complete.

SQL> select count(*) from raja.restore_raja;

COUNT(*)
----------
48696

SQL> create table raja.restore_raja_test as select * from raja.restore_raja;

Table created.

---Bounce the database in mount stage

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 535662592 bytes
Fixed Size 1348508 bytes
Variable Size 176163940 bytes
Database Buffers 352321536 bytes
Redo Buffers 5828608 bytes
Database mounted.

SQL> select file#,CHECKPOINT_CHANGE#,CHECKPOINT_TIME from v$datafile_header;

FILE# CHECKPOINT_CHANGE# CHECKPOIN
---------- ------------------ ---------
1 908132 21-APR-10
2 908132 21-APR-10
3 908132 21-APR-10
4 908132 21-APR-10

------ Flashback the database

SQL> flashback database to restore point TEST_APRIL21;

Flashback complete.

SQL> select file#,CHECKPOINT_CHANGE#,CHECKPOINT_TIME from v$datafile_header;

FILE# CHECKPOINT_CHANGE# CHECKPOIN
---------- ------------------ ---------
1 905360 21-APR-10
2 905360 21-APR-10
3 905360 21-APR-10
4 905360 21-APR-10

SQL> SELECT scn, time, database_incarnation#, guarantee_flashback_database from

2 v$restore_point;

SCN TIME DATABASE_INCARNATION# GUA
---------- -------------------------------- --------------------- ---
905359 21-APR-10 09.18.21.000000000 AM 2 NO

SQL> alter database open resetlogs;

Database altered.

For testing purpose we created table & deleted 10000 records from raja.restore_raja table.

SQL> select count(*) from raja.restore_raja_test;
select count(*) from raja.restore_raja_test
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select file#,CHECKPOINT_CHANGE#,CHECKPOINT_TIME from v$datafile_header;

FILE# CHECKPOINT_CHANGE# CHECKPOIN
---------- ------------------ ---------
1 905364 21-APR-10
2 905364 21-APR-10
3 905364 21-APR-10
4 905364 21-APR-10

SQL> select count(*) from raja.restore_raja;

COUNT(*)
----------
58695

Table level flashback testing:

SQL> create restore point table_restore_raja1;

Restore point created.

SQL> alter table raja.restore_raja enable row movement;

Table altered.

SQL> select count(*) from raja.restore_raja;

COUNT(*)
----------
38697

SQL> delete from raja.restore_raja where rownum<10000; 9999 rows deleted. SQL> commit;

Commit complete.

SQL> select count(*) from raja.restore_raja;

COUNT(*)
----------
28698

SQL> FLASHBACK TABLE raja.restore_raja TO RESTORE POINT TABLE_RESTORE_RAJA1;

Flashback complete.

SQL> select count(*) from raja.restore_raja;

COUNT(*)
----------
38697

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. 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. 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 DROP RESTORE POINT statement.

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.

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 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 are always preserved. They must be dropped explicitly by the user using the DROP RESTORE 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.
This example shows how to create a guaranteed restore point:
SQL> CREATE RESTORE POINT GUARANTEE FLASHBACK DATABASE;
Restore point created.

Drop restore point

SQL> DROP RESTORE POINT ;

Reference:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_6011.htm

I Hope this article helped to you. Suggestions are welcome.

Regards
Rajabaskar Thangaraj
WWW.dbarajabaskar.blogspot.com