In this post, I am going to share the details regarding Data Guard Broker. The article is valid for 10g and 11g. There is no difference with 11g DGMGRL configuration, but there are differences to the broker parameters/properties.
For instance, in 10g DGMGRL the parameter is LocalListenerAddress while in 11g it is replaced with StaticConnectIdentifier.
For instance, in 10g DGMGRL the parameter is LocalListenerAddress while in 11g it is replaced with StaticConnectIdentifier.
What is Data Guard Broker?
It is a framework that centralize managing and monitoring of primary and standby database as a single unit. We can switchover and failover with a single commands there by resulting in database role changes. Broker configuration can be managed by Oracle Enterprise Manager (OEM/Grid) or using Command-Line Interface (DGMGRL).
Note:
- Below is an example of 10g DGMGRL configuration.
- This example assumes that you have the primary and standby setup complete and logs are being applied. Both Primary and Standby database use SPFILE.
DB_NAME/Primary/Standby Database: APL
Primary DB Unique Name: APL
Primary Hostname: apldb.vaidiyan.com (or vaid2)
Standby DB Unique Name: APLSTY
Standby Hostname: aplsbdb.vaidiyan.com (or vaid1)
OS: HP Unix (UX) Itanium
Database Flavor: Enterprise Edition
Primary Database(APL):
File System: /p
1. Update the listener.ora file changes on Primary Database ($ORACLE_HOME/network/admin) with static entry for DGMGRL. The GLOBAL_DBNAME parameter must be defined as db_unique_name_DGMGRL.db_domain. This avoids the ORA-12154 error when switchover happens.
APL =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = apldb.vaidiyan.com)(PORT = 1532))))
SID_LIST_APL =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME= APL_DGMGRL.vaidiyan.com)
(ORACLE_HOME= /p01/app/oracle/aplora/10.2.0)
(SID_NAME = APL)
(SERVICE_NAME=APL)
)
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /p01/app/oracle/aplora/10.2.0)
(PROGRAM = extproc)
)
)
2. Make sure the tnsnames for Primary and Standby are added to tnsnames.ora ($ORACLE_HOME/network/admin). Test it with tnsping.
vaid2> tnsping APL
TNS Ping Utility for HPUX: Version 10.2.0.3.0 - Production on 06-AUG-2013 17:37:00
Copyright (c) 1997, 2006, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP) (HOST = apldb.vaidiyan.com)(PORT = 1532))) (CONNECT_DATA = (SID = APL)))
OK (0 msec)
vaid2> tnsping APLSTY
TNS Ping Utility for HPUX: Version 10.2.0.3.0 - Production on 06-AUG-2013 17:37:04
Copyright (c) 1997, 2006, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP) (HOST = aplsbdb.vaidiyan.com)(PORT = 1532))) (CONNECT_DATA = (SID = APL)))
OK (0 msec)
3. Enable LOCAL_LISTENER parameter in the database.
Note: This is required if your listener.ora service name is using a non-default port ie. other than 1521. In this example, the database listener port in 1532, it is mandatory to set this parameter.
ALTER SYSTEM SET LOCAL_LISTENER='APL' SCOPE=BOTH;
4. Edit the SPFILE for Dataguard Broker Parameters - DG_BROKER_START and DB_BROKER_CONFIG_FILEn
SQL>show parameter dg
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string /p01/app/oracle/aplora/1
0.2.0/dbs/dr1APL.dat
dg_broker_config_file2 string /p01/app/oracle/aplora/1
0.2.0/dbs/dr2APL.dat
dg_broker_start boolean FALSE
SQL>
ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;
You can verify the Data Guard process at OS level by:
oracle 892 1 0 10:55:09 ? 0:02 ora_dmon_APL
vaid2>
You can also change the DB_BROKER_CONFIG_FILEn parameter from default location -
ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1='/p02/app/oracle/aplora/dr1APL.dat' SID='*';
Standby Database (APLSTY)
File System: /s
a. Similar to how we updated the listener.ora on primary database, edit the listener.ora file changes on Standby Database
($ORACLE_HOME/network/admin) with static entry for DGMGRL. The
GLOBAL_DBNAME parameter must be defined as
_DGMGRL.db_domain.
In the example, both listener names on Primary and Standby are the same as 'APL' but hostname and GLOBAL_DBNAME are different.
APL =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = aplsbdb.vaidiyan.com)(PORT = 1532)))
)
SID_LIST_APL =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME= APLSTY_DGMGRL.vaidiyan.com)
(ORACLE_HOME= /s01/app/oracle/aplora/10.2.0)
(SID_NAME = APL)
(SERVICE_NAME = APLSTY)
)
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /s01/app/oracle/aplora/10.2.0)
(PROGRAM = extproc)
)
)
b. Add the tnsnames for Primary and Standby are added to
tnsnames.ora ($ORACLE_HOME/network/admin). Test it with tnsping.
vaid1> tnsping APL
TNS Ping Utility for HPUX: Version 10.2.0.3.0 - Production on 06-AUG-2013 17:57:44
Copyright (c) 1997, 2006, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP) (HOST = apldb.vaidiyan.com)(PORT = 1532))) (CONNECT_DATA = (SID = APL)))
OK (0 msec)
vaid1> tnsping APLSTY
TNS Ping Utility for HPUX: Version 10.2.0.3.0 - Production on 06-AUG-2013 17:57:47
Copyright (c) 1997, 2006, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP) (HOST = aplsbdb.vaidiyan.com)(PORT = 1532))) (CONNECT_DATA = (SID = APL)))
OK (10 msec)
c. Enable LOCAL_LISTENER parameter in the database. I am repeating the reason for this parameter again here. If your listener.ora service name is using a non-default port ie. other than 1521, this is required.
ALTER SYSTEM SET LOCAL_LISTENER='APLSTY' SCOPE=BOTH;
SQL>show parameter local_listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string APLSTY
d. Add the Dataguard Broker Parameters - DG_BROKER_START and DB_BROKER_CONFIG_FILEn to the SPFILE.
SQL>show parameter dg
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string /s01/app/oracle/aplora/1
0.2.0/dbs/dr1APLSTY.dat
dg_broker_config_file2 string /s01/app/oracle/aplora/1
0.2.0/dbs/dr2APLSTY.dat
dg_broker_start boolean FALSE
SQL>
ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;
You can verify the Data Guard process at OS level by:
vaid1> ps -ef|grep dmon|grep -v grep
oracle 11303 1 0 10:55:06 ? 0:00 ora_dmon_APL
vaid1>
I am using the default location for DB_BROKER_CONFIG_FILEn parameter.
Data Guard Broker Configuration:
1. Initiate the CLI - DGMGRL from operating system and connect using the 'SYS' user
> dgmgrl
DGMGRL for HPUX: Version 10.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2005, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/xxxx!yyyy
Connected.
DGMGRL> show configuration
Error: ORA-16532: Data Guard broker configuration does not exist
Configuration details cannot be determined by DGMGRL
2. Create Configuration with Primary Database (APL)
DGMGRL> create configuration 'APL_primary'
> as primary database is 'APL'
> connect identifier is 'APL';
Configuration "APL_primary" created with primary database "APL"
DGMGRL>
DGMGRL> show configuration;
Configuration
Name: APL_primary
Enabled: NO
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
APL - Primary database
Current status for "APL_primary":
DISABLED
DGMGRL>
Where,
APL_primary: is the name of the configuration.
Primary database is 'APL': 'APL' is primary database db_unique_name value
Connect identifier is 'APL': 'APL' is alias name defined in tnsnames.ora to connect to primary database.
3. Add the Standby Database (APLSTY) to the configuration
DGMGRL> ADD DATABASE 'APLSTY' AS CONNECT IDENTIFIER IS 'APLSTY' MAINTAINED AS PHYSICAL;
Database "APLSTY" added
DGMGRL>
DGMGRL> show configuration
Configuration
Name: APL_primary
Enabled: NO
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
APL - Primary database
APLSTY - Physical standby database
Current status for "APL_primary":
DISABLED
DGMGRL>
4. Validate the Database Configuration
Standby Database:
DGMGRL> show database verbose 'APLSTY';
Database
Name: APLSTY
Role: PHYSICAL STANDBY
Enabled: NO
Intended State: OFFLINE
Instance(s):
APL
Properties:
InitialConnectIdentifier = 'APLSTY'
LogXptMode = 'ASYNC'
Dependency = ''
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '10'
MaxConnections = '1'
ReopenSecs = '15'
NetTimeout = '30'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '2'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '/p, /s'
LogFileNameConvert = '/p, /s'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'vaid1'
SidName = 'APL'
LocalListenerAddress = '(ADDRESS=(PROTOCOL=TCP)(HOST=aplsbdb.vaidiyan.com)(PORT=1532))'
StandbyArchiveLocation = '/s02/arch'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'APL_%s_%t_%r.log'
LatestLog = '(monitor)'
TopWaitEvents = '(monitor)'
Current status for "APLSTY":
DISABLED
DGMGRL>
Primary Database:
DGMGRL> show database verbose 'APL';
Database
Name: APL
Role: PRIMARY
Enabled: NO
Intended State: ONLINE
Instance(s):
APL
Properties:
InitialConnectIdentifier = 'APL'
LogXptMode = 'ASYNC'
Dependency = ''
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '180'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '2'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'vaid2'
SidName = 'APL'
LocalListenerAddress = '(ADDRESS=(PROTOCOL=TCP)(HOST=apldb.vaidiyan.com)(PORT=1532))'
StandbyArchiveLocation = 'dgsby_APL'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'APL_%s_%t_%r.log'
LatestLog = '(monitor)'
TopWaitEvents = '(monitor)'
Current status for "APL":
DISABLED
DGMGRL>
5. Enable the configuration
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;
Configuration
Name: APL_primary
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
APL - Primary database
APLSTY - Physical standby database
Current status for "APL_primary":
Warning: ORA-16610: command 'EDIT DATABASE APLSTY SET PROPERTY' in progress
If you receive "Warning: ORA-16610" error, Please give Data Guard broker few minutes to complete the setup and try again.
DGMGRL> show configuration;
Configuration
Name: APL_primary
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
APL - Primary database
APLSTY - Physical standby database
Current status for "APL_primary":
SUCCESS
DGMGRL>
Now confirm that logs are being transported between primary and standby databases by querying 'V$ARCHIVED_LOG'. Also check for errors in Primary and Standby alert.log located at the background_dump_dest (or $BDUMP). In 11g, the alert log is in diagnostic_dest.
There is Data Guard Broker log file (drcSID.log)which is also located where alert_SID.log is.
Reference:
DGMGRL takes default port if configuration is not set properly (Doc ID 735758.1)
ORA-16664 ORA-16792 Data guard Broker (Doc ID 1228797.1)
11.2 Data Guard Physical Standby Switchover Best Practices using the Broker (Doc ID 1305019.1)
11.2 Data Guard Physical Standby Switchover Best Practices using the Broker (Doc ID 1305019.1)
I admire this article for the well-researched content and excellent wording. I got so involved in this material that I couldn’t stop reading. I am impressed with your work and skill. Thank you so much. City Index reviews
ReplyDeleteTickets for the event are on sale now and can be purchased at www.mgmgrand.com or www.axs.com. The event is promoted by BombZquad Promotions, TGB Promotions, Top Rank and Frank Warren's Queensberry Promotions. A Premier Boxing Champions presentation.
ReplyDeleteWilder vs Fury 2 Live Streaming
Bruc Bond endeavor to lead the financial sector with sustainability, customizable product offering, and open communication. At Bruc Bond we aim to make 21st century banking straightforward, simple, and transparent.
ReplyDeleteThis changing way of thinking as well as the modern technology of cloud computing made it feasible to develop new tailor-maked services and also standard operating procedures such as supplying access to financial account, settlement and also transfer of money with immediately converted money. Due to policy and also high expectations on clients side FinTech business primary goal is to develop solutions and executions with long-lasting potential.
ReplyDeleteinternational payment service
Though the sector creates photos of start-ups as well as industry-changing technology, standard firms and banks are also regularly taking on fintech solutions for their very own functions. Below's a fast look at how the sector is both interfering with as well as enhancing some locations of finance.
ReplyDeletefintech startup