Assuming that you already have configured the Primary and Physical Standby databases.
Primary Database: MANILA
Physical Standby: GENSAN
Version: 11.2.0.1.0
The following parameters needs to be setup on both primary and standby:
DG_BROKER_START=TRUE
DG_BROKER_CONFIG_FILE1='<PATH>' ### The default value is: {ORACLE_HOME}/dbs/dr1{ORACLE_SID}.dat
DG_BROKER_CONFIG_FILE2='<PATH>' ### The default value is: {ORACLE_HOME}/dbs/dr2{ORACLE_SID}.dat
LOCAL_LISTENER
GLOBAL_DBNAME in listener.ora as db_unique_name_DGMGRL.db_domain
To enable DGMGRL to restart instances, a service with a specific name must be statically registered with the local listener of each instance.
For RAC, ensure dg_broker_config_files are on shared storage and accessible to all instances.
START_OPTIONS for RAC database must be set to MOUNT in OCR using SRVCTL (For switchover/Failover operations for broker and CRS to coordinate while restarting instances and database role reversal)
SPFILE must be used
Procedure:
1. On both Primary and Standby database start the Data Guard Broker (DMON) process.
On Primary:
[MANILA /home/oracle]$ sqlplus '/as sysdba'
SQL> alter system set dg_broker_start=true sid='*';
On Standby:[MANILA /home/oracle]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.1.0 Production on Mon Sep 17 02:26:32 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter system set dg_broker_start=true sid='*';
System altered.
SQL>
[GENSAN /home/oracle]$ sqlplus '/as sysdba'
SQL> alter system set dg_broker_start=true sid='*';
2. Edit the listener.ora on both nodes to add a static entry for DGMGRL with GLOBAL_DBNAME value as a concatenation of db_unique_name_DGMGRL.db_domain[GENSAN /home/oracle]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.1.0 Production on Mon Sep 17 02:28:40 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter system set dg_broker_start=true sid='*';
System altered.
SQL>
To get the value of db_unique_name and db_domain:
SQL> show parameter db_unique_name
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string MANILA
SQL>
SQL> show parameter db_domainSQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string GENSAN
SQL> show parameter db_domain
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string
SQL>
In this case, the value for db_domain is not set. Thus, GLOBAL_DBNAME would be MANILA_DGMGRL and GENSAN_DGMGRL for Primary and Standby respectively. Setting this parameter will prevent ORA-12154 TNS:could not resolve the connect identifier specified error which can be observed on startup of the standby database after performing a switchover.SQL> show parameter db_domain
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string
On Primary:
[MANILA /home/oracle]$ vi $ORACLE_HOME/network/admin/listener.ora
Add the following entry inside SID_LIST:
(SID_DESC =
(GLOBAL_DBNAME = MANILA_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = MANILA)
)
E.g.
On Standby:LISTENER11G =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dgprim.localdomain)(PORT = 1521))
)
SID_LIST_LISTENER11G =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = MANILA_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = MANILA)
)
)
ADR_BASE_LISTENER11G = /u01/app/oracle
[GENSAN /home/oracle]$ vi $ORACLE_HOME/network/admin/listener.ora
Add the following entry inside SID_LIST:
(SID_DESC =
(GLOBAL_DBNAME = GENSAN_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = GENSAN)
)
E.g.
3. After making changes to listener.ora, reload the it from both Primary and Standby.LISTENER11G =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dgstan.localdomain)(PORT = 1521))
)
SID_LIST_LISTENER11G =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = GENSAN_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = GENSAN)
)
)
ADR_BASE_LISTENER11G = /u01/app/oracle
On Primary:
[MANILA /home/oracle]$ lsnrctl stop LISTENER11G
[MANILA /home/oracle]$ lsnrctl start LISTENER11G
On Standby:[MANILA /home/oracle]$ lsnrctl stop LISTENER11G
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 17-SEP-2012 08:03:40
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dgprim.localdomain)(PORT=1521)))
The command completed successfully
[MANILA /home/oracle]$ lsnrctl start LISTENER11G
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 17-SEP-2012 08:03:52
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/dgprim/listener11g/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dgprim.localdomain)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dgprim.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER11G
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 17-SEP-2012 08:03:52
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/dgprim/listener11g/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dgprim.localdomain)(PORT=1521)))
Services Summary...
Service "MANILA" has 1 instance(s).
Instance "MANILA", status UNKNOWN, has 1 handler(s) for this service...
Service "MANILA_DGMGRL" has 1 instance(s).
Instance "MANILA", status UNKNOWN, has 1 handler(s) for this service...
Service "RCAT11G" has 1 instance(s).
Instance "RCAT11G", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[GENSAN /home/oracle]$ lsnrctl stop LISTENER11G
[GENSAN /home/oracle]$ lsnrctl start LISTENER11G
4. At this point, we are now ready. Add a dataguard broker configuration from DGMGRL.[GENSAN /home/oracle]$ lsnrctl stop LISTENER11G
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 17-SEP-2012 08:05:33
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dgstan.localdomain)(PORT=1521)))
The command completed successfully
[GENSAN /home/oracle]$ lsnrctl start LISTENER11G
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 17-SEP-2012 08:05:38
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/dgstan/listener11g/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dgstan.localdomain)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dgstan.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER11G
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 17-SEP-2012 08:05:39
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/dgstan/listener11g/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dgstan.localdomain)(PORT=1521)))
Services Summary...
Service "GENSAN" has 1 instance(s).
Instance "GENSAN", status UNKNOWN, has 1 handler(s) for this service...
Service "GENSAN_DGMGRL" has 1 instance(s).
Instance "GENSAN", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[MANILA /home/oracle]$ dgmgrl
DGMGRL> connect sys/password@MANILA
DGMGRL> CREATE CONFIGURATION 'DGCONF' AS PRIMARY DATABASE IS 'MANILA' CONNECT IDENTIFIER IS MANILA;[MANILA /home/oracle]$ dgmgrl
DGMGRL for Linux: 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/password@MANILA
Connected.
Add the Standby database to your configuration.DGMGRL> CREATE CONFIGURATION 'DGCONF' AS PRIMARY DATABASE IS 'MANILA' CONNECT IDENTIFIER IS MANILA;
Configuration "DGCONF" created with primary database "MANILA"
DGMGRL> ADD DATABASE 'GENSAN' AS CONNECT IDENTIFIER IS 'GENSAN' MAINTAINED AS PHYSICAL;
Verify your configuration. Ensure that Primary and Standby databases are added.DGMGRL> ADD DATABASE 'GENSAN' AS CONNECT IDENTIFIER IS 'GENSAN' MAINTAINED AS PHYSICAL;
Database "GENSAN" added
DGMGRL> show configuration verbose;
Enable the configuration.DGMGRL> show configuration verbose;
Configuration - DGCONF
Protection Mode: MaxPerformance
Databases:
MANILA - Primary database
GENSAN - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
DGMGRL> enable configuration;
DGMGRL> show configuration verbose;DGMGRL> enable configuration;
Enabled.
DGMGRL> show database verbose 'MANILA';DGMGRL> show configuration verbose;
Configuration - DGCONF
Protection Mode: MaxPerformance
Databases:
MANILA - Primary database
GENSAN - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> show database verbose 'GENSAN';DGMGRL> show database verbose 'MANILA';
Database - MANILA
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
MANILA
Properties:
DGConnectIdentifier = 'manila'
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 = '/u02/oradata/GENSAN/datafile/, /u02/oradata/AUX11G/datafile/'
LogFileNameConvert = '/u02/oradata/GENSAN/onlinelog/, /u02/oradata/AUX11G/onlinelog/, /u02/flash_recovery_area/GENSAN/onlinelog/, /u02/flash_recovery_area/AUX11G/onlinelog/'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'dgprim.localdomain'
SidName = 'MANILA'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dgprim.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=MANILA_DGMGRL)(INSTANCE_NAME=MANILA)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status:
SUCCESS
For help on other DGMGRL commands, simply type 'help'.DGMGRL> show database verbose 'GENSAN';
Database - GENSAN
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: ON
Instance(s):
GENSAN
Properties:
DGConnectIdentifier = 'GENSAN'
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 = '/u02/oradata/MANILA/datafile/, /u02/oradata/GENSAN/datafile/'
LogFileNameConvert = '/u02/oradata/MANILA/onlinelog/, /u02/oradata/GENSAN/onlinelog/, /u02/flash_recovery_area/MANILA/onlinelog/, /u02/flash_recovery_area/GENSAN/onlinelog/'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'dgstan.localdomain'
SidName = 'GENSAN'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dgstan.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=GENSAN_DGMGRL)(INSTANCE_NAME=GENSAN)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.arc'
TopWaitEvents = '(monitor)'
Database Status:
SUCCESS
5. Perform switchover.DGMGRL> help
The following commands are available:
add Adds a standby database to the broker configuration
connect Connects to an Oracle database instance
convert Converts a database from one type to another
create Creates a broker configuration
disable Disables a configuration, a database, or fast-start failover
edit Edits a configuration, database, or instance
enable Enables a configuration, a database, or fast-start failover
exit Exits the program
failover Changes a standby database to be the primary database
help Displays description and syntax for a command
quit Exits the program
reinstate Changes a database marked for reinstatement into a viable standby
rem Comment to be ignored by DGMGRL
remove Removes a configuration, database, or instance
show Displays information about a configuration, database, or instance
shutdown Shuts down a currently running Oracle database instance
start Starts the fast-start failover observer
startup Starts an Oracle database instance
stop Stops the fast-start failover observer
switchover Switches roles between a primary and standby database
Use "help <command>" to see syntax for individual commands
Connect to Primary:
DGMGRL> connect sys/password@MANILA
DGMGRL> switchover to 'GENSAN';
DGMGRL> show configuration verbose;DGMGRL> switchover to 'GENSAN';
Performing switchover NOW, please wait...
New primary database "GENSAN" is opening...
Operation requires shutdown of instance "MANILA" on database "MANILA"
Shutting down instance "MANILA"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "MANILA" on database "MANILA"
Starting instance "MANILA"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "GENSAN"
You have now successfully switched over to the new Standby database.DGMGRL> show configuration verbose;
Configuration - DGCONF
Protection Mode: MaxPerformance
Databases:
GENSAN - Primary database
MANILA - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
6. Switchover back to the original Primary server.
Connect to the new Primary:
DGMGRL> connect sys/password@GENSAN
DGMGRL> show configuration verbose;DGMGRL> switchover to 'MANILA';
Performing switchover NOW, please wait...
New primary database "MANILA" is opening...
Operation requires shutdown of instance "GENSAN" on database "GENSAN"
Shutting down instance "GENSAN"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "GENSAN" on database "GENSAN"
Starting instance "GENSAN"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "MANILA"
6. Monitor the Data Guard Broker ConfigurationDGMGRL> show configuration verbose;
Configuration - DGCONF
Protection Mode: MaxPerformance
Databases:
MANILA - Primary database
GENSAN - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> show database 'MANILA' statusreport
Code: Select all
DGMGRL> show database 'MANILA' statusreport
STATUS REPORT
INSTANCE_NAME SEVERITY ERROR_TEXT
Code: Select all
DGMGRL> show database 'MANILA' InconsistentProperties
INCONSISTENT PROPERTIES
INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE
