Create restore points for specific SCN and one that preserve in 11g

Restore points were introduced in 10g as part of amazing new flashback technology. You can check my earlier post about them here if you want to know more about them first hand.

In 11g there are two minor tweaks in the concept. Normally when you create restore point, it is created on current SCN. Now you can customize this behavior. You can create restore point to some earlier SCN. The only rule is here that SCN should exist in first place.

For example, issue the following command to know the current SCN of database.

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

    1017119

Suppose we want to create restore point on 1017000 instead of current one. Following command will do that.

SQL> create restore point restore1 as of scn 1017000;

Restore point created.

You can also use time-stamp instead of SCN.

SQL> create restore point restore2 as of timestamp to_date('10-Feb-2013');

Restore point created.

The second concept is of perseverance. Oracle has a retention policy for keeping restore points and once that retention point is reached, the restore points created earlier are deleted in order of their creation. Now you can control that as well. Suppose you want to create a restore point and also want it to be preserved until and unless you drop it yourself, you can use the following command.

SQL> create restore point restore3 preserve;

Restore point created.

You can check which restore points are preserved and which are not by using the following query.

SQL> select name,preserved from v$restore_point;

NAME                           PRE

------------------------------ ---

RESTORE3                       YES

RESTORE1                       NO

RESTORE2                       NO