Oracle Data Guard fail over using Broker

This topic  demonstrates how to  fail over on Oracle 11g data guard environment.

The below scenario is tested in 11.2.0.4 version. The following database is used for fail over testing.

Data Guard	Database
Primary	devdb12
Standby	devdb12_dg

The manual fail over is tested from primary(devdb12) to standby(devdb12_dg). 

Things to check prior to fail over :

Make sure, application is down and no application connection on the database.

Verify the data guard replication  and see standby is close to primary. we need to ensure that, all the archive logs are shipped to standby and applied successfully. If not, then there will be data loss after fail over.

If we  have multiple standby, then it is better to choose the standby which has highest log sequence# to  minimize the data loss.

Generally Fail over can be done for the following circumstances

• Primary  is crashed
• Primary is not performing well
• Primary database is in mount mode and not able to open the database  

Prerequisite : The data guard broker should be configured on the database.  Click  here to review the broker configuration steps in case you need.

>dgmgrl

DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect /

Connected.

DGMGRL> show configuration;

Configuration - devdb12_dg_broker

 Protection Mode: MaxPerformance

  Databases:

    chfsdb43    - Primary database

      Error: ORA-16810: multiple errors or warnings detected for the database

    devdb12_dg - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

ERROR

DGMGRL> failover to devdb12_dg;

Performing failover NOW, please wait...

Error: ORA-16798: unable to complete recovery during failover

Failed.

Unable to failover

DGMGRL> failover to devdb12_dg immediate;

Performing failover NOW, please wait...

Failover succeeded, new primary is "devdb12_dg"

DGMGRL> exit

After fail over, we need to rebuild the standby or flash back to prior stage and apply the archive log files.

Oracle Data Guard switch over using Broker

I am covering switch over steps(using Broker) on this topic. The below step is tested in Oracle 11.2.0.4 version.

Primary database : devdb12
Standby database : devdb12_dg

Just in case if you want to do manual switch over, refer this Click

Prerequisite : The data guard broker should be configured on the database.  Click  here to review the broker configuration steps in case you need. Also double check the archive log files and  make sure standby is not behind. Otherwise, switch over might take long time and it will cause longer application outage.

Step 1 Just make sure application is down and no other connection on the database.

Run this query and verify the DB connections.

select count(*),username from v$session
group by username;

Some times, if there is a lot of activity on the database, oracle LOCAL process’ may optionally be killed. Here is the sample script using example of chfdallindb41/42.

$tokill=`ps -ef | grep LOCAL=NO | grep -v grep | awk '{print $2}'`; echo $tokill;

6809 6811 6813 6815 6817 6819 6873 6941

$tokill=`ps -ef | grep LOCAL=NO | grep -v grep | awk '{print $2}'`; kill -9 $tokill;

Step 2  Login to devdb12 and connect as sys using broker

switchover to devdb12_dg ;

Watch out the alert log and make sure replicating the data between devdb12_dg to devdb12.

Step 3 It is optional step. Just in case if you want to switch back to old primary.

switchover to devdb12

Watch out the alert log and make sure replicating the data between devdb12  to devdb12_dg.