Dataguard Switchover in 12c
Switchover is a two way process in Oracle Dataguard. This way of role transition where the current primary database starts behaving as a Physical Standby and on the other hand the current Physical standby database starts behaving as a Primary database. Switchover in 12c is still the same, but new features have been incorporated and the usage of the “switchover commands” have in a great change.
Oracle has introduced the “Verify” functionality which does not perform the switchover but does verify if the switchover can be performed to the target standby database.
It mainly does:
1. The redo shipping from the primary to the standby database.
2. Checks if Managed Recovery Process (MRP) is running on the standby.
3. And finally checks if the standby database is in sync with the primary.
The syntax used is “Alter database switchover to <target_standby_database_unique_name> verify;” where in the “target_standby_database_unique_name” is the unique name of the target database to which the switchover needs to be performed.
As an example, below is how the “verify” functionality checks and reports the error.
SYS @ oraprim > alter database switchover to orastb verify; alter database switchover to orastb verify * ERROR at line 1: ORA-16470: Redo Apply is not running on switchover target
This error clearly states that Redo Apply (MRP) is not running on the standby database. A copy of the error message will also be written on the alert log file and necessary action can be taken up.
Another set of example is:
SYS @ oraprim > alter database switchover to orastb verify; alter database switchover to orastb verify * ERROR at line 1: ORA-16467: switchover target is not synchronized
Here, the error message reads out that the target standby database is not in sync with the primary database for the switchover to take place.
Necessary action will have to be taken out and get the standby in sync with the primary before moving on with the switchover.
Now, let’s move on with the demonstration of the switchover process in 12c.
Environment:
Primary Database Name : oraprim Standby Database Name : orastb Database Version : 12.1.0.1
Primary Database Details:
SYS @ oraprim > select status,instance_name,database_role from v$database,v$instance; STATUS INSTANCE_NAME DATABASE_ROLE ------------ ---------------- ---------------- OPEN oraprim PRIMARY
Physical standby database details:
SYS @ orastb > select status,instance_name,database_role from v$database,v$instance; STATUS INSTANCE_NAME DATABASE_ROLE ------------ ---------------- ---------------- MOUNTED orastb PHYSICAL STANDBY
Now, let’s verify if the switchover of “oraprim” to “orastb” is possible or not using the “verify” functionality.
SYS @ oraprim > alter database switchover to orastb verify; Database altered.
There has been no error message reported which signifies that we are good to go with the switchover.
The alert log on the primary database would read out the message something as below.
Sat Oct 31 11:49:22 2015 alter database switchover to orastb verify Sat Oct 31 11:49:22 2015 SWITCHOVER VERIFY: Send VERIFY request to switchover target ORASTB SWITCHOVER VERIFY COMPLETE Completed: alter database switchover to orastb verify
Now, perform the actual switchover to the standby. With the legacy database versions (until 11.2), the syntax of the switchvoer from the primary to physical standby database used to be as “alter database commit to switchvoer to physical standby with session shutdown;” and then on the standby server, you had to switchover the physical standby to primary database which required to fire “alter database commit to switchover to primary with session shutdown;” commands, but with 12c, it’s a single command that does everything for us.
“Alter database switchover to <target_standby_db_unique_name>;” this command switches first the primary to the standby database and on the standby server, switches the physical standby database to primary database. So, it’s just a single command that needs to be fired and Oracle does everything for us.
On the primary database server, I run the switchover command.
SYS @ oraprim > alter database switchover to orastb; Database altered.
This is how the alert log of the primary database would contain the information about the switchover.
The EOR (End Of Redo) is generated at sequence 293.
Sat Oct 31 15:53:09 2015 ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 2612] (oraprim) Sat Oct 31 15:53:09 2015 Thread 1 cannot allocate new log, sequence 294 Checkpoint not complete Current log# 3 seq# 293 mem# 0: /u01/app/oracle/oradata/oraprim/redo03.log Sat Oct 31 15:53:09 2015 Waiting for target standby to receive all redo Sat Oct 31 15:53:09 2015 Waiting for all non-current ORLs to be archived... Sat Oct 31 15:53:09 2015 All non-current ORLs have been archived. Sat Oct 31 15:53:09 2015 Waiting for all FAL entries to be archived... Sat Oct 31 15:53:09 2015 All FAL entries have been archived. Sat Oct 31 15:53:09 2015 Waiting for dest_id 3 to become synchronized... Sat Oct 31 15:53:09 2015 Active, synchronized Physical Standby switchover target has been identified Preventing updates and queries at the Primary Generating and shipping final logs to target standby Switchover End-Of-Redo Log thread 1 sequence 293 has been fixed Switchover: Primary highest seen SCN set to 0x0.0x275a24 ARCH: Noswitch archival of thread 1, sequence 293 ARCH: End-Of-Redo Branch archival of thread 1 sequence 293 ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_3 ARCH: Standby redo logfile selected for thread 1 sequence 293 for destination LOG_ARCHIVE_DEST_3 ARCH: Archiving is disabled due to current logfile archival
The alert log on the standby database server would have the following information written down.
If you go through carefully, the EOR is received at sequence 293 and then oracle internally runs the command “Alter database commit to switchvoer to primary” and finally the role transition is done.
RFS[3]: Selected log 4 for thread 1 sequence 293 dbid 4209209247 branch 890507875 Sat Oct 31 15:53:08 2015 Resetting standby activation ID 4209242527 (0xfae3f19f) Sat Oct 31 15:53:08 2015 Media Recovery End-Of-Redo indicator encountered Sat Oct 31 15:53:08 2015 Media Recovery Continuing Media Recovery Waiting for thread 1 sequence 294 Sat Oct 31 15:53:08 2015 Archived Log entry 197 added for thread 1 sequence 293 ID 0xfae3f19f dest 1: Sat Oct 31 15:53:09 2015 SWITCHOVER: received request 'ALTER DTABASE COMMIT TO SWITCHOVER TO PRIMARY' from primary database. Sat Oct 31 15:53:09 2015 ALTER DATABASE SWITCHOVER TO PRIMARY (orastb) Maximum wait for role transition is 15 minutes. Switchover: Media recovery is still active Role Change: Canceling MRP - no more redo to apply Sat Oct 31 15:53:11 2015 MRP0: Background Media Recovery cancelled with status 16037 Sat Oct 31 15:53:11 2015 Errors in file /u01/app/oracle/diag/rdbms/orastb/orastb/trace/orastb_mrp0_2605.trc: ORA-16037: user requested cancel of managed recovery operation Managed Standby Recovery not using Real Time Apply Recovery interrupted! Sat Oct 31 15:53:11 2015 MRP0: Background Media Recovery cancelled with status 16037 Sat Oct 31 15:53:11 2015 Errors in file /u01/app/oracle/diag/rdbms/orastb/orastb/trace/orastb_mrp0_2605.trc: ORA-16037: user requested cancel of managed recovery operation Managed Standby Recovery not using Real Time Apply Recovery interrupted! Sat Oct 31 15:53:11 2015 MRP0: Background Media Recovery process shutdown (orastb) Sat Oct 31 15:53:11 2015 Role Change: Canceled MRP Killing 2 processes (PIDS:2595,2593) (all RFS) in order to disallow current and future RFS connections. Requested by OS process 2627 Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/orastb/orastb/trace/orastb_rmi_2627.trc SwitchOver after complete recovery through change 2578980 Online logfile pre-clearing operation disabled by switchover Online log /u01/app/oracle/oradata/orastb/redo01.log: Thread 1 Group 1 was previously cleared Online log /u01/app/oracle/oradata/orastb/redo02.log: Thread 1 Group 2 was previously cleared Online log /u01/app/oracle/oradata/orastb/redo03.log: Thread 1 Group 3 was previously cleared Standby became primary SCN: 2578978 Switchover: Complete - Database mounted as primary SWITCHOVER: completed request from primary database. Sat Oct 31 15:57:27 2015
Let’s check the status of the databases. The former standby database (orastb) is now behaving as a Primary database .
SYS @ orastb > select status,instance_name,database_role from v$database,v$instance; STATUS INSTANCE_NAME DATABASE_ROLE ------------ ---------------- ---------------- MOUNTED orastb PRIMARY SYS @ orastb > alter database open; Database altered.
On the other hand, the former primary database (oraprim) is behaving as a physical standby database.
SYS @ oraprim > select status,instance_name,database_role from v$database,v$instance; STATUS INSTANCE_NAME DATABASE_ROLE ------------ ---------------- ---------------- MOUNTED oraprim PHYSICAL STANDBY
Let’s start the MRP on the new standby database.
SYS @ oraprim > alter database recover managed standby database disconnect; Database altered. SYS @ oraprim > select process,status,sequence# from v$managed_standby; PROCESS STATUS SEQUENCE# --------- ------------ ---------- ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CLOSING 295 ARCH CLOSING 294 RFS IDLE 0 RFS IDLE 296 RFS IDLE 0 MRP0 APPLYING_LOG 296 8 rows selected.
It could be seen that Managed Recovery Process is now running on the new standby database “oraprim” with sequence 296 being applied.
Sync status: On the Primary database:
SYS @ orastb > select max(sequence#) from v$archived_log; MAX(SEQUENCE#) -------------- 295
On the Physcial Standby Database:
SYS @ oraprim > select max(sequence#) from v$archived_log where applied='YES'; MAX(SEQUENCE#) -------------- 295
After the switchover, the corresponding PDBS of the databases will be in the closed state and will have to be opened manually.
On the new primary, we can see that all the PDBs are closed and will have to be opened manually.
SYS @ orastb > select name,open_mode from v$pdbs; NAME OPEN_MODE ------------------------------ ---------- PDB$SEED READ ONLY TESTPDB1 MOUNTED TESTPDB3 MOUNTED
On the new standby as well, they are in closed state.
SYS @ oraprim > select name,open_mode from v$pdbs; NAME OPEN_MODE ------------------------------ ---------- PDB$SEED MOUNTED TESTPDB1 MOUNTED TESTPDB3 MOUNTED
On a note, the physical standby database need not have to be in MOUNT state for switchover to be performed. If Active Dataguard is in place and the standby is in OPEN READ ONLY MODE with APPLY and it’s corresponding PDBs are in READ ONLY mode, it’s still possible to perform the switchvoer and not necessary to have them in MOUNT state.
COPYRIGHT
© Shivananda Rao P, 2012 to 2018. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.
DISCLAIMER
The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.
Leave a Reply