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.