Switchover to Standby

• Introduction: In Oracle, a switchover is a role transition operation between the primary and standby databases in a Data Guard environment. A switchover allows you to switch roles between the primary and standby databases in a planned manner, without data loss. This is typically done for maintenance, testing, or load balancing.
  
  
• Prerequisites:
o Downtime required at Database end for 30 mins.
o Application and Database connectivity for Standby site.
o Primary and Standby databases should be in sync.


• Environment:
  

Server	Primary	Standby
Hostname	Source	Target
IP	192.168.80.51	192.168.80.111
OS	OEL 9	OEL 9
SID	ORCLDC	ORCLDR
Service Name	ORCLDC	ORCLDR



• Now start Switchover activity step by step:
  
  

1. Check DB status and sync between Primary and Standby
At Primary:

SQL> select NAME,DB_UNIQUE_NAME,DATABASE_ROLE,OPEN_MODE,SWITCHOVER_STATUS from v$database; NAME     DB_UNIQUE_NAME     DATABASE_ROLE     OPEN_MODE     SWITCHOVER_STATUS ---------    ------------------------------    ----------------    --------------------    -------------------- ORCL     ORCLDC                   PRIMARY                        READ WRITE                        TO STANDBY SQL> set lines 200 pages 300 SQL> alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS'; SQL> select d.db_unique_name, d.database_role, a.thread#, b.last_seq, a.applied_seq, a.last_app_timestamp, b.last_seq - a.applied_seq ARC_DIFF FROM (select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp from gv$archived_log where applied='YES' group by thread#) a, (select thread#, MAX(sequence#) last_seq from gv$archived_log group by thread#) b, (select db_unique_name, database_role from v$database) d where a.thread#=b.thread#; SQL> Session altered. SQL>DB_UNIQUE_NAME       DATABASE_ROLE        THREAD#    LAST_SEQ  APPLIED_SEQ  LAST_APP_TIMESTAMP      ARC_DIFF ------------------------------ ---------------- ---------- ---------- ----------- -------------------- ---------- ORCLDC        PRIMARY            1            103    103        08-FEB-2025 20:24:32        0

At Standby:

SQL> select NAME,DB_UNIQUE_NAME,DATABASE_ROLE,OPEN_MODE,SWITCHOVER_STATUS from v$database; NAME     DB_UNIQUE_NAME     DATABASE_ROLE     OPEN_MODE     SWITCHOVER_STATUS --------- ------------------------------ ---------------- -------------------- -------------------- ORCL       ORCLDR               PHYSICAL STANDBY    READ ONLY WITH APPLY    NOT ALLOWED SQL> set lines 200 pages 300 SQL> alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS'; SQL> select d.db_unique_name, d.database_role, a.thread#, b.last_seq, a.applied_seq, b.last_seq - a.applied_seq ARC_DIFF, a.last_app_timestamp, round((sysdate - a.last_app_timestamp) * 24 * 60, 2) Gap_in_Mins, round((sysdate - a.last_app_timestamp) * 24 * 60 * 60, 2) Gap_in_Seconds FROM (select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp from gv$archived_log where REGISTRAR='RFS' and applied='YES' group by thread#) a, (select thread#, MAX(sequence#) last_seq from gv$archived_log group by thread#) b, (select db_unique_name, database_role from v$database) d where a.thread#=b.thread#; SQL> Session altered. SQL>DB_UNIQUE_NAME                    DATABASE_ROLE        THREAD#    LAST_SEQ  APPLIED_SEQ  ARC_DIFF  LAST_APP_TIMESTAMP      GAP_IN_MINS  GAP_IN_SECONDS ------------------------------ ---------------- ---------- ---------- ----------- ---------- -------------------- ----------- -------------- ORCLDR                    PHYSICAL STANDBY        1        103       103       0       08-FEB-2025 20:24:32         12.85          771

2. Now run verify command on Primary to check switchover status

SQL> alter database switchover to orcldr verify; Database altered.

If no error is showing then proceed for Switchover.
If you receive any warning message like below then check DB alert log and take action accordingly.

SQL> alter database switchover to orcldr verify; alter database switchover to orcldr verify * ERROR at line 1: ORA-16475: succeeded with warnings, check alert log for more details

3. Switchover to Standby. Monitor DB alert log for any issue

SQL> alter database switchover to orcldr; Database altered.

4. Now start new Standby (orcldc) in mount state and new Primary (orcldr) in read/write state
At new Primary:

SQL> startup ORACLE instance started. Total System Global Area 1459616616 bytes Fixed Size 9177960 bytes Variable Size 905969664 bytes Database Buffers 536870912 bytes Redo Buffers 7598080 bytes Database mounted. Database opened. SQL> select NAME,DB_UNIQUE_NAME,DATABASE_ROLE,OPEN_MODE,LOG_MODE from v$database; NAME       DB_UNIQUE_NAME    DATABASE_ROLE     OPEN_MODE              LOG_MODE ---------     ----------------     ----------------     --------------------     ------------ ORCL      ORCLDR     PRIMARY    READ   WRITE            ARCHIVELOG

At new Standby:

SQL> startup mount; ORACLE instance started. Total System Global Area 1459616616 bytes Fixed Size 9177960 bytes Variable Size 1157627904 bytes Database Buffers 285212672 bytes Redo Buffers 7598080 bytes Database mounted. SQL> select NAME,DB_UNIQUE_NAME,DATABASE_ROLE,OPEN_MODE,LOG_MODE from v$database; NAME       DB_UNIQUE_NAME    DATABASE_ROLE     OPEN_MODE              LOG_MODE ---------     ----------------     ----------------     --------------------     ------------ ORCL      ORCLDC    PHYSICAL STANDBY    MOUNTED            ARCHIVELOG SQL> alter database recover managed standby database disconnect from session; Database altered.

5. Now do some log switches on new Primary and check the sync.

SQL> alter system switch logfile; System altered. SQL> / System altered.

Similarly you can do switchback activity. Just change the service name from ORCLDR  to ORCLDC while running switchover command.

Thanks for visiting!!