Oracle DG Broker Configuration - Data Guard Broker to manage oracle standby

Dataguard Broker configuration

1. Add listener as below in listener.ora (example is from Primary node1)

LISTENER_DG=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_DG))))

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_DG=ON

SID_LIST_LISTENER_DG =

   (SID_LIST=

      (SID_DESC=

        (SID_NAME=t1enb3d31)

        (GLOBAL_DBNAME=t1enb3d3_DGMGRL.india.com)

        (ORACLE_HOME=/opt/app/t1enb3d5/oracle/product/11.2.0.4)

       )

      (SID_DESC=

        (SID_NAME=t1enb3d41)

        (GLOBAL_DBNAME=t1enb3d4_DGMGRL.india.com)

        (ORACLE_HOME=/opt/app/t1enb3d5/oracle/product/11.2.0.4)

       )

      (SID_DESC=

        (SID_NAME=t1enb3d51)

        (GLOBAL_DBNAME=t1enb3d5_DGMGRL.india.com)

        (ORACLE_HOME=/opt/app/t1enb3d5/oracle/product/11.2.0.4)

       )

      (SID_DESC=

        (SID_NAME=t1enb3d51)

        (GLOBAL_DBNAME=t1enb3d6_DGMGRL.india.com)

        (ORACLE_HOME=/opt/app/t1enb3d5/oracle/product/11.2.0.4)

       )

      (SID_DESC=

        (SID_NAME=t1enb3d71)

        (GLOBAL_DBNAME=t1enb3d7_DGMGRL.india.com)

        (ORACLE_HOME=/opt/app/t1enb3d5/oracle/product/11.2.0.4)

       )

      (SID_DESC=

        (SID_NAME=t1enb3d81)

        (GLOBAL_DBNAME=t1enb3d8_DGMGRL.india.com)

        (ORACLE_HOME=/opt/app/t1enb3d5/oracle/product/11.2.0.4)

       )

    )

2. Once listener.ora changes done, add listener_dg as crs

srvctl add listener -l LISTENER_DG -o /opt/app/oragrid/oracle/product/11.2.0.4 -p 1522

3. start listener

srvctl start listener -l LISTENER_DG

4. create below directories

example for primary t1enb3d5

+T1ENB3D5_ORAFLSH/T1ENB3D5/DATAGUARDCONFIG

+T1ENB3D5_ORADATA/T1ENB3D5/DATAGUARDCONFIG

example for standby t2enb4d4

+T2ENB4D4_ORAFLSH/T2ENB4D4/DATAGUARDCONFIG

+T2ENB4D4_ORADATA/T2ENB4D4/DATAGUARDCONFIG

5. add below parameters on databases from sql prompt (please note the names for DG boker config files I am using environment names for easy identification)

Primary (t1enb3d5)

alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=blth078-vip.india.com)(PORT=1521))','(ADDRESS=(PROTOCOL=TCP)(HOST=blth078-vip.india.com)(PORT=1522))' scope=both sid='t1enb3d51';

alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=blth079-vip.india.com)(PORT=1521))','(ADDRESS=(PROTOCOL=TCP)(HOST=blth079-vip.india.com)(PORT=1522))' scope=both sid='t1enb3d52';

ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1='+T1ENB3D5_ORAFLSH/T1ENB3D5/DATAGUARDCONFIG/T1ENB3D5_01.ora' SCOPE=BOTH sid='*';

ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2='+T1ENB3D5_ORADATA/T1ENB3D5/DATAGUARDCONFIG/T1ENB3D5_02.ora' SCOPE=BOTH sid='*';

alter system set dg_broker_start=true scope=both sid='*';

standby (t2enb4d4) (please note the names for DG boker config files I am using environment names for easy identification -> kept same as primary)

alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=blth046-vip.india.com)(PORT=1521))','(ADDRESS=(PROTOCOL=TCP)(HOST=blth047-vip.india.com)(PORT=1522))' scope=both sid='t2enb4d41';

alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=blth047-vip.india.com)(PORT=1521))','(ADDRESS=(PROTOCOL=TCP)(HOST=blth047-vip.india.com)(PORT=1522))' scope=both sid='t2enb4d42';

ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1='+T2ENB4D4_ORAFLSH/T2ENB4D4/DATAGUARDCONFIG/T1ENB3D5.ora' SCOPE=BOTH sid='*';

ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2='+T2ENB4D4_ORADATA/T2ENB4D4/DATAGUARDCONFIG/T1ENB3D5.ora' SCOPE=BOTH sid='*';

alter system set dg_broker_start=true scope=both sid='*';

6. create DGMGRL configuration as below (please note the names I am using environment names for easy identification -> kept same as primary)

DGMGRL> connect sys/January#2016

Connected.

DGMGRL> CREATE CONFIGURATION 'TESTING_DG' AS PRIMARY DATABASE IS 't1enb3d5' CONNECT IDENTIFIER IS 't1enb3d5';

DGMGRL> ADD DATABASE 't2enb4d4' AS CONNECT IDENTIFIER IS t2enb4d4;

DGMGRL> show configuration;

7. Enable the configuration

DGMGRL> enable configuration;

DGMGRL> show configuration;

Configuration - TESTING_DG

  Protection Mode: MaxPerformance

  Databases:

    t1enb3d5 - Primary database

    t2enb4d4 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

DGMGRL> show database t1enb3d5

Database - t1enb3d5

  Role:            PRIMARY

  Intended State:  TRANSPORT-ON

  Instance(s):

    t1enb3d51

    t1enb3d52

Database Status:

SUCCESS

DGMGRL> show database t2enb4d4

Database - t2enb4d4

  Role:            PHYSICAL STANDBY

  Intended State:  APPLY-ON

  Transport Lag:   0 seconds (computed 1 second ago)

  Apply Lag:       0 seconds (computed 1 second ago)

  Apply Rate:      92.05 MByte/s

  Real Time Query: ON

  Instance(s):

    t2enb4d41 (apply instance)

    t2enb4d42

Database Status:

SUCCESS