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
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>
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>
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>
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.