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.

Please like us:

Leave a Reply

Your email address will not be published. Required fields are marked *