DG Broker Configuration

How to setup Data Guard Broker Configuration

Pre-requisites: Configre Physical Standby database. Please click here

0. Enviroment
1. Check the current state of DG Broker on Primary Database
2. Check the current state of DG Broker on Standby Database
3. Vefiry GAP
4. Enable DG broker
5. Modify Listener.ora
6. Create the Dataguard Broker configuration on primary


0. Enviroment

Source:

		Platform: Linuxx86_64
		Server Name: RAC1.RAJASEKHAR.COM, IP: 192.168.2.101
		DB Version: Oracle 11.2.0.3, File system: ASM
		DB Name: w5005pr, DB_UNIQUE_NAME: w5005pr
		Flashback: Disabled
		Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1
		
Target:
		
		Platform: Linuxx86_64
		Server Name: RAC2.RAJASEKHAR.COM, IP: 192.168.2.102
		DB Version: Oracle 11.2.0.3, , File system: ASM
		DB Name: w5005pr, DB_UNIQUE_NAME: w5005prg
		Flashback: Disabled
		Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1


1. Check the current state of DG Broker on Primary Database

SQL> show parameter DG_BROKER_START

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     FALSE
SQL>


2. Check the current state of DG Broker on Standby Database

SQL> show parameter DG_BROKER_START

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     FALSE
SQL>


3. Vefiry GAP

SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    PROTECTION_MODE
------------ ---------------- ---------------- --------------------
OPEN         w5005pr          PRIMARY          MAXIMUM PERFORMANCE

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
            35

SQL>


Standby Side

SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    PROTECTION_MODE
------------ ---------------- ---------------- --------------------
OPEN         w5005prg         PHYSICAL STANDBY MAXIMUM PERFORMANCE

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
            35

SQL>


4. Enable DG broker

On Primary

SQL> alter system set dg_broker_start=true;

System altered.


SQL> show parameter dg_broker_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u01/app/oracle/product/11.2.0
                                                 /db_1/dbs/dr1w5005pr.dat
dg_broker_config_file2               string      /u01/app/oracle/product/11.2.0
                                                 /db_1/dbs/dr2w5005pr.dat
SQL>

On Standby

SQL> alter system set dg_broker_start=true;

System altered.

SQL> show parameter dg_broker_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u01/app/oracle/product/11.2.0
                                                 /db_1/dbs/dr1w5005prg.dat
dg_broker_config_file2               string      /u01/app/oracle/product/11.2.0
                                                 /db_1/dbs/dr2w5005prg.dat
SQL>


5. Modify Listener.ora

Add a static entry for the DGMGRL in the listener.ora on both the primary and standby servers.

On Primary

(SID_DESC =
 (GLOBAL_DBNAME=w5005pr_dgmgrl)
 (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
 (SID_NAME=w5005pr)
)

On Standby 

(SID_DESC =
 (GLOBAL_DBNAME=w5005prg_dgmgrl)
 (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
 (SID_NAME=w5005prg)
)

Listener.ora file contents

Primary

[oracle@rac1 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/11.2.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent

SID_LIST_LISTENER =
(SID_LIST =
        (SID_DESC =
                (GLOBAL_DBNAME = w5005pr)
                (SID_NAME = w5005pr)
                (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
        )
)

(SID_DESC =
 (GLOBAL_DBNAME=w5005pr_dgmgrl)
 (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
 (SID_NAME=w5005pr)
)
[oracle@rac1 admin]$

On Standby

[oracle@rac2 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/11.2.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent

SID_LIST_LISTENER =
(SID_LIST =
        (SID_DESC =
                (GLOBAL_DBNAME = w5005prg)
                (SID_NAME = w5005prg)
                (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
        )
)

(SID_DESC =
 (GLOBAL_DBNAME=w5005prg_dgmgrl)
 (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
 (SID_NAME=w5005prg)
)
[oracle@rac2 admin]$


6. Create the Dataguard Broker configuration. On the primary

[oracle@rac1 ~]$ which dgmgrl
/u01/app/oracle/product/11.2.0/db_1/bin/dgmgrl
[oracle@rac1 ~]$ 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 sys/sys@w5005pr
Connected.
DGMGRL> create configuration 'w5005pr' as primary database is 'w5005pr' connect identifier is w5005pr;
Configuration "w5005pr" created with primary database "w5005pr"
DGMGRL>
DGMGRL> show configuration;

Configuration - w5005pr

  Protection Mode: MaxPerformance
  Databases:
    w5005pr - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> add database 'w5005prg' as connect identifier is w5005prg maintained as physical;
Database "w5005prg" added
DGMGRL>
DGMGRL> show configuration;

Configuration - w5005pr

  Protection Mode: MaxPerformance
  Databases:
    w5005pr  - Primary database
    w5005prg - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;

Configuration - w5005pr

  Protection Mode: MaxPerformance
  Databases:
    w5005pr  - Primary database
    w5005prg - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.