Step-By-Step Configuration Of Data Guard Broker in Oracle 11g
As we have already discuss about the Data Guard Broker and its benefits in earlier post . Here we will configure the data Guard Broker . Here are the steps :
Primary Databse = Noida
Standby Database = Delhi
Step 1 : Check the Data Guard Broker process
SQL> sho parameter dg_broker
NAME TYPE VALUE
----------------- ---------- ----------
dg_broker_start boolean FALSE
Step 2 : Start the Data Guard Broker Process on Primary database
SQL>alter system set dg_broker_start=true scope=both;
System altered.
Step 3 : Check DG_BROKER on standby database and start it
SQL> sho parameter dg_broker
NAME TYPE VALUE
----------------- ---------- ----------
dg_broker_start boolean FALSE
SQL>alter system set dg_broker_start=true scope=both ;
System altered.
Step 4 : Edit the listener.ora file
SQL> show parameter db_domain
NAME TYPE VALUE
-------------- ----------- --------------
db_domain string
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = noida_DGMGRL)
(ORACLE_HOME = C:\app\neerajs\product\11.2.0\dbhome_1)
(SID_NAME = noida)
)
)
Similarly, edit the listener.ora file on standby database .
Step 5 : Configure the Data Guard Configuration
C:\> dgmgrl
DGMGRL for 32-bit Windows: Version 11.2.0.1.0 - Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/xxxx@noida
Connected.
DGMGRL> create configuration 'dgnoida'
> as primary database is 'noida'
> connect identifier is noida ;
Configuration "dgnoida" created with primary database "noida" .
Once the configuration is created then check the status of configuration .
DGMGRL> show configuration
Configuration - dgnoida
Protection Mode : MaxPerformance
Databases : noida - Primary database
Fast-Start Failover : DISABLED
Configuration Status : DISABLED
Step 6 : Add standby database to the data broker configuration
DGMGRL> add database 'delhi' as
> connect identifier is delhi
> maintained as physical ;
Database "delhi" added
DGMGRL> show configuration
Configuration - dgnoida
Protection Mode : MaxPerformance
Databases : noida - Primary database
: delhi - Physical standby database
Fast-Start Failover : DISABLED
Configuration Status : DISABLED
Step 7 : Enable the configuration
DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration
Configuration - dgnoida
Protection Mode : MaxPerformance
Databases : noida - Primary database
: delhi - Physical standby database
Fast-Start Failover : DISABLED
Configuration Status : SUCCESS
Step 8 : View the Primary and Standby database properties
DGMGRL> show database verbose noida
Database - noida
Role : PRIMARY
Intended State : TRANSPORT-ON
Instance(s) : noida
Properties:
DGConnectIdentifier = 'noida'
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 = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'TECH-199'
SidName = 'noida'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=TECH-199)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=noida_DGMGRL)(INSTANCE_NAME=noida)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'D:\archive\'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'ARC%S_%R.%T'
TopWaitEvents = '(monitor)'
Database Status = SUCCESS
DGMGRL> show database verbose delhi
Database - delhi
Role: PHYSICAL STANDBY
Intended State : APPLY-ON
Transport Lag : 0 seconds
Apply Lag : 0 seconds
Real Time Query : ON
Instance(s) : delhi
Properties:
DGConnectIdentifier = 'delhi'
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 = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = 'C:\app\neerajs\oradata\noida\, D:\app\stand\oradata\, E:\oracle\, D:\app\stand\oradata\'
LogFileNameConvert = 'C:\app\neerajs\oradata\noida\, D:\app\stand\oradata\'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'TECH-284'
SidName = 'delhi'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=TECH-284)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=delhi_DGMGRL)(INSTANCE_NAME=delhi)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'D:\app\stand\archive\'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'ARC%S_%R.%T'
TopWaitEvents = '(monitor)'
Database Status : SUCCESS
DGMGRL>
Standby Database = Delhi
Step 1 : Check the Data Guard Broker process
SQL> sho parameter dg_broker
NAME TYPE VALUE
----------------- ---------- ----------
dg_broker_start boolean FALSE
Step 2 : Start the Data Guard Broker Process on Primary database
SQL>alter system set dg_broker_start=true scope=both;
System altered.
Step 3 : Check DG_BROKER on standby database and start it
SQL> sho parameter dg_broker
NAME TYPE VALUE
----------------- ---------- ----------
dg_broker_start boolean FALSE
SQL>alter system set dg_broker_start=true scope=both ;
System altered.
Step 4 : Edit the listener.ora file
Edit the listener.ora file which includes the db_unique_name_DGMGRL.db_domain values for theGLOBAL_DBNAME in both primary and standby database . To set the value, lets check the db_domainvalue .
SQL> show parameter db_domain
NAME TYPE VALUE
-------------- ----------- --------------
db_domain string
Since the value of db_domain is null so the the value of GLOBAL_DBNAME = NOIDA_DGMGRL for primary database and for standby GLOBAL_DBNAME = DELHI_DGMGRL. The primary listener.ora file is as
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = noida_DGMGRL)
(ORACLE_HOME = C:\app\neerajs\product\11.2.0\dbhome_1)
(SID_NAME = noida)
)
)
Similarly, edit the listener.ora file on standby database .
Step 5 : Configure the Data Guard Configuration
C:\> dgmgrl
DGMGRL for 32-bit Windows: Version 11.2.0.1.0 - Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/xxxx@noida
Connected.
DGMGRL> create configuration 'dgnoida'
> as primary database is 'noida'
> connect identifier is noida ;
Configuration "dgnoida" created with primary database "noida" .
Once the configuration is created then check the status of configuration .
DGMGRL> show configuration
Configuration - dgnoida
Protection Mode : MaxPerformance
Databases : noida - Primary database
Fast-Start Failover : DISABLED
Configuration Status : DISABLED
Step 6 : Add standby database to the data broker configuration
DGMGRL> add database 'delhi' as
> connect identifier is delhi
> maintained as physical ;
Database "delhi" added
DGMGRL> show configuration
Configuration - dgnoida
Protection Mode : MaxPerformance
Databases : noida - Primary database
: delhi - Physical standby database
Fast-Start Failover : DISABLED
Configuration Status : DISABLED
Step 7 : Enable the configuration
DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration
Configuration - dgnoida
Protection Mode : MaxPerformance
Databases : noida - Primary database
: delhi - Physical standby database
Fast-Start Failover : DISABLED
Configuration Status : SUCCESS
Step 8 : View the Primary and Standby database properties
DGMGRL> show database verbose noida
Database - noida
Role : PRIMARY
Intended State : TRANSPORT-ON
Instance(s) : noida
Properties:
DGConnectIdentifier = 'noida'
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 = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'TECH-199'
SidName = 'noida'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=TECH-199)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=noida_DGMGRL)(INSTANCE_NAME=noida)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'D:\archive\'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'ARC%S_%R.%T'
TopWaitEvents = '(monitor)'
Database Status = SUCCESS
DGMGRL> show database verbose delhi
Database - delhi
Role: PHYSICAL STANDBY
Intended State : APPLY-ON
Transport Lag : 0 seconds
Apply Lag : 0 seconds
Real Time Query : ON
Instance(s) : delhi
Properties:
DGConnectIdentifier = 'delhi'
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 = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = 'C:\app\neerajs\oradata\noida\, D:\app\stand\oradata\, E:\oracle\, D:\app\stand\oradata\'
LogFileNameConvert = 'C:\app\neerajs\oradata\noida\, D:\app\stand\oradata\'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'TECH-284'
SidName = 'delhi'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=TECH-284)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=delhi_DGMGRL)(INSTANCE_NAME=delhi)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'D:\app\stand\archive\'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'ARC%S_%R.%T'
TopWaitEvents = '(monitor)'
Database Status : SUCCESS
DGMGRL>
