ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Before start to create standby database by using DUPLICATE command, try to connect to  auxiliary instance:

RMAN>  connect auxiliary  sys/password@testdbsty

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-04006: error from auxiliary database: 
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
$ sqlplus sys/password@testdbsty as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 15 15:18:35 2019

Copyright (c) 1982, 2013, Oracle. All rights reserved.

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Workaround:

Add an entry in listener.ora, and then reload listener to make it effective:

SID_LIST_LISTENER =
(SID_LIST =
   (SID_DESC =
      (GLOBAL_DBNAME = TESTDBSTY)
      (SID_NAME = TESTDBSTY)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
  )
..
.
}

$lsnrctl status
Services Summary...
Service "TESTDBSTY" has 2 instance(s).
  Instance "TESTDBSTY", status UNKNOWN, has 1 handler(s) for this service...
  Instance "TESTDBSTY", status BLOCKED, has 1 handler(s) for this service...
...
.
.

ORA-12514 from DGMGRL Switchover Database

For DGMGRL to switchover database, add an entry into LISTENER.ora with static registration with listeners for each host where database resides on respectively.

ISSUES

When using DGMGRL command line to switchover database, we see ORA-12514 error.  The following are two databases for primary and standby database :

Sydney – Primary database
Melbourne – Physical standby database

DGMGRL> switchover to 'Melbourne';
Performing switchover NOW, please wait...
Operation requires a connection to instance "Melbourne" on database "Melbourne"
Connecting to instance "Melbourne"
connected
New primary database "Melbourne" is opening ...
Operation requires startup of instance "Sydney" on database "Sydney"
Starting instance "Sydney"...
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 and mount instance "Sydney" of database "Sydney"

SOLUTION

we need have static registration with listeners, where a standby database/Data Guard scenario exists. Basically once dmon terminates the instance it was unable to connect back to it again to restart the new standby database without a static entry.

Add an entry into LISTENER.ora for each host where database resides on respectively.

For host where Sydney database resides on:

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = Sydney_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = Sydney)
)

For host where Melbourne database resides on:

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = Melbourne_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = Melbourne)
)

Check property “StaticConnectIdentifier” to see static registration successfully or not.

DGMGRL> show database "Sydney" StaticConnectIdentifier
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hosta)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=Sydney_DGMGRL)(INSTANCE_NAME=Sydney)(SERVER=DEDICATED)))'
DGMGRL> show database "Melbourne" StaticConnectIdentifier
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hostb)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=Melbourne_DGMGRL)(INSTANCE_NAME=Melbourne)(SERVER=DEDICATED)))'