Archive

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

Before continuing our exploration of various sqlnet connection errors, let’s take a quick look at the oracle utility ‘tnsping’. We’ll see what it does, what it doesn’t do, and bust a few myths along the way.
The tnsping utility is used to determine if a service on an Oracle Net network can be reached. A complete description of its use is found in the Net Services Administrators Guide, located with the rest of the Oracle documentation at tahiti.oracle.com. TNSPING serves, for sqlnet, much the same purpose as does ‘ping’ for the OS.

Let’s take a look at a simple case. Given this entry in my tnsnames.ora:

And we think, “Aha! I can connect!” – well, maybe. Maybe not. I’ll get to that later. First, let’s look at what all we can learn from what we see here.
The first thing we learn, from line 3, is that we are running Oracle client 11.2.0.2. Note that this is the version of the client, not the database. They do not have to be the same, and this tells us only about the client.
From lines 8 and 9, we see that the network parameter file (sqlnet.ora) used by this client is at C:\oracle\11.2.0\CLIENT\network\admin. This is also a very strong indication that the tnsnames.ora file is also located in that directory. However, there are other influences on the location of tnsnames.ora – see my previous post on that subject, here.
From line 11, we see that we are using the TNSNAMES adapter to resolve the alias (the alias was ‘myorcl’). This adapter was chosen based on the value of the NAMES.DIRECTORY_PATH parameter in the sqlnet.ora file.
Lines 12 and 13 show us how sqlnet resolved the alias, or “connect identifier”, into a connect descriptor. We see that the request was routed using the tcp network protocol (PROTOCOL = TCP), to server orclsvr (HOST = orclsvr), and placed at that server on port 1521. This information was gathered from the tnsnames.ora entry for ‘myorcl’, shown above.
And from line 14 we see that the response time was 10 milliseconds. Well, not quite – according to the Net Administrators Guide, tnsping “displays an estimate of the round trip time”.

We also see that it requested a connection to (SERVICE_NAME = orcl). Or did it? What does the listener show?

Hmm – “The listener supports no services”. How can this be? Wasn’t our tnsping successful? What does a real connection request do?
Of course the listener “does not currently know of service requested”. At the moment, the listener does not know of any services at all. By the way, I made sure the listener did not know of any services. My listener relies on dynamic database registration, and I had stopped the database before starting this test. Not only does the listener not know of any services, there is no database running at all. I could just as easily specified SERVICE_NAME=fubar, or SERVICE_NAME=btzflx and received the same result.

And if the listener is not running at all? Let’s stop the listener:
then test tnsping again:
As we would expect, tnsping reports that there is no listener.

While tnsping is a very useful tool for diagnosing a variety of connection problems, we must be very clear about one important point: it tells us absolutely nothing about the state of a database instance. This is not a shortcoming of tnsping. We just need to understand that it is a tool for diagnosing sqlnet issues, and sqlnet is not the database.