Posts Tagged ‘ORA-12560’

Resolving Oracle networking problems – general introduction.

The original version of the below article was created by Ed Stevens and could be find here.

Some of the most frequently problems I have to deal in my work are resolving Oracle networking problems. Tracing the problem isn’t rocket science, but I often see people not paying attention to (or not trusting) specific error messages and riding off in all directions at once. A computer will always do exactly what it is told. The problem comes in that we often don’t really know everything we’re telling it. So let me try to explain a little about how Oracle handles a request to “connect me to my database” and actually locates a database running on a machine on the other side of the planet (or even on the very machine from which the request originated).

Before digging in, let’s talk about a very simple concept that an amazing number of people struggle with. For purposes of the current discussion there are two “entities”, or processes, involved. First there is the server process. Depending on one’s semantic precision and the context in which the term is used, the “server” could refer to the database, the database instance, the database server process, or the physical computer on which any of these execute. In terms of network routing it all comes back to a specific box with a specific IP address. The second process is the client process. That is the process that is requesting the connection to (and services from) the database. Again, depending on one’s semantic precision and context, the term “client” could refer to a process or a physical computer, but for our purposes it means the process. And this process could be running on any computer, including (understand this) the same computer that is acting as the server. In this case, it is still a client and the fact that it is running on the server computer is totally coincidental and irrelevant.

So let’s say you are using sqlplus. You issue this statement to start it and connect to your database:

C:\> sqlplus scott/tiger@myorcl

Of course, the first thing that will happen really has nothing to do with Oracle. First, the OS must locate an executable called ‘sqlplus’, load it, and pass it the rest of the command line (scott/tiger@myorcl) do with as it sees fit. And what sqlplus sees fit is to ask TNS to make a connection to “myorcl”, using the userid “scott” and the password “tiger” as its authentication credentials. So TNS has to figure out what is meant by “myorcl”. By default it will do this by looking in a file called tnsnames.ora. Since we are still at the client making the request, this file must be found on the client machine. By default it will be found in $ORACLE_HOME/network/admin (for example: c:\oracle\11.2.0\CLIENT\network\admin).

Let’s make it easy and suppose our tnsnames file has this entry:

myorcl =
			(ADDRESS = (PROTOCOL = TCP)(HOST = orclsvr)(PORT = 1521))
			(SERVICE_NAME = orcl)

TNS will look in your tnsnames.ora for an entry called ‘myorcl’. Finding it, a request is sent through the normal OS network stack to (PORT = 1521) on (HOST = orclsvr) using (PROTOCOL = TCP), asking for a connection to (SERVICE_NAME = orcl). Notice where it got this information from the entry in the tnsnames file (this entry is known as the “connect identifier”). Also notice that what is going on here is the resolution of an alias “myorcl” to an actual destination “orcl”. In this respect the tnsnames.ora file serves the same purpose for sqlnet as the OS’s “hosts” file serves for the standard network stack.

Where is (HOST = orclsvr) on the network? When the request gets passed from TNS to the standard network stack, the name ‘orclsvr’ will get resolved to an IP address, either via a local “hosts” file, via DNS, or possibly other less used mechanisms. You can also hard-code the IP address (HOST = in the tnsnames.ora.

Once the Ip address is determined, the standard networking process delivers the message to the designated port (PORT = 1521) on the designated host/IP address. Hopefully, there is an Oracle database listener on “orclsvr” configured to listen on the specified port, and that listener knows about SERVICE_NAME = orcl. If so, the listener will spawn a server process to act as the intermediary between your client and the database instance. Communication to that server process will be on a different port, selected by the listener. At that point the listener is out of the process and continues to await other connection requests coming in on its configured port.

Before running sqlplus (or the other application which connects to the database), you could test communication between the client and the listener. We will use tnsping to complete this step. It’s a common misconception that tnsping tests connectivity to the instance or database. In actual fact, it only tests connectivity to the listener. Here, we will use it to prove that:
a) the tnsnames.ora has the correct hostname and port
b) that there is a listener listening on the specified host and port.

You can issue tnsping like this:

C:\> tnsping myorcl

If it is successful you will see something like this:

If not, here are some common errors, and some suggestions for fixing them:
First, there may not be an entry for ‘myorcl’ in your tnsnames. In that case you get “ORA-12154: TNS:could not resolve the connect identifier specified“. I’ll expand on the various reasons ‘myorcl’ may not have been found at a later date, but make no mistake, if you receive a ORA-12154, it is an absolute certainty your request never got past this point. You are wasting your time trying to solve this by looking at your listener. If you can’t place a telephone call because you don’t know the number (can’t find your telephone directory – aka “tnsnames.ora” – or can’t find the party you are looking for listed in it – no entry for orcl) you don’t look for problems at the telephone switchboard.

Maybe the entry for ‘myorcl’ was found, but ‘orclsvr’ couldn’t be resolved to an IP address (say there was no entry for ‘orclsvr’ in the local hosts file). This will result in “ORA-12545: Connect failed because target host or object does not exist“.

Maybe there was an entry for “orclsvr” in the local hosts file, but it specified a bad IP address. This will result in “ORA-12545: Connect failed because target host or object does not exist“.

Maybe the IP was good, but there is no listener running: “ORA-12541: TNS:no listener“.

Maybe the IP was good, there is a listener at orclsvr, but it is listening on a different port => “ORA-12560: TNS:protocol adapter error“.

Maybe the IP was good, there is a listener at orclsvr, it is listening on the specified port, but doesn’t know about SERVICE_NAME = orcl => “ORA-12514: TNS:listener does not currently know of service requested in connect descriptor“.

Maybe the IP was good, there is a listener at orclsvr, it is listening on the specified port, knows about SERVICE_NAME = orcl, but you have other application running on the same port as the listener. In that case you don’t get any errors – tnsping just hangs for a long time…

Ok, that is how we get *from* the client connection request *to* the listener. What about the listener’s part of all this?

The listener is very simple. It runs on the server (not the client) and it’s job is to listen for connection requests and make the connection (server process) between the client and the database instance. Once that connection is made, the listener is out of the picture. If you were to kill the listener, all existing connections would continue.

The listener is configured with the listener.ora file, but if that file doesn’t exist, the listener is quite capable of starting up with all default values. One common mistake with the listener configuration is to specify “HOST=localhost” or “HOST=127.0.01”. This is a NONROUTABLE ip address. LOCALHOST and IP address always mean “this machine on which I am sitting”. So, *all* computers are known as “localhost” or “”. If you specify this address in your listener configuration, the listener will only be capable of receiving requests from the machine on which it is running. If you specified that address in your tnsnames file, the request would be routed to the machine on which the requesting client resides. Probably not what you want.

From here I have a few ideas for future posts, each focusing on potential complications at each step of the process.