The ORA-12514 (or TNS-12514) error is a common issue encountered by many newcomers when connecting to an Oracle database. It generally indicates that the connection to the database instance can’t be established. In this article, I’ll guide you through troubleshooting this error.

Symptoms

When attempting to connect to an Oracle database, you might encounter the following error:

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

This error message is usually displayed in the Oracle client or application.

Causes

While this error can be frustrating, there are still two pieces of good news:

1. You have configured the correct IP address and port for the database you are trying to connect. If either the IP address or port were incorrect, you would receive no response.
2. Oracle database listener is up and running. If the listener were stopped, the error message would be “ORA-12541: TNS:no listener”.

The primary cause of this error is that the requested service name has not been registered with the listener. There are two main reasons for this:

1. Database Registration Delay: Immediately after a database starts up, it takes a brief moment (less than 1 minute) to register with the listener. Attermpting to connect too soon can result in this error.
2. Incorrect Service Name: The service name you’ve provided doesn’t match the one registered with the listener. This mismatch prevents the connection.

Actions

To resolve the ORA-12514 error, you can take the following actions:

• Wait a moment and try again.
• Check the the listener’s log for any error messages.
• Verify the service names that have been registered with the listener.
• Ensure the service name you configured is correct and free of typos.

An Example

Here is an example demonstrating the process of trouble shooting this issue.

I encountered the ORA-12514 error when trying to connect to an Oracle database by SQL*Plus:

[oracle@yuan ~]$ sqlplus sys/yaoyuan@localhost/orc1 as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 25 14:40:36 2024

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

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

Checking the listener’s log reveals:

[oracle@yuan ~]$ tail -n 20 /u01/app/oracle/diag/tnslsnr/centos66/listener/alert/log.xml
...
<msg time='2024-07-25T14:08:54.523+08:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='centos66'
 host_addr='192.168.171.34'>
 <txt>25-JUL-2024 14:08:54 * (CONNECT_DATA=(SERVICE_NAME=orc1)(CID=(PROGRAM=sqlplus)(HOST=centos66)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=49767)) * establish * orc1* 12514
 </txt>
</msg>
<msg time='2024-07-25T14:08:54.524+08:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='centos66'
 host_addr='192.168.171.34'>
 <txt>TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
 </txt>
</msg>

The lsnrctl service command shows the services registered with the listener:

[oracle@yuan ~]$ lsnrctl service

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 25-JUL-2024 14:46:18

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=centos66)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER

Shaped-eyed readers may have spotted the typo: the service registered with listener is orcl, but the service name I entered was orc1. Correcting this typo resolves the issue.

If you still can’t resolve the problem, feel free to consult with me.