11gR2 DataGuard Broker Setup

This article would describe DataGuard Broker Setup on 11gR2 RAC database.

Pre-Requisite

We need to have dataguard setup already done before setting up DG broker. In case if you use Grid Control, then both setups can be done at same time. You can refer to  article  11gR2 RAC DataGuard setup using ASM for manually setting up Dataguard on ASM.

Environment Details

We will be following environment for our setup.  tnsnames.ora and listener.ora entries can be found in 11gR2 RAC DataGuard setup using ASM

OS - RHEL5
Database Version - 11.2.0.3
DB Host - oradbdev01,oradbdev02
Primary DB Unique Name - tintin
BCP DB Unique Name - snowy

ORACLE_HOME=/home/oracle/product/11.2.0/dbhome_1
ORACLE_GRID=/home/oragrid/product/11.2.0/grid

oradbdev01[tintin1]:/home/oracle>srvctl status db -d tintin
Instance tintin1 is running on node oradbdev01
Instance tintin2 is running on node oradbdev02
oradbdev01[tintin1]:/home/oracle>srvctl status db -d snowy
Instance snowy2 is running on node oradbdev01
Instance snowy1 is running on node oradbdev02

Configuration

DG broker configuration is stored in config files whose location is controlled by dg_broker_config_file1 and dg_broker_config_file2 parameters. In case of RAC this has to be on shared location. Since we are using ASM, we will use it for storing the files. We will create two directories in Diskgroup +DATA and +FLASH for storing the files for both primary and standby database

ASMCMD> cd DATA/tintin
ASMCMD> pwd
+DATA/tintin
ASMCMD> mkdir DGBROKER
ASMCMD> mkdir DGBROKER
ASMCMD> pwd
+FLASH/tintin
ASMCMD> pwd
+DATA/SNOWY
ASMCMD> mkdir DGBROKER
ASMCMD> cd ../../FLASH/SNOWY
ASMCMD> pwd
+FLASH/SNOWY
ASMCMD> mkdir DGBROKER

Now we set the Initialization parameters in Primary database i.e tintin

alter system set dg_broker_config_file1='+DATA/tintin/DGBROKER/dr1tintin.dat' scope=both sid='*';
alter system set dg_broker_config_file2='+FLASH/tintin/DGBROKER/dr2tintin.dat' scope=both sid='*';

Similarly we setup parameters for Standby database i.e snowy

alter system set dg_broker_config_file1='+DATA/snowy/DGBROKER/dr1snowy.dat' scope=both sid='*';
alter system set dg_broker_config_file2='+FLASH/snowy/DGBROKER/dr2snowy.dat' scope=both sid='*';

We will now start DG Broker at both places by setting parameter dg_broker_start to true

alter system set dg_broker_start=true scope=BOTH sid='*';

This will start Data Guard monitor process(DMON)

oradbdev01:snowy2:/home/oracle>ps -ef|grep _dmon|grep -v grep
oracle   23867     1  0 Feb16 ?        00:00:02 ora_dmon_tintin1
oracle   26408     1  0 06:01 ?        00:00:00 ora_dmon_snowy2

DMON process is the server-side component that interacts with the local database and the DMON processes of the other databases to perform the requested function.It is also responsible for monitoring the health of the broker configuration and for ensuring that every database has a consistent description of the configuration.
We can  use dgmgrl/Grid Control to manage DG broker configuration. In this article we will be using dgmgrl utility

Setup

We need to connect to dgmgrl and connect to database using sys username and password. You can also use '/ as sysdba' authentication, but it will cause issues when you try to switchover as DG broker will error out.

DGMGRL> connect sys/oracle123
Connected.

DGMGRL> show configuration
ORA-16532: Data Guard broker configuration does not exist

Since this is new setup, there is no configuration currently in database. Let's add tintin database to the configuration.

DGMGRL> create configuration tintin as primary database is tintin connect identifier is tintin_dgmgrl;
Configuration "tintin" created with primary database "tintin"

Now we can see tintin database has been added to configuration

DGMGRL> show configuration

Configuration - tintin

  Protection Mode: MaxPerformance
  Databases:
    tintin - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

Add standby database to configuration

DGMGRL> add database snowy as connect identifier is snowy_dgmgrl maintained as physical;
Database "snowy" added

DGMGRL> show configuration

Configuration - tintin

  Protection Mode: MaxPerformance
  Databases:
    tintin - Primary database
    snowy  - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

Now we see that show configuration is showing that both databases have been added to configuration. Configuration Status is still set to Disabled. You can use below command to enable the configuration (meaning DG broker will start managing dataguard now)

DGMGRL>enable configuration;
Enabled.
DGMGRL> show database tintin;

Database - tintin

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    tintin1
    tintin2

Database Status:
SUCCESS

DGMGRL> show database snowy;

Database - snowy

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    snowy1

We see now that Apply is running on Database -snowy and there is no transport/apply lag.

DG Broker Administration

To Stop Managed recovery

DGMGRL> edit database snowy set state='APPLY-OFF';
Succeeded.

Modifying Apply Instance in RAC

Since this is RAC, we have two instances but apply can be active at only one Instance. Let's say we now need to modify the apply instance to snowy2

DGMGRL> show database snowy;

Database - snowy

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-OFF
  Transport Lag:   0 seconds
  Apply Lag:       (unknown)
  Real Time Query: OFF
  Instance(s):
    snowy1 (apply instance)
    snowy2

Apply is off as we had turned it off.

DGMGRL> edit database snowy set state='APPLY-ON' with apply instance='snowy2';
Succeeded.
DGMGRL> show database snowy;

Database - snowy

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    snowy1
    snowy2 (apply instance)

Change Data Protection mode

In above cases, we are running in Maximum Performance mode i.e ASYNC mode is being used to ship redo. We can verify this by using following command

DGMGRL> show database verbose snowy;

Database - snowy

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    snowy1 (apply instance)
    snowy2

  Properties:
    DGConnectIdentifier             = 'snowy_dgmgrl'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = 'snowy1'
--some output truncated

We would like to switch to Maximum Availability protection mode which means that we will have to set log shipping to SYNC. This can be done by setting property LogXptMode to SYNC. This has to be done for Standby database. But we should do it for primary database at same time so that post switchover we have same mode.

DGMGRL>EDIT DATABASE snowy set property LogXptMode='SYNC';
Property "logxptmode" updated
DGMGRL> edit database tintin set property LogXptMode='SYNC';
Property "logxptmode" updated

Once it's done on all primary/standby in configuration, we set new mode with following commands

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
Succeeded.
DGMGRL> show configuration

Configuration - tintin

  Protection Mode: MaxAvailability
  Databases:
    tintin - Primary database
    snowy  - Physical standby database

Fast-Start Failover: DISABLED

Database Switchover

Database switchover can be done by using single command i.e switchover to (standby_name). To do this we need to first ensure that there is no standby lag

DGMGRL> show database snowy;

Database - snowy

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    snowy1 (apply instance)
    snowy2

Database Status:
SUCCESS

Next is to execute the switchover command

DGMGRL> switchover to snowy;

Leave a Reply