Switchover steps in Oracle RAC Environment

Here are the switchover steps in Oracle RAC environment, which involve transitioning roles between the primary and standby databases. This article assumes both databases are in a Real Application Clusters (RAC) setup and a Data Guard environment already configured.

Our Environment:

Primary: 2 Node RAC
Standby: 2 Node RAC

1. Manually Switchover Method.
2. Switchover using DG Broker.

In this practice, we are going to follow the Manually switchover method step by step.

Step 1. Verify the Log archive Gaps between Primary and Standby

Before switchover it is mandatory to verify the log archive gaps between the Primary and Standby databases using the following commands:

On Primary:

The following command will the the current sequence number for each thread on the primary database.

SQL> SELECT THREAD#, SEQUENCE# FROM V$THREAD;

THREAD#    SEQUENCE#
---------- ----------
1           22
2           31

On Standby:

SQL>ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
SQL>SELECT sequence#, first_time, next_time, applied FROM v$archived_log 
WHERE APPLIED = 'YES'
ORDER BY sequence#;

OR

SQL> SELECT THREAD#, MAX(SEQUENCE#) FROM V$ARCHIVED_LOG
WHERE APPLIED = 'YES' AND RESETLOGS_CHANGE# = (SELECT RESETLOGS_CHANGE# FROM V$DATABASE_INCARNATION WHERE STATUS = 'CURRENT') GROUP BY THREAD#;

Step 2. Very the switchover status on Primary

The below query will help us to find out the switchover status on the Primary Database.

SQL> SELECT  NAME,DB_UNIQUE_NAME,DATABASE_ROLE,SWITCHOVER_STATUS FROM V$DATABASE;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    SWITCHOVER_STATUS
--------- ------------------------------ ---------------- --------------------
OCPTECH    ocptech                         PRIMARY          TO STANDBY

Step 3. Check the Instance status

Before doing the switchover in the Oracle RAC environment, must check the instance status on the primary side as well as the standby side.

Primary Side:

D:\OCP>srvctl status database -d ocptech
Instance ocptech1 is running on node ocptech-pr1
Instance ocptech2 is running on node ocptech-pr2

Standby Side:

D:\OCP>srvctl status database -d ocptechdr
Instance ocptechdr1 is running on node ocptech-dr1
Instance ocptechdr2 is running on node ocptech-dr2

Step 4. Shutdown the secondary instance

As we verify the instance status in step 3, all the instances are running on both sides. Now we have to shutdown the secondary instances on both side (Primary & Standby).

On Primary:

D:\OCP>srvctl stop instance  -d ocptech -i ocptech2 -o immediate

Verify the status now on the primary:

D:\OCP>srvctl status database -d ocptech
Instance ocptech1 is running on node ocptech-pr1
Instance ocptech2 is not running on node ocptech-pr2

On Standby:

D:\OCP>srvctl stop instance  -d ocptechdr -i ocptechdr2 -o immediate

Verify the status now on standby:

D:\OCP>srvctl status database -d ocptechdr
Instance ocptechdr1 is running on node ocptech-dr1
Instance ocptechdr2 is not running on node ocptech-dr2

Step 5. Switchover the Primary Database Role

On Primary - it is time to switchover the database role using the following command:

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

Database altered.

After successful switchover, shut the database and start the database in mount or read-only mode:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area 1.0335E+10 bytes
Fixed Size                 15818216 bytes
Variable Size            3958759680 bytes
Database Buffers        7442450944 bytes
Redo Buffers               17734528 bytes
Database mounted.

SQL> alter database open read only;

Database altered.

Check the primary Database role now:

SQL> SELECT  NAME,DB_UNIQUE_NAME,DATABASE_ROLE,SWITCHOVER_STATUS FROM V$DATABASE;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    SWITCHOVER_STATUS
--------- ------------------------------ ---------------- --------------------
OCPTECH    ocptech                         PHYSICAL STANDBY RECOVERY NEEDED

Step 6. Verify the switchover status on STANDBY

The blow command will help us to get the switchover status on the standby database.

SQL> SELECT  NAME,DB_UNIQUE_NAME,DATABASE_ROLE,SWITCHOVER_STATUS FROM V$DATABASE;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    SWITCHOVER_STATUS
--------- ------------------------------ ---------------- --------------------
OCPTECH    ocptechdr                      PHYSICAL STANDBY TO PRIMARY

Step 7. Switchover the Standby Database Role

As we checked the standby database status shows the switchover status is "TO PRIMARY" which means we can switch the database role now.

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

Database altered.

Now open the new primary database:

SQL> SHUTDOWN IMMEDIATE;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> STARTUP OPEN;
ORACLE instance started.

Total System Global Area 1.0335E+10 bytes
Fixed Size                 14818216 bytes
Variable Size            1544167168 bytes
Database Buffers         8757043456 bytes
Redo Buffers               17734528 bytes
Database mounted.
Database opened.

Check the database role on the new Primary:

SQL> SELECT  NAME,DB_UNIQUE_NAME,DATABASE_ROLE,SWITCHOVER_STATUS FROM V$DATABASE;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    SWITCHOVER_STATUS
--------- ------------------------------ ---------------- --------------------
OCPTECH    ocptechdr                       PRIMARY          RESOLVABLE GAP

Step 8. Start the MRP process on the current standby database

using the following command you can start the MRP process on the new standby side:

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

Database altered.

Let's verify the MRP and RFS process status:

L>  select sequence#,process,status from v$managed_standby;

 SEQUENCE# PROCESS   STATUS
---------- --------- ------------
         0 DGRD      ALLOCATED
         0 ARCH      CONNECTED
         0 DGRD      ALLOCATED
         0 ARCH      CONNECTED
         0 ARCH      CONNECTED
         0 ARCH      CONNECTED
         0 ARCH      CONNECTED
        37 ARCH      CLOSING
         0 ARCH      CONNECTED
        40 ARCH      CLOSING
         0 RFS       IDLE
         0 RFS       IDLE
        38 RFS       IDLE
        57 MRP0      APPLYING_LOG
         0 RFS       IDLE
        57 RFS       IDLE
         0 RFS       IDLE

17 rows selected.

Switchover steps in Oracle RAC Environment