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 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 underlying network stack.
Let’s take a look at a simple case. Given this entry in my tnsnames.ora . . .
C:\>type C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\tnsnames.ora
# tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\client_1\net
work\admin\tnsnames.ora
# Generated by Oracle configuration tools.
#===========================
larry =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = vmlnx01)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = vlnxora1)
)
)
C:\>
. . . 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)
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.
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) C:\>
The first thing we learn, from line 3, is that we are running 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) C:\>
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. See my post on that subject, 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) C:\>
From line 11, we see that we are using the TNSNAMES adapter to resolve the alias. (The alias was ‘vlnxora1′). 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) C:\>
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. This information was gathered from the tnsnames.ora entry for ‘larry’, shown 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 requested a connection to (SERVICE_NAME = vlnxora1). Or did it? What does the listener show?
[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]$
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
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 is not running at all? 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 ~]$
. . .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
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 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.
