Friday, August 15, 2008

Open a Oracle Dataguard physical standby read-only

#The following process can be used to open a physical Dataguard standby read-only to run reports or check standby database status.
#The only difference after we change from ARCH to LGWR transport is that we can recover from the standby redo log in near real-time rather than from the archivelog and this is detailed in step 6.

1. On the primary database defer log shipping (be careful that you defer the correct destination)
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;

2. On the standby enable cancel recovery
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

3. Open the standby database read-only
ALTER DATABASE OPEN;

4. Run select statements on opened standby database.

5. Put database back in mount state (this is optional, but is best practice and if not done can cause problems with switchover in the future)
STARTUP MOUNT FORCE;

6. Restart recovery on standby database.
# If the database has standby redo logs :-
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
# If the database does not have standby redo logs :-
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

7. on the primary database re-enable log shipping that was deferred in step 1
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

8. switch logs on the primary database (this is optional but will force the standby FAL server to get missing archivelogs ASAP)
alter system switch logfile;