Implementing Data Guard Broker 11g

In this  article let us try to understand the method to implement data guard broker, It is assumed that we have a working primary and physical standby servers in place. Let us proceed on how to implement Broker.

Broker works with spfile, in the coming steps you will realize, hence make sure the databases are using spfile.

The first parameter which holds the entire configuration is determined by the parameter

DG_BROKER_CONFIG_FILE1
DG_BROKER_CONFIG_FILE2

The default location is $ORACLE_HOME/dbs

Let us check this on the database.

SQL> show parameter dg_broker_config_file




NAME                        TYPE          VALUE
----------------------   -----------  ------------------------------
dg_broker_config_file1 string     /u01/app/oracle/product/11.2.0/dbs/dr1MOBPRO_COLLAB1.dat
dg_broker_config_file2 string     /u01/app/oracle/product/11.2.0/dbs/dr2MOBPRO_COLLAB1.dat

Let us understand few important points on this configuration file

• By default the name takes dr1.dat & dr2.dat. We can confirm this by check the unique db name on the database. 
  
  
  

        SQL> show parameter db_unique_name

        NAME                          TYPE        VALUE
        -----------------------  ----------    ------------
      db_unique_name string  MOBPRO_COLLAB1  

• There are two copies of this file, it is advisable not to keep both the copies on the single file system to avoid loosing if there is a disk failure.
  
• On the RAC make sure this configuration file lies where it is accesible by all the instances.
  

Let us now enable the broker process to start by altering  a parameter, this needs to be done on the primary as well as standby.

collab1:/u01/app/oracle/product/11.2.0/network/admin[mobpro]$ ps -ef  | grep dmon
oracle 24024 3599 0 17:49 pts/1 00:00:00 grep dmon

SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;

System altered.

[oracle@collab1 dbs]$ ps -ef  | grep dmon
oracle 24064 1 0 17:50 ? 00:00:00 ora_dmon_mobpro
oracle 24174 23811 0 17:51 pts/2 00:00:00 grep dmon

Before the parameter is set I checked for a process dmon, it didn't exist, after the parameter is set the process is created and I have painted in red .

There is a lot more to go, till now we have just started the broker background process.  Probably we can do a check by invoking the command line interface to check whats going on until now.

collab1:/u01/app/oracle/product/11.2.0/dbs[mobpro]$ dgmgrl

DGMGRL for Linux: Version 11.2.0.1.0 - Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/sys
Connected.
DGMGRL> show configuration
ORA-16532: Data Guard broker configuration does not exist
Configuration details cannot be determined by DGMGRL
DGMGRL>

The  above message clearly tells that configuration does not exist, means we need to create a configuration, this can be done as shown below.

DGMGRL> CREATE CONFIGURATION MOBPRO AS PRIMARY DATABASE IS MOBPRO_COLLAB1 CONNECT IDENTIFIER IS MOBPRO_COLLAB1_DG;

Configuration "mobpro" created with primary database "mobpro_collab1"

The above conveys that in this configuration the primary is MOBPRO_COLLAB1 which is nothing but the value of the parameter DB_UNIQUE_NAME. Also we are specifiying that the broker will connect to the primary using TNS service MOBPRO_COLLAB1_DG. For your convenience here is the entry from tnsnames.ora.

MOBPRO_COLLAB1_DG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = collab1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mobpro)
))

Let us now go back the command line prompt of the broker and run a command to check the configuration.

DGMGRL> show configuration
Configuration - mobpro
Protection Mode: MaxPerformance
Databases:
mobpro_collab1 - Primary database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED

It means we have created a configuration by name called mobpro with the primary database called mobpro_collab1and the status is disabled, which clearly indicates that we should now go ahead and enable this configuration.

Before we enable the configuration, its quiet obvious we are missing something here, we  are missing the standby configuration details, just like how we created a configuration by specifying the name of the primary, now its time that to the configuration created above we need to attach the details of the standby, let us go ahead and complete this.

DGMGRL> ADD DATABASE MOBPRO_COLLAB2 AS CONNECT IDENTIFIER IS MOBPRO_COLLAB2_DG;


Database "mobpro_collab2" added

DGMGRL> SHOW CONFIGURATION

Configuration - mobpro
Protection Mode: MaxPerformance
Databases:
mobpro_collab1 - Primary database
mobpro_collab2 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED

MOBPRO_COLLAB2  is the db_unique_name of the standby database and MOBPRO_COLLAB2_DG is the TNS service name which connects to standby from the primary. For your convenience here is the entry.

MOBPRO_COLLAB2_DG =

(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = collab2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mobpro)
))

Let us recap as to what we have done so far

• Created a configuration called mobpro, indicated the name of the primary along with the connect identifier
• Added the details of the Standby Database along with the connect identifier
• As of now the configuration details are stored in the files indicated by the parameter dg_broker_config_file1 & dg_broker_config_file2. However the configuraion is still in disabled state

We can see the different properties our configuration has with respect to the primary and the standby as shown below.

DGMGRL> show database verbose 'mobpro_collab1';

Database - mobpro_collab1
Role: PRIMARY
Intended State: OFFLINE
Instance(s):
mobpro
Properties:
DGConnectIdentifier = 'mobpro_collab1_dg'
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 = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'collab1'
SidName = 'mobpro'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=collab1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=MOBPRO_COLLAB1_DGMGRL)(INSTANCE_NAME=mobpro)(SERVER=DEDICATED)))'
standbyArchiveLocation = '/u01/app/oracle/admin/mobpro/arch'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status:
DISABLED


DGMGRL> show database verbose 'mobpro_collab2';

Database - mobpro_collab2
Role: PHYSICAL STANDBY
Intended State: OFFLINE
Transport Lag: (unknown)
Apply Lag: (unknown)
Real Time Query: OFF
Instance(s):
mobpro
Properties:
DGConnectIdentifier = 'mobpro_collab2_dg'
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 = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'collab2'
SidName = 'mobpro'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=collab2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=mobpro_collab2_DGMGRL)(INSTANCE_NAME=mobpro)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/u01/app/oracle/admin/mobpro/arch'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status:
DISABLED

Now the configuration is all set to be enabled

DGMGRL> ENABLE CONFIGURATION;

Enabled.

DGMGRL> SHOW CONFIGURATION;

Configuration - mobpro
Protection Mode: MaxPerformance
Databases:
mobpro_collab1 - Primary database
mobpro_collab2 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

We have completed the configuration of the broker, please look forward in the coming days on using the broker for effectively managing the environment.