In an Oracle Data Guard setup, sometimes it is necessary to recreate the broker configuration. We will look at how to do that in this article.
The following steps were done on a single instance database (non RAC).
1. Check location of the Broker configuration on the Primary
Connect to sqlplus on the Primary
[oracle@PRIM]$ sqlplus / as sysdba
SQL> show parameter dg_broker_config
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string /u01/app/oracle/product/11.2.0
.2/db_1/dbs/dr1primdb.dat
dg_broker_config_file2 string /u01/app/oracle/product/11.2.0
.2/db_1/dbs/dr2primdb.dat
2. Stop the Broker on the Primary
SQL> ALTER SYSTEM SET DG_BROKER_START=FALSE SCOPE=BOTH;
System altered.
3. Check location of the Broker configuration on the Standby
[oracle@STBY]$ sqlplus / as sysdba
SQL> show parameter dg_broker_config
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string /u01/app/oracle/product/11.2.0
.2/db_1/dbs/dr1stbydb.dat
dg_broker_config_file2 string /u01/app/oracle/product/11.2.0
.2/db_1/dbs/dr2stbydb.dat
4. Stop the broker on the Standby
[oracle@STBY]$ sqlplus / as sysdba
SQL> ALTER SYSTEM SET DG_BROKER_START=FALSE SCOPE=BOTH;
System altered.
5. Remove old Broker configuration from both Primary and Standby
The locations were obtained from the show parameter dg_broker_config command above.
ON PRIMARY
[oracle@PRIM ~]$ cd /u01/app/oracle/product/11.2.0.2/db_1/dbs/
[oracle@PRIM dbs]$ rm dr1primdb.dat dr2primdb.dat
ON STANDBY
[oracle@STBY ~]$ cd /u01/app/oracle/product/11.2.0.2/db_1/dbs/
[oracle@STBY dbs]$ rm dr1stbydb.dat dr2stbydb.dat
6. Start the Broker on both Primary and Standby
ON PRIMARY
[oracle@PRIM]$ sqlplus / as sysdba
SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;
System altered.
ON STANDBY
[oracle@STBY]$ sqlplus / as sysdba
SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;
System altered.
7. Connect to DGMGRL on the Primary
[oracle@PRIM dbs]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.2.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys
Password:
Connected.
8. Create configuration on Primary
You need to know the name of the Oracle Data Guard Broker configuration that you want to create, the name of the primary database, and the net service name through which to connect.
DGMGRL> CREATE CONFIGURATION 'test_cfg' AS PRIMARY DATABASE IS 'primdb' CONNECT IDENTIFIER IS primdb;
Configuration "test_cfg" created with primary database "primdb"
DGMGRL>
9. Add the Standby to the configuration
You need to know the name of the standby database, the net service name through which to connect, and the type of standby (physical or logical).
DGMGRL> ADD DATABASE 'stbydb' AS CONNECT IDENTIFIER IS 'stbydb' MAINTAINED AS PHYSICAL;
Database "stbydb" added
10. Enable the configuration
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;
Configuration - test_cfg
Protection Mode: MaxPerformance
Databases:
primdb - Primary database
stbydb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> show database stbydb;
Database - stbydb
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: OFF
Instance(s):
stbydb
Database Status:
SUCCESS
DGMGRL> show database primdb;
Database - primdb
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
primdb
Database Status:
SUCCESS