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

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

This article has been edited on may 15, 2008.

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

This error can be seen when the listener has been started, but the database has not been started (yet) or automatic database service registration has not been performed (yet) or it has been disabled. Additionaly, the error can be returned when an incomplete or wrong service name is specified in the TNSNAMES.ora file or in the connector descriptor used by tools relying on JDBC drivers, i.e. SQLDeveloper.

If you are not the database administrator and you are experiencing this error on a remote client, then you should ask the DBA for help.

(see the documentation for various oracle editions about starting up a database)

Note that a bad service name or a bad SID name in the tnsnames.ora file may easily lead to ORA-12514, however the fact you got ORA-12514 is a confirmation that at least the client could connect to the listener, because otherwise you'd get ORA-12541. The problem must be then either in the service name or in the associated database process.
Given the multiple combinations originated by the various parameters involved, the following table should help you with figuring out what's wrong and where. Supposing the valid name of our database is ORCL11:



Legenda:
text in red on the leftmost column represents an invalid entry in comparison with either of the two column headings in the upper right part of the table. Valid names are obtained by running the LSNRCTL utility and executing the command SERVICES. Depending on the configuration of the instance, you may see values with or without a domain. On the client side you can either use the SID or the SERVICE_NAME method in the CONNECT_DATA specifier, depending on the method you choose, the error will be different.
The case of ORA-12514 in yellow represent a special situation, where you provided the SERVICE_NAME including a valid domain, however the instance was not configured with the necessary initialization parameters (SERVICE_NAMES and/or DB_DOMAIN), resulting in ORA-12514. Note however that if you try to connect using the SID or you specify the SERVICE_NAME without the domain portion of it, the connection will succeed.

As mentioned at the beginning of the posting, there can be also other situations.
If the listener has been bounced or it has been started after the database, there could be a period of up to 60 seconds during which the database service is not known to the listener.
From the server command line prompt, open a SQL*Plus session and try forcing the automatic registration with the listener by issuing:

ALTER SYSTEM REGISTER;

Note that a listener listening on a non standard port requires additional information (see some examples for Oracle 10G) in terms of initialization parameters in order to support automatic registration because the oracle installer sets up a database expecting to communicate with a local listener on port 1521 (default port).

On Windows, Oracle services are almost always configured to start up automatically, however there is nothing wrong with changing the start-up mode to "manual", especially in test or development environments where you could have multiple oracle homes and instances.

Be aware that there is also a registry entry called ORA_SID_AUTOSTART, containing either TRUE (default) or FALSE, whose location varies with the different Oracle versions, that can prevent a database from starting up when the windows service is started. If it is set to FALSE, you may still see the service status as "started", but you'll have to bring up the database manually, using the SQL*Plus command STARTUP or from the database console.

You may also get ORA-12514 in case you have renamed the computer or changed the domain name or workgroup the server was belonging to without updating these references in the relevant configuration files of the Oracle server.
I had to perform this operation yesterday on a virtual machine whose name was conflicting with another node created from the same baseline.

The absolute minimum required to bring the db back up to an operational state consists in opening files listener.ora and tnsnames.ora stored in %ORACLE_HOME%/network/admin (the letter case may be different depending on the OS) and updating the server and/or domain names as needed.

Most likely you'll also have to update all client tnsnames.ora files accordingly, unless you are using a centralized naming system, like a LDAP server, otherwise the clients will get ORA-12541.

Unfortunately these are not the only steps required to restore the situation, the DB console and other services rely on configuration files, hard-coded directory names containing the former server name and the repository data held in the db itself, so putting all pieces back together can be a rather pesky job so you may want to consider reinstalling everything from scratch as a highly advisable option.

To cut it short, i strongly discourage anybody from changing a production server name ever.

See message translations for ORA-12514 and search additional resources.