In this post i will setup dataguard broker services for managing dataguard setup. Follwing are prerequisites for this setup:
1) Fully functional Primary and Standby setup
2) Spfile must be in use at primary and standby database
3) DG_BROKER_START must be set TRUE at both primary and standby
4) DG_BROKER_CONFIG_FILEn parameter is optional
5) GLOBAL_DBNAME must be set in listener.ora with predefined format
My assumption in this post are Primary database name is CORE and standby database name is CORESTD. Standby setup is running fine. Among the above requirements, we have already fulfilled the requirements 1 – 4 during our init.ora parameters settings. Following is the entry for GLOBAL_DBNAME in listener.ora file of both the primary and standby databases. This is a basic requirement for broker setup and following is example from the standby database. Make necessary change at primary DB listener too, only host name will be changed in most of the cases.
SID_LIST_LISTENER_CORE =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/incore/product/11.2.0.1/dbhome_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = CORE)
(ORACLE_HOME = /u01/app/incore/product/11.2.0.1/dbhome_1)
(SID_NAME = CORE)
)
(SID_DESC =
(GLOBAL_DBNAME = CORE_DGMGRL.in.isol.net)
(ORACLE_HOME = /u01/app/incore/product/11.2.0.1/dbhome_1)
(SID_NAME = CORE)
)
)
LISTENER_CORE =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ind-del-db02-s)(PORT = 1522))
)
)
)
Add the standby redo logfiles at both primary and standby side, if they are not already added. Once these are added, we are ready for our broker setup. use “DGMGRL” prompt for broker setup.
incore@ind-del-db01-s $ dgmgrl
DGMGRL for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@core
Password:
Connected.
DGMGRL>
DGMGRL>
DGMGRL> create configuration 'DRConfig' as primary database is 'CORE' connect identifier is 'CORE';
Configuration "DRConfig" created with primary database "CORE"
DGMGRL>
DGMGRL>
DGMGRL> show configuration;
Configuration - DRConfig
Protection Mode: MaxPerformance
Databases:
CORE - Primary database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
DGMGRL> add database 'CORESTD' as connect identifier is 'CORESTD';
Database "CORESTD" added
DGMGRL> show configuration;
Configuration - DRConfig
Protection Mode: MaxPerformance
Databases:
CORE - Primary database
CORESTD - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;
Configuration - DRConfig
Protection Mode: MaxPerformance
Databases:
CORE - Primary database
CORESTD - Physical standby database
Error: ORA-16664: unable to receive the result from a database
Fast-Start Failover: DISABLED
Configuration Status:
ERROR
As the configuration status is error, we need to resolve this issue before we can use broker to manage our standby. Use the following command to see the error in Standby configuration.
DGMGRL> show database verbose 'CORESTD';
Database - CORESTD
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 3 minutes 45 seconds
Real Time Query: OFF
Instance(s):
CORE
Warning: ORA-16714: the value of property ArchiveLagTarget is inconsistent with the database setting
Warning: ORA-16714: the value of property LogArchiveMaxProcesses is inconsistent with the database setting
Warning: ORA-16714: the value of property LogArchiveMinSucceedDest is inconsistent with the database setting
Warning: ORA-16714: the value of property LogArchiveTrace is inconsistent with the database setting
Warning: ORA-16714: the value of property LogArchiveFormat is inconsistent with the database setting
Database Warning(s):
ORA-16826: apply service state is inconsistent with the DelayMins property
Properties:
DGConnectIdentifier = 'CORESTD'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
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 = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '/u04/oradata/CORE, /u04/oradata/CORE/CORE'
LogFileNameConvert = '/u02/oradata/CORE, /u02/oradata/CORE/CORE, /u03/oradata/CORE, /u03/oradata/CORE/CORE'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'ind-del-db02-s'
SidName = 'CORE'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ind-del-db02-s)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=CORESTD_DGMGRL.in.isol.net)(INSTANCE_NAME=CORE)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status:
WARNING
DGMGRL> edit database 'CORESTD' set property 'ArchiveLagTarget'=0;
Property "ArchiveLagTarget" updated
DGMGRL>
DGMGRL> edit database 'CORESTD' set property 'LogArchiveMaxProcesses'=4;
Property "LogArchiveMaxProcesses" updated
DGMGRL> edit database 'CORESTD' set property 'LogArchiveMinSucceedDest'=1;
Property "LogArchiveMinSucceedDest" updated
DGMGRL> edit database 'CORESTD' set property 'LogArchiveTrace'=0;
Property "LogArchiveTrace" updated
DGMGRL>
DGMGRL> edit database 'CORESTD' set property 'LogArchiveFormat'='%t_%s_%r.dbf';
Property "LogArchiveFormat" updated
DGMGRL>
DGMGRL> edit database 'CORESTD' set property 'DelayMins'=0;
Property "DelayMins" updated
Once, you corrected all the warning messages, now command should return the with success message.
DGMGRL> show database verbose 'CORESTD';
Database - CORESTD
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: OFF
Instance(s):
CORE
Properties:
DGConnectIdentifier = 'CORESTD'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
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 = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '/u04/oradata/CORE, /u04/oradata/CORE/CORE'
LogFileNameConvert = '/u02/oradata/CORE, /u02/oradata/CORE/CORE, /u03/oradata/CORE, /u03/oradata/CORE/CORE'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'ind-del-db02-s'
SidName = 'CORE'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ind-del-db02-s)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=CORESTD_DGMGRL.in.isol.net)(INSTANCE_NAME=CORE)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status:
SUCCESS
Now, check the configuration, it should be success.
DGMGRL> show configuration;
Configuration - DRConfig
Protection Mode: MaxPerformance
Databases:
CORE - Primary database
CORESTD - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
Now, we are ready to use the broker service for managing our standby by setup. To disable the archive shipping to standby we use the following command in SQL:
SQL> alter system set log_archive_dest_state_2='DEFER' scope=both;
System altered.
Now, we will be using below command for disabling the archive shipping.
DGMGRL> edit database 'CORE' set state=transport-off;
Succeeded.
DGMGRL>
Similarly, to cancel the managed mode of standby database use the below command.
DGMGRL> connect sys@corestd
Password:
Connected.
DGMGRL>
DGMGRL>
DGMGRL> edit database 'CORESTD' set state='APPLY-OFF';
Succeeded.
0.000000
0.000000
ORASteps 