The Power of Oracle Stdby Database

The Power of Oracle Standby Database with a Time Lag!!
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;