Description:-
- Data Guard broker is a centralized framework to manage entire Data Guard configuration through a client connection to any database in the configuration
- DGMGRL does not have the ability to create standby (GUI can do it). CLI is used mostly for configuration and management.
- Easy switchover/failover with one command thereby minimizing overall downtime associated with the planned/unplanned outage
- Instead of managing primary and standby databases with various SQL*Plus statements, broker provides a single unified configuration
- Broker keeps its configuration details in flat file. The files are stored at each database nodes in the Data Guard configuration.Additionally two copies of the configuration files are always stored on each database for redundancy.
- Below parameters control where the configuration files will be stored.
DG_BROKER_CONFIG_FILE1 & DG_BROKER_CONFIG_FILE2
Step by Step to configure Oracle 19c Data Guard Physical Standby Here
Environment Details:-
Let’s Start the Demo:-
Step1:-Creating a Service Listener in primary and standby side(Both Side)
On Primary Side:-
(SID_DESC =
(GLOBAL_DBNAME = chennai_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/19.3.0/db_1)
(SID_NAME = Chennai)
)
)
Reload the listener
Instance “chennai”, status UNKNOWN, has 1 handler(s) for this service…
Instance “chennai”, status READY, has 1 handler(s) for this service…
Service “chennaiXDB” has 1 instance(s).
Instance “chennai”, status READY, has 1 handler(s) for this service…
Service “chennai_DGMGRL” has 1 instance(s).
Instance “chennai”, status UNKNOWN, has 1 handler(s) for this service…
Service “dbwr” has 1 instance(s).
On Standby Side:-
(SID_DESC =
(GLOBAL_DBNAME = delhi_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/19.3.0/db_1)
(SID_NAME = delhi)
)
)
Reload the listener
Service “delhi” has 1 instance(s).
Instance “delhi”, status UNKNOWN, has 1 handler(s) for this service…
Service “delhi_DGMGRL” has 1 instance(s).
Instance “delhi”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
Step2:-Changing the parameters on both side
On Primary Side
SQL> ALTER SYSTEM SET dg_broker_start=true scope=both;
System altered.
SQL> ALTER SYSTEM SET local_listener='(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.125.155)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = chennai)))’;
System altered.
On Standby Side
SQL> ALTER SYSTEM SET dg_broker_start=true scope=both;
System altered.
SQL> ALTER SYSTEM SET local_listener='(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.125.156)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = delhi)))’;
System altered.
Step3:- Login in to the primary database through DGMGRL
[oracle@dev19c ~]$ export ORACLE_SID=chennai
[oracle@dev19c ~]$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 – Production on Tue Oct 22 22:40:29 2019
Version 19.4.1.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type “help” for information.
DGMGRL> connect sys/oracle@chennai
Connected to “CHENNAI”
Connected as SYSDBA.
DGMGRL> show configuration;
ORA-16532: Oracle Data Guard broker configuration does not exist
Configuration details cannot be determined by DGMGRL
Step4:-Configure Dataguard Broker for primary database
DGMGRL> create configuration ‘hari’ as primary database is ‘chennai’ connect identifier is chennai;
Configuration “hari” created with primary database “chennai”
DGMGRL> show configuration;
Configuration – hari
Protection Mode: MaxAvailability
Members:
chennai – Primary database
Fast-Start Failover: Disabled
Configuration Status:
DISABLED
Step5:-Configure Dataguard Broker for standby database
DGMGRL> Add database ‘delhi’ as connect identifier is delhi maintained as physical;
Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set
Failed.
Fix for this issue
SQL> alter system set LOG_ARCHIVE_DEST_2= ‘ ‘; (both Side)
System altered.
DGMGRL> Add database ‘delhi’ as connect identifier is delhi maintained as physical;
Database “delhi” added
DGMGRL> show configuration;
Configuration – hari
Protection Mode: MaxAvailability
Members:
chennai – Primary database
delhi – Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
DISABLED
Step6:-enable the Dataguard broker
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;
Configuration – hari
Protection Mode: MaxAvailability
Members:
chennai – Primary database
delhi – Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 48 seconds ago)
DGMGRL> show configuration verbose;
Configuration – hari
Protection Mode: MaxAvailability
Members:
chennai – Primary database
delhi – Physical standby database
Properties:
FastStartFailoverThreshold = ’30’
OperationTimeout = ’30’
TraceLevel = ‘USER’
FastStartFailoverLagLimit = ‘0’
CommunicationTimeout = ‘180’
ObserverReconnect = ‘0’
FastStartFailoverAutoReinstate = ‘TRUE’
FastStartFailoverPmyShutdown = ‘TRUE’
BystandersFollowRoleChange = ‘ALL’
ObserverOverride = ‘FALSE’
ExternalDestination1 = ”
ExternalDestination2 = ”
PrimaryLostWriteAction = ‘CONTINUE’
ConfigurationWideServiceName = ‘chennai_CFG’
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS
DGMGRL> enable database ‘delhi’;
Enabled.
DGMGRL> show database ‘delhi’;
Database – delhi
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: 1.00 KByte/s
Real Time Query: ON
Instance(s):
delhi
Database Status:
SUCCESS
Step7:-Check using Verbose command
DGMGRL> show database verbose ‘delhi’;
Database – delhi
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 1.00 KByte/s
Active Apply Rate: 0 Byte/s
Maximum Apply Rate: 0 Byte/s
Real Time Query: ON
Instance(s):
delhi
Properties:
DGConnectIdentifier = ‘delhi’
ObserverConnectIdentifier = ”
FastStartFailoverTarget = ”
PreferredObserverHosts = ”
LogShipping = ‘ON’
RedoRoutes = ”
LogXptMode = ‘SYNC’
DelayMins = ‘0’
Binding = ‘optional’
MaxFailure = ‘0’
ReopenSecs = ‘300’
NetTimeout = ’30’
RedoCompression = ‘DISABLE’
PreferredApplyInstance = ”
ApplyInstanceTimeout = ‘0’
ApplyLagThreshold = ’30’
TransportLagThreshold = ’30’
TransportDisconnectedThreshold = ’30’
ApplyParallel = ‘AUTO’
ApplyInstances = ‘0’
StandbyFileManagement = ”
ArchiveLagTarget = ‘0’
LogArchiveMaxProcesses = ‘0’
LogArchiveMinSucceedDest = ‘0’
DataGuardSyncLatency = ‘0’
LogArchiveTrace = ‘0’
LogArchiveFormat = ”
DbFileNameConvert = ”
LogFileNameConvert = ”
ArchiveLocation = ”
AlternateLocation = ”
StandbyArchiveLocation = ”
StandbyAlternateLocation = ”
InconsistentProperties = ‘(monitor)’
InconsistentLogXptProps = ‘(monitor)’
LogXptStatus = ‘(monitor)’
SendQEntries = ‘(monitor)’
RecvQEntries = ‘(monitor)’
HostName = ‘devdr19c’
StaticConnectIdentifier = ‘(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.125.156)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=delhi_DGMGRL)(INSTANCE_NAME=delhi)(SERVER=DEDICATED)))’
TopWaitEvents = ‘(monitor)’
SidName = ‘(monitor)’
Log file locations:
Alert log : /u01/app/oracle/diag/rdbms/delhi/delhi/trace/alert_delhi.log
Data Guard Broker log : /u01/app/oracle/diag/rdbms/delhi/delhi/trace/drcdelhi.log
Database Status:
SUCCESS
DGMGRL> show database verbose ‘chennai’;
Database – chennai
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
chennai
Properties:
DGConnectIdentifier = ‘chennai’
ObserverConnectIdentifier = ”
FastStartFailoverTarget = ”
PreferredObserverHosts = ”
LogShipping = ‘ON’
RedoRoutes = ”
LogXptMode = ‘SYNC’
DelayMins = ‘0’
Binding = ‘optional’
MaxFailure = ‘0’
ReopenSecs = ‘300’
NetTimeout = ’30’
RedoCompression = ‘DISABLE’
PreferredApplyInstance = ”
ApplyInstanceTimeout = ‘0’
ApplyLagThreshold = ’30’
TransportLagThreshold = ’30’
TransportDisconnectedThreshold = ’30’
ApplyParallel = ‘AUTO’
ApplyInstances = ‘0’
StandbyFileManagement = ”
ArchiveLagTarget = ‘0’
LogArchiveMaxProcesses = ‘0’
LogArchiveMinSucceedDest = ‘0’
DataGuardSyncLatency = ‘0’
LogArchiveTrace = ‘0’
LogArchiveFormat = ”
DbFileNameConvert = ”
LogFileNameConvert = ”
ArchiveLocation = ”
AlternateLocation = ”
StandbyArchiveLocation = ”
StandbyAlternateLocation = ”
InconsistentProperties = ‘(monitor)’
InconsistentLogXptProps = ‘(monitor)’
LogXptStatus = ‘(monitor)’
SendQEntries = ‘(monitor)’
RecvQEntries = ‘(monitor)’
HostName = ‘dev19c’
StaticConnectIdentifier = ‘(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.125.155)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=CHENNAI_DGMGRL)(INSTANCE_NAME=chennai)(SERVER=DEDICATED)))’
TopWaitEvents = ‘(monitor)’
SidName = ‘(monitor)’
Log file locations:
Alert log : /u01/app/oracle/diag/rdbms/chennai/chennai/trace/alert_chennai.log
Data Guard Broker log : /u01/app/oracle/diag/rdbms/chennai/chennai/trace/drcchennai.log
Database Status:
SUCCESS
Successfully configured the Oracle Dataguard Broker in 19c database.
Connect with me:-
Telegram App:https://t.me/oracledbwr
LinkedIn:https://www.linkedin.com/in/hariprasathdba
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page : https://www.facebook.com/dbahariprasath/?
Twitter : https://twitter.com/hariprasathdba