HOW TO RECREATE DGBROKER CONFIGURATION

Step By Step How to Recreate Dataguard Broker Configuration on Windows and Unix.

Step 1: Stop DG_BROKER on both primary and standby database ( in all instances in case of RAC)

Step 2: Drop old broker configuration files from both primary and standby.

Step 3: Start the DG_BROKER on both primary and standby database

Step 4: create configuration on primary

Step 5: Add standby 

Step 6: Enable configuration

Step 7: Show configuratoin

Step 1: Stop DG_BROKER on both primary and standby database ( in all instances in case of RAC)

ALTER SYSTEM SET DG_BROKER_START=FALSE;

SQL> 

show parameter dg_broker_config_file

NAME                   TYPE   VALUE

---------------------- ------ ----------------------------------------

dg_broker_config_file1 string \<PATH>\<FILE_NAME>.dat

dg_broker_config_file2 string \<PATH>\<FILE_NAME>.dat

Step 2: Drop old broker configuration files from both primary and standby.

ASMCMD

cd <path>

rm <filename.dat>

rm <filename.dat>

SQL>

Step 3: Start the DG_BROKER on both primary and standby database

ALTER SYSTEM SET DG_BROKER_START=TRUE;

Step 4: create configuration on primary

DGMGRL>

connect sys/password

DGMGRL> CREATE CONFIGURATION '<CONFIGURATION_NAME>' AS PRIMARY DATABASE IS '<PRIMARY_DATABASE_NAME>' CONNECT IDENTIFIER IS <CONNECT_IDENTIFIER>;

Configuration "<CONFIGURATION_NAME>" created with primary database "<PRIMARY_DATABASE_NAME>"

Step 5: Add standby 

DGMGRL> 

ADD DATABASE '<STANDBY_DATABASE_NAME>' AS CONNECT IDENTIFIER IS <CONNECT_IDENTIFIER> MAINTAINED AS PHYSICAL;

Database "<STANDBY DATABASE NAME>" added

Step 6: Enable configuration

DGMGRL>

ENABLE CONFIGURATION;

Step 7: Show configuratoin

SHOW CONFIGURATION;



SQL>
ALTER SYSTEM SET DG_BROKER_START=FALSE;SQL>

System altered.

SQL> show parameter dg_broker_config_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u01/app/oracle/product/19.3.0
                                                 .0/dbhome_1/dbs/dr1DB193H1.dat
dg_broker_config_file2               string      /u01/app/oracle/product/19.3.0
                                                 .0/dbhome_1/dbs/dr2DB193H1.dat
SQL> !rm /u01/app/oracle/product/19.3.0.0/dbhome_1/dbs/dr1DB193H1.dat /u01/app/oracle/product/19.3.0.0/dbhome_1/dbs/dr2DB193H1.dat

SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE;

System altered.


DGMGRL> CREATE CONFIGURATION 'db_broker_config' AS PRIMARY DATABASE IS 'db193h1' CONNECT IDENTIFIER IS db193h1;
Configuration "db_broker_config" created with primary database "db193h1"
DGMGRL> ADD DATABASE 'db193h1_stdby' AS CONNECT IDENTIFIER IS db193h1_stdby MAINTAINED AS PHYSICAL;
Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set

Failed.
DGMGRL> ADD DATABASE 'db193h1_stdby' AS CONNECT IDENTIFIER IS db193h1_stdby MAINTAINED AS PHYSICAL;
Database "db193h1_stdby" added
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL> ENABLE CONFIGURATION;
Enabled.

Note : For 12c DB Redo log shipping should Not be configured at the Primary and Standby while configuring Broker so you need to set log_archive_dest_n to Null at Primary and Standby.

Else will get the below error while Adding the DB in Broker

Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added"

DGMGRL> CREATE CONFIGURATION 'db_broker_config' AS PRIMARY DATABASE IS 'db193h1' CONNECT IDENTIFIER IS db193h1;
Configuration "db_broker_config" created with primary database "db193h1"
DGMGRL> ADD DATABASE 'db193h1_stdby' AS CONNECT IDENTIFIER IS db193h1_stdby MAINTAINED AS PHYSICAL;
Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set