Switchover Testing from Primary database to Physical Standby database.

Switchover from Primary database to Physical Standby database

Here today we discuss how the switchover will be performed between Primary and Physical Standby database. During a Switchover, The primary database transition to the standby role and standby database transition to the primary role. Usually This activity will come into picture when there is a planned Maintenance on the primary server like OS Patching, upgradation of the hardware 

(Hardware Changes) or DR Testing.

From my experience i did this activity as part of the DR testing. I worked in a environment where we have Datacenters in Singapore and Hongkong. 

Singapore  -- Primary Site

Hongkong  -- Standby Site ( DR Site )

I setup the production database and configured the dataguard. Before Go-Live of the project we have to make sure the switchover and failover must work well. Failover scenarios will discuss in my next topic. 

Switchover :

This procedure is non-destructive, Guarantee no loss of data and can be performed in reverse when the primary site becomes available, without having to rebuild either database. A switchover is initiated on the primary database and is completed on the target standby database.

I have just briefed in the below steps

1) Application team has to shut down the application and make sure there are no active sessions connected to the database.

2) Comment the crontab jobs & reschedule the jobs in OEM if any during the scheduled period. 

3) Once the application is brought down. Verify whether the primary database can be switched over to the standby role or not by executing the below.

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
---------------------------------
TO STANDBY

If there are any still any active sessions then the above query returns SESSIONS ACTIVE. We can ignore safely as we are performing the switchover with session shutdown option. So, it will rollback the active session automatically.

In the primary database initiate the switchover

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

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> SELECT OPEN_MODE, DATABASE_ROLE FROM V$DATABASE;

OPEN_MODE  DATABASE_ROLE
---------- ----------------
MOUNTED    PHYSICAL STANDBY

Now the primary database has been switched to physical standby database. Now proceed with switching the standby database to primary as below.

In the standby database, Verify whether it Can Be Switched to the Primary Role.

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

---------------------------------

TO PRIMARY

On Standby Database, Switch Standby Database Role to the Primary Role

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

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP;

SQL> SELECT OPEN_MODE, DATABASE_ROLE FROM V$DATABASE;


OPEN_MODE  DATABASE_ROLE
---------- ----------------
READ WRITE PRIMARY

Now start the listener on the earlier primary database. 

Start the MRP Process on the current standby database.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Post Steps

1. Do a log switch on the current primary database and check whether the log is applying on the current standby database.
2. Provide the new tns details to the application team and ask them to test the application connectivity.
3. Once the planned Maintenance or DR testing is completed. Revert back using the same steps above.

Switchover in Primary RAC and Standby RAC servers

The process will be the same for both the RAC Dataguard configuration or Standalone Dataguard Configuration except the below step.

• Before performing a switchover to a RAC primary/standby shut down all but one primary/standby instance (they can be restarted after the role transition has completed).