Switchover and Failover with TIME Delay

To protect against application corrupted or erroneous data to the standby database, DELAY (minutes) option can be used in LOG_ARCHIVE_DEST_n to delay applying of archived log at standby database. (It does not delay transport of redo data to standby instead time lag begin when redo data is completely archived at standby)

At primary site set init parameter as follows

SQL> alter system set log_archive_dest_2='SERVICE=stdby DELAY=240'; (4 hrs time delay)

Switchover with TIME delay

  • Switchover will not begin until standby database applies all archived log files.

            Lift DELAY by using NODELAY keyword on Physical Standby as

SQL> alter database recover managed standby database nodelay disconnect from
session through last switchover;

  • Users Logoff from Primary and Standby database
  • Switch Primary to Standby

SQL> alter database commit to switchover to physical standby with session shutdown;

SQL> shutdown normal

SQL> startup mount


The above statement does the following:

  1. Closes the primary database, terminating any active sessions
  2. Transmits any unarchived redo log files and applies them to the standby database
  3. Adds an end-of-redo marker to the header of the last log file being archived
  4. Creates a backup of the current control file
  5. Converts the current control file into a standby control file
  • Switch original standby to Primary role

SQL> alter database commit to switchover to primary database;

SQL> shutdown

SQL> startup

Failover with TIME delay

Failover to a physical standby database for which the application of archived redo log files is delayed. By doing so, you transition the standby database to the primary role at a point before the problem occurred, but you will likely incur some data loss

  • On Physical Standby:

SQL> alter database recover managed standby database cancel;

SQL> alter database activate physical standby database;

SQL> shut immediate

SQL> startup

ACTIVATE immediately transitions the standby db to primary role without applying additional redo that might exist at standby site.

NOTE: Potential data loss

  • Then re-create standby from this new primary db

Cancel Delay:

SQL> alter database recover managed standby database nodelay;

NOTE: For real time apply DELAY is ignored


About Raheel Syed

Oracle DBA
This entry was posted in Data Guard and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s