Role Transitions with Oracle 12c Data Guard "As easy as pie"

Oracle Community

Role Transitions with Oracle 12c Data Guard "As easy as pie"

Introduction

 
Role transitions are no more complicated starting from Oracle Database 12c. Role transition, basically, comprises switchover and failover. Using the previous release role transitions, in order to perform a switchover, you have to execute the commands from both the primary and the secondary databases with appropriate syntaxes. We will see in this article that, from Oracle 12c and on, role transitions are smart enough to determine whether your switchover will work or not before you actually start this switchover. We will see in this article as well much more new commands and how easy to use them.

 

Role Transitions

 
To say Oracle Data Guard, at least two or more databases should be involved, One designated as primary database and the other standby database either it can be Physical or Logical standby or Snapshot Standby database. Oracle Data Guard allows you to change the database role depending on the requirement from primary to standby and vice versa.

Switchover

With the switchover procedure the primary database role is converted to physical standby database and standby database will be converted and works as new primary database. Usually switchover servers its purpose only after testing the standby database whether standby can give same level of performance and availability as primary database in case if its future production database. Basically it's a planned event and during switchover there is no place for data loss.

Failover

In case of Primary database is unavailable because of any hardware failures or corruptions where production is not functioning as normal in those cases standby will convert to Primary database to use as new production database and old standby database will be removed from the configuration. But some data loss may occur in case of Maximum performance mode.
 
Prior to 12c, If you are about to perform switchover, we will check whether switchover status is either "To Standby" or "Sessions Active" , If fortunately the switchover status is "To Standby" then you are very much fine to proceed for switchover but in case of "Sessions Active" then you have to use clause "With Session Shutdown" with the switchover command and apart from that many things you may have to ensure before performing switchover.
 
SQL> select db_unique_name,database_role,switchover_status from v$database;
DB_UNIQUE_NAME DATABASE_ROLE SWITCHOVER_STATUS
--------------- ---------------- --------------------
canada PRIMARY TO STANDBY
SQL>
 
Switchover can be performed by using SQL*Plus, the Data Guard broker, or Enterprise Manager Cloud Control. Whenever we initiate switchover, redo generation will be stopped immediately and no other operations will be allowed to be performed and the current log sequence will be archived, which is also known as End of Redo (EOR).
 

12c Switchover Preview & Switchover

 
In this article we will discuss how easy to use Switchover using the SQL command and using Data Guard broker and several new commands. This overall article will be demonstrated on Primary database and One standby database with maximum availability mode, More details are below.
 
 
INSTANCE_NAME
DB_UNIQUE_NAME
Net Service Name
Primary
MCDB
CANADA
CANADA
Standby
DRMCDB
INDIA
INDIA
Table 1-1
 
Using SQLPlus, with the switchover new option "VERIFY" will perform the preliminary checks so that we can easily evaluate whether we can do a successful switchover or not? "VERIFY" clause will check many conditions such as standby is synchronized or not, whether redo apply is running or not and many more.
 
As per from the above table the standby database DB_UNIQUE_NAME is INDIA and you can use the command to verify as
 
SQL> alter database switchover to india verify;
 
Database altered.
 
SQL>
 
If this query results as "Database Altered", that mean Data Guard configuration is ready for switchover operations, For more information on switchover verify process you can review the alert log of primary database and the same information you can find from standby database because primary database also sends switchover verification signals to the standby database.
 
Primary Alert Log
Sun Oct 26 19:12:25 2014
SWITCHOVER VERIFY: Send VERIFY request to switchover target INDIA
SWITCHOVER VERIFY COMPLETE
Completed: alter database switchover to india verify
 
Standby Alert Log
Sun Oct 26 19:12:18 2014
Recovery of Online Redo Log: Thread 1 Group 5 Seq 159 Reading mem 0
Mem# 0: /u02/app/oracle/fast_recovery_area/INDIA/onlinelog/o1_mf_5_9z3t82fx_.log
Sun Oct 26 19:12:26 2014
SWITCHOVER VERIFY BEGIN
SWITCHOVER VERIFY COMPLETE
 
Let's see the other dark side of the command if it fails.
 
1)
SQL> alter database switchover to india verify;
alter database switchover to india verify
*
ERROR at line 1:
ORA-16475: succeeded with warnings, check alert log for more details
SQL>
 
So from above command output, it says the standby is not yet ready to perform switchover and to know the valid reason you have to visit the alert log file.
 
SQL> !oerr ora 16475
16475, 0000, "succeeded with warnings, check alert log for more details"
// *Cause: The ALTER DATABASE SWITCHOVER TO <target_db_name> VERIFY command
// succeeded but with warnings.
// *Action: Check alert log for more details.
 
SQL>
 
Primary Alert Log
SWITCHOVER VERIFY WARNING: no standby redo logfiles configured. Standby redo logfiles are recommended configuration for physical standby database.
SWITCHOVER VERIFY: Send VERIFY request to switchover target INDIA
SWITCHOVER VERIFY COMPLETE
ORA-16475 signalled during: alter database switchover to india verify...
 
2) Try interpreting by cancelling MRP on standby and check how the VERIFY is working
 
SQL> alter database switchover to india verify;
alter database switchover to india verify
*
ERROR at line 1:
ORA-16470: Redo Apply is not running on switchover target
SQL>
 
From above error messages it's clear that redo apply is not running on target standby database and thus how we can preview and fix the issue prior to switchover.
 

Switchover:

 
After all the above validation and verification prior to switchover, now you can safely perform switchover operation. Prior to 12c in order to perform switchover you have to execute "SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;" from primary and " SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;" from standby database. Now it is more simpler with the single command.
 
 
SQL> select db_unique_name,database_role,switchover_status from v$database;
 
DB_UNIQUE_NAME DATABASE_ROLE SWITCHOVER_STATUS
--------------- ---------------- --------------------
canada PRIMARY TO STANDBY
 
SQL> select db_unique_name,database_role,switchover_status from v$database;
 
DB_UNIQUE_NAME DATABASE_ROLE SWITCHOVER_STATUS
--------------- ---------------- --------------------
india PHYSICAL STANDBY NOT ALLOWED
 
SQL> alter database switchover to india;
 
Database altered.
 
SQL>
 
Primary Alert Log
Sun Oct 26 19:27:33 2014
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 4646] (mcdb)
Sun Oct 26 19:27:33 2014
LGWR: Standby redo logfile selected to archive thread 1 sequence 160
LGWR: Standby redo logfile selected for thread 1 sequence 160 for destination LOG_ARCHIVE_DEST_2
........................
Primary will check for some target standby to have received all redo
Waiting for target standby to apply all redo
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/canada/mcdb/trace/mcdb_ora_4646.trc
Converting the primary database to a new standby database
...........................
Switchover: Primary controlfile converted to standby controlfile succesfully.
Switchover: Complete - Database shutdown required
Sending request(convert to primary database) to switchover target INDIA
Switchover complete. Database shutdown required
 
Standby Alert Log
Sun Oct 26 19:28:10 2014
SWITCHOVER: received request 'ALTER DTABASE COMMIT TO SWITCHOVER TO PRIMARY' from primary database.
Sun Oct 26 19:28:10 2014
ALTER DATABASE SWITCHOVER TO PRIMARY (drmcdb)
Maximum wait for role transition is 15 minutes.
Switchover: Media recovery is still active
Role Change: Canceling MRP - no more redo to apply
..............
Standby became primary SCN: 2530077
AUDIT_TRAIL initialization parameter is changed back to its original value as specified in the parameter file.
Switchover: Complete - Database mounted as primary
SWITCHOVER: completed request from primary database.
 
  •          From the above process, Old primary database(Canada) will be terminated after the role changed to physical standby, So start the new standby database to OPEN(if ADG) and then start redo apply(MRP)
  •         New primary database (India) after the role changed to Primary database it will be remained in MOUNT status, now you can startup again the new Primary database.
 
Using FORCE, if the switchover process completed with errors, then you might have to perform and use additional steps in order to complete the process.
 
  •          As per the above table 1-1, If CANADA database is primary and INDIA database is Standby then review the alert log to identify the root cause and repeat the switchover process from beginning.
  •          If CANADA database is Standby and INDIA database is also in Standby, Then on INDIA database use the SQL command to convert it into primary database role using "FORCE" option.

SQL> alter database switchover to india force;

 
Note: Use the appropriate database name based on the database role, you have to convert and it can be either INDIA or CANADA based on the target database role.
 
If the above command is failed, then start redo apply and again reissue the command
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
SQL> ALTER DATABASE SWTICHOVER TO INDIA FORCE;
 
 
Using Data Guard Broker, in the same manner in order to perform switchover preview we can use Broker also. Prior to that installation of Broker is necessary and let's drive through with the command.
 
DGMGRL> show configuration;
 
Configuration - toadworld
 
Protection Mode: MaxAvailability
Databases:
india - Primary database
canada - Physical standby database
 
Fast-Start Failover: DISABLED
 
Configuration Status:
SUCCESS
 
DGMGRL>
 
The below command is similar to the "VERIFY" option we use in SQLPlus , Before that ensure the Data Guard configuration is in good state. Now primary database is in INDIA and standby database is CANADA.
 
DGMGRL> validate database canada;
 
Database Role: Physical standby database
Primary Database: india
 
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
 
Flashback Database Status:
india: Off
canada: On
 
Current Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups
(india) (canada)
1 3 2
 
Future Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups
(canada) (india)
1 3 3
 
DGMGRL>
 
Sun Oct 26 21:45:12 2014
SWITCHOVER VERIFY: Send VERIFY request to switchover target canada
SWITCHOVER VERIFY COMPLETE
 
Validate command is very informative, Apart from the switchover status it also lets us know how many standby redo logs are configured on primary and standby databases and whether the flashback enabled or not from all the databases.
 
For more additional information of switchover preview, we can use command "DGMGRL> validate database verbose canada;"
 
Switchover Using Broker, There are no changes in switchover command of 12c so we can use the same commands as prior releases.
 
DGMGRL> switchover to canada;
Performing switchover NOW, please wait...
Operation requires a connection to instance "mcdb" on database "canada"
Connecting to instance "mcdb"...
Connected as SYSDBA.
New primary database "canada" is opening...
Operation requires startup of instance "mcdb" on database "canada"
Starting instance "mcdb"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "canada"
DGMGRL>
 
Latest configuration after the role transition
 
DGMGRL> show configuration;
 
Configuration - toadworld
 
Protection Mode: MaxAvailability
Databases:
canada - Primary database
india - Physical standby database
 
Fast-Start Failover: DISABLED
 
Configuration Status:
SUCCESS
 
DGMGRL>
 
 
Things to Check before you handover
 
1) Verify the temp file is available?
 
SQL> select name from v$tempfile;
 
NAME
-------------------------------------------------------------
/u02/app/oracle/oradata/mcdb/temp01.dbf
/u02/app/oracle/oradata/mcdb/pdbseed/pdbseed_temp01.dbf
 
2) Disable Tracing, In case tracing is enabled during switchover then disable tracing as below.
SQL> alter system set log_archive_trace=0;
 
3) Verify Synchronization after the changes in database role.
ID STATUS DB_MODE RECOVERY_MODE PROTECTION_MODE ARCHIVED_SEQ#
---------- --------- --------------- ----------------------- -------------------- -------------
1 VALID OPEN IDLE MAXIMUM PERFORMANCE 241
2 VALID OPEN_READ-ONLY MANAGED REAL TIME APPLY MAXIMUM AVAILABILITY 240

 Conclusion

 
The role transition process has been drastically simplified in the Oracle Database 12c release. Use the 'VERIFY' command and it will takes care of checking two or three important prerequisites while in the preceding releases, you would have been obliged to use separate commands to check each different pre-requisite switchover step. The enhanced 12c Role Transition will take care of checking Standby synchronization, applying Redo and many other important things before you can smoothly start your switchover operation.
11387 3 /
Follow / 29 Oct 2014 at 9:19pm

excellent work nassyam.

Follow / 29 Oct 2014 at 9:58pm

good article man!

Follow / 2 Nov 2014 at 5:25pm

Thank you Guys