Role Transitions with Oracle 12c Data Guard

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.