Environment Details:
Oracle Database 19.0.0.0 EE
Operating System: Windows 11 Pro 64-bit
Primary DB Server:
Database Name: PR
DB_UNIQUE_NAME: PR
Database Role: Primary
Server IP: 10.168.20.101
Primary DB Server:
Database Name: PR
DB_UNIQUE_NAME: DR
Database Role: Physical Standby
Server IP: 10.168.20.102
Note: In older Oracle DB release i.e. prior to Oracle 12c, you used to perform switchover/switchback operations by manual method where you used to complete switchover/switchback activities in 4-5 commands on both Primary and Standby databases, but from Oracle 12c onwards, Oracle introduced new feature where switchover is performed in just one command.
Lets begin the Activity.
Step 1: Ensure below things are working fine without any issue.
- Ensure Primary and Standby Databases are in sync and logs are getting applied from Primary to Standby database.
- Check if any ORA- messages are there in both Primary and Standby database alert log file.
- Ensure tnsping connectivity is working fine from Primary to Standby database server and vice-versa.
- Ensure sqlplus connectivity is working fine on both Primary and Standby database servers.
- Check if standby destination has no errors.
- Ensure dataguard configuration parameters are set correctly.
- Make sure telnet is working fine for respective PORT on both Primary and Standby database servers
- Take server reboot of standby servers prior a week before actual DR-DRILL activity time. This is recommended or best practice in case of any server issue at Standby site which might come in to effect after reboot and you will be noticed prior the activity so that activity can be cancelled due to issues.
|
1) Ensure Primary and Standby Databases are in sync and logs are getting applied from Primary to Standby database. On 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 ---- -------------- ------------- ---------- ------- ----------------- PR PR PRIMARY READ WRITE CURRENT TO STANDBY SQL> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) -------------- 260 On 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 ----- --------------- ---------------- ---------- ------- ----------------- PR DR PHYSICAL STANDBY MOUNTED STANDBY NOT ALLOWED SQL> select max(sequence#) from v$archived_log where applied='YES'; MAX(SEQUENCE#) -------------- 260 2) Check if any ORA- messages are there in both Primary and Standby database alert log file. Login to RDBMS alert log file in case of single instance database. Check ASM and cluster alert log file for any error messages in case of Oracle Restart or Oracle RAC. 3) Ensure tnsping connectivity is working fine from Primary to Standby database server and vice-versa. On Primary: C:\Windows\System32>tnsping PR TNS Ping Utility for 64-bit Windows: Version 19.0.0.0.0 - Production on 18-DEC-2023 00:06:19 Copyright (c) 1997, 2022, Oracle. All rights reserved. Used parameter files: D:\RUPESH\Setups\WINDOWS.X64_193000_db_home\network\admin\sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (SDU = 65535) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.168.20.101)(PORT = 1530))) (CONNECT_DATA = (SERVICE_NAME = PR))) OK (0 msec) C:\Windows\System32>tnsping DR TNS Ping Utility for 64-bit Windows: Version 19.0.0.0.0 - Production on 18-DEC-2023 00:06:21 Copyright (c) 1997, 2022, Oracle. All rights reserved. Used parameter files: D:\RUPESH\Setups\WINDOWS.X64_193000_db_home\network\admin\sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (SDU = 65535) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.168.20.102)(PORT = 1531))) (CONNECT_DATA = (SERVICE_NAME = DR))) OK (0 msec) On Standby: C:\Windows\System32>tnsping PR TNS Ping Utility for 64-bit Windows: Version 19.0.0.0.0 - Production on 18-DEC-2023 00:06:19 Copyright (c) 1997, 2022, Oracle. All rights reserved. Used parameter files: D:\RUPESH\Setups\WINDOWS.X64_193000_db_home\network\admin\sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (SDU = 65535) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.168.20.101)(PORT = 1530))) (CONNECT_DATA = (SERVICE_NAME = PR))) OK (0 msec) C:\Windows\System32>tnsping DR TNS Ping Utility for 64-bit Windows: Version 19.0.0.0.0 - Production on 18-DEC-2023 00:06:21 Copyright (c) 1997, 2022, Oracle. All rights reserved. Used parameter files: D:\RUPESH\Setups\WINDOWS.X64_193000_db_home\network\admin\sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (SDU = 65535) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.168.20.102)(PORT = 1531))) (CONNECT_DATA = (SERVICE_NAME = DR))) OK (0 msec) 4) Ensure sqlplus connectivity is working fine on both Primary and Standby database server. On Primary: C:\Windows\System32>sqlplus sys/sys123@PR as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Mon Dec 18 00:05:55 2023 Version 19.16.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.16.0.0.0 SQL> set lines 300 pages 3000 SQL> select name,db_unique_name,database_role,open_mode from v$database; NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE ----- -------------- ------------- ---------- PR PR PRIMARY READ WRITE SQL> exit C:\Windows\System32>sqlplus sys/sys123@DR as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Mon Dec 18 00:06:09 2023 Version 19.16.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.16.0.0.0 SQL> set lines 300 pages 3000 SQL> select name,db_unique_name,database_role,open_mode from v$database; NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE ----- --------------- ---------------- --------- PR DR PHYSICAL STANDBY MOUNTED SQL> exit On Standby: C:\Windows\System32>sqlplus sys/sys123@PR as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Mon Dec 18 00:05:55 2023 Version 19.16.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.16.0.0.0 SQL> set lines 300 pages 3000 SQL> select name,db_unique_name,database_role,open_mode from v$database; NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE ----- -------------- ------------- ---------- PR PR PRIMARY READ WRITE SQL> exit C:\Windows\System32>sqlplus sys/sys123@DR as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Mon Dec 18 00:06:09 2023 Version 19.16.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.16.0.0.0 SQL> set lines 300 pages 3000 SQL> select name,db_unique_name,database_role,open_mode from v$database; NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE ----- --------------- ---------------- --------- PR DR PHYSICAL STANDBY MOUNTED SQL> exit 5) Check if standby destination has no errors. On Primary: SQL> show parameter _3 NAME TYPE VALUE -------------------------- ----------- ------------------------------ log_archive_dest_3 string service=DR valid_for=(all_logf iles,all_roles) db_unique_name =DR log_archive_dest_state_3 string ENABLE SQL> select dest_id,error,status from v$archive_dest where dest_id=3; DEST_ID ERROR STATUS ------- ----------------- --------- 3 VALID On Standby: SQL> show parameter _3 NAME TYPE VALUE -------------------------- ----------- ------------------------------ log_archive_dest_3 string service=PR valid_for=(all_logf iles,all_roles) db_unique_name =PR log_archive_dest_state_3 string ENABLE SQL> select dest_id,error,status from v$archive_dest where dest_id=3; DEST_ID ERROR STATUS ------- ----------------- --------- 3 VALID 6) Ensure dataguard configuration parameters are set properly. On Primary: log_archive_config='dg_config=(pr,dr)' log_archive_dest_3='service=dr valid_for=(all_logfiles,all_roles) db_unique_name=dr' log_archive_dest_state_3=enable remote_login_passwordfile=exclusive standby_file_management=AUTO fal_server=DR db_file_name_convert='D:\RUPESH\Setups\Standby\DB\DR','D:\RUPESH\Setups\Primary\DB\PR\' log_file_name_convert='D:\RUPESH\Setups\Standby\DB\DR\','D:\RUPESH\Setups\Primary\DB\PR\' On Standby: log_archive_config='dg_config=(pr,dr)' log_archive_dest_3='service=pr valid_for=(all_logfiles,all_roles) db_unique_name=pr' log_archive_dest_state_3=enable remote_login_passwordfile=exclusive standby_file_management=AUTO fal_server=PR db_file_name_convert='D:\RUPESH\Setups\Primary\DB\PR\','D:\RUPESH\Setups\Standby\DB\DR' log_file_name_convert='D:\RUPESH\Setups\Primary\DB\PR\','D:\RUPESH\Setups\Standby\DB\DR\' |
Switchover:
Step 2: Verify the switchover.
Before executing switchover, you can execute "switchover verify" command which does not perform switchover but informs you in advance in case of any issues.
On Primary:
SQL> alter database switchover to DR verify;
Database altered.
The above commands performs below checks:
- Verifies that the database release version of the target standby database is 12.1 or later.
- Verifies that the target destination state is enabled on Standby database. In our case, log_archive_dest_state_3=enable on standby which will be used after switchover to transfer the logs from New Primary to New Standby.
- Verifies whether target Standby database is in sync with Primary DB.
- Verifies whether log apply process or MRP is running on Standby DB server.
- Verifies redo transport service
|
When you execute "switchover verify" command, then below entries will be appeared in Primary and Standby database alert log file. Primary DB Alert: 2023-12-18T00:24:14.458402+05:30 alter database switchover to DR verify 2023-12-18T00:24:14.601488+05:30 SWITCHOVER VERIFY: Send VERIFY request to switchover target DR SWITCHOVER VERIFY COMPLETE: READY FOR SWITCHOVER Completed: alter database switchover to DR verify 2023-12-18T00:25:12.462663+05:30 Standby DB Alert: 2023-12-18T00:24:14.665753+05:30 SWITCHOVER VERIFY BEGIN SWITCHOVER VERIFY COMPLETE 2023-12-18T00:25:12.574048+05:30 |
Step 3: Now its time to start switchover.
|
On Primary: SQL> alter database switchover to DR; Database altered. The above command will take care of entire switchover operation in one short and it does not require to execute any switchover command in Standby database. It keeps the Standby database in mount state. You have to manually open the database. SQL> select name,db_unique_name,database_role,open_mode,controlfile_type,switchover_status from v$database; select name,db_unique_name,database_role,open_mode,controlfile_type,switchover_status from v$database * ERROR at line 1: ORA-01034: ORACLE not available Process ID: 6400 Session ID: 10 Serial number: 51202 This means that the switchover terminates the Primary DB instance. You have to start/mount it manually. SQL> startup mount ORACLE instance started. Total System Global Area 2147482432 bytes Fixed Size 9030464 bytes Variable Size 771751936 bytes Database Buffers 1358954496 bytes Redo Buffers 7745536 bytes Database mounted. 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 ---- -------------- ---------------- --------- ------- ----------------- PR PR PHYSICAL STANDBY MOUNTED STANDBY RECOVERY NEEDED On Standby: 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 ---- -------------- ------------- ---------- ------- ----------------- PR DR PRIMARY MOUNTED CURRENT NOT ALLOWED SQL> alter database open; Database altered. NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE CONTROL SWITCHOVER_STATUS ---- -------------- ------------- ----------- ------- ------------------ PR DR PRIMARY READ WRITE CURRENT FAILED DESTINATION When you execute switchover command, then below messages will be appeared in Primary and Standby DB alert log files. Primary: 2023-12-18T00:25:12.462663+05:30 alter database switchover to DR 2023-12-18T00:25:12.462663+05:30 NET (PID:6400): The Time Management Interface (TMI) is being enabled for role transition NET (PID:6400): information. This will result in messages beingoutput to the alert log NET (PID:6400): file with the prefix 'TMI: '. This is being enabled to make the timing of NET (PID:6400): the various stages of the role transition available for diagnostic purposes. NET (PID:6400): This output will end when the role transition is complete. TMI: dbsdrv switchover to target BEGIN 2023-12-18 00:25:12.479576 NET (PID:6400): Starting switchover [Process ID: 6400] TMI: kcv_switchover_to_target convert to physical BEGIN 2023-12-18 00:25:12.685418 NET (PID:6400): Waiting for target standby to receive all redo 2023-12-18T00:25:12.718262+05:30 NET (PID:6400): Waiting for all non-current ORLs to be archived 2023-12-18T00:25:12.718262+05:30 NET (PID:6400): All non-current ORLs have been archived 2023-12-18T00:25:12.732978+05:30 NET (PID:6400): Waiting for all FAL entries to be archived 2023-12-18T00:25:12.732978+05:30 NET (PID:6400): All FAL entries have been archived 2023-12-18T00:25:12.748301+05:30 NET (PID:6400): Waiting for LAD:3 to become synchronized 2023-12-18T00:25:13.772289+05:30 NET (PID:6400): Active, synchronized Physical Standby switchover target has been identified NET (PID:6400): Preventing updates and queries at the Primary 2023-12-18T00:25:15.818089+05:30 NET (PID:6400): Generating and shipping final logs to target standby Switchover End-Of-Redo Log thread 1 sequence 261 has been fixed Switchover: Primary highest seen SCN set to 0x0000000000d5f927 NET (PID:6400): Noswitch archival of T-1.S-261 NET (PID:6400): End-Of-Redo Branch archival of T-1.S-261 NET (PID:6400): LGWR is scheduled to archive to LAD:3 after log switch NET (PID:6400): Archiving is disabled due to current logfile archival Primary will check for some target standby to have received all redo NET (PID:6400): Waiting for target standby to apply all redo 2023-12-18T00:25:17.276437+05:30 Backup controlfile written to trace file D:\RUPESH\APP\RGHUBADE\diag\rdbms\pr\pr\trace\pr_ora_6400.trc NET (PID:6400): Converting the primary database to a new standby database Clearing standby activation ID 2844908597 (0xa991dc35) 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 209715200; ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 209715200; ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 209715200; ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 209715200; Archivelog for thread 1 sequence 261 required for standby recovery Switchover: Primary controlfile converted to standby controlfile succesfully. Switchover: Complete - Database shutdown required TMI: kcv_switchover_to_target convert to physical END 2023-12-18 00:25:17.378335 NET (PID:6400): Sending request(convert to primary database) to switchover target DR 2023-12-18T00:25:20.436489+05:30 NET (PID:6400): Switchover complete. Database shutdown required USER (ospid: 6400): terminating the instance 2023-12-18T00:25:32.947285+05:30 Instance terminated by USER, pid = 6400 TMI: dbsdrv switchover to target END 2023-12-18 00:25:32.961794 Completed: alter database switchover to DR Shutting down ORACLE instance (abort) (OS id: 6400) Shutdown is initiated by sqlplus.exe. License high water mark = 13 2023-12-18T00:25:33.261712+05:30 Instance shutdown complete (OS id: 6400) Standby: 2023-12-18T00:25:12.574048+05:30 rfs (PID:16204): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is Foreground (PID:6400) 2023-12-18T00:25:12.815157+05:30 rfs (PID:5600): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is Foreground (PID:13588) 2023-12-18T00:25:15.986431+05:30 rfs (PID:17936): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is Foreground (PID:6400) 2023-12-18T00:25:16.065442+05:30 rfs (PID:17936): Selected LNO:4 for T-1.S-261 dbid 2829010735 branch 1150397905 2023-12-18T00:25:16.224004+05:30 ARC1 (PID:14268): Archived Log entry 21 added for T-1.S-261 ID 0xa991dc35 LAD:1 2023-12-18T00:25:16.257111+05:30 PR00 (PID:15060): Resetting standby activation ID 2844908597 (0xa991dc35) 2023-12-18T00:25:16.273052+05:30 Media Recovery End-Of-Redo indicator encountered 2023-12-18T00:25:16.273052+05:30 Media Recovery Continuing PR00 (PID:15060): Media Recovery Waiting for T-1.S-262 2023-12-18T00:25:17.488394+05:30 .... (PID:11040): The Time Management Interface (TMI) is being enabled for role transition .... (PID:11040): information. This will result in messages beingoutput to the alert log .... (PID:11040): file with the prefix 'TMI: '. This is being enabled to make the timing of .... (PID:11040): the various stages of the role transition available for diagnostic purposes. .... (PID:11040): This output will end when the role transition is complete. SWITCHOVER: received request 'ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY' from primary database. Maximum wait for role transition is 15 minutes. TMI: kcv_commit_to_so_to_primary wait for MRP to finish BEGIN 2023-12-18 00:25:17.504393 Switchover: Media recovery is still active rmi (PID:11040): Role Change: Canceling MRP - no more redo to apply 2023-12-18T00:25:17.553331+05:30 PR00 (PID:15060): MRP0: Background Media Recovery cancelled with status 16037 2023-12-18T00:25:17.568800+05:30 Errors in file D:\RUPESH\APP\RGHUBADE\diag\rdbms\dr\dr\trace\dr_pr00_15060.trc: ORA-16037: user requested cancel of managed recovery operation PR00 (PID:15060): Managed Standby Recovery not using Real Time Apply Recovery interrupted! Stopping change tracking 2023-12-18T00:25:17.962853+05:30 Errors in file D:\RUPESH\APP\RGHUBADE\diag\rdbms\dr\dr\trace\dr_pr00_15060.trc: ORA-16037: user requested cancel of managed recovery operation 2023-12-18T00:25:18.547535+05:30 rmi (PID:11040): Role Change: Canceled MRP TMI: kcv_commit_to_so_to_primary wait for MRP to finish END 2023-12-18 00:25:18.547535 TMI: kcv_commit_to_so_to_primary Switchover from physical BEGIN 2023-12-18 00:25:18.563933 rmi (PID:11040): Killing 2 processes (PIDS:5600,8716) (all RFS) in order to disallow current and future RFS connections. Requested by OS process 11040 2023-12-18T00:25:20.047195+05:30 Backup controlfile written to trace file D:\RUPESH\APP\RGHUBADE\diag\rdbms\dr\dr\trace\dr_rmi_11040.trc SwitchOver after complete recovery through change 14022951 rmi (PID:11040): ORL pre-clearing operation disabled by switchover Online log D:\RUPESH\SETUPS\STANDBY\DB\DR\ONLINELOG\O1_MF_1_LQLFRV0Y_.LOG: Thread 1 Group 1 was previously cleared Online log D:\RUPESH\SETUPS\STANDBY\DB\DR\ONLINELOG\O1_MF_2_LQLFRV74_.LOG: Thread 1 Group 2 was previously cleared Online log D:\RUPESH\SETUPS\STANDBY\DB\DR\ONLINELOG\O1_MF_3_LQLFRVG0_.LOG: Thread 1 Group 3 was previously cleared Standby became primary SCN: 14022949 rmi (PID:11040): RT: Role transition work is not done rmi (PID:11040): The Time Management Interface (TMI) is being enabled for role transition rmi (PID:11040): information. This will result in messages beingoutput to the alert log rmi (PID:11040): file with the prefix 'TMI: '. This is being enabled to make the timing of rmi (PID:11040): the various stages of the role transition available for diagnostic purposes. rmi (PID:11040): This output will end when the role transition is complete. rmi (PID:11040): Redo network throttle feature is disabled at mount time AUDIT_TRAIL initialization parameter is changed back to its original value as specified in the parameter file. 2023-12-18T00:25:20.395059+05:30 rmi (PID:11040): Database role cleared from PHYSICAL STANDBY [kcvs.c:1114] Switchover: Complete - Database mounted as primary TMI: kcv_commit_to_so_to_primary Switchover from physical END 2023-12-18 00:25:20.412994 SWITCHOVER: completed request from primary database. 2023-12-18T00:26:10.925638+05:30 New Primary: 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 ----- -------------- ------------- ---------- ------- ----------------- PR DR PRIMARY READ WRITE CURRENT TO STANDBY SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) -------------- 263 New Standby: 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 ---- -------------- ---------------- --------- ------- ----------------- PR PR PHYSICAL STANDBY MOUNTED STANDBY NOT ALLOWED SQL> select max(sequence#) from v$archived_log where applied='YES' and RESETLOGS_CHANGE#=12155220; MAX(SEQUENCE#) -------------- 260 SQL> recover managed standby database disconnect from session; Media recovery complete. SQL> select max(sequence#) from v$archived_log where applied='YES' and RESETLOGS_CHANGE#=12155220; MAX(SEQUENCE#) -------------- 263 |
Check below things after switchover activity.
- Ensure no ORA- messages are in alert log file of both Primary and Standby DBs.
- Ensure tnsping and sqlplus connectivity is working fine without any issue.
- Ensure logs are getting applied from New Primary to New Standby DB.
- Make sure an Application is able to connect the database.
The number of days an Application can run on Standby DB server will be decided by the customer and once he decided to perform switchback operation then use below steps to complete Switchback Activity.
Switchback:
Step 1: Ensure below things are working fine without any issue.
- Ensure New Primary and New Standby Databases are in sync and logs are getting applied from New Primary to New Standby database.
- Check if any ORA- messages are there in both New Primary and New Standby database alert log file.
- Ensure tnsping connectivity is working fine from New Primary to New Standby database server and vice-versa.
- Ensure sqlplus connectivity is working fine on both New Primary and New Standby database servers.
- Check if standby destination has no errors.
- Ensure dataguard configuration parameters are set correctly.
- Make sure if telnet is working fine for respective PORT on both New Primary and New Standby database servers.
- Take server reboot of New standby server prior a week before actual DR-DRILL activity time. This is recommended or best practice in case of any server issue at Standby site which might come in to effect after server reboot and you will be noticed prior the activity so that activity can be cancelled due to issues.
|
On New 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 ---- -------------- ---------------- ---------- ------- ----------------- PR DR PRIMARY READ WRITE CURRENT TO STANDBY SQL> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) -------------- 270 On New 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 ---- -------------- ---------------- ---------- ------- ----------------- PR PR PHYSICAL STANDBY MOUNTED STANDBY NOT ALLOWED SQL> select max(sequence#) from v$archived_log where applied='YES'; MAX(SEQUENCE#) -------------- 270 |
|
Execute "switchover verify" command. SQL> alter database switchover to PR verify; Database altered. Primary DB Alert: 2023-12-18T00:38:41.661049+05:30 alter database switchover to PR verify 2023-12-18T00:38:41.898293+05:30 SWITCHOVER VERIFY: Send VERIFY request to switchover target PR SWITCHOVER VERIFY COMPLETE: READY FOR SWITCHOVER Completed: alter database switchover to PR verify Standby DB Alert: 2023-12-18T00:38:41.980205+05:30 SWITCHOVER VERIFY BEGIN SWITCHOVER VERIFY COMPLETE |
|
Execute "switchover" command. On New Primary: SQL> alter database switchover to PR; Database altered. SQL> select max(sequence#) from v$archived_log; select max(sequence#) from v$archived_log; * ERROR at line 1: ORA-01034: ORACLE not available Process ID: 12072 Session ID: 1338 Serial number: 47572 SQL> startup mount ORACLE instance started. Total System Global Area 2147482432 bytes Fixed Size 9030464 bytes Variable Size 452984832 bytes Database Buffers 1677721600 bytes Redo Buffers 7745536 bytes Database mounted. SQL> New Standby: SQL> alter database open; Database altered. 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 ---- -------------- ------------- ---------- ------- ------------------ PR PR PRIMARY READ WRITE CURRENT FAILED DESTINATION Below messages will be appeared in New Primary and New Standby DB alert log file when you execute switchover command. New Primary DB Alert: 2023-12-18T00:38:57.215931+05:30 alter database switchover to PR 2023-12-18T00:38:57.215931+05:30 NET (PID:12072): The Time Management Interface (TMI) is being enabled for role transition NET (PID:12072): information. This will result in messages beingoutput to the alert log NET (PID:12072): file with the prefix 'TMI: '. This is being enabled to make the timing of NET (PID:12072): the various stages of the role transition available for diagnostic purposes. NET (PID:12072): This output will end when the role transition is complete. TMI: dbsdrv switchover to target BEGIN 2023-12-18 00:38:57.231843 NET (PID:12072): Starting switchover [Process ID: 12072] TMI: kcv_switchover_to_target convert to physical BEGIN 2023-12-18 00:38:57.406909 NET (PID:12072): Waiting for target standby to receive all redo 2023-12-18T00:38:57.455369+05:30 NET (PID:12072): Waiting for all non-current ORLs to be archived 2023-12-18T00:38:57.469877+05:30 NET (PID:12072): All non-current ORLs have been archived 2023-12-18T00:38:57.471011+05:30 NET (PID:12072): Waiting for all FAL entries to be archived 2023-12-18T00:38:57.486276+05:30 NET (PID:12072): All FAL entries have been archived 2023-12-18T00:38:57.501282+05:30 NET (PID:12072): Waiting for LAD:3 to become synchronized 2023-12-18T00:38:58.528941+05:30 NET (PID:12072): Active, synchronized Physical Standby switchover target has been identified NET (PID:12072): Preventing updates and queries at the Primary 2023-12-18T00:38:59.551076+05:30 NET (PID:12072): Generating and shipping final logs to target standby Switchover End-Of-Redo Log thread 1 sequence 271 has been fixed Switchover: Primary highest seen SCN set to 0x0000000000d78c5c NET (PID:12072): Noswitch archival of T-1.S-271 NET (PID:12072): End-Of-Redo Branch archival of T-1.S-271 NET (PID:12072): LGWR is scheduled to archive to LAD:3 after log switch NET (PID:12072): Archiving is disabled due to current logfile archival Primary will check for some target standby to have received all redo NET (PID:12072): Waiting for target standby to apply all redo 2023-12-18T00:39:00.936134+05:30 Backup controlfile written to trace file D:\RUPESH\APP\RGHUBADE\diag\rdbms\dr\dr\trace\dr_ora_12072.trc NET (PID:12072): Converting the primary database to a new standby database Clearing standby activation ID 2850359929 (0xa9e50a79) 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 209715200; ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 209715200; ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 209715200; ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 209715200; Archivelog for thread 1 sequence 271 required for standby recovery Switchover: Primary controlfile converted to standby controlfile succesfully. Switchover: Complete - Database shutdown required TMI: kcv_switchover_to_target convert to physical END 2023-12-18 00:39:01.096149 NET (PID:12072): Sending request(convert to primary database) to switchover target PR 2023-12-18T00:39:04.646276+05:30 NET (PID:12072): Switchover complete. Database shutdown required USER (ospid: 12072): terminating the instance 2023-12-18T00:39:18.432469+05:30 Instance terminated by USER, pid = 12072 TMI: dbsdrv switchover to target END 2023-12-18 00:39:18.432469 Completed: alter database switchover to PR Shutting down ORACLE instance (abort) (OS id: 12072) Shutdown is initiated by sqlplus.exe. License high water mark = 14 2023-12-18T00:39:18.700910+05:30 Instance shutdown complete (OS id: 12072) 2023-12-18T00:40:33.030318+05:30 New Standby DB alert: 2023-12-18T00:38:57.311483+05:30 rfs (PID:7736): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is Foreground (PID:12072) 2023-12-18T00:38:57.564601+05:30 rfs (PID:12096): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is Foreground (PID:1352) 2023-12-18T00:38:59.697037+05:30 rfs (PID:17032): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is Foreground (PID:12072) 2023-12-18T00:38:59.742007+05:30 rfs (PID:17032): Selected LNO:4 for T-1.S-271 dbid 2829010735 branch 1150397905 2023-12-18T00:38:59.884788+05:30 PR00 (PID:10032): Resetting standby activation ID 2850359929 (0xa9e50a79) 2023-12-18T00:38:59.900723+05:30 Media Recovery End-Of-Redo indicator encountered 2023-12-18T00:38:59.900723+05:30 Media Recovery Continuing 2023-12-18T00:39:00.905356+05:30 ARC3 (PID:10064): Archived Log entry 2343 added for T-1.S-271 ID 0xa9e50a79 LAD:1 2023-12-18T00:39:00.936134+05:30 PR00 (PID:10032): Media Recovery Waiting for T-1.S-272 2023-12-18T00:39:01.175787+05:30 .... (PID:252): The Time Management Interface (TMI) is being enabled for role transition .... (PID:252): information. This will result in messages beingoutput to the alert log .... (PID:252): file with the prefix 'TMI: '. This is being enabled to make the timing of .... (PID:252): the various stages of the role transition available for diagnostic purposes. .... (PID:252): This output will end when the role transition is complete. SWITCHOVER: received request 'ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY' from primary database. Maximum wait for role transition is 15 minutes. TMI: kcv_commit_to_so_to_primary wait for MRP to finish BEGIN 2023-12-18 00:39:01.206593 Switchover: Media recovery is still active rmi (PID:252): Role Change: Canceling MRP - no more redo to apply 2023-12-18T00:39:01.270696+05:30 PR00 (PID:10032): MRP0: Background Media Recovery cancelled with status 16037 2023-12-18T00:39:01.270696+05:30 Errors in file D:\RUPESH\APP\RGHUBADE\diag\rdbms\pr\pr\trace\pr_pr00_10032.trc: ORA-16037: user requested cancel of managed recovery operation PR00 (PID:10032): Managed Standby Recovery not using Real Time Apply Recovery interrupted! Stopping change tracking 2023-12-18T00:39:01.682255+05:30 Errors in file D:\RUPESH\APP\RGHUBADE\diag\rdbms\pr\pr\trace\pr_pr00_10032.trc: ORA-16037: user requested cancel of managed recovery operation 2023-12-18T00:39:02.250478+05:30 rmi (PID:252): Role Change: Canceled MRP TMI: kcv_commit_to_so_to_primary wait for MRP to finish END 2023-12-18 00:39:02.250478 TMI: kcv_commit_to_so_to_primary Switchover from physical BEGIN 2023-12-18 00:39:02.266196 rmi (PID:252): Killing 4 processes (PIDS:12096,6224,3216,14508) (all RFS) in order to disallow current and future RFS connections. Requested by OS process 252 2023-12-18T00:39:04.235521+05:30 Backup controlfile written to trace file D:\RUPESH\APP\RGHUBADE\diag\rdbms\pr\pr\trace\pr_rmi_252.trc SwitchOver after complete recovery through change 14126172 rmi (PID:252): ORL pre-clearing operation disabled by switchover Online log D:\RUPESH\SETUPS\PRIMARY\DB\PR\REDO_01.LOG: Thread 1 Group 1 was previously cleared Online log D:\RUPESH\SETUPS\PRIMARY\DB\PR\REDO_02.LOG: Thread 1 Group 2 was previously cleared Online log D:\RUPESH\SETUPS\PRIMARY\DB\PR\REDO_03.LOG: Thread 1 Group 3 was previously cleared Standby became primary SCN: 14126170 rmi (PID:252): RT: Role transition work is not done rmi (PID:252): The Time Management Interface (TMI) is being enabled for role transition rmi (PID:252): information. This will result in messages beingoutput to the alert log rmi (PID:252): file with the prefix 'TMI: '. This is being enabled to make the timing of rmi (PID:252): the various stages of the role transition available for diagnostic purposes. rmi (PID:252): This output will end when the role transition is complete. rmi (PID:252): Redo network throttle feature is disabled at mount time 2023-12-18T00:39:04.609792+05:30 rmi (PID:252): Database role cleared from PHYSICAL STANDBY [kcvs.c:1114] Switchover: Complete - Database mounted as primary TMI: kcv_commit_to_so_to_primary Switchover from physical END 2023-12-18 00:39:04.624064 SWITCHOVER: completed request from primary database. 2023-12-18T00:39:46.183190+05:30 On Primary: 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 ----- -------------- ------------- ---------- ------- ----------------- PR PR PRIMARY READ WRITE CURRENT TO STANDBY SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. .... .... SQL> alter system switch logfile; System altered. SQL> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) -------------- 277 On 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 ---- -------------- ---------------- ---------- ------- ----------------- PR DR PHYSICAL STANDBY MOUNTED STANDBY NOT ALLOWED SQL> recover managed standby database disconnect from session; SQL> select max(sequence#) from v$archived_log where applied='YES'; MAX(SEQUENCE#) -------------- 277 |
Check below things after switchback activity.
- Ensure no ORA- messages are in alert log file of both Primary and Standby DBs.
- Ensure tnsping and sqlplus connectivity is working fine without any issue.
- Ensure logs are getting applied from New Primary to New Standby DB.
- Ensure an Application is able to connect the database.
Thanks for reading this post ! Please comment if you like this post ! Click FOLLOW to get future blog updates !
Nice explain Rupesh....!
ReplyDeleteAmazing
ReplyDelete