DataGuard Broken?
A guest blog from colleague Simon Ryan
Last year we did some sandpit work on implementing DataGuard. This was 10g on a Windows platform. When it came to the broker we didn’t have much luck. At the UKOUG Michael Moller referred to the Broker as DataGuard Broken (DataGuard 11g New Features). From the audience reaction this seemed to be a popular perception.
Since then we’ve successfully implemented DataGuard on most of our Production databases (both 10g and 11g) on HP-UX. However, we’ve still not embraced the Broker! The ‘broken’ tag still taints its reputation. A recent visit by Oracle attempted to assure us that the Broker with 11g is much improved.
The following is a basic guide to implementing the broker and our findings:
Our Setup
o Primary – Two node RAC Cluster (XXXPRD1A)
o Physical Standby – Single node (XXXPRD1B)
o HP-UX 11.31 Itanium
o Database using ASM (both 11.1.0.7).
o DataGuard running in Max Availability with Real Time Apply.
Configuration
Add the following entries to tnsnames.ora on all instances:
XXXPRD1A_DGMGRL =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=xxxem03-vip)(PORT=1526))
(CONNECT_DATA=
(SERVICE_NAME=XXXPRD1A_DGMGRL)
)
)
XXXPRD2A_DGMGRL =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=xxxem04-vip)(PORT=1526))
(CONNECT_DATA=
(SERVICE_NAME=XXXPRD2A_DGMGRL)
)
)
XXXPRD1B_DGMGRL =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=xxxem01-vip)(PORT=1526))
(CONNECT_DATA=
(SERVICE_NAME=XXXPRD1B_DGMGRL)
)
)
Add the following entry to SID_LIST of your listener in listener.ora on all instances:
(SID_DESC =
(ORACLE_HOME = /app/oracle/product/11.1.0/db_1)
(SID_NAME = XXXPRDxx)
(GLOBAL_DBNAME = XXXPRDxx_DGMGRL)
)
Stop/start listener.
Test you can now connect:
sqlplus sys@XXXPRD2A_dgmgrl as sysdba
Make the following parameter changes:
XXXPRD1A:
<pre>
alter system set dg_broker_start=true scope=spfile;
alter system set dg_broker_config_file1='+DATA/XXXPRD1A/broker1.dat' scope=spfile;
alter system set dg_broker_config_file2='+FRA/XXXPRD1A/broker2.dat' scope=spfile;
XXXPRD1B:
alter system set dg_broker_start=true scope=spfile;
alter system set dg_broker_config_file1='+DATA/XXXPRD1B/broker1.dat' scope=spfile;
alter system set dg_broker_config_file2='+FRA/XXXPRD1B/broker2.dat' scope=spfile;
Stop/Start the databases:
Stop XXXPRD1A:
<pre>
srvctl stop database -d XXXPRD1A -o immediate
Stop XXXPRD1B:
shutdown immediate
Start XXXPRD1B:
startup mount
Start XXXPRD1A:
srvctl start database -d XXXPRD1A
Check for the broker process:
XXXPRD1A:
<pre>
ps –ef | grep ora_dmon_XXXPRD1A
XXXPRD1B:
ps –ef | grep ora_dmon_XXXPRD1B
Create DG Broker Configuration:
1 Primary:
dgmgrl
connect sys
<em>enter sys password</em>
create configuration XXXPRD1A_DGCONFIG as primary database is “XXXPRD1A” connect identifier is XXXPRD1A;
Configuration "XXXPRD1A_dgconfig" created with primary database "XXXPRD1A"
add database “XXXPRD1B” as connect identifier is XXXPRD1B maintained as physical;
Database "XXXPRD1B" added
enable configuration;
Enabled.
show configuration;
Configuration
Name: XXXPRD1A_dgconfig
Enabled: YES
Protection Mode: MaxAvailability
Databases:
XXXPRD1A - Primary database
XXXPRD1B - Physical standby database
Fast-Start Failover: DISABLED
Current status for "XXXPRD1A_dgconfig":
SUCCESS
Check Broker Files Exist:
Primary and Standby ASM:
Check they are there.
Show Database:
Primary:
show database verbose “XXXPRD1A”;
Database
Name: XXXPRD1A
Role: PRIMARY
Enabled: YES
Intended State: TRANSPORT-ON
Instance(s):
XXXPRD1A
XXXPRD2A
Properties:
DGConnectIdentifier = 'XXXPRD1A'
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 = '10'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = 'XXXPRD1B, XXXPRD1A'
LogFileNameConvert = 'XXXPRD1B, XXXPRD1A'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName(*)
SidName(*)
StaticConnectIdentifier(*)
StandbyArchiveLocation(*)
AlternateLocation(*)
LogArchiveTrace(*)
LogArchiveFormat(*)
LatestLog(*)
TopWaitEvents(*)
(*) - Please check specific instance for the property value
Current status for "XXXPRD1A":
SUCCESS
Standby:
show database verbose “XXXPRD1B”;
Database
Name: XXXPRD1B
Role: PHYSICAL STANDBY
Enabled: YES
Intended State: APPLY-ON
Instance(s):
XXXPRD1B
Properties:
DGConnectIdentifier = 'XXXPRD1B'
ObserverConnectIdentifier = ''
LogXptMode = 'SYNC'
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '60'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '10'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = 'XXXPRD1A, XXXPRD1B'
LogFileNameConvert = 'XXXPRD1A, XXXPRD1B'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'xxxem01'
SidName = 'XXXPRD1B'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxem01)(PORT=1526))(CONNECT_DATA=(SERVICE_NAME=XXXPRD1B_DGMGRL)(INSTANCE_NAME=XXXPRD1B)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
LatestLog = '(monitor)'
TopWaitEvents = '(monitor)'
Current status for "XXXPRD1B":
SUCCESS
Ensure logs are still being transported and applied
All still works!
What does it give us?
- Improved monitoring in OEM.
- Simpler administration via DGMGRL or OEM i.e. changing Protection Mode.
- Easy switchover/failover
- DGMGRL: switchover to “XXXPRD1A”
- Click of a button in OEM.
- Fast-Start Failover (with an observer).
Conclusions
The broker configuration was relatively painless and I have successfully performed a switchover via both DGMGRL and OEM. I’ve also used the various OEM functions without issue too. I’m uneasy about performing such tasks via OEM but then most DBA’s prefer to be at the helm via the command line rather than clicking a button (that’s too easy!).
If you plan to use DataGuard Broker then embrace it and make sure you don’t alter parameters behind the covers. It does that for you and it doesn’t like you meddling! I also think it’s a good idea to setup DataGuard initially without the broker and then configure it once you’re happy that DataGuard is working.
So what’s made the difference since our experience last year? UNIX v Windows (maybe), 10g v 11g (probably). A big factor would also have to be our experience level. In the last year we’ve increased our DataGuard count from zero to ten so we are therefore much more comfortable in the DataGuard world.
When a sand-pit database becomes available I’d like to try and setup the observer and test Fast-Start Failover. I’m inclined to remove the ‘broken’ tag but I’ll reserve judgment until I’ve tested the remaining functions.
Simon Ryan
