We have seen how to set up or create an Oracle physical standby database in this post. As a continuation, we will look into how to set up a Data Guard Broker to manage the primary and standby databases. Data Guard Broker provides many advantages such as
- enabling configure and manage multiple databases from single location and automatically unifies all DBs in broker configuration,
- automatically setting up redo transport services and log apply services,
- simplifying switchover and failover, integrating role changes with Oracle clusterware, etc.,
All of the details and advantages can be found in the Oracle Data Guard Broker concepts guide here.
In this post we will look into configuring the Oracle Data Guard Broker and general issues we will encounter during the set up with their work arounds/ fixes.
Step 1: Preserve current pfile or spfile
As we will be altering/adding a few parameters, it is always a best practice to backup the original pfile/spfile before changing the contents.
SQL> create pfile='/oracle/ABC/19.0.0/dbs/init_ABC.ora_b4DGBroker' from spfile; File created. SQL>
- During physical standby set up we would have set log_archive_dest_2 parameter. We need to clear this parameter where Broker will automatically takes care of this parameter.
- If you do not reset the parameter, you will encounter the below error when performing the Create configuration command.
- We can alter the location of the broker configuration files as needed using dg_broker_config_file1 and dg_broker_config_file2 parameters
- dg_broker_start should be set to true to let the broker DMON process start automatically when the instance starts up
SQL> ALTER SYSTEM SET dg_broker_config_file1 = '+DATA/ABC1/broker1.dat' scope=both; System altered. SQL> ALTER SYSTEM SET dg_broker_config_file2 = '+RECO/ABC1/broker2.dat' scope=both; System altered. SQL> alter system reset log_Archive_dest_2 scope=both; System altered. SQL> alter system set dg_broker_start=true scope=both; System altered. SQL>
SQL> ALTER SYSTEM SET dg_broker_config_file1 = '+DATA/ABC_STANDBY/broker1.dat' scope=both; System altered. SQL> ALTER SYSTEM SET dg_broker_config_file2 = '+RECO/ABC_STANDBY/broker2.dat' scope=both; System altered. SQL> alter system reset log_Archive_dest_2 scope=both; System altered. SQL> alter system set dg_broker_start=true scope=both; System altered. SQL>Step 3: Create configuration
Connect to dgmgrl and create configuration as shown below.
-sh-4.2$ dgmgrl DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu Jan 21 23:40:44 2021 Version 19.8.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Welcome to DGMGRL, type "help" for information. DGMGRL> connect sys Password: Connected to "ABC1" Connected as SYSDBA. DGMGRL> CREATE CONFIGURATION 'ABC_DGD' AS PRIMARY DATABASE IS 'ABC1' CONNECT IDENTIFIER IS ABC1; Configuration "ABC_DGD" created with primary database "ABC1" DGMGRL> show configuration; Configuration - ABC_DGD Protection Mode: MaxPerformance Members: ABC1 - Primary database Fast-Start Failover: Disabled Configuration Status: DISABLED DGMGRL>Step 4: Add Standby database
DGMGRL> add database ABC_STANDBY as connect identifier is ABC_STANDBY;
Database "ABC_standby" added
DGMGRL> show configuration;
Configuration - ABC_DGD
Protection Mode: MaxPerformance
Members:
ABC1 - Primary database
ABC_standby - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
DISABLED
DGMGRL>
Now you can see the standby database is added but the status is showing as DISABLEDStep 5: Enable configuration
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;
Configuration - ABC_DGD
Protection Mode: MaxPerformance
Members:
ABC1 - Primary database
ABC_standby - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 8 seconds ago)
DGMGRL> show database ABC1;
Database - ABC1
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
ABC
Database Status:
SUCCESS
DGMGRL> show database ABC_STANDBY;
Database - ABC_standby
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 1 second (computed 0 seconds ago)
Average Apply Rate: 66.78 MByte/s
Real Time Query: OFF
Instance(s):
ABC
Database Status:
SUCCESS
DGMGRL>
We can see the Configuration status is now showing as SUCCESS. Line 18 and 30 provides the properties of both Primary and Standby database where the status of both the database is SUCCESS
Step 6: Validate the databases
We can now validate the configuration in which the actual connectivity testing along with a comprehensive set of database checks are being performed. We need to make sure validate database works without any issue for the conversion to take place if intended to.
DGMGRL> validate database verbose ABC1;
Database Role: Primary database
Ready for Switchover: Yes
Flashback Database Status:
ABC1: Off
Capacity Information:
Database Instances Threads
ABC1 1 1
Managed by Clusterware:
ABC1: NO
Validating static connect identifier for the primary database ABC1...
Unable to connect to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hic023124.dc.honeywell.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ABC1_DGMGRL)(INSTANCE_NAME=ABC)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor Failed.
Warning: Ensure primary database's StaticConnectIdentifier property
is configured properly so that the primary database can be restarted
by DGMGRL after switchover
Temporary Tablespace File Information:
ABC1 TEMP Files: 31
Data file Online Move in Progress:
ABC1: No
Transport-Related Information:
Transport On: Yes
Log Files Cleared:
ABC1 Standby Redo Log Files: Cleared
DGMGRL>
We can see that the validate database is throwing connection error w.r.to connect identifier even though we have properly set up tnsnames and is working fine. The reason being broker adds the static entry in broker configuration as SID_DGMGRL where as the GLOBAL_NAME in listener will be SID which causes the mismatch. Refer Oracle support note Doc ID 1582927.1 for details. The fix is to set the StaticConnectIdentifier configuration property properly as below
DGMGRL> edit database ABC1 set property StaticConnectIdentifier='ABC1'; Property "staticconnectidentifier" updated DGMGRL> edit database ABC_STANDBY set property StaticConnectIdentifier='ABC_STANDBY'; Property "staticconnectidentifier" updated DGMGRL>Next we will validate the standby database similar to the above we did for primary database.
DGMGRL> validate database verbose ABC_STANDBY; Error: ORA-16713: The Oracle Data Guard broker command timed out. DGMGRL>Now we have a different issue when validating the standby database. Referring Support note Doc ID 1322877.1 and Doc ID 2300040.1 we can extend the Operationtimeout parameter of the broker configuration. The ADR can also be cleaned up prior to running validate database if the database is too huge.
DGMGRL> show configuration OperationTimeout;
OperationTimeout = '30'
DGMGRL> validate database verbose ABC_STANDBY;
Error: ORA-16713: The Oracle Data Guard broker command timed out.
DGMGRL> EDIT CONFIGURATION SET PROPERTY OperationTimeout=600;
Property "operationtimeout" updated
DGMGRL> validate database verbose ABC_STANDBY;
Database Role: Physical standby database
Primary Database: ABC1
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
Flashback Database Status:
ABC1 : Off
ABC_standby: Off
Capacity Information:
Database Instances Threads
ABC1 1 1
ABC_standby 1 1
Managed by Clusterware:
ABC1 : NO
ABC_standby: NO
Validating static connect identifier for the primary database ABC1...
The static connect identifier allows for a connection to database "ABC1".
Temporary Tablespace File Information:
ABC1 TEMP Files: 31
ABC_standby TEMP Files: 30
Data file Online Move in Progress:
ABC1: No
ABC_standby: No
Standby Apply-Related Information:
Apply State: Running
Apply Lag: 0 seconds (computed 0 seconds ago)
Apply Delay: 0 minutes
Transport-Related Information:
Transport On: Yes
Gap Status: No Gap
Transport Lag: 0 seconds (computed 0 seconds ago)
Transport Status: Success
Log Files Cleared:
ABC1 Standby Redo Log Files: Cleared
ABC_standby Online Redo Log Files: Cleared
ABC_standby Standby Redo Log Files: Available
Current Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status
(ABC1) (ABC_standby)
1 8 9 Sufficient SRLs
Future Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status
(ABC_standby) (ABC1)
1 8 4 Insufficient SRLs
Current Configuration Log File Sizes:
Thread # Smallest Online Redo Smallest Standby Redo
Log File Size Log File Size
(ABC1) (ABC_standby)
1 3136 MBytes 3136 MBytes
Future Configuration Log File Sizes:
Thread # Smallest Online Redo Smallest Standby Redo
Log File Size Log File Size
(ABC_standby) (ABC1)
1 3136 MBytes 3136 MBytes
Apply-Related Property Settings:
Property ABC1 Value ABC_standby Value
DelayMins 0 0
ApplyParallel AUTO AUTO
ApplyInstances 0 0
Transport-Related Property Settings:
Property ABC1 Value ABC_standby Value
LogShipping ON ON
LogXptMode ASYNC ASYNC
Dependency <empty> <empty>
DelayMins 0 0
Binding optional optional
MaxFailure 0 0
ReopenSecs 300 300
NetTimeout 30 30
RedoCompression DISABLE DISABLE
DGMGRL>
The command now executed successfully without any issues after configuration change. We can see in line 62, broker is reporting insufficient Standby Redo Logs. This is because when the SRLs were added the thread number is not specified in the command. We can drop and recreate the SRLs for which the thread number is not correct to overcome this issue. Refer support note Doc ID 1956103.1 for more details
SQL> select thread#,group#,bytes,status from v$standby_log;
THREAD# GROUP# BYTES STATUS
---------- ---------- ---------- ----------
1 11 4.1943E+10 UNASSIGNED
1 12 4.1943E+10 UNASSIGNED
1 13 4.1943E+10 UNASSIGNED
1 14 4.1943E+10 UNASSIGNED
0 15 4.1943E+10 UNASSIGNED
0 16 4.1943E+10 UNASSIGNED
0 17 4.1943E+10 UNASSIGNED
0 18 4.1943E+10 UNASSIGNED
0 19 4.1943E+10 UNASSIGNED
9 rows selected.
SQL> alter database drop logfile group 15;
Database altered.
SQL> alter database add standby logfile thread 1 group 15 size 40000m;
Database altered.
/* Drop and recreate other groups as well
--alter database drop logfile group 16;
--alter database drop logfile group 17;
--alter database drop logfile group 18;
--alter database drop logfile group 19;
--alter database add standby logfile thread 1 group 16 size 40000m;
--alter database add standby logfile thread 1 group 17 size 40000m;
--alter database add standby logfile thread 1 group 18 size 40000m;
--alter database add standby logfile thread 1 group 19 size 40000m;
*/
SQL> select thread#,group#,bytes/1024/1024, status from v$standby_log;
THREAD# GROUP# BYTES/1024/1024 STATUS
---------- ---------- --------------- ----------
1 11 40000 UNASSIGNED
1 12 40000 UNASSIGNED
1 13 40000 UNASSIGNED
1 14 40000 UNASSIGNED
1 15 40000 UNASSIGNED
1 16 40000 UNASSIGNED
1 17 40000 UNASSIGNED
1 18 40000 UNASSIGNED
1 19 40000 UNASSIGNED
9 rows selected.
SQL>
You can now see that all the SRLs are with proper thread number assigned. Let's check the validate database command for the standby database again. DGMGRL> validate database verbose ABC_STANDBY;
Database Role: Physical standby database
Primary Database: ABC1
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
Flashback Database Status:
ABC1 : Off
ABC_standby: Off
Capacity Information:
Database Instances Threads
ABC1 1 1
ABC_standby 1 1
Managed by Clusterware:
ABC1 : NO
ABC_standby: YES
Validating static connect identifier for the primary database ABC1...
The static connect identifier allows for a connection to database "ABC1".
Temporary Tablespace File Information:
ABC1 TEMP Files: 31
ABC_standby TEMP Files: 30
Data file Online Move in Progress:
ABC1: No
ABC_standby: No
Standby Apply-Related Information:
Apply State: Running
Apply Lag: 1 second (computed 0 seconds ago)
Apply Delay: 0 minutes
Transport-Related Information:
Transport On: Yes
Gap Status: No Gap
Transport Lag: 0 seconds (computed 0 seconds ago)
Transport Status: Success
Log Files Cleared:
ABC1 Standby Redo Log Files: Cleared
ABC_standby Online Redo Log Files: Cleared
ABC_standby Standby Redo Log Files: Available
Current Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status
(ABC1) (ABC_standby)
1 8 9 Sufficient SRLs
Future Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status
(ABC_standby) (ABC1)
1 8 9 Sufficient SRLs
Current Configuration Log File Sizes:
Thread # Smallest Online Redo Smallest Standby Redo
Log File Size Log File Size
(ABC1) (ABC_standby)
1 3136 MBytes 3136 MBytes
Future Configuration Log File Sizes:
Thread # Smallest Online Redo Smallest Standby Redo
Log File Size Log File Size
(ABC_standby) (ABC1)
1 3136 MBytes 3136 MBytes
Apply-Related Property Settings:
Property ABC1 Value ABC_standby Value
DelayMins 0 0
ApplyParallel AUTO AUTO
ApplyInstances 0 0
Transport-Related Property Settings:
Property ABC1 Value ABC_standby Value
LogShipping ON ON
LogXptMode ASYNC ASYNC
Dependency <empty> <empty>
DelayMins 0 0
Binding optional optional
MaxFailure 0 0
ReopenSecs 300 300
NetTimeout 30 30
RedoCompression DISABLE DISABLE
DGMGRL> exit
-sh-4.2$
Everything is set up properly now. We are ready to rock and roll standby management using Oracle Data Guard Broker and the system is ready for switchover and failover operations if needed. References:
- Oracle Data Guard Broker concepts
- Oracle Data Guard Command-Line Interface Reference
- Create Configuration Failing with ORA-16698 [1582179.1]
- Step by Step How to Create Dataguard Broker Configuration [984622.1]
- 12c Create Dataguard Broker Configuration - DGMGRL [1583588.1]
- Known issues when using "Validate database" DGMGRL command [2300040.1]
- Data Guard: Server Hang And Crash Because RSM0 Keeps Re-spawning [1322877.1]
- Warning: standby redo logs not configured for thread <n> on <db_unique_name> [1956103.1]
Happy Brokering!!!
