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>
Comments