Oracle Data Guard Broker Switchover and Failover
Let’s look at how to perform oracle data guard switchover / failover using broker configuration. It’s very simple to perform switchover / failover using data guard broke as its only one single command that does the work.
Data Guard Broker Switchover
A database can be in one of two mutually exclusive modes (primary or standby). These roles can be altered at run-time without loss of data or resetting of redo logs. This process is known as a Switchover and can be performed using the following commands.
Switchover Using DGMGRL
Consider proddb and proddb_st as primary and standby databases respectively. Connect to DGMGRL and issue below command
On primary:
===========
dgmgrl sys/sys@proddb
DGMGRL> show configuration;
DGMGRL> SWITCHOVER TO proddb_st;
Performing switchover NOW, please wait...
Operation requires a connection to instance "proddb_st" on database "proddb_st"
Connecting to instance "proddb_st"...
Connected.
New primary database "proddb_st" is opening...
Operation requires startup of instance "proddb" on database "proddb"
Starting instance "proddb"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "proddb_st"
DGMGRL> show configuration;Revert Back Using DGMGRL
Once you perform switchover, the original primary becomes standby and original standby becomes primary. At this point, our primary is proddb_st and standby is proddb. Connect to current primary proddb_st and switchover to current stnadby proddb
On current primary(proddb_st):
==============================
dgmgrl sys/sys@proddb_st
DGMGRL> show configuration;
DGMGRL> switchover to proddb;
Performing switchover NOW, please wait...
Operation requires a connection to instance "proddb" on database "proddb"
Connecting to instance "proddb"...
Connected.
New primary database "proddb" is opening...
Operation requires startup of instance "proddb_st" on database "proddb_st"
Starting instance "proddb_st"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "proddb"
DGMGRL> show configuration;Data Guard Broker Failover
A failover is when you have lost primary database. It’s very simple to perform failover using data guard broker.
Crash Primary Database (Simulate)
Let us simulate failure. We will kill the PMON process at OS level on primary
On primary:
===========
ps -ef|grep pmon
grid 2636 1 0 Apr15 ? 00:00:12 asm_pmon_+ASM
oracle 16914 1 0 14:03 ? 00:00:00 ora_pmon_proddb
oracle 17722 15893 0 15:08 pts/0 00:00:00 grep pmon
kill -9 16914 Failover to Standby
Connect to standby database proddb_st (as primary crashed or not available) and failover to standby proddb_st
On standby:
===========
dgmgrl sys/sys@proddb_st
DGMGRL> show configuration;
DGMGRL> FAILOVER TO proddb_st;
Performing failover NOW, please wait...
Failover succeeded, new primary is "proddb_st"
DGMGRL> show configuration;Rebuild Primary After Failover
Post failover, there are two methods of rebuilding your failed primary
Method 1: Rebuild from scratch –> RMAN duplicate
Method 2: Flashback database –> only if Flashback was enabled
Reinstate failed primary: When you use data guard broker, with just one command, the primary can be rebuilt. Start the failed primary server, in this case start proddb server
On current primary (proddb_st):
===============================
dgmgrl sys/sys@proddb_st
DGMGRL> show configuration;
DGMGRL> reinstate database proddb;
Reinstating database "proddb", please wait...
Operation requires shutdown of instance "proddb" on database "proddb"
Shutting down instance "proddb"...ORA-01109: database not open
Database dismounted.ORACLE instance shut down.
Operation requires startup of instance "proddb" on database "proddb"
Starting instance "proddb"...ORACLE instance started.
Database mounted.
Continuing to reinstate database "proddb" ...
Reinstatement of database "proddb" succeededVerify proddb post reinstate: The best part is broker will automatically recover earlier failed primary proddb, mount the database and start MRP too
On failed primary (proddb):
===========================
select name,open_mode from v$database;
select process, status, sequence# from v$managed_standby;Switchover to get original configuration: At this stage, you can perform switchover to again get back original configuration
On current primary (proddb_st):
===============================
dgmgrl sys/sys@proddb_st
DGMGRL> switchover to proddb;Enjoy!