Data Guard Broker Configuration (DGMGRL Utility)
Posted by Mir Sayeed Hassan on October 2nd, 2017
Data Guard Broker Configuration (DGMGRL Utility)
Data Guard broker configuration using the command line dgmgrl interface, Below are the step by step procedure tested & verified in our Test ENV
Primary Database: PR
Standby Database: STD
Set the below parameter as on both Primary as well as Standby database start the Data Guard Broker process
SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH; System altered.
Edit the listener.ora on both nodes to add a static entry for DGMGRL
This is to prevent the ORA-12154 error which can be observed on startup of the standby database after performing a switchover. Ensure that the GLOBAL_DBNAME is set to db_unique_name_DGMGRL.db_domain
$ cat $ORACLE_HOME/network/admin/listener.ora LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (GLOBAL_DBNAME = pr_dgmgrl) (ADDRESS = (PROTOCOL = TCP)(HOST = pr)(PORT = 1521)) ) ) ADR_BASE_LISTENER = /u01/app/oracle
Create the configuration
[oracle@pr ~]$ dgmgrl
DGMGRL> connect sys/xxx; Connected
DGMGRL> create configuration 'PRDG' AS PRIMARY DATABASE IS 'pr' Connect identifier is 'pr'; Configuration "PRDG" created with primary database "pr" Add the Standby database to the configuration
DGMGRL> add database 'std' as connect identifier is 'std'; Database "std" added
DGMGRL> show configuration Configuration - PRDG Protection Mode: MaxPerformance Databases: pr - Primary database std - Physical standby database Fast-Start Failover: DISABLED Configuration Status: DISABLED
Enable the configuration
DGMGRL> enable configuration Enabled
DGMGRL> show configuration Configuration - PRDG Protection Mode: MaxPerformance Databases: pr - Primary database std - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS
View the Standby and Primary database properties
DGMGRL> show database pr Database - pr Role: PRIMARY Intended State: TRANSPORT-ON Instance(s): pr Database Status: SUCCESS
DGMGRL> show database std Database - std Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 1 second ago) Apply Lag: 0 seconds (computed 1 second ago) Apply Rate: 1.92 MByte/s Real Time Query: OFF Instance(s): std Database Status: SUCCESS
DGMGRL> show database verbose pr Database - pr Role: PRIMARY Intended State: TRANSPORT-ON Instance(s): pr Properties: DGConnectIdentifier = 'pr' ObserverConnectIdentifier = '' LogXptMode = 'ASYNC' DelayMins = '0' Binding = 'optional' MaxFailure = '0' MaxConnections = '1' ReopenSecs = '300' NetTimeout = '30' RedoCompression = 'DISABLE' LogShipping = 'ON' PreferredApplyInstance = '' ApplyInstanceTimeout = '0' ApplyParallel = 'AUTO' StandbyFileManagement = 'AUTO' ArchiveLagTarget = '0' LogArchiveMaxProcesses = '30' LogArchiveMinSucceedDest = '1' DbFileNameConvert = '/u02/oradata/redo, /oradata/redo' LogFileNameConvert = '/oradata/redo, /u02/oradata/redo/' FastStartFailoverTarget = '' InconsistentProperties = '(monitor)' InconsistentLogXptProps = '(monitor)' SendQEntries = '(monitor)' LogXptStatus = '(monitor)' RecvQEntries = '(monitor)' ApplyLagThreshold = '0' TransportLagThreshold = '0' TransportDisconnectedThreshold = '30' SidName = 'pr' StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=pr)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=pr_DGMGRL)(INSTANCE_NAME=pr)(SERVER=DEDICATED)))' StandbyArchiveLocation = '/u01/app/oracle/fast_recovery_area/PR/arch' AlternateLocation = '' LogArchiveTrace = '0' LogArchiveFormat = '%t_%s_%r.arc' TopWaitEvents = '(monitor)' Database Status: SUCCESS
DGMGRL> show database verbose std Database - std Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 1 second ago) Apply Lag: 0 seconds (computed 1 second ago) Apply Rate: 1.92 MByte/s Real Time Query: OFF Instance(s): std Properties: DGConnectIdentifier = 'std' ObserverConnectIdentifier = '' LogXptMode = 'ASYNC' DelayMins = '0' Binding = 'OPTIONAL' MaxFailure = '0' MaxConnections = '1' ReopenSecs = '300' NetTimeout = '30' RedoCompression = 'DISABLE' LogShipping = 'ON' PreferredApplyInstance = '' ApplyInstanceTimeout = '0' ApplyParallel = 'AUTO' StandbyFileManagement = 'AUTO' ArchiveLagTarget = '0' LogArchiveMaxProcesses = '30' LogArchiveMinSucceedDest = '1' DbFileNameConvert = '/oradata/redo, /u02/oradata/redo' LogFileNameConvert = '/oradata/redo/, /u02/oradata/redo/' FastStartFailoverTarget = '' InconsistentProperties = '(monitor)' InconsistentLogXptProps = '(monitor)' SendQEntries = '(monitor)' LogXptStatus = '(monitor)' RecvQEntries = '(monitor)' ApplyLagThreshold = '0' TransportLagThreshold = '0' TransportDisconnectedThreshold = '30' SidName = 'std' StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=std)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=std_DGMGRL)(INSTANCE_NAME=std)(SERVER=DEDICATED)))' StandbyArchiveLocation = '/u02/oradata/std/arch' AlternateLocation = '' LogArchiveTrace = '0' LogArchiveFormat = '%t_%s_%r.arc' TopWaitEvents = '(monitor)' Database Status: SUCCESS
Change the properties of a configured database
DGMGRL> edit database 'pr' set property 'LogXptMode'='SYNC'; Property "LogXptMode" updated
DGMGRL> show database pr statusreport STATUS REPORT INSTANCE_NAME SEVERITY ERROR_TEXT DGMGRL> show database std statusreport STATUS REPORT INSTANCE_NAME SEVERITY ERROR_TEXT
DGMGRL> show database pr InconsistentProperties INCONSISTENT PROPERTIES INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE
On Standby_Database – Configuration
DGMGRL> show configuration not logged on
DGMGRL> connect sys/sys@std Connected.
DGMGRL> show configuration Configuration - PRDG Protection Mode: MaxPerformance Databases: pr - Primary database std - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS
DGMGRL> show database pr Database - pr Role: PRIMARY Intended State: TRANSPORT-ON Instance(s): pr Database Status: SUCCESS
DGMGRL> show database std Database - std Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 0 seconds ago) Apply Lag: 0 seconds (computed 0 seconds ago) Apply Rate: 1.92 MByte/s Real Time Query: OFF Instance(s): std Database Status: SUCCESS
Enable the configuration
DGMGRL> enable database std Enabled.
DGMGRL> show database std Database - std Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 0 seconds ago) Apply Lag: 0 seconds (computed 0 seconds ago) Apply Rate: 1.89 MByte/s Real Time Query: OFF Instance(s): std Database Status: SUCCESS
DGMGRL> edit database 'std' set state='APPLY-OFF'; Succeeded.
DGMGRL> edit database 'std' set state='APPLY-ON';