In earlier versions of Oracle, the Data Guard Broker has been regarded with suspicion. However, in Oracle 11gR2 the Data Guard Broker is much more stable and has become the preferred tool for managing Data Guard configurations.
Given a solid foundation, implementing the Broker is almost trivial.
This page assumes that the db_name is PROD and that the db_unique_names are PROD and STBY. These names follow my customers existing standards; where possible I prefer to use geographic names for the DB_UNIQUE_NAME parameter as I think it is easier to visualize the configuration during role management.
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 in a RAC environment the SID_NAME will be different on each node, so don't copy the listener.ora file between nodes.
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.
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:
The default values 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).
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 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 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
DGMGRL> ENABLE CONFIGURATION\nEnabled.
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.\nOperation requires startup of instance "PROD" on database "PROD" Starting instance "PROD"... ORACLE instance started. Database mounted. Switchover succeeded, new primary is "STBY"If the environment has been correctly configured then switchover generally works first time. However, it can take a few minutes to complete the switchover operation. If the operation does not complete immediately then don't panic. Wait for a few minutes and then check the status again.
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.
The configuration can be disabled at any time using:
DGMGRL> DISABLE CONFIGURATION Disabled.
The configuration can be removed using:
DGMGRL> REMOVE CONFIGURATION Removed 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\n 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
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
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: SUCCESSI 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)))'\n StandbyArchiveLocation = '+FRA2' AlternateLocation = '' LogArchiveTrace = '0' LogArchiveFormat = '%t_%s_%r.dbf' TopWaitEvents = '(monitor)' Database Status: SUCCESS