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.