Thursday, November 14, 2013

Guaranteed restore points in standby databases

"Before an important release, I decided to create a guaranteed restore point on the primary database.
Unfortunately I forgot to create the same on my standby database. Can I use the as of timestamp to create a guaranteed restore point in the past?"

No, this is not supported.

The syntax below:
SQL> create restore point PRE_RELEASE2_STBY as of timestamp to_date('09.11.2013 09:15:00','DD.MM.YYYY HH24:MI:SS') guarantee flashback database;

Will fail with:
ERROR at line 1:
ORA-38864: cannot create a guaranteed restore point with user specified SCN or
time.

// *Cause:  An SCN or timestamp was specified when creating a guaranteed
//          restore point. This is not supported. You can only create a
//          guaranteed restore point as of the current time.
// *Action: Avoid this combination of options for creating a restore point.

You will have to do with a normal restore point instead:
CREATE RESTORE POINT PRE_RELEASE2_STBY 
AS OF TIMESTAMP TO_DATE('09.11.2013 09:15:00','DD.MM.YYYY HH24:MI:SS');
But you will receive the following error:

ERROR at line 1:
ORA-38867: database not open: cannot create restore point with the specified
timestamp

// *Cause:  An attempt was made to create a restore point with the specified
//          timestamp while the database was not open. The database must be
//          open in order to map the timestamp to an SCN.
// *Action: Open the database first or pick another way to create the restore 
//          point.
So on a standby database, using the AS OF TIMESTAMP is not allowed on a normal restore point, either.
How about using the SCN?

SQL> SELECT timestamp_to_scn(to_date('09.11.2013 09:15:00','DD.MM.YYYY HH24:MI:SS')) SCN 
     FROM   dual;

     SCN
--------
170259165
SQL> create restore point PRE_RELEASE2_STBY AS OF SCN 170259165;
Restore point created.

No comments:

Post a Comment