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.

How to Configure the Data Guard broker?

Data Guard broker is a nice tool to switch over and fail over the database in data guard environment. Using data guard broker is like a driving a car with navigation system. Hence, if broker fails in the middle of switch over, then we don't know where we are and we got to troubleshoot and see which step it failed.

I personally use manual switch over and of course there are some database i use broker too.

How do we configure the broker?  Please follow these steps.

My Database info :

Primary database devdb12

Standby database  devdb12_dg

Step 1  Configuring the broker parameters

Set initialization parameter DG_BROKER_START to a value of TRUE on both databasesso that Data Guard Broker background process (DMON) would start automatically whenever a Data Guard configuration was successfully implemented.

ALTER SYSTEM SET dg_broker_start = TRUE;

Broker creates two data file to store the information. It creates on the default directory$ORACLE_HOME/dbs.  We can also change the directory by using the below command.

alter system set dg_broker_config_file1 = '/data01/dbArch/' scope=both;

alter system set dg_broker_config_file2 = '/data01/dbArch/' scope=both;

Step 2   Configuring the listener.

During actions like a swichtover and a failover the Dataguard Broker interface will stop and start the instances. The Dataguard Broker Interface needs static registration of the databases in the listener.ora files. The required format is db_unique_name_DGMGRL[.db_domain].

The below entry should be in primary database listener.ora file. Listener should be up and running.

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 99.999.99.999)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

SID_LIST_LISTENER =

  (SID_LIST =

   (SID_DESC =

      (GLOBAL_DBNAME = devdb12)

      (ORACLE_HOME = /ora/app/oracle/product/11.2.0/db_1)

      (SID_NAME = devdb12)

    )

   (SID_DESC =

      (GLOBAL_DBNAME = devdb12_dgmgrl)

      (ORACLE_HOME = /ora/app/oracle/product/11.2.0/db_1)

      (SID_NAME = devdb12)

    )

)

The below entry should be in standby database listener.ora file. Listener should be up and running.

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 99.999.99.999)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

SID_LIST_LISTENER =

  (SID_LIST =

   (SID_DESC =

      (GLOBAL_DBNAME = devdb12_dg)

      (ORACLE_HOME = /ora/app/oracle/product/11.2.0/db_1)

      (SID_NAME = devdb12_dg)

    )

   (SID_DESC =

      (GLOBAL_DBNAME = devdb12_dg_dgmgrl)

      (ORACLE_HOME = /ora/app/oracle/product/11.2.0/db_1)

      (SID_NAME = devdb12_dg)

    )

)

Step 3   Create the broker for primary database. This command can be executed onlyon primary database.  Use the CREATE CONFIGURATION command to initialize a Data Guard Broker configuration named devdb12_broker, and confirm its creation with the SHOW CONFIGURATION command:

create configuration 'devdb12_broker' as

primary database is devdb12

connect identifier is devdb12;

show configuration

Step 4 Add the broker for standby database. This command can be excuted only on primary database. If we have three standby then, we need to add broker config for three times on the primary database.

add database devdb12_dg as

 connect identifier is devdb12_dg

 maintained as physical;

show configuration

Step 5  Enabling the broker

Issue the ENABLE CONFIGURATION command to activate the configuration and then confirm the successful activation of the primary and standby databases via the SHOW DATABASE command:

enable configuration;

show database devdb12
show database devdb12_dg

Show configuration

Now you are done with Broker setup! :-)