fix dataguard broker configuration

Steps to rebuild dataguard broker configuration

Issue with dgmgrl configuration

DGMGRL> show configuration;

Configuration - apps

  Protection Mode: MaxPerformance
  Databases:
    appsnew_dca - Primary database
      Error: ORA-16810: multiple errors or warnings detected for the database

    appsnew_vgs - Physical standby database
      Error: ORA-16810: multiple errors or warnings detected for the database

    apps_lax    - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
ERROR

Viewing exact database error

DGMGRL> show database appsnew_dca;

Database - appsnew_dca

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    APPS
      Error: ORA-16782: instance not open for read and write access

  Database Error(s):
    ORA-16816: incorrect database role

Database Status:
ERROR

DGMGRL> show database appsnew_vgs;

Database - appsnew_vgs

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   (unknown)
  Apply Lag:       (unknown)
  Apply Rate:      (unknown)
  Real Time Query: OFF
  Instance(s):
    APPS

  Database Error(s):
    ORA-16816: incorrect database role
    ORA-16700: the standby database has diverged from the primary database
    ORA-16766: Redo Apply is stopped

  Database Warning(s):
    ORA-16858: last communication time from redo source could not be determined

Database Status:
ERROR

Steps to remove existing dataguard broker configuration :

On primary and standby dbs

DGMGRL> disable configuration;
 Disabled.

SQL> alter system set dg_broker_start=FALSE;

System altered.
SQL> show parameter dg_broker
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u01/app/oracle/product/112/dbs/dr1APPSNEW_DCA.dat
dg_broker_config_file2               string      /u01/app/oracle/product/112/dbs/dr2APPSNEW_DCA.dat
dg_broker_start                      boolean     FALSE

mv /u01/app/oracle/product/112/dbs/dr1APPSNEW_DCA.dat /u01/app/oracle/product/112/dbs/dr1APPSNEW_DCA.dat.bak
mv /u01/app/oracle/product/112/dbs/dr2APPSNEW_DCA.dat /u01/app/oracle/product/112/dbs/dr2APPSNEW_DCA.dat.bak

SQL> alter system set dg_broker_start=TRUE;
System altered.

Steps to configure Data guard broker :

On Primary, connect as sys user and add configuration :

DGMGRL> show configuration;
ORA-16532: Data Guard broker configuration does not exist

Configuration details cannot be determined by DGMGRL

DGMGRL> connect sys
Password:
Connected.

DGMGRL> create configuration 'apps' as primary database is 'appsnew_vgs' connect identifier is 'appsnew_vgs';
Configuration "apps" created with primary database "appsnew_vgs"
DGMGRL> show configuration;

Configuration - apps

  Protection Mode: MaxPerformance
  Databases:
    appsnew_vgs - Primary database

DGMGRL> add database 'appsnew_dca' as connect identifier is 'appsnew_dca';
Database "appsnew_dca" added

DGMGRL> add database 'apps_lax' as connect identifier is 'apps_lax';
Database "apps_lax" added

Verify and enable configuration :

DGMGRL> show configuration;

Configuration - apps

  Protection Mode: MaxPerformance
  Databases:
    appsnew_vgs - Primary database
    appsnew_dca - Physical standby database
    apps_lax    - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> enable configuration;
Enabled.

 

While adding configuration we need to make sure to have a new service registered with the local listener in the format – <db_unique_name>_DGMGRL.<db_domain>

For example

APPS =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = mydb.domain.com)(PORT = 1521))
    )
  )
 
SID_LIST_APPS =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME= /u01/app/oracle/product/112)
      (SID_NAME = APPS)
    )
    (SID_DESC =
      (SID_NAME = APPS)
      (GLOBAL_DBNAME=appsnew_vgs_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/112)
    )
  )