Creating A Dataguard Broker Configuration Using DGMGRL

      In this article we will see step by step instructions on how to configure dataguard broker configuration using DGMGRL utility by using Command Line. The dataguard broker makes it easy to maintain and administer several standby databases. Dataguard broker maintains its own configuration files and runs a background process DMON both on primary and standby database servers. In the present article we are using DGMGRL to create dataguard broker configuration to maintain standby databases.

Set up

HostName	HostA	HostB
Database Name	TDEENC	TDEENC
DB Unique Name	TDEENC	TDESTBY
DB Version	12.1.0.2	12.1.0.2
Domain	WORLD	WORLD
Net Service Name	TDEENC.WORLD	TDESTBY.WORLD

1)  Clear LOG_ARCHIVE_DEST_2 on Primary and Standby databases (If already set )
2)  Make Changes in listener.ora  on Primary  for static listener registration
3) Restart the Listener or Reload listener on Primary
4) Make Changes in listener.ora  on Standy   for static listener registration
5) Restart the Listener or Reload listener on Standby
6) Prepare Primary Site ( Modify init.ora )
7) Prepare Standby Site  ( Modify init.ora )
8) start the broker on Primary and Standby  database
9) Verify the Connection through TNSNAMES using dgmgrl
10) you can now create the configuration using dgmgrl
11)  Enable Configuration 
12)  switch over to standby (Optional Step)
13)  switchback to Primary (Optional Step)

1.       Clear LOG_ARCHIVE_DEST_2 on Primary and Standby databases.

To add the dg broker configuration clear the existing log_archive_dest_* parameter.

In the existing setup destination 2  for log shipping.

-- Primary

SQL>  alter system set LOG_ARCHIVE_DEST_2='' scope=both;

System altered.

-- Standby

SQL>  alter system set LOG_ARCHIVE_DEST_2='' scope=both;

System altered.

2)  Make Changes in listener.ora  on Primary  for static listener registration

GLOBAL_DBNAME should be set to <>_DGMGRL.<> in listener.ora    on all instances of both primary and standby.

SID_LIST_LISTENER =

  (SID_LIST =

   (SID_DESC =

        (GLOBAL_DBNAME = TDEENC_DGMGRL.WORLD)

        (ORACLE_HOME = /u00/app/oracle/product/12.1.0.2/DB)

        (SID_NAME = TDEENC)

        (SERVICE_NAME = TDEENC.WORLD)

   )

 )

Step 3) Restart the Listener or Reload listener

$ srvctl stop listener

$ srvctl start listener

                  (or)

lsnrctl reload listener

-- reload listener

> lsnrctl status |grep -i TDE

Service "TDEENC_DGMGRL.WORLD" has 1 instance(s).

  Instance "TDEENC", status UNKNOWN, has 1 handler(s) for this service...

 Make sure Service Status Should show UNKNOWN on primary .

Step 4)  Make Changes in listener.ora  on Standy   for static listener registration

GLOBAL_DBNAME should be set to <>_DGMGRL.<> in listener.ora    on all instances of both primary and standby.

SID_LIST_LISTENER =

  (SID_LIST =

   (SID_DESC =

     (GLOBAL_DBNAME=TDESTBY_DGMGRL.WORLD)

     (ORACLE_HOME=/u00/app/oracle/product/12.1.0.2/DB)

     (SID_NAME=TDESTBY)

     (SERVICE_NAME=TDESTBY.WORLD)

   )

  )

Step 5) Restart the Listener or Reload listener on Standby

$ srvctl stop listener

$ srvctl start listener

                  (or)

lsnrctl reload listener

-- reload listener

> lsnrctl status |grep -i TDE

Service "TDESTBY_DGMGRL.WORLD" has 1 instance(s).

  Instance "TDESTBY", status UNKNOWN, has 1 handler(s) for this service...

-- Make sure Service Status Should show UNKNOWN on both primary and standby.You should verify that the DGMGRL services are visible in the output of “lsnrctl status” on each node. If not, go back and fix!

Step 6) Prepare Primary Site 

Init.ora Changes: DB_BROKER_CONFIG_FILEn parameter is used to specify the location of the dataguard configuration. DG_BROKER_START parameter is used to start the broker automatically when the instance starts.

SQL> alter system set dg_broker_config_file1 = '+DATA/TDEENC/dr1TDEENC.dat' scope=both;

SQL> alter system set dg_broker_config_file2 = '+DATA/TDEENC/dr2TDEENC.dat' scope=both;

Step 7) Prepare Standby Site 

SQL> alter system set dg_broker_config_file1 = '+DATA/TDESTBY/dr1TDESTBY.dat' scope=both;

SQL> alter system set dg_broker_config_file2 = '+DATA/TDESTBY/dr2TDESTBY.dat' scope=both;

Step 8) start the broker on Primary and Standby  database

-- on Primary 

SQL> alter system set dg_broker_start=TRUE;

-- on Standby 

SQL> alter system set dg_broker_start=TRUE;

Step 9) Verify the Connection through TNSNAMES using dgmgrl

-- Verify the Connections from primary database server 

i)  Connect as sys to the primary database and create the configuration

dgmgrl SYS/XXXXXXX@TDEENC.WORLD

Connected as SYSDBA.

ii)  Connect as sys to the Standby database and create the configuration

dgmgrl SYS/XXXXXXX@TDESTBY.WORLD

Connected as SYSDBA.  

Step 10) you can now create the configuration using dgmgrl

Connect as sys to the primary database and create the configuration

dgmgrl SYS/XXXXXXX@TDEENC.WORLD

Connected as SYSDBA.  

 i)  Add Primary Database

DGMGRL> CREATE CONFIGURATION TDE_dg_config AS PRIMARY DATABASE IS TDEENC CONNECT IDENTIFIER IS TDEENC.WORLD;

Configuration "tde_dg_config" created with primary database "tdeenc"

DGMGRL>  show configuration

Configuration - tde_dg_config

  Protection Mode: MaxPerformance

  Members:

  tdeenc - Primary database

Fast-Start Failover: DISABLED

Configuration Status:

DISABLED

ii) Add Standby Database to DGMGRL Cofniguration

DGMGRL>   ADD DATABASE TDESTBY AS CONNECT IDENTIFIER IS TDESTBY.WORLD MAINTAINED

Database "tdestby" added

DGMGRL> show configuration;

Configuration - tde_dg_config

  Protection Mode: MaxPerformance

  Members:

  tdeenc  - Primary database

    tdestby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

DISABLED  

Step 11)  Enable Configuration 

DGMGRL> ENABLE CONFIGURATION;

Enabled.

DGMGRL> show configuration

Configuration - tde_dg_config

  Protection Mode: MaxPerformance

  Members:

  tdeenc  - Primary database

    tdestby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS   (status updated 17 seconds ago)     

Step 12)  switch over to standby   (Optional Step if required only you can perform ) 

DGMGRL>  switchover to "tdestby"

Performing switchover NOW, please wait...

Operation requires a connection to instance "TDESTBY" on database "tdestby"

Connecting to instance "TDESTBY"...

Connected as SYSDBA.

New primary database "tdestby" is opening...

Oracle Clusterware is restarting database "tdeenc" ...

Switchover succeeded, new primary is "tdestby"

Step 13)  switchback to Primary     (Optional Step if required only you can perform ) 

DGMGRL>  switchover to "tdeenc";

Performing switchover NOW, please wait...

Operation requires a connection to instance "TDEENC" on database "tdeenc"

Connecting to instance "TDEENC"...

Connected as SYSDBA.

New primary database "tdeenc" is opening...

Oracle Clusterware is restarting database "tdestby" ...

Switchover succeeded, new primary is "tdeenc"