Failover to standby with a delay (until time)

Disaster scenario :

1) you have a primary database
2) you have a standby database
3) you want to be able to failover to the standby database until a given time (ex: within the last 24 hours)

First, you create your primary and standby databases.

If you have dataguard broker, you then set the DELAYMINS to 1440 (=1 day) for the standby database.

DGMGRL> edit database sdb01 set property DelayMins=1440;

If you do not use dg broker, then set the delay in your log_archive_dest_2 parameter

SQL> ALTER SYSTEM SET log_archive_dest_2='service=sdb01','LGWR ASYNC NOAFFIRM delay=1440';


Ok, verify your configuration, with OEM, with show configuration or simply with alter system archive log current.

Wait one day 😉

After one day, you will have a lag between the last retrieved logfile and the last applied redo log entry.

SQL> select applied, max(NEXT_TIME) from v$archived_log group by applied;
APPLIED MAX(NEXT_TIME)
--------- -------------------
NO 2011-11-17_14:06:53
YES 2011-11-17_13:51:46


Obviously I did not wait one day for this test, but I already see a log of 15 minutes.

Now for some obscure reason, your beloved colleague messed up the primary database and you must recover the database until 14:00.

Note that you cannot use the dataguard broker or OEM to do this.

Ok. Shutdown the production.


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


Now cancel the standby process (but do not issue a recover managed standby database finish).


SQL> alter database recover managed standby database cancel;

Database altered.


Now we need to recover the standby until time. Check the syntax twice before you type enter!

On standby

SQL> recover automatic standby database until time '2011-11-17 14:00:00';
Media recovery complete.


On standby, start the database as a primary database

SQL> alter database activate standby database;

Database altered.


Before writing this post I did some research on how to do it with dataguard, but dataguard does not seem to offer point in time failover. Either you do an immediate failover (and you will lose 1440 minutes of data) or you do a complete failover (and you will apply all logs, inclusive the one after 14:00)