ORA-12514 During Switchover to Standby

Recently I was performing a switchover from primary to standby database using DGMGRL utility (My Dataguard environment utilizes Dataguard broker). I logged into the server which was hosting current standby database and initiated the DGMGRL command line interface. When I issued the switchover statement, DG Broker was able to convert
primary to standby and standby to primary and bringing up the current primary database, but it returned ORA-12514 while starting up the standby (previous primary on the remote host) and suggested to start the standby manually as can be seen bellow

DGMGRL> switchover to 'MYDB_PROD'; Performing switchover NOW, please wait... New primary database "MYDB_PROD" is opening... Operation requires shutdown of instance "mydb" on database "MYDB_STBY" Shutting down instance "mydb"... ORACLE instance shut down. Operation requires startup of instance "mydb" on database "MYDB_STBY" Starting instance "mydb"... Unable to connect to database ORA-12514: TNS:listener does not currently know of service requested in connect descriptor Failed. Warning: You are no longer connected to ORACLE. Please complete the following steps to finish switchover: start up instance "mydb" of database "MYDB_STBY"

Reason of the Issue

The reason for this issue was that my both primary and standby databases were not registered with the respective listeners. If we check the StaticConnectIdentifier property of primary and standby database, we can see that SERVICE_NAME by default has a value in the format  <unique_db_name>_DGMGRL.DOMAIN_NAME ( In my case domain_name was not specified and hence it was null) and this SERVICE_NAME is required to be registered with the listener so that DG Broker can connect to the respective database to complete the switchover operation.

DGMGRL> show instance verbose 'mydb' on database 'MYDB_PROD'; StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.23.23.16)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=mydb_prod_DGMGRL)(INSTANCE_NAME=mcydb)(SERVER=DEDICATED)))' DGMGRL> show instance verbose 'mydb' on database 'MYDB_STBY'; StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.23.23.17)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=mydb_stby_DGMGRL)(INSTANCE_NAME=mydb)(SERVER=DEDICATED)))'

When I checked that whether my listeners on both primary and standby having abovementioned service_name(s) registered with them or not, and I found that these are not registered as can be seen bellow.

[grid@PRIMARYDB01 admin]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 18-NOV-2014 11:31:33 Copyright (c) 1991, 2011, Oracle.  All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) STATUS of the LISTENER ------------------------ Alias                     LISTENER Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production Start Date                11-NOV-2014 10:53:15 Uptime                    7 days 0 hr. 38 min. 18 sec Trace Level               off Security                  ON: Local OS Authentication SNMP                      OFF Listener Parameter File   /u01/app/11203/grid/network/admin/listener.ora Listener Log File         /u01/app/grid/diag/tnslsnr/PRIMARYB01/listener/alert/log.xml Listening Endpoints Summary...   (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=PRIMARYB01.mydomain.com)(PORT=1521))) Services Summary... Service "+ASM" has 1 instance(s).   Instance "+ASM", status READY, has 1 handler(s) for this service... Service "MYDB" has 1 instance(s).   Instance "mydb", status READY, has 1 handler(s) for this service... Service "MYDBXDB" has 1 instance(s).   Instance "mydb", status READY, has 1 handler(s) for this service... Service "mydb_prod" has 1 instance(s).   Instance "mydb", status READY, has 1 handler(s) for this service... Service "mydb_prod_DGB" has 1 instance(s).   Instance "mydb", status READY, has 1 handler(s) for this service... The command completed successfully

Above command was issued on primary server where it can be seen that no “mydb_prod_DGMGRL” is registered with the listener and this actually caused ORA-12514 error and hence DBA intervention was required to start up the remote standby database after the switchover.

It will be pertinent to mention here that that service “mydb_prod_DGB” is a default service registered to the listener used by DMON process to perform different kind of maintenance tasks.

Solution
To solve the issue, it is required to add static registration entries in LISTENER.ORA file on both primary and standby servers. In my case, I added following entries on my primary and standby listener.ora files

On Primary LISTENER.ORA

SID_LIST_LISTENER =         (SID_LIST =                 (SID_DESC =                         (GLOBAL_DBNAME = mydb_prod_DGMGRL)                         (SID_NAME = mydb)                         (ORACLE_HOME = /u01/app/oracle/product/11203/db1)                 )         )

On Standby LISTENER.ORA

SID_LIST_LISTENER =         (SID_LIST =                 (SID_DESC =                         (GLOBAL_DBNAME = mydb_stby_DGMGRL)                         (SID_NAME = mydb)                         (ORACLE_HOME = /u01/app/oracle/product/11203/db1)                 )         )

After this change, both listeners (primary and standby) were able to register these services. For example, primary server’s listener showed following services registered with it.

[grid@PRIMARYB01 admin]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 18-NOV-2014 11:31:33 Copyright (c) 1991, 2011, Oracle.  All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) STATUS of the LISTENER ------------------------ Alias                     LISTENER Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production Start Date                11-NOV-2014 10:53:15 Uptime                    7 days 0 hr. 38 min. 18 sec Trace Level               off Security                  ON: Local OS Authentication SNMP                      OFF Listener Parameter File   /u01/app/11203/grid/network/admin/listener.ora Listener Log File         /u01/app/grid/diag/tnslsnr/PRIMARYB01/listener/alert/log.xml Listening Endpoints Summary...   (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=PRIMARYB01.mydomain.com)(PORT=1521))) Services Summary... Service "+ASM" has 1 instance(s).   Instance "+ASM", status READY, has 1 handler(s) for this service... Service "MYDB" has 1 instance(s).   Instance "mydb", status READY, has 1 handler(s) for this service... Service "MYDBXDB" has 1 instance(s).   Instance "mydb", status READY, has 1 handler(s) for this service... Service "mydb_prod" has 1 instance(s).   Instance "mydb", status READY, has 1 handler(s) for this service... Service "mydb_prod_DGB" has 1 instance(s).   Instance "mydb", status READY, has 1 handler(s) for this service... Service "mydb_prod_DGMGRL" has 1 instance(s).   Instance "mydb", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully

Related Articles

• Error 12154 received logging on to the standby
• Heartbeat failed to connect to standby. Error is 16009
• ORA-00313 ORA-00312 ORA-17503 ORA-15173 in Standby Alert Log File
• Error 1017 received logging on to the standby - Along with ORA-16191
• Error 1031 received logging on to the standby
• Error 1033 received logging on to the standby