Skip to content

TNSPING, How and Why

TNSPING Utility

tnsping is a network testing utility provides by Oracle to test the availability of listener through the substrate of Oracle Net.

Somebody may mistakenly think tnsping is a database connection testing tool, no, it's not. It's a listener connection testing one.

Essentially, tnsping only care about the reachability of listener from client sides, the required elements to test a target are hostname (or IP address) and port number only. It really does not care about whether SERVICE_NAME in the connection string is correct or not.

TNSPING Usages

The simplest format of using tnsping is:

tnsping + <Target>

The target can be a connect identifier, an IP address or a full connection description.

  1. Connect Identifier
  2. IP Address or Hostname
  3. Connection Description

1. Connect Identifier

A connect identifier is not an IP address, it's an alias to represent a connection string defined in tnsnames.ora.

Single Testing

Let's see a standard tnsping testing.

C:\Users\Administrator>tnsping orclpdb

TNS Ping Utility for 64-bit Windows: Version 19.0.0.0.0 - Production on 20-JAN-2021 19:08:23

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

Used parameter files:
C:\app\client\product\19.0.0\client_1\network\admin\sqlnet.ora


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

As you can see, the testing includes several information.

  • Client release version
  • The Oracle client we used is release 19c.

  • Testing date time
  • The testing date time is shown, which follows the client release number.

  • Applied SQL parameter file
  • The applied sqlnet.ora is in the same folder of tnsnames.ora.

  • Connect description
  • Since the target we tested is a connect identifier, tnsping needs to lookup the its content in tnsnames.ora at run-time.

    By the way, do you know where tnsnames.ora is?

  • Response
  • The response message "OK" means a successful reaching to the listener and 30 ms of response time is good.

Multiple Testing

We can sequentially test the same destination multiple times.

C:\Users\Administrator>tnsping orclpdb 5
...
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCLPDB)))
OK (30 msec)
OK (20 msec)
OK (20 msec)
OK (10 msec)
OK (0 msec)

The response times are pretty decent for a cross-LAN environment. In my opinion, any response times less than 1 second are all good. If your tnsping cannot respond quickly, it may hang for some reason.

2. IP Address or Hostname

IP address or hostname can be used as a testing target. In such case, tnsping use easy connect to parse the connection.

IP Address Only

C:\Users\Administrator>tnsping 192.168.1.11
...
Used EZCONNECT adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.11)(PORT=1521)))
OK (10 msec)

Without specifying port number, tnsping assumes that you use port 1521 by default to test your environment.

With Port Number

Of course, we can specify a port number in the testing.

C:\Users\Administrator>tnsping 192.168.1.11:1521
...
Used EZCONNECT adapter to resolve the alias Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.11)(PORT=1521)))
OK (20 msec)

Service Name Added

As I said earlier, tnsping does not care about SERVICE_NAME, this is another proof.

In this case, I added a random string for SERVICE_NAME in the connection string, such string is actually an incorrect service name on the listener.

C:\Users\Administrator>tnsping 192.168.1.11:1521/anythinghere
...
Used EZCONNECT adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=anythinghere))(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.11)(PORT=1521)))
OK (10 msec)

It alway succeeds if we provided correct IP address and port number.

Hostname

A hostname needs to be resolve before connecting to the target listener.

C:\Users\Administrator>tnsping primary01
...
Used EZCONNECT adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.11)(PORT=1521)))
OK (20 msec)

3. Connection Description

In some cases, you may not be able to access tnsnames.ora because of permission or something. However, you can use a full connection description to test it.

C:\Users\Administrator>tnsping "(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCLPDB)))"
...
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCLPDB)))
OK (30 msec)

In this case, the command does not parse anything, just use the connection description you provided.

Errors Related to TNSPING

If there's any problem in the network channel to the destination, it will fail with different aspects. Here I list several possible error types below for your reference.

  1. "tnsping is not recognized as an internal or external command" or "tnsping command not found"
  2. tnsping message 3511 not found
  3. TNS-03505: Failed to resolve name
  4. TNS-12545: Connect failed because target host or object does not exist
  5. TNS-12535: TNS:operation timed out
  6. TNS-12541: TNS:no listener
  7. TNS-12547: TNS:lost contact
  8. ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Leave a Reply

Your email address will not be published.