Wednesday, March 12, 2008

More Oracle Networking troubleshooting - ORA-12514

As always in database servers, you have two completely separate sides - the client and the server. The TNSNAMES.ORA file is for the client and must reside with the client. The LISTENER.ORA file is for the server and must reside with the server.

It is easy to get confused about the TNSNAMES.ORA because it can be found with the LISTENER.ORA file. This is mainly because there are a bunch of client tools on the machine, such as sqlplus, sqlload (and even the database when it uses database links).

The first step is on the server side: determine whether the listener is running and how it is running. To do that,

  • enter the command line 'lsnrctl status'
    • if the listener is not up, start it
  • check host for which the listener is listening
    • if not correct, fix the the LISTENER.ORA
    • this is a common problem when using DHCP (esp. Windows)
  • check port (on that host) on which the listener is listening
    • if not correct, fix the the LISTENER.ORA
  • check whether the listener knows of a service for the database
    • if the listener does not know about the service, start the database, or register the database using sqlplus DBA command 'alter system register;'
    • the database may be configured to support many services (see my blog)

Now you are ready to connect using

sqlplus user/password@alias

The second step, and this is repeated at each client that has a problem) is to check the TNSNAMES.ORA (using the output of lsnrctl status as reference)

  • find the a entry that starts with 'alias = Descrip...' (DESCRIPTION might be on a separate line)
  • if no entry, add one
    • enter it manually if you know the syntax, OR (preferred)
    • using netca (Start > ... > Ora ...> Network Configuration Assistant
  • verify the entry's ADDRESS points to the correct host (HOST = xyz)
    • common issue causing 12514, esp. when server has DHCP
    • should match the host name from the listener record above
  • verify the entry's ADDRESS points to the correct port (PORT = 1521)
    • should match the port number from the listener record above
  • verify the entry's ADDRESS points to a known service (SERVICE = my_database)
    • should match a service from the listener record above

If that still does not work, check the SQLNET.ORA to see whether there is a DEFAULT_DOMAIN= record. If there is, append the value to your alias in the TNSNAMES.ORA


Frank said...

12514, 00000, "TNS:listener does not currently know of service requested in connect descriptor"
Nitpicking here, but:
- if the listener does not run, you cannot get a TNS-12514 - the fact you get it means a listener is running.
- if tnsnames.ora on the client has the wrong port number, you will not reach the listener, and this error cannot occur (OK - maybe another listener on the same machine, but that would be an odd setup on the server)
- default domain will not resolve anything, it would cause the error to be tns-12154 (same numbers, different sequence): 12154, 00000, "TNS:could not resolve the connect identifier specified"

One tip: if your servicename on the listener is different (e.g. it has a domain concatenated) from what is specified on the client, you could resolve this by specifying the global_name parameter in listener.ora (when using a SID list in listener.ora, of course)

Forbrich said...

I agree with your comments - they are good points. My objective was to get the troubleshooting process started, not hit every combination.

For example:

You mention that 12514 means that a listener is running - but is it the right one?

You also mention 'odd setup on the server'.

But 'odd setups' can and do often happen in a DHCP environment - several different laptops with Oracle database and listener and after reboots, the laptops trade hostnames and IP addresses. (I see that regularily, especially in training centers.)

Matt! said...

dude, you totally just saved my bacon. Awesome! It turns out my tnsnames.ora file look alright, but in fact the listener had been configured to listen dbinstancename1, but the tnsnames.ora was referencing dbinstancename1.orafarm.mydomain.tld

removed the orafarm.mydomain.tld and we're good.

Great post.

Anonymous said...

you have a nice site. thanks for sharing this valuable resources. keep it up. anyway, various kinds of ebooks are available here