Reconfigure Broken DGMGRL configuration

Sometimes the broker configuration might break due to which either we start getting lag alert and also the standby database status start reflecting as disable in DGMGRL and it may happen in a situation like patching the database using gold image, non-asm to asm migration, create standby database from scratch after failure. So, to resolve this issue we’ve to reconfigure the broker configuration.

 How The broken dgmgrl configuration look like

 DGMGRL> show configuration

ORA-16532: Oracle Data Guard configuration does not exist

OR

DGMGRL> show configuration

Configuration - < configuration_file_name>

      Protection Mode: MaxPerformance

       Members:

        PROD – Primary Database

        STDBY – Physical standby database (disable)

             ORA-16906: The member was shutdown.

        STDBY2 – Physical Standby database (disable)

              ORA-16906: The member was shutdown.

Fast-Start Failover: Disabled

Configuration Status:

SUCCESS (status updated 17 seconds ago)

 

 How To Reconfigure Broken DGMGRL configuration

 STEP 1: Set dg_broker_start parameter as false in primary & standby

 SQL> alter system set dg_broker_start=false scope=both sid=’*’;

 STEP 2: Change the broker configuration file location, if the issue is related to file location.

Example: Suppose, we’d performed a database patch using gold image and by mistakenly we forgot to move the broker configuration file into the new home then in that case the broker configuration will break and we’ve to move the configuration file into the new location in addition to add the configuration with dgmgrl utility.

 SQL> show parameter dg_broker_config

 OLD LOCATION

NAME                        TYPE           VALUE

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

dg_broker_config_file_1       string        /oracle/product/19.0.0/db_home/dr1PROD.dat

dg_broker_config_file_2       string        /oracle/product/19.0.0/db_home/dr2PROD.dat

SQL> alter system set dg_broker_config_file_1=’/oracle/PROD/datafile/dr1PROD.dat’ scope=both sid=’*’;

 SQL> alter system set dg_broker_config_file_2=’/oracle/PROD/datafile/dr2PROD.dat’ scope=both sid=’*’;

 STEP 3: Create new configuration in broker

 DGMGRL > create CONFIGURATION DG_PROD AS PRIMARY DATABASE IS PROD CONNECT IDENTIFIER IS PROD;

DGMGRL> ADD DATABASE STDBY1 AS CONNECT IDENTIFIER IS STDBY1 MAINTAINED AS PHYSICAL;

DGMGRL> ADD DATABASE STDBY2 AS CONNECT IDENTIFIER IS STDBY2 MAINTAINED AS PHYSICAL;

 STEP 4: SET dg_broker_start parameter as TRUE in primary and standby both

 SQL> alter system set dg_broker_start=true scope=both sid=’*’;

 STEP 5: ENABLE CONFIGURATION

DGMGRL > ENABLE CONFIGURATION

 DGMGRL> show configuration

Configuration - DG_PROD

      Protection Mode: MaxPerformance

       Members:

        PROD – Primary Database

         STDBY – Physical standby database

         STDBY2 – Physical Standby database

Fast-Start Failover: Disabled

Configuration Status:

SUCCESS (status updated 1 seconds ago)