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;