DGMGRL: How to enable BROKER (19c)

On this practice, we are going to configure DATAGUARD BROKER in a new PRIMARY/STANDBY configuration and see what errors we get since this is a new setup.

This is our current lab:

primary:

INST HOST		INST_NAME  STATUS     DB_ROLE		OPEN_MODE	      STARTUP_TIME
---- ------------------ ---------- ---------- ----------------- --------------------- --------------------
*1   RACnode1		test19c1   OPEN       PRIMARY		READ WRITE	      30-OCT-2021 21:31:17
2    RACnode2		test19c2   OPEN       PRIMARY		READ WRITE	      30-OCT-2021 21:40:51

standby:

INST HOST		INST_NAME  STATUS     DB_ROLE		OPEN_MODE	      STARTUP_TIME
---- ------------------ ---------- ---------- ----------------- --------------------- --------------------
*1   standbyracnode	TESTSTB    OPEN       PHYSICAL STANDBY	READ ONLY WITH APPLY  30-OCT-2021 22:30:51

1:- Add the DG broker files to ASM on both primary and Standby:

= primary:
SQL> ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1 = '+DATA/TEST19C/dr1TEST19C.dat' SCOPE=BOTH;
System altered.
SQL> ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1 = '+RECO/TEST19C/dr2TEST19C.dat' SCOPE=BOTH;
System altered.
SQL> 

= standby:

SQL> ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1 = '+DATA/TESTSTB/dr1TESTSTB.dat' SCOPE=BOTH;
System altered.
SQL> ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1 = '+RECO/TESTSTB/dr2TESTSTB.dat' SCOPE=BOTH;
System altered.
SQL> 

2:- Start DG broker at DB level on both systems:

SQL> alter system set dg_broker_start=true scope=both sid='*';
System altered.

3:- Create and enable configuration. (Run on Primary side):

DGMGRL> connect /
Connected to "test19c"
Connected as SYSDG.
DGMGRL> CREATE CONFIGURATION DG_TEST19C AS PRIMARY DATABASE IS TEST19C CONNECT IDENTIFIER IS TEST19C;
Configuration "dg_test19c" created with primary database "test19c"
DGMGRL> ADD DATABASE TESTSTB AS CONNECT IDENTIFIER IS TESTSTB MAINTAINED AS PHYSICAL;
Error: ORA-12537: TNS:connection closed

Failed.
DGMGRL> 

First error! In this case, it was because in Standby DB, parameter local_listener was not set up:

SQL> show parameter local_

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
local_listener			     string	 -oraagent-dummy-
 

SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.24.33)(PORT=1521))';
System altered.
SQL> 

Now we can continue:

DGMGRL>  ADD DATABASE TESTSTB AS CONNECT IDENTIFIER IS TESTSTB MAINTAINED AS PHYSICAL;
Database "teststb" added
DGMGRL> 

DGMGRL> ENABLE CONFIGURATION;
Enabled.
DGMGRL> 

This should be it, but let’s see the current state:

DGMGRL> show configuration

Configuration - dg_test19c

  Protection Mode: MaxPerformance
  Members:
  test19c - Primary database
    Warning: ORA-16905: The member was not enabled yet.

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

Fast-Start Failover:  Disabled

Configuration Status:
ERROR   (status updated 1392 seconds ago)

DGMGRL> 

We need to enable test19c:

DGMGRL> enable database  'test19c'
Enabled.
DGMGRL> show configuration

Configuration - dg_test19c

  Protection Mode: MaxPerformance
  Members:
  test19c - Primary database
    teststb - Physical standby database 
      Warning: ORA-16809: multiple warnings detected for the member

Fast-Start Failover:  Disabled

Configuration Status:
WARNING   (status updated 52 seconds ago)

DGMGRL>

Lets see wha’s happening to teststb…

DGMGRL> VALIDATE DATABASE verbose 'teststb'
Error: ORA-16541: member is not enabled

DGMGRL> 
DGMGRL> enable database  'teststb'
Warning: ORA-16713: The Oracle Data Guard broker command timed out.

Enabled.
DGMGRL> 

We restarted Standby after enabling member and now the BROKER is not reporting any error:

DGMGRL>  show configuration

Configuration - dg_test19c

  Protection Mode: MaxPerformance
  Members:
  test19c - Primary database
    teststb - Physical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 10 seconds ago)

DGMGRL> show database 'teststb'

Database - teststb

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 6.00 KByte/s
  Real Time Query:    ON
  Instance(s):
    TESTSTB

Database Status:
SUCCESS

DGMGRL>