Data Guard Broker installation, configuration, monitoring and deactiving

1.PURPOSE AND SCOPE

Oracle Data Guard broker software install with oracle database software and program bin file could find at $ORACLE_HOME/bin/dgmgrl location. Benefit of dgmgrl is most of the actions on primary/standby database can be done easyly through this tool, especilly switchover and managing primary/standby database.

2. IMPORTANT

When you are using the Broker to maintain your Data Guard configuration you must use either the Broker command line interface ‘DGMGRL’ or Grid Control to make any changes to your Data Guard configuration.  If you desire to use SQL*Plus then you should remove the the Data Guard Broker configuration using the ‘REMOVE CONFIGURATION PRESERVE DESTINATIONS’  command in DGMGRL. Then  you must  set the DG_BROKER_START parameter to ‘FALSE’ on all database in the configuration.

2.PRACTICE

Information

Primary Database SID: PRIM

Primary Database Domain/IP: ABSO

Standby Database SID: PRIM_SBY

Standby Database Domain/IP: CROO

2.1. INSTALLATION

2.1.1  If you didnt set GLOBAL_DBNAME = ORACLE_SID parameter inside of your listener configuration please set it.

Primary

LSNR_PRIM =
 (DESCRIPTION_LIST =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = IPC)(KEY = PRIM))
 (ADDRESS = (PROTOCOL = TCP)(HOST = ABSO)(PORT = 1521))
 )
 )
SID_LIST_LSNR_PRIM =
 (SID_LIST =
 (SID_DESC =
 (GLOBAL_DBNAME = PRIM)
 (SID_NAME = PRIM)
 (ORACLE_HOME = /oracle/11.2.0.3)
 )
 )

Standby

LSNR_PRIM_SBY =
 (DESCRIPTION_LIST =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = IPC)(KEY = PRIM_SBY))
 (ADDRESS = (PROTOCOL = TCP)(HOST = CROO)(PORT = 1521))
 )
 )
SID_LIST_LSNR_PRIM_SBY =
 (SID_LIST =
 (SID_DESC =
 (GLOBAL_DBNAME = PRIM_SBY)
 (SID_NAME = PRIM_SBY)
 (ORACLE_HOME = /oracle/11.2.0.3)
 )
 )

2.2. Connect to dgmgrl application. On connecting process always sys/password need to be used otherwise its possible to take “ORA-01031: insufficient privileges” alert.(don’t use connect / )

ABSO:oracle(PRIM):/home/oracle > dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/****password****
Connected.

2.1.2.  Add primary database to broker

DGMGRL> CREATE CONFIGURATION 'PRIM_DGB' AS 
PRIMARY DATABASE IS 'PRIM'
CONNECT IDENTIFIER IS 'PRIM'
;> > > >
Configuration "PRIM_DGB" created with primary database "PRIM"

2.1.3.  Second add standby database

DGMGRL> ADD DATABASE 'PRIM_SBY' AS
CONNECT IDENTIFIER IS 'PRIM_SBY' ;
>
Database "PRIM_SBY" added

2.1.4. Checking configuration

DGMGRL> SHOW CONFIGURATION
Configuration - PRIM_DGB
Protection Mode: MaxPerformance
 Databases:
 PRIM - Primary database
 PRIM_SBY - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
Monitoring Data Guard Broker

Standby and Primary databases added to Broker.

2.2 CONFIGURATION OF DATA GUARD BROKER
2.2.1 To not to get TNS network error (ORA-12514: TNS:listener does not currently know of service requested in connect descriptor) set StaticConnectIdentifier as SID name.

DGMGRL> edit database "PRIM" set property staticConnectidentifier='PRIM';
Property "staticconnectidentifier" updated
DGMGRL> edit database "PRIM_SBY" set property staticConnectidentifier='PRIM_SBY';
Property "staticconnectidentifier" updated

2.2 2. Enable configuration

DGMGRL> ENABLE CONFIGURATION
Enabled.
DGMGRL> SHOW CONFIGURATION
Configuration - PRIM_DGB
Protection Mode: MaxPerformance
 Databases:
 PRIM - Primary database
 PRIM_SBY - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

2.2.3 To temporarily stop Redo Apply on a physical standby. (Primary would send archive log but standby wont apply it)

DGMGRL> EDIT DATABASE 'PRIM_SBY' SET STATE='APPLY-OFF';
Succeeded.

On standby alert log

Wed Mar 26 08:53:17 2014
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Wed Mar 26 08:53:17 2014
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /oracle/diag/rdbms/PRIM_sby/PRIM_SBY/trace/PRIM_SBY_pr00_13497.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 2004941
Wed Mar 26 08:53:17 2014
MRP0: Background Media Recovery process shutdown (PRIM_SBY)
Managed Standby Recovery Canceled (PRIM_SBY)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

2.2.4 To start Redo Apply on a physical standby.

DGMGRL> EDIT DATABASE 'PRIM_SBY' SET STATE='APPLY-ON';
Succeeded.

On standby alert log

Wed Mar 26 08:56:59 2014
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE
Attempt to start background Managed Standby Recovery process (PRIM_SBY)
Wed Mar 26 08:56:59 2014
MRP0 started with pid=37, OS id=18469
MRP0: Background Managed Standby Recovery process started (PRIM_SBY)
 started logmerger process
Wed Mar 26 08:57:04 2014
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 24 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log /oradata/PRIM_SBY/fra/PRIM_SBY/archivelog/2014_03_26/o1_mf_1_174_9m51zdgd_.arc
Media Recovery Waiting for thread 1 sequence 175 (in transit)
Recovery of Online Redo Log: Thread 1 Group 12 Seq 175 Reading mem 0
 Mem# 0: /oradata/PRIM_SBY/redo_pri/sbyredo02pri.log
 Mem# 1: /oradata/PRIM_SBY/redo_sec/sbyredo02sec.log
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE

2.2.5 To stop the transmittal of redo data to the standby database. (Primary would stop to send archive log)

DGMGRL> edit database 'PRIM' set state = 'LOG-TRANSPORT-OFF';
Succeeded.

Primary alert log

Wed Mar 26 09:02:33 2014
ALTER SYSTEM SET log_archive_dest_state_2='RESET' SCOPE=BOTH;

2.2.6 To start the transmittal of redo data to the standby database

Primary alert log

DGMGRL> edit database 'PRIM' set state = 'ONLINE';
Succeeded.
Primary alert log
Wed Mar 26 09:10:13 2014
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
ALTER SYSTEM ARCHIVE LOG
Wed Mar 26 09:10:13 2014
Thread 1 cannot allocate new log, sequence 176
Private strand flush not complete
 Current log# 5 seq# 175 mem# 0: /oradata/PRIM/redo_pri/redo05pri.log
 Current log# 5 seq# 175 mem# 1: /oradata/PRIM/redo_sec/redo05sec.log
Thread 1 advanced to log sequence 176 (LGWR switch)
 Current log# 6 seq# 176 mem# 0: /oradata/PRIM/redo_pri/redo06pri.log
 Current log# 6 seq# 176 mem# 1: /oradata/PRIM/redo_sec/redo06sec.log
Wed Mar 26 09:10:13 2014
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
Archived Log entry 245 added for thread 1 sequence 175 ID 0x4bd2839 dest 1:
LNS: Standby redo logfile selected for thread 1 sequence 176 for destination LOG_ARCHIVE_DEST_2

2.3 MONITORING DATA GUARD BROKER
2.3.1.  “Show database” command is used to see replication status of databases, if its SUCCESS its showing that replication work without problem. Otherwise it would show us ORA-XXXXX alerts.

DGMGRL> show database "PRIM"
Database - PRIM
 Role: PRIMARY
 Intended State: TRANSPORT-ON
 Instance(s):
 PRIM
Database Status:
SUCCESS
DGMGRL> show database "PRIM_SBY"
Database - PRIM_SBY
Role: PHYSICAL STANDBY
 Intended State: APPLY-ON
 Transport Lag: 0 seconds
 Apply Lag: 0 seconds
 Real Time Query: ON
 Instance(s):
 PRIM_SBY
Database Status:
SUCCESS

2.3.2. To see replication configuration and status.

DGMGRL> SHOW DATABASE VERBOSE "PRIM"
Database - PRIM
Role: PRIMARY
 Intended State: TRANSPORT-ON
 Instance(s):
 PRIM
Properties:
 DGConnectIdentifier = 'PRIM'
 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 = '10'
 LogArchiveMinSucceedDest = '1'
 DbFileNameConvert = '/oradata/PRIM_SBY/, /oradata/PRIM/'
 LogFileNameConvert = '/oradata/PRIM_SBY/, /oradata/PRIM/'
 FastStartFailoverTarget = ''
 InconsistentProperties = '(monitor)'
 InconsistentLogXptProps = '(monitor)'
 SendQEntries = '(monitor)'
 LogXptStatus = '(monitor)'
 RecvQEntries = '(monitor)'
 SidName = 'PRIM'
 StaticConnectIdentifier = 'PRIM'
 StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
 AlternateLocation = ''
 LogArchiveTrace = '0'
 LogArchiveFormat = '%t_%s_%r.dbf'
 TopWaitEvents = '(monitor)'
Database Status:
SUCCESS
DGMGRL> SHOW DATABASE VERBOSE "PRIM_SBY"
Database - PRIM_SBY
Role: PHYSICAL STANDBY
 Intended State: APPLY-ON
 Transport Lag: 0 seconds
 Apply Lag: 0 seconds
 Real Time Query: ON
 Instance(s):
 PRIM_SBY
Properties:
 DGConnectIdentifier = 'PRIM_SBY'
 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 = '10'
 LogArchiveMinSucceedDest = '1'
 DbFileNameConvert = 'PRIM, PRIM_SBY'
 LogFileNameConvert = 'PRIM, PRIM_SBY'
 FastStartFailoverTarget = ''
 InconsistentProperties = '(monitor)'
 InconsistentLogXptProps = '(monitor)'
 SendQEntries = '(monitor)'
 LogXptStatus = '(monitor)'
 RecvQEntries = '(monitor)'
 SidName = 'PRIM_SBY'
 StaticConnectIdentifier = 'PRIM_SBY'
 StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
 AlternateLocation = ''
 LogArchiveTrace = '0'
 LogArchiveFormat = '%t_%s_%r.dbf'
 TopWaitEvents = '(monitor)'
Database Status:
SUCCESS

2.3.3. To identify errors seen at Database status , please use statusreport .

DGMGRL> show database "PRIM" statusreport
STATUS REPORT
 INSTANCE_NAME SEVERITY ERROR_TEXT
 PRIM ERROR ORA-16737: the redo transport service for 
standby "PRIM_SBY" has an error
 PRIM WARNING ORA-16714: the value of property 
LogArchiveTrace is inconsistent with the database setting
 PRIM WARNING ORA-16715: redo transport-related property 
ReopenSecs of standby database "PRIM_SBY " is inconsistent

2.3.4. To identify the exact log transport error(ORA-16737), you can use LogXptStatus monitorable database property.

DGMGRL> SHOW DATABASE "PRIM" 'LogXptStatus'
LOG TRANSPORT STATUS
PRIMARY_INSTANCE_NAME STANDBY_DATABASE_NAME STATUS
 PRIM PRIM_SBY ORA-12541: TNS:no listener

2.3.5. To identify the inconsistent values for property LogArchiveTrace(ORA-16714), you can use the InconsistentProperties monitorable database property

DGMGRL> SHOW DATABASE "PRIM" 'InconsistentProperties'
INCONSISTENT PROPERTIES
INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE
PRIM LogArchiveTrace 255 0 0

2.4. SWITCHOVER BETWEEN DATABASES
2.4.1.  Check which instance is primary

DGMGRL> SHOW CONFIGURATION
Configuration - PRIM_DGB
Protection Mode: MaxPerformance
 Databases:
 PRIM - Primary database
 PRIM_SBY - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
Monitoring Data Guard Broker

2.4.2. Primary is PRIM, lets switch it to PRIM_SBY

DGMGRL> switchover to "PRIM_SBY"
Performing switchover NOW, please wait...
New primary database "PRIM_SBY" is opening...
Operation requires shutdown of instance "PRIM" on database "PRIM"
Shutting down instance "PRIM"...
ORACLE instance shut down.
Operation requires startup of instance "PRIM" on database "PRIM"
Starting instance "PRIM"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "PRIM_SBY"

2.4.3. Primary is PRIM_SBY, lets switch back to PRIM and check the status of both db

DGMGRL> switchover to "PRIM"
Performing switchover NOW, please wait...
New primary database "PRIM" is opening...
Operation requires shutdown of instance "PRIM_SBY" on database "PRIM_SBY"
Shutting down instance "PRIM_SBY"...
ORACLE instance shut down.
Operation requires startup of instance "PRIM_SBY" on database "PRIM_SBY"
Starting instance "PRIM_SBY"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "PRIM"
DGMGRL> show configuration
Configuration - PRIM_DGB
Protection Mode: MaxPerformance
 Databases:
 PRIM_SBY - Primary database
 PRIM - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
- Switch back to PRIM

DGMGRL> show database "PRIM"
Database - PRIM
 Role: PRIMARY
 Intended State: TRANSPORT-ON
 Instance(s):
 PRIM
Database Status:
SUCCESS
DGMGRL> show database "PRIM_SBY"
Database - PRIM_SBY
 Role: PHYSICAL STANDBY
 Intended State: APPLY-ON
 Transport Lag: 0 seconds
 Apply Lag: 0 seconds
 Real Time Query: ON
 Instance(s):
 PRIM_SBY
Database Status:
SUCCESS

2.5 DEACTIVING ORACLE DATA GUARD BROKER
2.5.1.  To disable configuration: this command to disable management of the entire broker configuration including the primary database.

DGMGRL> disable configuration;
Disabled.
DGMGRL> show configuration;
Configuration - PRIM_DGB
Protection Mode: MaxPerformance
 Databases:
 PRIM - Primary database
 PRIM_SBY - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED

2.5.2. Disable a standby database: It need to use when broker don’t need to monitor or manage a standby database.

DGMGRL> DISABLE DATABASE "PRIM_SBY";
Disabled.

2.5.3. Remove database from configuration.

DGMGRL> remove database "PRIM_SBY"
Removed database "PRIM_SBY" from the configuration
DGMGRL> DGMGRL> SHOW CONFIGURATION;
Configuration - PRIM_DGB
Protection Mode: MaxPerformance
 Databases:
 PRIM - Primary database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

2.5.4. Remove configuration.

DGMGRL> REMOVE CONFIGURATION;
Removed configuration
DGMGRL> SHOW CONFIGURATION;
ORA-16532: Data Guard broker configuration does not exist
Configuration details cannot be determined by DGMGRL

2.5.5. Disable broker from oracle database: On Primary and Standby databases dg_broker_start parameter need to be set FALSE.

Primary

PUBLIC@ABSO:PRIM: >ALTER SYSTEM SET DG_BROKER_START=FALSE SCOPE=BOTH;
System altered.

Standby

SYS@CROO:PRIM_SBY: >ALTER SYSTEM SET DG_BROKER_START=FALSE SCOPE=BOTH;
System altered.