While the Instant Client is a popular client installation for many applications, very often developers and admins become frustrated by the conspicuous absence of the TNSPING utility. Developers using jdbc thin clients may also miss having this handy tool at their disposal.
Fortunately there are ways to replicate some of the functionality of TNSPING using other tools. The functionality breaks down into two components – name lookup and pinging the listener at a host/port combination found in a TNS descriptor.
Name Lookup
Within name lookup there are 3 options only two of which are in common usage.
- local naming – i.e. using tnsnames.ora
- directory naming – i.e. using some form of LDAP
- external naming – i.e. using a third party NIS (this is uncommon)
There is a fourth connection method: Easy Connect, but it is not a name-lookup methodology. Rather, you must already know the target host. In many cases you will also need to know the port and service if defaults are not in use on the target host.
Local Naming
With local naming, you will have a tnsnames.ora file either in your Oracle home:
$ORACLE_HOME/network/admin
directory, or if using read only homes, you may find it here:
$ORACLE_BASE_HOME/network/admin
Alternately you may have a TNS_ADMIN environment variable telling your client to look in another location. TNS_ADMIN is useful if you have multiple homes but want to maintain only a single set of network configuration files.
Once you find your tnsnames.ora file, it is a simple text file that can be read with any text editor or dumped to standard output with tools like cat, more, less, etc. The format is simple collection of entries where each entry is of the form:
name=(descriptor)
The descriptor value is the result returned by TNSPING in the “Attempting to contact” line.
Directory Naming
In directory naming, your client must request an LDAP server to do a search for a name and return the descriptor. Within the LDAP hierarchy the names are stored under the “cn=OracleContext” path of your domain’s dn. For example if you have North and South regions of your company you may choose to create domains distinct to each region and your databaes names would be listed under:
cn=OracleContext,dc=north,dc=example,dc=com
The default domain context is defined in your client’s ldap.ora file which is stored in the same directory paths as the tnsnames.ora file would be.
Since name lookup is just an ldap search, any tool that lets you request such a search will allow you to perform the same function without the need for tnsping. One common tool is the appropriately named: ldapsearch. In the example below I’m searching for the descriptor for the TESTPDB name.
$ ldapsearch -LLL -h myldapserver -b "cn=testpdb,cn=OracleContext,dc=example,dc=com" -s base "objectclass=*" orclnetdescstring dn: cn=testpdb,cn=OracleContext,dc=example,dc=com orclNetDescString: (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP)(HOST=mydbserver) (PORT=1521)) (CONNECT_DATA= (SERVER=dedicated)(SERVICE_NAME=svctestpdb)))
The exact parameter usage may vary between ldapsearch implementations but should be somewhat similar to those above. The parameters in that example are:
- -LLL this is to remove header and version comments from the output
- -h <hostname>this specifies the name of the ldap server, this will be found in the ldap.ora file. If a non-default port is used it will be of the form host:port.
- -b <basepath> this is the search path, starting with the name you want to find, to search for other databases within the same domain you just change the testpdb portion
- -s base this specifies the scope, which should be base as you only want to search for the specified path and no sub trees
- “objectclass=*” this is an LDAP filter which essentially means read whatever type of entry you find at the specified path
- orclnetdescstring this is the LDAP attribute to be returned, in this case, the named descriptor
The output is in LDIF format, so it will be prefixed with the distinguished name of your path which will be the base you provided along with the requested attribute. Once you have these results, text parsing tools can extract the descriptor portion if needed.
Another option if you don’t have ldapsearch is to use curl which can be compiled with support for LDAP urls.
$ curl "ldap://myldapserver/cn=testpdb,cn=OracleContext,dc=example,dc=com?orclnetdescstring?base?(objectclass=*)"
DN: cn=testpdb,cn=OracleContext,dc=example,dc=com
orclNetDescString: (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=mydbserver) (PORT=1521)) (CONNECT_DATA= (SERVER=dedicated) (SERVICE_NAME=svcsdspdb1)))
The LDAP URL has the same components as the ldapsearch parameters, just in a different format. The summarized format is:
ldap://ldapserver[:port]/basepath?attribute?scope?filter
As with ldapsearch, the output is in LDIF format so you may want to parse the output to extract the descriptor if you use this in a script.
If you do not have either of those or if you’d prefer a GUI interface for browsing of entries in the LDAP server there are many free tools available for various platforms. ldap.com has descriptions and links to many such tools and libraries.
External Naming (NIS)
The NIS naming method, to my knowledge, is not used much anymore and I have not personally had any experience with it as a database name lookup service. However if you are using such a service, the same tools and methods you would use to search your NIS server would apply to database names. Probably the most common method would be the ypmatch command. Similar to the ldap you would specify a domain and then the name within that domain you wanted to search.
ypmatch example.com testpdb
DBMS_TNS
One last option for name resolution – if you happen to already be connected to an Oracle database of 12cR2 or higher version, you might be able to use the DBMS_TNS package to resolve a name. The package consists of a single function RESOLVE_TNSNAME. By default the package only has execute privileges to the DBA role, so you might not have permission to execute it.
Also note, the name resolution will be based on the configuration of the database, not of your client. So it’s possible the results returned from the function will not be the same as what you would get using your own configuration.
If, despite these cavates, this option is still adequate for your needs, the usage is quite simple.
select dbms_tns.resolve_tnsname('testpdb') from dual
Pinging the Listener
After you have the descriptor for a name using one of the methods above you may want to test it by using the host, port, and service (or possibly SID if it’s a very old definition.) Unfortunately, while the methods above give you the exact same descriptor text that TNSPING would use, there is no exact correlation in ping functionality.
You can check if something is listening at the target by using telnet to the host and port. You won’t get a response, but if you establish a connection instead of getting a “Connection refused” type error you will at least know the port is open.
If you really want to test the listener, you’ll have to try to establish a connection and any tool using the oracle client like sqlplus can connect with the given descriptor string. If you want to test the host, port, and service directly you could use them in an Easy Connect string, or in a url for a jdbc thin connection.
Such a test will do both more and less than what TNSPING would do. By trying to connect to the database you’re not just pinging the listener, you’re requesting the listener to do the extra work of forwarding you on to the database via dedicated or shared processes and then the database authenticating, creating a session, etc. So this will be a much heavier test than what TNSPING does. As a result of all this extra work, you will not get the response time from TNSPING, and even if you time it yourself, your timing will be for the entire communication effort from client, to listener, to database and back. Unfortunately I don’t know of a reliable means getting around this limitation.
23c SQL*Plus PING
In 23c, Oracle’s SQL*Plus tool now comes with a PING command that overlaps, in part, with TNSPING functionality. I discuss it more fully here.
Conclusion
I’m not aware of any third party tools with a fully analogous set of TNSPING functionality. In most cases where I or developers I worked with wanted TNSPING functionality it was for the name lookup and not the timing, so hopefully this gap in functionality is not too much of a hardship. I also hope the methods above will help those struggling with the lack of the tool and one or more of these alternates will suffice.
For Windows there is mctnsping
http://www.orafaq.com/forum/t/200058/