Monday, January 21, 2008

Thinking through an ORA-12514

There are a LOT of people who run into Oracle Networking problems. Funny thing is that it just ain't that hard. It simply takes a bit of time to think through the problem. Most of the time it's blindingly obvious IF you understand what is happening.

Oracle has a wonderful explanation of each message, including this one for the ORA-12514. In a nutshell, the user is requesting a connection to a specific service, but the listener has no idea how to find that service.

Let's take a quick and very high level, look at the connection process as it pertains to this problem:

1) listener gets started on the service to listen on a specific port for a specific host name or IP address. This is configured in the $ORACLE_HOME/network/admin/listener.ora

Note that hostname/IP and port are in that file. You change the hostname (as happens with DHCP) and the file is no longer valid.

2) Database gets started and registers itself to the listener. Until the database registers itself, the listener does not currently know of the requested service

If the database gets started before the listener, it may be necessary to force registration by using the DBA's 'ALTER SYSTEM REGISTER' command.

The database uses the initialization parameter LOCAL_LISTENER to either point directly to the listener or to a TNSNAMES.ORA entry that points to the listener. (The default value for LOCAL_LISTENER is null which means 'local host on port 1521.)

If the host name changes or the port changes, the database may not be able to 'find' the listener at which time, the listener does not currently know of the requested service

3) The user then attempts to connect using the user's client. The user generally specifies an alias (to look up an entry into the client's TNSNAMES.ORA) or a connection description (which looks like an entry in TNSNAMES.ORA)

The description lists a host, a port and a service. If the system can get to the host (via IP, via /etc/hosts name, or via DNS resilution) and find the listener listening on the port, it asks the listener for a connection to that service.

If that service is registrered, all is well. Otherwise if the service is not registered, or an unregistered service is requested, the listener does not currently know of the requested service

So the problem can be in one of 3 areas:

1) listener is not configured right (but it is started, otherwise we get a 'no listener' message)
2) database is not started or is not registering correctly to the listener
3) user is asking for a non-existant service (possibly a typo)


Anonymous said...

it is still a badly implemented name service.

Imagine if internet DNS didnt work if the addressed machine started before or after the name server, with each client then needing reconfigured...

...on alternate Tuesdays, depending on which way the wind was blowing?

Forbrich said...

Actually, I do find the name service equivalent (LDAP or ONAMES based) to be pretty good.

But most people insist on using the tnsnames.ora, which is comparable to the /etc/hosts part of the naming service. Can you imagine the internet being useful when BIND is maintained using hosts files?

When intelligent people understand and maintain it, I find it is very reliable.

So I guess my experience differs from yours.

Santosh said...

I am trying to connect from my Windows machine to Oracle server running MY_SERVICE on the network.

My Windows machine has Oracle 10g client. Tnsnames.ora has proper entry for service MY_SERVICE.

sqlplus on my machine connects to MY_SERVICE fine.

Toad on my machine can connect to MY_SERVICE fine.

But I can't connect from java program on my machine to MY_SERVICE. I get 12514 error. ??

Santosh said...

In continuation to my previous question, I am using java 1.7 and ojdbc14.jar. I can load the Oracle driver fine in the java program. No error there.

While connecting to MY_SERVICE I am getting the following error:

java.sql.SQLException: Io exception: Connection refused(DESCRIPTION=(TMP=)(VSNNUM=169870080)(ERR=12514)(ERROR_STACK=(ERROR=(CODE=12514)(EMFI=4))))

Santosh said...

Oracle is 10g.

Forbrich said...

Thanks for the comment Santosh.

It would certainly help to see
1) the output of lsnrctl status on the database server; and
2) the actual Java connect string.

Without both of those, it's all guesswork.

Santosh said...

Hello, the problem is solved.

In the connection string, earlier I was putting SID instead of SERVICE_NAME and that was causing problem. Once I fixed that, it worked.

So following is the syntax (I am sure you all already know that):

("jdbc:oracle:thin:@//full_hostname:1521/SERVICE_NAME", "user", "password")