Monday, March 17, 2008

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.

5 comments:

dickson said...

Hi

i have the same problem every time i restart the machine. do you know how to fix it permanently?

Byte64 said...

Dickson,
what do you normally do after restarting the server and getting the error?

Is the error permanent or does it show up only for a couple of minutes initially?

Are you using a default listener configuration?

Bye,
Flavio

Anonymous said...

Hi,

I have a same problem in my RAC setup. When my server's getting reboot and I try to connect with sqlprompt it shows me below error:
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor....

Can u please help me what could be the possible scenarios for this?

Byte64 said...

I must be missing something here, the scenarios have been already outlined in my blog posting, so, i must guess that the conditions i described did not apply to your case, in which case you should describe what is your configuration, what is the content of your listener.ora, what is the content of the tnsnames.ora file and explain by which means you normally work around the problem, as i understand that this is a transient problem that is arising only after rebooting. Does it go away after a few minutes?

Given these inputs i can try to throw an answer, perhaps.

Bye,
Flavio

Alexander Murdoch said...

Congratulations my friend.

This is a really good post and was written just in the way a like it. Is not a manual-based explanation and this is the way we can rally learn.

Thanks. I'll stay around.
Alex.

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles