Today we faced issue when switching over the PRIMARY database to STANDBY database we faced the below issue.
Issue:
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/manager
Connected.
DGMGRL> switchover to 'CV_S';
Performing switchover NOW, please wait...
Operation requires shutdown of instance "CV" on database "CV_P"
Shutting down instance "CV"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "CV" on database "CV_S"
Shutting down instance "CV"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "CV" on database "CV_P"
Starting instance "CV"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Failed.
You are no longer connected to ORACLE
Please connect again.
Unable to start instance "CV"
You must start instance "CV" manually
Operation requires startup of instance "CV" on database "CV_S"
You must start instance "CV" manually
Switchover succeeded, new primary is "CV_S"
DGMGRL>
Troubleshooting:
Metalink and other notes pointed out that the issue will occur if we miss adding the parameter GLOBAL_DBNAME = db_unique_name_DGMGRL to the listener on the primary and standby database as the DMON uses service db_unique_name_DGMGRL when starting the databases during switchover.
But when we checked the listener configuration we found that desired parameter was added and also configured in the listener
Primary Database
------------------
LISTENER_DG_CV =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(Host = db-d6.abc.com)(Port = 1530)(ip = first))
)
SID_LIST_LISTENER_DG_CV_db-d6 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = CV)
(SDU = 32768)
(TDU = 32768)
(ORACLE_HOME = /oracle/10.2.0.4/CV)
(SID_NAME = CV)
)
(SID_DESC =
(GLOBAL_DBNAME = CV_P_DGMGRL)
(SDU = 32768)
(TDU = 32768)
(ORACLE_HOME = /oracle/10.2.0.4/CV)
(SID_NAME = CV)
)
)
Standby Database
------------------
LISTENER_DG_CV =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(Host = db2-d6.abc.com)(Port = 1530)(ip = first))
)
SID_LIST_LISTENER_DG_CV_db-d6 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = CV)
(SDU = 32768)
(TDU = 32768)
(ORACLE_HOME = /oracle/10.2.0.4/CV)
(SID_NAME = CV)
)
(SID_DESC =
(GLOBAL_DBNAME = CV_S_DGMGRL)
(SDU = 32768)
(TDU = 32768)
(ORACLE_HOME = /oracle/10.2.0.4/CV)
(SID_NAME = CV)
)
)
Also the listener had registered the service name
Primary Side
-------------
$ lsnrctl status LISTENER_DG_CV_db-d6
LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 29-FEB-2012 09:53:13
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=TCP)(Host=db-d6.abc.com)(Port=1530)(ip=first))
STATUS of the LISTENER
------------------------
Alias LISTENER_DG_CV_db-d6
Version TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date 28-FEB-2012 12:08:48
Uptime 0 days 21 hr. 44 min. 24 sec
Trace Level off
Security ON: Local OS Authentication
SNMP ON
Listener Parameter File /etc/listener.ora
Listener Log File /oracle/10.2.0.4/CV/network/log/listener_dg_cv_db-d6.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=173.38.0.22)(PORT=1530)))
Services Summary...
Service "CV" has 1 instance(s).
Instance "CV", status UNKNOWN, has 1 handler(s) for this service...
Service "CV_P_DGMGRL" has 1 instance(s).
Instance "CV", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
Standby Side
-------------
$ lsnrctl status LISTENER_DG_CV_db2-d6
LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 29-FEB-2012 09:53:13
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=TCP)(Host=db2-d6.abc.com)(Port=1530)(ip=first))
STATUS of the LISTENER
------------------------
Alias LISTENER_DG_CV_db2-d6
Version TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date 28-FEB-2012 12:08:48
Uptime 0 days 21 hr. 44 min. 24 sec
Trace Level off
Security ON: Local OS Authentication
SNMP ON
Listener Parameter File /etc/listener.ora
Listener Log File /oracle/10.2.0.4/CV/network/log/listener_dg_cv_db-d6.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=173.38.0.22)(PORT=1530)))
Services Summary...
Service "CV" has 1 instance(s).
Instance "CV", status UNKNOWN, has 1 handler(s) for this service...
Service "CV_S_DGMGRL" has 1 instance(s).
Instance "CV", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
Further when ever we tried making the sqlplus session (sqlplus system/abc@CV_P_DGMGRL or sqlplus system/abc@CV_S_DGMGRL) from standby to primary or vice-versa it went perfectly fine and had no issues.
Also we had other databases in our system which had the same configuration and the switchover was working fine. When we started to compare those databases with this database having the issue we found that the parameter db_domain was set here to abc.com
It seemed like the DMON was trying to connect to service db_unique_name_DGMGRL.abc.com (db_unique_name_DGMGRL.domain.com) in the connect descriptor of the tns names it was using to start the database. It made me think that DMON constructed its own tnentry to start the database and did not use the tns entry we had made in the tnsnames.ora
Solution:
In our case
db_unique_name = CV_P on primary database
db_unique_name = CV_S on standby database
To make the failover successful we re-registered the listener with service name db_unique_name_DGMGRL.abc.com by editing the listener file as below and reloading it.
Primary Database
------------------
LISTENER_DG_CV =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(Host = db-d6.abc.com)(Port = 1530)(ip = first))
)
SID_LIST_LISTENER_DG_CV_db-d6 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = CV.abc.com)
(SDU = 32768)
(TDU = 32768)
(ORACLE_HOME = /oracle/10.2.0.4/CV)
(SID_NAME = CV)
)
(SID_DESC =
(GLOBAL_DBNAME = CV_P_DGMGRL.abc.com)
(SDU = 32768)
(TDU = 32768)
(ORACLE_HOME = /oracle/10.2.0.4/CV)
(SID_NAME = CV)
)
)
$ lsnrctl reload LISTENER_DG_CV
$ lsnrctl status LISTENER_DG_CV_db-d6
LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 29-FEB-2012 09:53:13
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=TCP)(Host=db-d6.abc.com)(Port=1530)(ip=first))
STATUS of the LISTENER
------------------------
Alias LISTENER_DG_CV_db-d6
Version TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date 28-FEB-2012 12:08:48
Uptime 0 days 21 hr. 44 min. 24 sec
Trace Level off
Security ON: Local OS Authentication
SNMP ON
Listener Parameter File /etc/listener.ora
Listener Log File /oracle/10.2.0.4/CV/network/log/listener_dg_cv_db-d6.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=173.38.0.22)(PORT=1530)))
Services Summary...
Service "CV.abc.com" has 1 instance(s).
Instance "CV", status UNKNOWN, has 1 handler(s) for this service...
Service "CV_P_DGMGRL.abc.com" has 1 instance(s).
Instance "CV", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
Repeated same steps on the Standby database also so that same changes and take place.
Then we brought up the database manually and now due to the initial switchover Standby database role is primary and Primary database role is Physical_Standby.
Hence to check the fix we switched over from Standby to Primary and it went perfectly fine
DGMGRL> switchover to 'CV_P';
Performing switchover NOW, please wait...
Operation requires shutdown of instance "CV" on database "CV_S"
Shutting down instance "CV"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "CV" on database "CV_P"
Shutting down instance "CV"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "CV" on database "CV_S"
Starting instance "CV"...
ORACLE instance started.
Database mounted.
Operation requires startup of instance "CV" on database "CV_P"
Starting instance "CV"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "CV_P"
DGMGRL>
Alternate Solution: Instead of reloading the listeners we can nullify the db_domain parameter on the Primary and Standby Databases. I have not tried incase you get hit by this issue you can try it.
Regards,
Tanveer
Posts
1 comment:
if you are using a broker config then check out the database parameter staticconnectidentifier it probably got a default value from creating the configuration;
Post a Comment