Switchover to Standby using Oracle Data Guard

• Steps to verify databases are prepared for Graceful Switchover.

In order to switch the primary and standby database roles, (Graceful Switchover) it is necessary to make sure that all the changes from the current primary are shipped and applied to the current standby prior to switchover.

SQL> select process, client_process, status, thread#, sequence#

from v$managed_standby;

PROCESS   CLIENT_P STATUS          THREAD#  SEQUENCE#
--------- -------- ------------ ---------- ----------
ARCH      ARCH     CLOSING               1      24698
ARCH      ARCH     CLOSING               1      24695
ARCH      ARCH     CLOSING               1      24696
ARCH      ARCH     CLOSING               1      24697
LNS       LNS      WRITING               1      24699
LNS       LNS      WRITING               1      24699

6 rows selected.

The query above is from the primary database and shows that there are 2 LNS processes currently writing log sequence 24699.

Verify, by using archive log list command, that this is the current redo log sequence.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /mnt/prdb/archive/local
Oldest online log sequence     24695
Next log sequence to archive   24699
Current log sequence           24699

In this case the LNS processes are working on the current log sequence.

From the point of view of the standby database the RFS process is receiving a log, (sequence 24699) from LGWR and the MRP0 process is applying it.

SQL> select process, client_process, status, thread#, sequence#

from v$managed_standby;

PROCESS   CLIENT_P STATUS          THREAD#  SEQUENCE#
--------- -------- ------------ ---------- ----------
ARCH      ARCH     CONNECTED             0          0
ARCH      ARCH     CONNECTED             0          0
MRP0      N/A      WAIT_FOR_LOG          1      24699
RFS       LGWR     IDLE                  1      24699
RFS       UNKNOWN  IDLE                  0          0
RFS       UNKNOWN  IDLE                  0          0
RFS       UNKNOWN  IDLE                  0          0

7 rows selected.

Once you have verified that the primary and standby databases are 
working on the same log sequence then you can proceed with the 
switchover.

• Steps to Perform Graceful Switchover.

The following steps make sure that no new changes are being made to the current primary database and only the sys user is logged in.

1.  sqlplus@prdb> SHUTDOWN IMMEDIATE
2.  sqlplus@prdb> STARTUP RESTRICT

Step 3 prepares the changes to the controlfile so the primary can become the standby. Additionally, an end of redo marker is placed in the redo log file. This is for synchronization with the standby.

3.  sqlplus> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;

Step 4 prepares the changes to the controlfile so the standby can become the primary. Additionally, it applies all changes in the archives and standby redo logs until it reaches the end of redo marker that was placed there by the primary. At this point the two databases are synchronized.

4.  sqlplus@stby> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Step 5 and 6 restarts the primary database. At this point the changes made to the controlfile in step 3 are implemented and the database is now the standby.

5.  sqlplus@prdb> SHUTDOWN IMMEDIATE

6.  sqlplus@prdb> STARTUP MOUNT

Step 7 starts the remote file server (RFS) to receive redo from the new primary and also starts the managed recover process (MRP0) to start applying the changes to the database. The clause, 'using current logfile' allows real time apply to be activated using the standby redo logs (SRLs must exist on new standby).

7.  sqlplus@prdb> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE using current logfile DISCONNECT;

Step 8 restarts the former standby database, at this point the changes made to the controlfile in step 4 are implemented and the database is now the new primary.

8.  sqlplus@stby> ALTER DATABASE OPEN;

*** SBLBAYDR is now the new PRIMARY ***