DR Drill : Performing Switchover and Switchback in Oracle DB

Primary Database : DC
Standby Database : DR

Step 1:  Ensure below connectivity is fine before performing DR-Drill.

• DC-DR Sync.
• Network Delay
• tnsping connectivity
• sqlplus connectivity
• PORT is working fine
• Any critical ORA-error in the alert logfile.

Step 2: Capture below data from both Primary and Standby database and ensure that logs are getting applied on standby from primary without any network delay.

Primary: SQL> set lines 300 pages 3000 SQL> select name,db_unique_name,database_role,open_mode,controlfile_type,switchover_status from v$database; NAME DB_UNIQUE_NAME  DATABASE_ROLE OPEN_MODE    CONTROL SWITCHOVER_STATUS ---- --------------- ------------- ------------ ------- ----------------- DC   DC              PRIMARY       READ WRITE   CURRENT SESSIONS ACTIVE SQL> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) --------------         26                                         SQL> col dest_name for a23 SQL> SELECT DEST_ID, DEST_NAME, STATUS, ERROR FROM  V$ARCHIVE_DEST; DEST_ID DEST_NAME        STATUS    ERROR ------- ---------------- --------- ------   1 LOG_ARCHIVE_DEST_1    VALID   2 LOG_ARCHIVE_DEST_2    VALID   3 LOG_ARCHIVE_DEST_3    INACTIVE   4 LOG_ARCHIVE_DEST_4    INACTIVE   5 LOG_ARCHIVE_DEST_5    INACTIVE   6 LOG_ARCHIVE_DEST_6    INACTIVE   7 LOG_ARCHIVE_DEST_7    INACTIVE   8 LOG_ARCHIVE_DEST_8    INACTIVE   9 LOG_ARCHIVE_DEST_9    INACTIVE  10 LOG_ARCHIVE_DEST_10   INACTIVE  11 LOG_ARCHIVE_DEST_11   INACTIVE  12 LOG_ARCHIVE_DEST_12   INACTIVE  13 LOG_ARCHIVE_DEST_13   INACTIVE  14 LOG_ARCHIVE_DEST_14   INACTIVE  15 LOG_ARCHIVE_DEST_15   INACTIVE  16 LOG_ARCHIVE_DEST_16   INACTIVE  17 LOG_ARCHIVE_DEST_17   INACTIVE  18 LOG_ARCHIVE_DEST_18   INACTIVE  19 LOG_ARCHIVE_DEST_19   INACTIVE  20 LOG_ARCHIVE_DEST_20   INACTIVE  21 LOG_ARCHIVE_DEST_21   INACTIVE  22 LOG_ARCHIVE_DEST_22   INACTIVE  23 LOG_ARCHIVE_DEST_23   INACTIVE  24 LOG_ARCHIVE_DEST_24   INACTIVE  25 LOG_ARCHIVE_DEST_25   INACTIVE  26 LOG_ARCHIVE_DEST_26   INACTIVE  27 LOG_ARCHIVE_DEST_27   INACTIVE  28 LOG_ARCHIVE_DEST_28   INACTIVE  29 LOG_ARCHIVE_DEST_29   INACTIVE  30 LOG_ARCHIVE_DEST_30   INACTIVE  31 LOG_ARCHIVE_DEST_31   INACTIVE 31 rows selected.

Standby : SQL> set lines 300 pages 3000 SQL> select name,db_unique_name,database_role,open_mode,controlfile_type,switchover_status from v$database; NAME DB_UNIQUE_NAME DATABASE_ROLE    OPEN_MODE  CONTROL SWITCHOVER_STATUS ---- -------------- ---------------- --------- ----------  ------------------ DC   DR             PHYSICAL STANDBY MOUNTED    STANDBY NOT ALLOWED SQL> select max(sequence#) from v$archived_log where applied='YES'; MAX(SEQUENCE#) --------------         26 SQL> col dest_name for a23 SQL> SELECT DEST_ID, DEST_NAME, STATUS, ERROR FROM  V$ARCHIVE_DEST; DEST_ID DEST_NAME           STATUS    ERROR ------- ----------------    --------- ----------   1 LOG_ARCHIVE_DEST_1       VALID   2 LOG_ARCHIVE_DEST_2       VALID   3 LOG_ARCHIVE_DEST_3       INACTIVE   4 LOG_ARCHIVE_DEST_4       INACTIVE   5 LOG_ARCHIVE_DEST_5       INACTIVE   6 LOG_ARCHIVE_DEST_6       INACTIVE   7 LOG_ARCHIVE_DEST_7       INACTIVE   8 LOG_ARCHIVE_DEST_8       INACTIVE   9 LOG_ARCHIVE_DEST_9       INACTIVE  10 LOG_ARCHIVE_DEST_10      INACTIVE  11 LOG_ARCHIVE_DEST_11      INACTIVE  12 LOG_ARCHIVE_DEST_12      INACTIVE  13 LOG_ARCHIVE_DEST_13      INACTIVE  14 LOG_ARCHIVE_DEST_14      INACTIVE  15 LOG_ARCHIVE_DEST_15      INACTIVE  16 LOG_ARCHIVE_DEST_16      INACTIVE  17 LOG_ARCHIVE_DEST_17      INACTIVE  18 LOG_ARCHIVE_DEST_18      INACTIVE  19 LOG_ARCHIVE_DEST_19      INACTIVE  20 LOG_ARCHIVE_DEST_20      INACTIVE  21 LOG_ARCHIVE_DEST_21      INACTIVE  22 LOG_ARCHIVE_DEST_22      INACTIVE  23 LOG_ARCHIVE_DEST_23      INACTIVE  24 LOG_ARCHIVE_DEST_24      INACTIVE  25 LOG_ARCHIVE_DEST_25      INACTIVE  26 LOG_ARCHIVE_DEST_26      INACTIVE  27 LOG_ARCHIVE_DEST_27      INACTIVE  28 LOG_ARCHIVE_DEST_28      INACTIVE  29 LOG_ARCHIVE_DEST_29      INACTIVE  30 LOG_ARCHIVE_DEST_30      INACTIVE  31 LOG_ARCHIVE_DEST_31      INACTIVE  32 STANDBY_ARCHIVE_DEST     VALID 32 rows selected.

Step 3: Confirm with an Application Developer whether he has stopped all Application Services connecting to the respective database. Also check any remote connection to the database from non-oracle users.

SQL> select username,status,event from v$session where username is not null group by username,status,event order by 1;

Step 4: Now initiate switchover activity.

Switchover Initiation

Primary Database: SQL> alter database commit to switchover to standby with session shutdown nowait; Database altered. SQL> shutdown immediate; ORA-01012: not logged on SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options -bash-4.1$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 23 16:33:24 2017 Copyright (c) 1982, 2013, Oracle.  All rights reserved. Connected to an idle instance. SQL> startup nomount; ORACLE instance started. Total System Global Area  784998400 bytes Fixed Size                2257352 bytes Variable Size           515903032 bytes Database Buffers        264241152 bytes Redo Buffers              2596864 bytes SQL> alter database mount standby database; Database altered. SQL> alter database recover managed standby database disconnect from session; Database altered. SQL> @rd NAME  DB_UNIQUE_NAME   DATABASE_ROLE    OPEN_MODE   CONTROL  SWITCHOVER_STATUS ---- ---------------- ----------------- ---------- --------- -------------- DC    DC               PHYSICAL STANDBY MOUNTED     STANDBY  TO PRIMARY MAX(SEQUENCE#) --------------      27 Primary Database Alert Logfile: SQL> alter database commit to switchover to standby with session shutdown nowait; Fri Jun 23 16:32:45 2017 alter database commit to switchover to standby with session shutdown nowait ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 5304] (DC) Switchover End-Of-Redo Log thread 1 sequence 27 has been fixed Switchover: Primary highest seen SCN set to 0x0.0xf5685 ARCH: Noswitch archival of thread 1, sequence 27 ARCH: End-Of-Redo Branch archival of thread 1 sequence 27 ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2 Archived Log entry 38 added for thread 1 sequence 27 ID 0x6306e1d7 dest 1: ARCH: Archiving is disabled due to current logfile archival Primary will check for some target standby to have received alls redo Final check for a synchronized target standby. Check will be made once. LOG_ARCHIVE_DEST_2 is a potential Physical Standby switchover target Active, synchronized target has been identified Target has also received all redo Backup controlfile written to trace file /home/oracle/u01/diag/rdbms/dc/DC/trace/DC_ora_5304.trc Clearing standby activation ID 1661395415 (0x6306e1d7) The primary database controlfile was created using the 'MAXLOGFILES 16' clause. There is space for up to 13 standby redo logfiles Use the following SQL commands on the standby database to create standby redo logfiles that match the primary database: ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800; ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800; ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800; ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800; Archivelog for thread 1 sequence 27 required for standby recovery Switchover: Primary controlfile converted to standby controlfile succesfully. Switchover: Complete - Database shutdown required USER (ospid: 5304): terminating the instance Instance terminated by USER, pid = 5304 Completed: alter database commit to switchover to standby with session shutdown nowait Shutting down instance (abort)

Standby Database: SQL> @rd NAME  DB_UNIQUE_NAME   DATABASE_ROLE    OPEN_MODE   CONTROL  SWITCHOVER_STATUS ---- ---------------- ----------------- ---------- --------- -------------- DC    DR               PHYSICAL STANDBY MOUNTED     STANDBY  TO PRIMARY MAX(SEQUENCE#) --------------      27 SQL> alter database recover managed standby database cancel; Database altered. SQL> alter database commit to switchover to primary with session shutdown nowait; Database altered. SQL> shut immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup; ORACLE instance started. Total System Global Area  784998400 bytes Fixed Size                        2257352 bytes Variable Size                 515903032 bytes Database Buffers          264241152 bytes Redo Buffers                   2596864 bytes Database mounted. Database opened. SQL> @rd NAME  DB_UNIQUE_NAME   DATABASE_ROLE    OPEN_MODE   CONTROL  SWITCHOVER_STATUS ---- ---------------- ----------------- ---------- --------- -------------- DC    DR               PRIMARY  READ WRITE CURRENT  RESOLVABLE GAP MAX(SEQUENCE#) --------------      27 Standby Database Alert Logfile: SQL> alter database commit to switchover to primary with session shutdown nowait; Fri Jun 23 16:38:23 2017 alter database commit to switchover to primary with session shutdown nowait ALTER DATABASE SWITCHOVER TO PRIMARY (DC) No wait specified for role transition. Backup controlfile written to trace file /home/oracle/u01/diag/rdbms/dr/DC/trace/DC_ora_5329.trc SwitchOver after complete recovery through change 1005189 Online logfile pre-clearing operation disabled by switchover Online log /home/oracle/u01/fast_recovery_area/DR/onlinelog/o1_mf_1_dnq8nzon_.log: Thread 1 Group 1 was previously cleared Online log /home/oracle/u01/fast_recovery_area/DR/onlinelog/o1_mf_2_dnq8o01m_.log: Thread 1 Group 2 was previously cleared Online log /home/oracle/u01/fast_recovery_area/DR/onlinelog/o1_mf_3_dnq8o0dc_.log: Thread 1 Group 3 was previously cleared Standby became primary SCN: 1005187 Switchover: Complete - Database mounted as primary Completed: alter database commit to switchover to primary with session shutdown nowait Fri Jun 23 16:38:29 2017

At this stage, we can say that switchover activity is completed successfully and now Primary database is new standby and standby database is new Primary.

New Primary: DR
New Standby : DC

Step 5: Verify everything is running fine without any issue. Also switch 3-4 logs from newly created primary database to standby database. Check with an Application Developer whether they are facing any issue while connecting Application and any other issue with newly converted primary database.

Note: Directly do not initiate switchback activity. First check with an Application Developer regarding this. Once he confirms then you can proceed. They can run their application for few days or few weeks.

Switchback Initiation

New Primary Database(DR): SQL> set lines 300 pages 3000 SQL> select name,db_unique_name,database_role,open_mode,controlfile_type,switchover_status from v$database; NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE   CONTROL SWITCHOVER_STATUS ---- -------------- ------------- ----------- ------- ----------------- DC   DR             PRIMARY       READ WRITE  CURRENT TO STANDBY MAX(SEQUENCE#) --------------         37 SQL> alter database commit to switchover to standby with session shutdown nowait; Database altered. SQL> shut immediate; ORA-01012: not logged on SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options -bash-4.1$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 23 17:17:18 2017 Copyright (c) 1982, 2013, Oracle.  All rights reserved. Connected to an idle instance. SQL> startup nomount; ORACLE instance started. Total System Global Area  784998400 bytes Fixed Size                        2257352 bytes Variable Size                 515903032 bytes Database Buffers          264241152 bytes Redo Buffers                   2596864 bytes SQL> alter database mount standby database; Database altered. SQL> alter database recover managed standby database disconnect from session; Database altered. SQL> @rd NAME DB_UNIQUE_NAME DATABASE_ROLE  OPEN_MODE   CONTROL SWITCHOVER_STATUS ---- -------------- ----------------- ------- ------- ----------------- DC   DR             PHYSICAL STANDBY    READ WRITE  STANDBY TO PRIMARY MAX(SEQUENCE#) --------------         38 Primary Database Alert Logfile: SQL> alter database commit to switchover to standby with session shutdown nowait; Fri Jun 23 17:16:27 2017 alter database commit to switchover to standby with session shutdown nowait ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 5953] (DC) Switchover End-Of-Redo Log thread 1 sequence 38 has been fixed Switchover: Primary highest seen SCN set to 0x0.0xfb01c ARCH: Noswitch archival of thread 1, sequence 38 ARCH: End-Of-Redo Branch archival of thread 1 sequence 38 ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2 Archived Log entry 35 added for thread 1 sequence 38 ID 0x630891dc dest 1: ARCH: Archiving is disabled due to current logfile archival Primary will check for some target standby to have received alls redo Final check for a synchronized target standby. Check will be made once. LOG_ARCHIVE_DEST_2 is a potential Physical Standby switchover target Active, synchronized target has been identified Target has also received all redo Backup controlfile written to trace file /home/oracle/u01/diag/rdbms/dr/DC/trace/DC_ora_5953.trc Clearing standby activation ID 1661506012 (0x630891dc) The primary database controlfile was created using the 'MAXLOGFILES 16' clause. There is space for up to 13 standby redo logfiles Use the following SQL commands on the standby database to create standby redo logfiles that match the primary database: ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800; ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800; ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800; ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800; Archivelog for thread 1 sequence 38 required for standby recovery Switchover: Primary controlfile converted to standby controlfile succesfully. Switchover: Complete - Database shutdown required USER (ospid: 5953): terminating the instance Instance terminated by USER, pid = 5953 Completed: alter database commit to switchover to standby with session shutdown nowait Shutting down instance (abort) License high water mark = 3

New Standby Database(DR): SQL> @rd NAME DB_UNIQUE_NAME  DATABASE_ROLE    OPEN_MODE   CONTROL  SWITCHOVER_STATUS ---- --------------- ---------------- ---------- -------- ----------------- DC   DC              PHYSICAL STANDBY  MOUNTED     STANDBY  TO PRIMARY   MAX(SEQUENCE#) --------------       38 SQL> alter database recover managed standby database cancel; Database altered. SQL> alter database commit to switchover to primary with session shutdown nowait; Database altered. SQL> shut immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup; ORACLE instance started. Total System Global Area  784998400 bytes Fixed Size                2257352 bytes Variable Size           515903032 bytes Database Buffers        264241152 bytes Redo Buffers              2596864 bytes Database mounted. Database opened. SQL> @rd NAME   DB_UNIQUE_NAME   DATABASE_ROLE   OPEN_MODE   CONTROL SWITCHOVER_STATUS ----- ---------------  --------------- ----------- -------- ------------------ DC     DC               PRIMARY        READ WRITE  CURRENT  RESOLVABLE GAP     MAX(SEQUENCE#) --------------        38 New-Standby Database Alert Logfile: SQL> alter database commit to switchover to primary with session shutdown nowait; Fri Jun 23 17:20:16 2017 alter database commit to switchover to primary with session shutdown nowait ALTER DATABASE SWITCHOVER TO PRIMARY (DC) No wait specified for role transition. Backup controlfile written to trace file /home/oracle/u01/diag/rdbms/dc/DC/trace/DC_ora_5807.trc SwitchOver after complete recovery through change 1028124 Online logfile pre-clearing operation disabled by switchover Online log /home/oracle/datafile/DC/redo01.log: Thread 1 Group 1 was previously cleared Online log /home/oracle/datafile/DC/redo02.log: Thread 1 Group 2 was previously cleared Online log /home/oracle/datafile/DC/redo03.log: Thread 1 Group 3 was previously cleared Standby became primary SCN: 1028122 Switchover: Complete - Database mounted as primary Completed: alter database commit to switchover to primary with session shutdown nowait

You have successfully converted back the standby database to primary and vice-versa.


DR Drill has been completed without any issue.


Step 6: If everything is running fine then you can handover it to an Application Developer. Also check with him for any DB level issue.

Thanks for reading this post ! Please comment if you like this post !

Refer: Switchover in Oracle by db_unique_name using single command