The Power of Oracle Standby Database with a Time Lag!!

 Author: Kim Roger

When the standby database performs a managed recovery then it automatically applies redo logs when they arrive from the primary database. However in certain scenarios we do want logs to be applied immediately. In such cases we can create a time lag between the redo log archiving at the primary site and the application of the log at standby site.

One potential benefit of adding time lag is that the transfer of erroneous or corrupted data from primary to standby site can be protected. One practical example of such scenario can be where every night a batch job is run on your primary database. Consider a scenario where the batch job is by mistake run twice and you become aware of it after your batch job is completed for the second time.

The ideal solution to this problem is to roll back the database to the point in time before the batch job began. Recovery can be made easy if your primary database has a standby database with a time lag. In such case you just need to fail over the standby database with the time lag and use it as your new primary database.

Creating Oracle Standby Database with a Time Lag:

You can create a standby database with a time lag by using the DELAY attribute of the LOG_ARCHIVE_DEST_n initialization parameter. The LOG_ARCHIVE_DEST_n initialization parameter is in the primary database initialization parameter file.

DELAY Attribute:

The DELAY attribute expresses time in minutes. By default the DELAY attribute is set to NODELAY. If DELAY attribute is specified without any value then default delay interval is taken as 30 minutes.

The DELAY attribute is used to indicate that the archived redo logs at the standby site will not be available for recovery until the time set for DELAY attribute has been expired.

The time interval is started when archived redo logs are transferred successfully to the standby site. The redo information will still be sent to the standby database and it will be written to the disk as normal without any delay.

For 1 hour delay you will write DELAY=60, similarly for 5 hour delay you will write DELAY=300.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=stdby_srvc DELAY=60';

Performing Switchover for Standby running in Time Lag:

If your standby database is running in a time lag then you will be following below steps in order to perform the switchover.

Startup a database instance (Current Primary):

SQL> STARTUP FORCE;

Issue Switchover Command (Current Primary):

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;

Shutdown (Current Primary):

SQL> SHUTDOWN IMMEDIATE;

Start database needs as a physical standby database (Current Primary):

SQL> STARTUP NOMOUNT;
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

Apply all archived redo logs (Current Standby):

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION THROUGH LAST SWITCHOVER;

Issue Switchover Command (Current Standby):

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

Shutdown (Current Standby):

SQL> SHUTDOWN IMMEDIATE;

Open Database (Current Standby):

SQL> STARTUP MOUNT;
SQL> ALTER DATABASE OPEN;

Recover Former Primary (Current Standby):

SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT; Read Again!!

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Beware of Oracle LOG_ARCHIVE_MAX_PROCESSES Parameter!!
   Efficient Oracle Migration, Amazing Tricks!
   Follow the Best Practices for using Oracle TKPROF!!
   Great Tips for Oracle Wrap Utility!!