Configure Dataguard Broker

Configure LISTENER.ORA

First, on each node add a static entry to the LISTENER.ORA file. For example:

SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC =
     (GLOBAL_DBNAME = PROD_DGMGRL)
     (ORACLE_HOME= /u01/app/oracle/product/11.2.0/dbhome_1)
     (SID_NAME = PROD)
   )
  )

The Data Guard Broker defaults to static service names with the DGMGRL suffix. It is possible to override the default service names, but in this case I chose the lazy option and stuck with the Oracle defaults.
Note that listener.ora will be different on each node, so don’t copy it around.

Configure TNSNAMES.ORA

We also specified entries for both the primary and standby databases in $ORACLE_HOME/network/admin/tnsnames.ora. For example:

PROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL=TCP)(HOST=server14.juliandyke.com)(PORT=1521))
   (CONNECT_DATA = 
       (SERVER = DEDICATED)
       (SERVICE_NAME = PROD)
   )
)
STBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL=TCP)(HOST=server15.juliandyke.com)(PORT=1521))
   (CONNECT_DATA = 
       (SERVER = DEDICATED)
       (SERVICE_NAME = STBY)
   )
)

In this example, the PROD database will run on server14 and the STBY database will run on server15.

Enable Data Guard Broker

Next enable the Data Guard Broker for each database (primary and standby) in the configuration by setting the DG_BROKER_START parameter to TRUE.

ALTER SYSTEM SET dg_broker_start = TRUE;

The Data Guard Broker maintains two copies of its configurations. The locations of these files are specified by the DG_BROKER_CONFIG_FILE1 and DG_BROKER_CONFIG_FILE2 parameters. For a single instance database, the default values are:

• $ORACLE_HOME/dbs/dr1<db_unique_name>.dat
• $ORACLE_HOME/dbs/dr2<db_unique_name>.dat

The defaults are probably adequate for a single instance database; in a RAC environment the defaults should be modified to specify a location in a shared file system or ASM diskgroup(s).

Start DGMGRL

On any node in the configuration, start the Data Guard Broker command utility (DGMGRL) and connect as the SYS user.

[oracle@server14]$ dgmgrl
DGMGRL> CONNECT SYS
Password: <Enter Password>

Note that it is not necessary to specify the SYSDBA privilege as this is assumed by DGMGRL.

Create Data Guard Broker Configuration 

Create a configuration. For example:

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

You will need to specify a configuration name and the unique name of the primary database.

Add standby database(s)

Add all standby databases to the configuration. In this example we just have one physical standby database:
 DGMGRL> ADD DATABASE ‘STBY’ AS
> CONNECT IDENTIFIER IS ‘STBY’;
Database ‘STBY’ added

Enable the configuration

By default the Data Guard configuration is disabled. Enable the configuration as follows:
DGMGRL> ENABLE CONFIGURATION
Enabled.

Switchover to standby

As we started with a working Data Guard configuration, it should be possible to switchover to the standby database at this stage. Switchover is achieved using a single command e.g.:

DGMGRL> SWITCHOVER TO 'STBY'
Performing switchover NOW, please wait...
New primary database "STBY" is opening...
Operation requires shutdown of instance "PROD" on database "PROD"
Shutting down instance "PROD"...
ORACLE instance shut down.
Operation requires startup of instance "PROD" on database "PROD"
Starting instance "PROD"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "STBY"

Switchover worked first time for us. However, we have experienced problems with the restart of the new standby (old primary) in subsequent tests. As these problems are intermittent we think they may be due to timing issues; the servers have new Intel 6-core processors.

Switchback

Switchback is very similar to switchover.

DGMGRL> SWITCHOVER TO 'PROD'
Performing switchover NOW, please wait...
New primary database "PROD" is opening...
Operation requires shutdown of instance "STBY" on database "STBY"
Shutting down instance "STBY"...
ORACLE instance shut down.
Operation requires startup of instance "STBY" on database "STBY"
Starting instance "STBY"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "PROD"

As with switchover we have seen occasional failures with switchback which again we think are timing-related.
Disabling Configuration
The configuration can be disabled at any time using:

DGMGRL> DISABLE CONFIGURATION
Disabled.

Removing the configuration
The configuration can be removed using:

DGMGRL> REMOVE CONFIGURATION
Removed configuration

We have removed the Data Guard Broker configuration a couple of times to fix problems with underlying parameters; it is possible to edit properties within DGMGRL that update parameters, but we wanted to ensure that the underlying configuration was also correct in case the Broker was subsequently deconfigured.

Checking the Data Guard Configuration

You can check the Data Guard configuration in DGMGRL using the SHOW CONFIGURATION command which has both default and verbose modes. For example:

DGMGRL> SHOW CONFIGURATION
 Configuration - DG1
  Protection Mode: MaxAvailability
  Databases:
    PROD - Primary database
    STBY - Physical standby database
  Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

The verbose option includes some additional properties:

DGMGRL> SHOW CONFIGURATION VERBOSE
Configuration - DG1 
  Protection Mode: MaxAvailability
  Databases:
    PROD - Primary database
    STBY - Physical standby database   
  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

Checking the Primary Database Configuration

The primary database configuration can be checked using the SHOW DATABASE command. This command also has a default and verbose mode:

DGMGRL> SHOW DATABASE 'PROD' 
Database - PROD

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    PROD
Database Status:
SUCCESS

 The verbose mode includes properties for the database, many of which reflect the underlying initialization parameters:

DGMGRL> SHOW DATABASE VERBOSE 'PROD'
 Database - PROD
   Role:            PRIMARY
   Intended State:  TRANSPORT-ON
  Instance(s):
    PROD
 Properties:
  DGConnectIdentifier            = 'PROD'
  ObserverConnectIdentifier      = ''
  LogXptMode                     = 'SYNC'
  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         = '5'
  LogArchiveMinSucceedDest       = '1'
  DbFileNameConvert              = '+DATA2, +DATA1'
  LogFileNameConvert             = '+FRA2, +FRA1'
  FastStartFailoverTarget        = ''
  InconsistentProperties         = '(monitor)'
  InconsistentLogXptProps        = '(monitor)'
  SendQEntries                   = '(monitor)'
  RecvQEntries                   = '(monitor)'
  SidName                        = 'PROD'
  StaticConnectIdentifier        =
  '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.227.27.1)
   (PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PROD_DGMGRL)
  (INSTANCE_NAME=PROD)(SERVER=DEDICATED)))'
  StandbyArchiveLocation         = '+FRA1'
  AlternateLocation              = ''
  LogArchiveTrace                = '0'
  LogArchiveFormat               = '%t_%s_%r.dbf'
  TopWaitEvents                  = '(monitor)'
Database Status:
SUCCESS

Checking the Physical Standby Database Configuration

The physical standby database configuration can also be checked using the SHOW DATABASE command. Again this command has a default and verbose mode:

DGMGRL> SHOW DATABASE 'STBY'
Database - STBY 
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    STBY
Database Status:
SUCCESS

I can never remember the names of the Data Guard dynamic performance views so I really like the Transport Lag and Apply Lag fields in the above output.
Again the verbose mode includes properties for the standby database:

DGMGRL> SHOW DATABASE VERBOSE 'STBY'
Database - STBY 
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    STBY 
Properties:
  DGConnectIdentifier            = 'STBY'
  ObserverConnectIdentifier      = ''
  LogXptMode                     = 'SYNC'
  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         = '5'
  LogArchiveMinSucceedDest       = '1'
  DbFileNameConvert              = '+DATA1, +DATA2'
  LogFileNameConvert             = '+FRA1, +FRA2'
  FastStartFailoverTarget        = ''
  InconsistentProperties         = '(monitor)'
  InconsistentLogXptProps        = '(monitor)'
  SendQEntries                   = '(monitor)
  RecvQEntries                   = '(monitor)'
  SidName                        = 'PROD'
  StaticConnectIdentifier        =
  '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.221.27.2)
  (PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STBY_DGMGRL)
  (INSTANCE_NAME=STBY)(SERVER=DEDICATED)))'
  StandbyArchiveLocation         = '+FRA2'
  AlternateLocation              = ''
  LogArchiveTrace                = '0'
  LogArchiveFormat               = '%t_%s_%r.dbf'
  TopWaitEvents                  = '(monitor)'
 Database Status:
SUCCESS