Sunday, February 22, 2009

Oracle: Restore points

Restore point is nothing but a name given to a specified point of a database status.It can define the status of a database on a specific time or SCN number.So, that when we flashback database , we can mention the name of the restore point in stead of a specific date and time or a SCN number .

Oracle will automatically flashback the database to that specific restore point. It is a mapping of SCN number to a restore point name. So, when we flashback database to a restore point we do, nothing but restoring to a specific SCN.

There are two different type of restore point:

a) Normal restore point or nonguaranteed restore point
b) Guaranteed restore point

a) To create Normal restore point do the following:

sql> create restore point ershad;
[I have given the restore point name as "ershad"]

So, when required , we can flashback the database upto this restore point.But, CONDITIONS APPLY....

whether database can be flashbacked upto the restore point depends on whether you have the flashback logs in the Flash Recovery Area or not.If you have, you can restore upto that point.But , it is not Guaranteed every, you may face the following error while trying to flashback to a normal restore point.

ORA-38729: Not enough flashback database log data to do FLASHBACK

This error means the database does not have the flashback logs needed to go back to the restore point or time or SCN that you’ve specified

***Check db_flashback_retention_target for more on flashback logs retention period.***

b) Guaranteed restore point:
This will guarantee that at any point in time you can flashback the database to the restore point. As , Oracle will make sure the flashback logs will not be deleted until that restore point , whatever the case is.

sql> create restore point askershad guarantee flashback database;
[I have given the restore point name as "askershad"]

Note: As Guaranteed restore point assures flashback logs will not be deleted.So Be Careful about the space consumed in Flash recovery area.And create this only when you , you need to check your database behaviour with a new application and you are going to revert back after the test. And please DROP the restore point after your job is done.Because,for a Guaranteed restore point, the associated flashback logs will never be deleted until you drop the restore point. And not doing so may end up no space in flash recovery area and cause a recovery writer(RVWR) process failure which leads to database instance failure......


sql> select * from v$restore_point;

------- -------------------------------- ------ ------------------- ----------- -------
916841 --------11----------------------YES-----3981312----------22-FEB-09-- ASKERSHAD
916831-------- 11---------------------- NO-----------0-----------22-FEB-09---ERSHAD

SCN--> To which SCN the restore point is mapped
DATABASE_INCARNATION#-->This column displays the incarnation of the database
when this restore point was created
GUARANTEE_FLASHBACK_DATABASE-->whether it is guaranteed or normal restore point
STORAGE_SIZE-->What is the flashback log size***
To what TIME the restore point is mapped
NAME-->Name of the restored point

***look here , The normal restored point does not have a size mentioned. As it requires the flashback logs from the point of SCN number. If logs are not there,it is not possible to flashback. The guaranteed one will keep the logs so, the size of logs belong to it and assures the flashback.


SQL> drop restore point ershad;(for both types the command is same)

More Topic:


Have a nice day..........


Anonymous said...

thank you boss!!

sukanta said...

thanks for this usefull topic

Wasimul Ratul said...

you've just helped me checking some references. Great work. Keep it up.
Wasimul, Oracle ACS.