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

Expert Oracle Tips by Burleson Consulting

August 27, 2010

Question:  I am attempting to enter SQL*Plus and I am getting this error:

oracle> sqlplus system/manager@mysid
ERROR
ORA-12514: TNS: Listener does not currently know of service requested in connect descriptor

How do I prevent this form of the ORA-12514 error?  Also, I often get a TNS-12514 along with the ORA-12514.  Are these the same error?   If not, how do I avoid the TNS-12514?

Answer:  First, the ORA error is a permutation of the ORA-12514: TNS listener cannot resolve service name error as shown below.  This form of the ORA-12541 error commonly happens when the database or the listener processes are in the middle of a startup, or when the database (mysid in your case) has not been registered with the listener. 

root> oerr ora 12514

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

Cause:

The listener received a request to establish a connection to a database or other service.

The connect descriptor received by the listener specified a service name for a service (usually a database service) that either has not yet dynamically registered with the listener or has not been statically configured for the listener.

This may be a temporary condition such as after the listener has started, but before the database instance has registered with the listener.

Action:

• Wait a moment and try to connect a second time.
• Check which services are currently known by the listener by executing: lsnrctl services
• Check that the SERVICE_NAME parameter in the connect descriptor of the net service name used specifies a service known by the listener.
• If an easy connect naming connect identifier was used, check that the service name specified is a service known by the listener.
• Check for an event in the listener.log file.  

To register the database with the listener

You have two options to register your database with the listener:

1. Simply add a service name to your tnsnames.ora file.
   
2. If you have the multiple listeners and have specified the database name in your pfile/spfile with local_listener=mysid then you can dynamically register the database with the listener.

The TNS-12514 is very similar to the ORA-12514 for a reason.  Sometimes Oracle does not get far enough along in the process for an Oracle error, so a similar network error will appear.  Resolving one should take care of the other.
Read on for specific information on dealing with the TNS-12514 on Windows.
ORA-12514: TNS listener cannot resolve service name error
Question:  I have an issue causing a ORA-12514: TNS: listener could not resolve SERVICE_NAME error:

SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.
ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor

I'm using Oracle 10g on Windows 2000.
What's cause of this error and how to resolve it?
Answer by Edward Stoever:  
I avoid using a listener connection when I startup or shutdown, as a matter of practice. I realize that cannot always be done. Are you physically on the machine? Can you ssh or telnet in and run sqlplus on the box? Then connect like so:

export ORACLE_SID=mydb  ###Unix/Linux
sqlplus sys as sysdba
[no password needed - just hit return ]
SQL> startup mount
...

Do not connect with the @mydb to avoid the listener.
Also, Windows systems are subject to a variety of other issues from screen savers that hog the CPU to the Catch-22 of either anti-virus protection or a virus interfering with connections.  These conditions and a few others can cause TNS-12514 to appear.
Also note the similarities in numbering and content for ORA-12154, which deals with TNS inability to resolve the service name.