tnsping – what it is, what it isn’t

(This is a revised version of an article I originally published at edstevensdba.wordpress.com. At that time I was using Oracle 10g client on Windows XP. While we have since moved on to newer versions of Oracle and Windows, the demonstrations using 10g are equally applicable to 11g and 12c installations.)

Exploring the use of tnsping

Before continuing our exploration of various tns 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 explode a few myths along the way.

The tnsping utility is used to determine if a listener 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 docs.oracle.com. TNSPING serves, for sqlnet, much the same purpose as does ‘ping’ for the underlying network stack.

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

larry =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = vmlnx01)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = vlnxora1)
    )
  )

Figure 1

. . .let’s run some tests.

C:\>tnsping larry 

TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 27-FEB-2 
011 16:21:22 
Copyright (c) 1997, 2007, Oracle. All rights reserved. 

Used parameter files: 
C:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora 

Used TNSNAMES adapter to resolve the alias 
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) 
(HOST = vmlnx01)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = vlnxora1))) 
OK (20 msec) 

Figure 2

And we think, “Aha!” I can connect! Well, maybe. Maybe not. I’ll get to that later. First, let’s look at what we can learn from what we see here.

C:\>tnsping larry

TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 27-FEB-2
011 14:46:56

Copyright (c) 1997,  2007, Oracle.  All rights reserved.

Used parameter files:
C:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = vmlnx01)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = vlnxora1)))
OK (20 msec)

Figure 3

The first thing we learn, from line 3, is that we are running the 32-bit version of Oracle client 10.2.0.4. 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.

C:\>tnsping larry

TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 27-FEB-2
011 14:46:56

Copyright (c) 1997,  2007, Oracle.  All rights reserved.

Used parameter files:
C:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = vmlnx01)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = vlnxora1)))
OK (20 msec)

Figure 4

From lines 8-9, we see that the network parameter file (sqlnet.ora) used by this client is at C:\oracle\product\10.2.0\client_1\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 and on resolving a net service name. See my post on that subject, Locating Oracle’s Network Configuration Files.

C:\>tnsping larry

TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 27-FEB-2
011 14:46:56

Copyright (c) 1997,  2007, Oracle.  All rights reserved.

Used parameter files:
C:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = vmlnx01)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = vlnxora1)))
OK (20 msec)

Figure 5

From line 11, we see that we are using the TNSNAMES adapter to resolve the alias, thus we did reference tnsnames.ora. (The alias – or net service name – was ‘larry’). This adapter was chosen based on the value of the NAMES.DIRECTORY_PATH parameter in the sqlnet.ora file.

C:\>tnsping larry

TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 27-FEB-2
011 14:46:56

Copyright (c) 1997,  2007, Oracle.  All rights reserved.

Used parameter files:
C:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = vmlnx01)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = vlnxora1)))
OK (20 msec)

Figure 6

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 vlmnx01 (HOST = vmlnx01), and placed at that server on port 1521 (PORT = 1521). This information was gathered from the tnsnames.ora entry for ‘larry’, shown in Figure 1, above.

And from line 14 we see that the response time was 20 milliseconds. Well, not quite. According to the Net Administrators Guide, tnsping “displays an estimate of the round trip time” (emphasis mine).

We also see that it connected to (SERVICE_NAME = vlnxora1). Or did it? We know that network connection requests are handled by the listener, so let’s see what the listener says about service name vlnxora1.

[oracle@vmlnx01 admin]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 27-FEB-2011 15:27:35

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                27-FEB-2011 15:27:26
Uptime                    0 days 0 hr. 0 min. 9 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /ora00/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vmlnx01.vmdomain)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@vmlnx01 admin]$

Figure 7

Hmm. Line 20 says “The listener supports no services”. How can this be? Wasn’t our tnsping successful? What does a real connection request do?

C:\>sqlplus scott/tiger@vlnxora1

SQL*Plus: Release 10.2.0.4.0 - Production on Sun Feb 27 15:32:18 2011

Copyright (c) 1982, 2007, Oracle All Rights Reserved.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

So a tnsping of ‘vlnxora1’ is successful, but an actual connection to the database returns ORA-12514. 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=BTZLFLX and received the same result.

And if the listener itself is not running? Let’s stop the listener . . .

[oracle@vmlnx01 ~]$ lsnrctl stop 

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 27-FEB-2011 15:40:41 

Copyright (c) 1991, 2007, Oracle. All rights reserved. 

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) 
The command completed successfully 

[oracle@vmlnx01 ~]$ lsnrctl status 

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 27-FEB-2011 15:40:50 

Copyright (c) 1991, 2007, Oracle. All rights reserved. 

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener 
   Linux Error: 111: Connection refused 
[oracle@vmlnx01 ~]$ 

Figure 8

. . .then test tnsping again

C:\>tnsping larry

TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 27-FEB-2
011 15:42:27

Copyright (c) 1997,  2007, Oracle.  All rights reserved.

Used parameter files:
C:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = vmlnx01)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = vlnxora1)))
TNS-12541: TNS:no listener

Figure 9

As we would expect, tnsping reports that there is no listener.

Conclusion

While tnsping is a very useful tool for diagnosing a variety of connection problems, we must be very clear about one important point: It tests only the network name resolution and routing between the client and the listener. It will tell us about the version of client being used. It will tell us the location of the client’s tns configuration files. It will tell us the final connect string derived from our net service name. It will tell us if the request can be routed to the specified host. It will tell us if there is a listener on the specified port at the specified host. But it will not tell us anything at all about the status of any database. This is not a shortcoming of tnsping. We just need to understand that it is a tool for diagnosing Oracle networking issues, and and the network is not the database.

Postscript

After posting the original version of this article on my old site, Joel Garry brought my attention to some very interesting related information. See this article, by Laurent Schneider.

Advertisement

Help! I can’t connect to my database …

(This is a revised version of an article I originally published at edstevensdba.wordpress.com)

Some of the most frequently asked questions on the Oracle Technology Network (OTN) forums deal with problems trying to connect to the database. Tracing the problem isn’t rocket science, but I often see people not paying attention to (or not trusting) the very specific error messages and riding off in all directions at once. 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 server process, or the computer on which that process executes. The second entity 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 computer on which the process executes. For our purposes both “client” and “server” mean the process. These processes could be running on any two separate computers, or (and understand this) they could be running on the very same computer. The important thing is the distinction between the two processes.

So let’s take a quick walk down the path from the client to the server. We will dig deeper in future posts. For our purposes, we will use the most common Oracle client program of all: sqlplus. At a command line you issue this statement to start it and connect to your database:

C:> sqlplus scott/tiger@larry

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@larry) do with as it sees fit. And what sqlplus sees fit is to pass a request to Oralce’s network layer (TNS, Transparent Network Substrate) to make a connection to “larry”, using the userid “scott” and the password “tiger” as its authentication credentials. So TNS has to figure out what is meant by “larry”. 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.

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

larry =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = curley)
)
)

TNS will look in tnsnames.ora for an entry called ‘larry’. Finding it, a request is sent through the normal OS network stack to (PORT = 1521) on (HOST = myhost) using (PROTOCOL = TCP), asking for a connection to (SERVICE_NAME = curley). 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 (“larry”) to an actual destination. 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. Or for a less technical analogy, it serves the same purpose as your telephone directory, where the the name of “larry” would be associated with the routing information (telephone number) needed by the telephone network.

All network routing is done by IP address, but all we have provided here is a host name of “myhost” Where is “myhost”) on the network? When the request gets passed from TNS to the standard network stack, the name ‘myhost’ will get resolved to an IP address, either via a local ‘hosts’ file or a DNS server. You can also hard-code the ip address (HOST = 123.456.789.101) in the tnsnames.ora but for ease of maintenance this is not recommended.

Once the ip address is determined, the standard networking process delivers the message to the designated port (PORT = 1521) at the specified ip address. Hopefully, there is an Oracle database listener process at that address and configured to listen on the specified port, and that listener knows about SERVICE_NAME=curley. 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 and communicated back to the client. At that point the listener is out of the process and continues to await other connection requests coming in on its configured port.

What can go wrong?

First, there may not be an entry for ‘larry’ 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 “larry” 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 larry) you don’t look for problems at the telephone switchboard.

Maybe the entry for “larry” was found, but “myhost” couldn’t be resolved to an IP address (neither the local hosts file nor the DNS server had an entry for “myhost”). This will result in “ORA-12545: Connect failed because target host or object does not exist”.

Maybe there was an entry for “myserver” in the local hosts file or the DNS server, but it specified an IP address that does not exist on the network or is otherwise unreachable. This will result in “ORA-12170: TNS:Connect timeout occurred”.

Maybe the IP that is reachable on the network, but there is no listener running on that machine. “ORA-12541: TNS:no listener”

Maybe the IP was good, there is a listener at that address, but it is listening on a different port. Again, “ORA-12541: TNS:no listener”

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

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 machine 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 very special ip address, known as the “local loopback” address. LOCALHOST and ip address 127.0.0.1 always mean “this machine on which I am sitting”. So, *all* computers are known as “localhost” or “127.0.0.1”. 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. Another post will use actual examples to deconstruct (break) a working TNS connection request, to demonstrate and prove the various factors.

“Film at eleven”.

Hello world!

You may be asking, “Why yet another Oracle blog?”  To be honest, I’m still asking myself that question.  But, I’ve had several people who’ve seen my comments on OTN urge me to start a blog to collect all that “wisdom”  (!?!?) into one place.  So . . . why not?

I’ll try to post about once a week, mostly demonstration and explanation of things that seem to get a lot of questions but also an occasional opinion piece (aka “rant”) about IT and the people in it.  My closest and oldest friend says I’ve been a curmudgeon all my life, so I guess you can expect a curmudgeon’s viewpoint.

So standby.  As they used to say as a teaser to the evening news, “Film at eleven.”