Manual Swithover of Oracle 19c Dataguard - Using new switchover statement

In this article we will cover how to perform Switchover of dataguard 19c using new switchover statement .

Before starting Manual Switchover we below mentioned are prerequisite which must meet

=> Ensure there is NO REDO GAP on Standby side
=> Ensure Log_archive_dest_2 is properly set on Both sides
=> Ensure Log_Archive_Dest_state_2 also set on Both Sides.
=> Ensure Standby Redo logs are present on both sides.
=> Ensure there are TEMPFILES available on Standby Side.
=> Ensure there are ONLINE REDO Groups also present on Standby Side.
=> Ensure to remove DELAY parameter from Both Side for fast switchover.

Starting from 12c there is new command  to test and switchover Primary to standby and this have to be run on Primary database.

ALTER DATABASE SWITCHOVER (DB_UNIQUE_NAME of Standby_Database) TO VERIFY;
Above command will verify is all is okay to proceed to switch over.It will not switchover actually.

ALTER DATABASE SWITCHOVER TO (DB_UNIQUE_NAME of Standby_Database);
Above command will  proceed to switch over from Primary to Standby.

Switchover Steps on Oracle Dataguard 19c

We will follow below steps to perform successfull Switchover
Step 1=> Check Primary database status

SQL> select name,open_mode,database_role,PROTECTION_MODE ,switchover_status from gv$database;

NAME      OPEN_MODE           DATABASE_ROLE    PROTECTION_MODE      SWITCHOVER_STATUS
--------- -------------------- ---------------- -------------------- --------------------
DBSET      READ WRITE           PRIMARY        MAXIMUM PERFORMANCE  TO STANDBY
DBSET      READ WRITE           PRIMARY        MAXIMUM PERFORMANCE  TO STANDBY

SQL> sho parameter db_uniq
NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_unique_name			     string	 DBSET
SQL> 

Step 2=> Check Log_archive_dest on Primary Side

SQL> show parameter log_archive_Dest

log_archive_dest_2             string     SERVICE=TNSTAR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DBTAR

log_archive_dest_state_2         string     ENABLE

Step 3=> Check Standby database status

SQL> select name,open_mode,database_role,PROTECTION_MODE ,switchover_status from gv$database;

NAME      OPEN_MODE           DATABASE_ROLE    PROTECTION_MODE      SWITCHOVER_STATUS
--------- -------------------- ---------------- -------------------- --------------------
DBSET      READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE  NOT ALLOWED
DBSET      READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE  NOT ALLOWED

SQL> sho parameter db_uniq
NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_unique_name			     string	 DBTAR
SQL> 

Step 4=> Check Log_archive_dest on Standby Side

log_archive_dest_2             string     SERVICE=TNSSET SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DBSET

log_archive_dest_state_2         string     ENABLE

Step 5=> Execute VERIFY command on Primary database before switchover.

SQL> ALTER DATABASE SWITCHOVER TO DBTAR VERIFY;
ALTER DATABASE SWITCHOVER TO DBTAR VERIFY
*
ERROR at line 1:
ORA-16475: succeeded with warnings, check alert log for more details

As checked alert log there is warning as below AND we can IGNORE this warning due to existing BUG.

SWITCHOVER VERIFY BEGIN
SWITCHOVER VERIFY WARNING: no standby database is defined in LOG_ARCHIVE_DEST_n to protect this database if it is converted to a primary database
SWITCHOVER VERIFY COMPLETE
2022-07-01T21:07:00.281725+05:30

Step 6=> Execute new SWITHOVER command on Primary database to INITIATE switchover.

SQL> ALTER DATABASE SWITCHOVER TO DBTAR;

Database altered.

SQL>   select name,open_mode,database_role,PROTECTION_MODE ,switchover_status from gv$database;
  select name,open_mode,database_role,PROTECTION_MODE ,switchover_status from gv$database
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 7214
Session ID: 150 Serial number: 54319

SQL> exit

As soon as we exeute this command OLD Primary Side DB will Shutdown  and OLD Standby side db will start in MOUNT mode

Alert log as below from OLD Pirmary.

ORA-16475 signalled during: ALTER DATABASE SWITCHOVER TO DBTAR VERIFY...
2022-07-01T21:06:59.838169+05:30
ALTER DATABASE SWITCHOVER TO DBTAR VERIFY
2022-07-01T21:07:00.121207+05:30
SWITCHOVER VERIFY: Send VERIFY request to switchover target DBTAR
SWITCHOVER VERIFY WARNING: switchover target has no standby database defined in LOG_ARCHIVE_DEST_n parameter. |
If the switchover target is converted to a primary database, the new primary database will not be protected.
ORA-16475 signalled during: ALTER DATABASE SWITCHOVER TO DBTAR VERIFY...
2022-07-01T21:07:45.573074+05:30
ALTER DATABASE SWITCHOVER TO DBTAR
2022-07-01T21:07:45.573141+05:30
NET  (PID:7214): The Time Management Interface (TMI) is being enabled for role transition
NET  (PID:7214): information.  This will result in messages beingoutput to the alert log
NET  (PID:7214): file with the prefix 'TMI: '.  This is being enabled to make the timing of
NET  (PID:7214): the various stages of the role transition available for diagnostic purposes.
NET  (PID:7214): This output will end when the role transition is complete.
TMI: dbsdrv switchover to target BEGIN 2022-07-01 21:07:45.573989
NET  (PID:7214): Starting switchover [Process ID: 7214]
TMI: kcv_switchover_to_target convert to physical BEGIN 2022-07-01 21:07:45.885186
2022-07-01T21:07:45.885278+05:30
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 7214] (DBSET1)
...
NET  (PID:7214): Generating and shipping final logs to target standby
Switchover End-Of-Redo Log thread 1 sequence 84 has been fixed
Switchover End-Of-Redo Log thread 2 sequence 56 has been fixed
....
Process (ospid 6360) is suspended due to switchover to physical standby operation.
2022-07-01T21:08:08.117744+05:30
NET  (PID:7214): Switchover complete. Database shutdown required
USER (ospid: 7214): terminating the instance
2022-07-01T21:08:09.286709+05:30
Instance terminated by USER, pid = 7214
TMI: dbsdrv switchover to target END 2022-07-01 21:08:09.296978
Completed: ALTER DATABASE SWITCHOVER TO DBTAR
Shutting down ORACLE instance (abort) (OS id: 7214)
License high water mark = 5
2022-07-01T21:08:09.441218+05:30
Instance shutdown complete (OS id: 7214)

Alert log as below from OLD Standby

ALTER DATABASE SWITCHOVER TO DBTAR VERIFY
2022-07-01T21:02:27.186789+05:30
Unable to find connect string for DBTAR
ORA-16468 signalled during: ALTER DATABASE SWITCHOVER TO DBTAR VERIFY..

2022-07-01T21:03:40.864707+05:30
Resize operation completed for file# 3, old size 645120K, new size 655360K
2022-07-01T21:03:52.144606+05:30
SWITCHOVER VERIFY BEGIN
SWITCHOVER VERIFY WARNING: no standby database is defined in LOG_ARCHIVE_DEST_n to protect this database if it is converted to a primary database
SWITCHOVER VERIFY COMPLETE
...
MRP0 (PID:9933): MRP0: Background Media Recovery cancelled with status 16037
2022-07-01T21:07:54.304154+05:30
Errors in file /ora_home/app/oracle/diag/rdbms/dbtar/DBTAR1/trace/DBTAR1_mrp0_9933.trc:
ORA-16037: user requested cancel of managed recovery operation
2022-07-01T21:07:54.306481+05:30
.... (PID:7389): Managed Standby Recovery not using Real Time Apply
AUDIT_TRAIL initialization parameter is changed back to its original value as specified in the parameter file.
2022-07-01T21:08:07.870638+05:30
.... (PID:7389): Database role cleared from PHYSICAL STANDBY [kcvs.c:1030]
Switchover: Complete - Database mounted as primary
2022-07-01T21:08:07.972395+05:30
Enabling Dynamic Remastering: STNDB->NORM switch
Shutting down ORACLE instance (immediate) (OS id: 20596)
Stopping background process SMCO
2022-07-01T21:20:31.257305+05:30
Shutting down instance: further logons disabled
....
Completed: ALTER DATABASE CLOSE NORMAL /* db agent *//* {1:3032:1252} */
ALTER DATABASE DISMOUNT /* db agent *//* {1:3032:1252} */
...
Instance shutdown complete (OS id: 20596)

Step 7=> Let we Login on New Primary (Having DB_UNIQUE_NAME =DBTAR) and Shutdown and restart again.

[oracle@racsetn1 ~]$
[oracle@racsetn1 ~]$ sqlplus "/as sysdba"

SQL> select name,open_mode from gv$database;

NAME      OPEN_MODE
--------- --------------------
DBSET      MOUNTED
DBSET      MOUNTED

[oracle@racsetn1 ~]$

[oracle@racsetn1 ~]$ srvctl stop database -d DBTAR
[oracle@racsetn1 ~]$ srvctl start database -db DBTAR -o open
[oracle@racsetn1 ~]$ sqlplus "/as sysdba"

SQL> set lines 5000
SQL>select name,open_mode,database_role,PROTECTION_MODE ,switchover_status from gv$database;

NAME      OPEN_MODE           DATABASE_ROLE    PROTECTION_MODE      SWITCHOVER_STATUS
--------- -------------------- ---------------- -------------------- --------------------
DBSET      READ WRITE           PRIMARY        MAXIMUM PERFORMANCE  NOT ALLOWED
DBSET      READ WRITE           PRIMARY        MAXIMUM PERFORMANCE  NOT ALLOWED

Step 8=> Let we Login on New Standby (Having DB_UNIQUE_NAME=DBSET) and start database and check RFS & MRP

[oracle@racnoden1 ~]$ ps -eaf |grep pmon
grid      4106     1  0 20:45 ?        00:00:00 asm_pmon_+ASM1
oracle   16569  5288  0 21:13 pts/0    00:00:00 grep --color=auto pmon
[oracle@racnoden1 ~]$
[oracle@racnoden1 ~]$ srvctl start database -d DBSET -startoption "READ ONLY"
[oracle@racnoden1 ~]$ sqlplus "/as sysdba"

SQL> set lines 5000
SQL> select name,open_mode,database_role,PROTECTION_MODE ,switchover_status from gv$database;

NAME      OPEN_MODE           DATABASE_ROLE    PROTECTION_MODE      SWITCHOVER_STATUS
--------- -------------------- ---------------- -------------------- --------------------
DBSET      READ ONLY           PHYSICAL STANDBY MAXIMUM PERFORMANCE  TO PRIMARY
DBSET      READ ONLY           PHYSICAL STANDBY MAXIMUM PERFORMANCE  TO PRIMARY

SQL> alter database recover managed standby database disconnect from session;

Database altered.

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

PROCESS   STATUS    SEQUENCE#
--------- ------------ ----------
ARCH      CLOSING           64
DGRD      ALLOCATED        0
DGRD      ALLOCATED        0
ARCH      CONNECTED        0
ARCH      CONNECTED        0
ARCH      CONNECTED        0
ARCH      CONNECTED        0
ARCH      CONNECTED        0
ARCH      CONNECTED        0
ARCH      CONNECTED        0
MRP0      APPLYING_LOG     65

PROCESS   STATUS    SEQUENCE#
--------- ------------ ----------
RFS      IDLE            0
RFS      IDLE            0
RFS      IDLE            0
RFS      IDLE            0
RFS      IDLE            0
RFS      IDLE            65
RFS      IDLE            0
RFS      IDLE            95

19 rows selected.

SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied",
(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME )
IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME )
IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;  2    3    4  

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
     1               94             94      0
     2               64             64      0

Step 9=> Change Default configuration of Database with respect to Cluster

On New Primary database .

[oracle@racsetn1 ~]$ srvctl config database -d DBTAR
Database unique name: DBTAR
Database name: DBSET
Oracle home: /ora_home/app/19c/db
Oracle user: oracle
Spfile: +DATA/DBTAR/PARAMETERFILE/spfile_new.ora
Password file: +DATA/DBTAR/PASSWORD/orapwDBTAR
Domain: 
Start options: mount
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools: 
Disk Groups: DATA,FRA
Mount point paths: 
Services: 
Type: RAC
Start concurrency: 
Stop concurrency: 
OSDBA group: oinstall
OSOPER group: oper
Database instances: DBTAR1,DBTAR2
Configured nodes: racsetn1,racsetn2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services: 
Database is administrator managed

[oracle@racsetn1 ~]$ srvctl MODIFY database -d DBTAR -startoption "OPEN" -role "PRIMARY"
[oracle@racsetn1 ~]$ 
[oracle@racsetn1 ~]$ srvctl config database -d DBTAR
Database unique name: DBTAR
Database name: DBSET
Oracle home: /ora_home/app/19c/db
Oracle user: oracle
Spfile: +DATA/DBTAR/PARAMETERFILE/spfile_new.ora
Password file: +DATA/DBTAR/PASSWORD/orapwDBTAR
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: 
Disk Groups: DATA,FRA
Mount point paths: 
Services: 
Type: RAC
Start concurrency: 
Stop concurrency: 
OSDBA group: oinstall
OSOPER group: oper
Database instances: DBTAR1,DBTAR2
Configured nodes: racsetn1,racsetn2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services: 
Database is administrator managed
[oracle@racsetn1 ~]$ 

On New Standby database .

[oracle@racnoden1 ~]$ srvctl config database -d DBSET

Database unique name: DBSET
Database name: DBSET
Oracle home: /ora_home/app/19c/db
Oracle user: oracle
Spfile: +DATA/DBSET/PARAMETERFILE/spfile.268.1107118827
Password file: +DATA/DBSET/PASSWORD/pwddbset.256.1107118247
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: 
Disk Groups: FRA,DATA
Mount point paths: 
Services: 
Type: RAC
Start concurrency: 
Stop concurrency: 
OSDBA group: oinstall
OSOPER group: oper
Database instances: DBSET1,DBSET2
Configured nodes: racnoden1,racnoden2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services: 
Database is administrator managed

[oracle@racnoden1 ~]$ srvctl modify database -d DBSET -startoption "READ ONLY" -role "PHYSICAL_STANDBY"

[oracle@racnoden1 ~]$ srvctl config database -d DBSET
Database unique name: DBSET
Database name: DBSET
Oracle home: /ora_home/app/19c/db
Oracle user: oracle
Spfile: +DATA/DBSET/PARAMETERFILE/spfile.268.1107118827
Password file: +DATA/DBSET/PASSWORD/pwddbset.256.1107118247
Domain: 
Start options: read only
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools: 
Disk Groups: FRA,DATA
Mount point paths: 
Services: 
Type: RAC
Start concurrency: 
Stop concurrency: 
OSDBA group: oinstall
OSOPER group: oper
Database instances: DBSET1,DBSET2
Configured nodes: racnoden1,racnoden2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services: 
Database is administrator managed
[oracle@racnoden1 ~]$