Dataguard broker is used to automate monitoring and controlling standby setups. It is very much useful, when the organization has multiple standby sites.
In this article, we will see, how to enable dg broker configuration in existing standby setup.
NOTE – Before setting dgbroker, make sure standby setup is ready.
ENVIRONMENT DETAILS:
PRIMARY UNIQUE DB NAME – > POCDB
STANDBY UNIQUE DB NAME – > POCDSBY
1. Set dg_broker_start to true [ ON BOTH PRIMARY AND STANDBY DB]
POCDB> alter system set dg_broker_start=true sid='*'; System altered. POCDSBY> alter system set dg_broker_start=true sid='*'; System altered.
2. Add primary db configuration [ ON PRIMARY]
$dgmgrl DGMGRL for Solaris: Release 12.2.0.1.0 - Production on Sun Apr 8 12:22:13 2018 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Welcome to DGMGRL, type "help" for information. DGMGRL> connect sysdg Password: Connected to "POCD" Connected as SYSDG. DGMGRL> CREATE CONFIGURATION 'poc_dg' AS PRIMARY DATABASE IS 'POCD' CONNECT IDENTIFIER IS POCD; Configuration "poc_dg" created with primary database "POCD" DGMGRL> SHOW CONFIGURATION Configuration - poc_dg Protection Mode: MaxPerformance Members: POCD - Primary database Fast-Start Failover: DISABLED Configuration Status: DISABLED
3. Add standby configuration [ ON PRIMARY ]
DGMGRL> add database POCDSBY as connect identifier is POCDSBY; Database "pocdsby" added
DGMGRL> show configuration
Configuration - poc_dg
Protection Mode: MaxPerformance
Members:
POCD - Primary database
pocdsby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
If you are getting error as ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set, then
To fix this error, disable any log_archive_dest_n parameter set on standby( excluding log_archive_dest_1)On standby:
SQL> alter system set log_Archive_dest_2=”;
System altered.
4. Enable configuration: [ ON PRIMARY]
DGMGRL> show configuration
Configuration - poc_dg
Protection Mode: MaxPerformance
Members:
POCD - Primary database
pocdsby - Physical standby database
Warning: ORA-16792: configurable property value is inconsistent with member setting
Fast-Start Failover: DISABLED
Configuration Status:
WARNING (status updated 57 seconds ago)
Now let’s troubleshoot the ORA-16792 error, We are getting error standby database.
Check the properties for inconsistency.
DGMGRL> show database 'pocdsby' InconsistentProperties;
INCONSISTENT PROPERTIES
INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE
POCDSBY StandbyFileManagement MANUAL MANUAL
POCDSBY ArchiveLagTarget 0 0
POCDSBY LogArchiveMaxProcesses 4 4
POCDSBY LogArchiveMinSucceedDest 1 1
POCDSBY DataGuardSyncLatency 0 0
POCDSBY LogArchiveTrace 0 (missing) 0
POCDSBY LogArchiveFormat %t_%s_%r.dbf (missing) %t_%s_%r.dbf
We can see one critical parameter StandbyFileManagement is set to MANUAL . To fix the error set it to auto and try again.
— On standby
SQL> alter system set standby_file_management=AUTO scope=both; System altered.
Enable the configuration again:
DGMGRL> enable configuration Enabled. DGMGRL> show database 'pocdsby' InconsistentProperties; INCONSISTENT PROPERTIES INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE
DGMGRL> show configuration
Configuration - poc_dg
Protection Mode: MaxPerformance
Members:
POCD - Primary database
pocdsby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 49 seconds ago)
Our dgbroker setup is ready and we can do switchover and failover using dgmgrl easily.
Very Usefull thanck you
DGMGRL> ADD DATABASE pdroms AS CONNECT IDENTIFIER IS pdroms;
Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set
Can’t add standby to configuration without stopping the current log shipping and deleting log_archive_dest