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)
Monday, January 21, 2008
Subscribe to:
Post Comments (Atom)
Posts
2 comments:
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?
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.
Post a Comment