Ping Checks Life, tnsping Checks Purpose #JoelKallmanDay

DBA Connectivity Toolbox: Ping, tnsping, ociping, connping and adbping Explained.

Before I start, I want to explain.

What is JoelKallman Day?

Joel Kallman was a professional whose life was dedicated to databases and one of the co-creators of Oracle Application Express. With a career spanning several decades, he made significant contributions to database development and management. He passed away from COVID-19 on May 25, 2021.

Since 2016, there has been an Oracle Community Day where bloggers share content on the same day to create a sense of community buzz. The name of the event has changed over the years, but in 2021, it was renamed “Joel Kallman Day.” This year, it will be held on Wednesday, October 15th. The details of how it works are explained in Tim Hall’s blog post.

When a client can’t connect to an Oracle database, the first question we ask is:

“Where is the connection failing?”

Oracle provides several utilities to help us find the answer: from simple network reachability tests to advanced client-layer diagnostics.
Let’s explore ping, tnsping, ociping, and adping with practical examples.

1. ping : Testing Basic Network Connectivity

Ping is an OS-level utility that checks whether the host is reachable over the network (typically via ICMP). It does not test Oracle connectivity, only basic TCP/IP reachability.

$ ping dbserver.example.com
PING dbserver.example.com (10.10.10.15) 56(84) bytes of data.
64 bytes from dbserver.example.com: icmp_seq=1 ttl=62 time=0.350 ms
64 bytes from dbserver.example.com: icmp_seq=2 ttl=62 time=0.340 ms
..

If ping fails, the problem is below Oracle’s networking layer : possibly DNS, routing, or firewall.

For a better network performance, the private interconnect is configured to use jumbo frames, typically with an MTU (Maximum Transmission Unit) size of 9000 bytes instead of the default 1500. Even when standard ping works fine, communication problems may occur if the network isn’t properly configured for jumbo frames (for example, if an intermediate switch doesn’t support them).
In such cases, you can test with a custom packet size using ping.

$ ping dbserver.example.com -c 10 -M do -s 8972
PING dbserver.example.com (10.10.10.15)  8972(9000) bytes of data.
8980 bytes from 10.10.10.15: icmp_seq=1 ttl=64 time=0.225 ms
...

Explanation:

• -s specifies the ICMP payload size (not including headers).
• -M do tells ping not to fragment the packet — ensuring the full frame size is supported end-to-end.
• The total frame size = payload (-s) + 28 bytes (IP + ICMP headers).
• So -s 1472 → 1500-byte frame, and -s 8972 → 9000-byte frame.

If you see an error like:

ping: local error: Message too long, mtu=8972

It means jumbo frames are not correctly configured somewhere along the path.

2. tnsping : Checking Listener Reachability via TNS

tnsping is an Oracle utility that tests whether a client can reach the listener specified in a TNS alias or connection string. It uses the TNS protocol (TCP/1521 by default), not ICMP.

$ tnsping orcl
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production
Used parameter files:
 /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/sqlnet.ora

Attempting to contact (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbserver.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)))
OK (10 msec)

What it tests:

• DNS resolution of the host name
• Network connectivity to the listener port
• Listener’s availability to accept connections

What it does not test:

• Database service availability
• User authentication

A common misconception:

It’s important to understand that an “OK” message from tnsping does not guarantee that the SERVICE_NAME you specified actually exists on the target database.

tnsping only tests the ability to reach the listener process on the host and port defined in the TNS entry.
It doesn’t verify whether the service name (in CONNECT_DATA=(SERVICE_NAME=...)) is registered with that listener.

If tnsping works but a SQL*Plus connection fails, the issue is likely inside the listener or the database service.

$ tnsping bjk
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production
Used parameter files:
 /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/sqlnet.ora

Attempting to contact (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbserver.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=bjk)))
OK (10 msec)

Even though the service name bjk does not exist in the database, tnsping still returns:

OK (10 msec)

That’s because tnsping stops testing after successfully reaching the listener, it doesn’t ask the listener to resolve or validate the service name.

To truly verify the service’s existence, use SQL*Plus or a direct connection:

$ sqlplus system@bjk
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

3. ociping : Testing Oracle Call Interface (OCI) Connectivity and measuring network latency

This tool is published by Oracle’s Real-World Performance Team, of which I’m a big fan for their outstanding work. You can also find some of their fascinating videos on the Oracle Learning Channel on YouTube. You may refer to Github Page(Measuring Oracle Net Latency) and Ociping Manual for details. It requires an Oracle Client installation to run.

The ociping tools makes one log on to a database , and then runs OCIPing() once per second for some period, showing the time for each roundtrip. The output is somewhat similar to the standard Linux ‘ping’ utility. By default, the tool runs for 60 seconds, and at completion, the average and standard deviation is shown. This tool is good in “stable” environments with little latency variation and has the benefit of showing lots of OCIPing on a single database connection.

A sample call using a URL style connect string and the resulting output is:

ociping -l insanedba/insanedbaiscool@//dbserver.example.com:1521/orcl --period=10

RWP*OCIPing Release 3.1.2.0 Production on Wed, 08 May 2024 12:57:32 UTC
Connected default database to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
0.322 0.0
0.286 1.0
0.307 2.0
0.313 3.0
0.331 4.0
0.284 5.0
0.294 6.0
0.292 7.0
0.318 8.0
0.314 9.0
ociping (ms) mean=0.306, stddev=0.015, min=0.284, max=0.331

4. connping : Testing Oracle Call Interface (OCI) Connectivity and executing a simple query.

This tool is also published by Oracle’s Real-World Performance Team. You may refer to Github Page(Measuring Oracle Net Latency) for details. It requires an Oracle Client installation to run.

The connping tool has a few differences to the ociping tool, most importantly that it does not only establish one database connection initially, instead it will establish a new database connection every second and subsequently use that database connection to perform the OCIPing() call. Additionally, it will also execute a very simple ‘select 1 from dual’ query. The time to do all three: Logon to the database, do OCIPing() and do the query is output. By default, connping runs for 60 seconds and it shows the averages and standard deviation of all three at the end.

A sample call in this case using an alias from tnsnames.ora and the resulting output is:

connping -l username/{password}@tnsalias --period=10

RWP*Connect/OCIPing Release 3.0.2.2 Production on Thu, 11 Aug 2022 09:21:59 UTC
Connected default database with reconnect to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
connect:405.45 ms, ociping:0.453 ms, dualping:0.620 ms, sid=5457, inst#=1, time=1.4
connect:323.61 ms, ociping:1.200 ms, dualping:0.606 ms, sid=27681, inst#=1, time=2.3
connect:295.12 ms, ociping:0.551 ms, dualping:0.474 ms, sid=49925, inst#=1, time=3.3
connect:291.14 ms, ociping:0.473 ms, dualping:0.535 ms, sid=46299, inst#=1, time=4.3
connect:284.86 ms, ociping:0.465 ms, dualping:0.538 ms, sid=46316, inst#=1, time=5.3
connect:358.89 ms, ociping:0.523 ms, dualping:0.518 ms, sid=41472, inst#=1, time=6.4
connect:317.53 ms, ociping:0.521 ms, dualping:0.552 ms, sid=6026, inst#=1, time=7.3
connect:340.69 ms, ociping:0.498 ms, dualping:0.490 ms, sid=6026, inst#=1, time=8.3
connect:271.11 ms, ociping:0.499 ms, dualping:0.692 ms, sid=58306, inst#=1, time=9.3
connect mean=320.93, stddev=39.90
ociping mean=0.58, stddev=0.22
dualping mean=0.56, stddev=0.07

If you have an environment where network performance varies, you may need to run connping and/or ociping multiple times to get complete understanding.

5. adbping : Connection and Latency Tool

adbping is an easy to use command line tool that can easily help end users determine the connection and SQL execution latency to benchmark the performance outside of their business workload. Tool is feature rich, allowing users to run the benchmark test with multitude of options including multi client support, multi threaded, configurable connection characteristics like pool size and other options.

More information, setup instructions, and command options are available on the “Connection and latency test tool – adbping (Doc ID 2863450.1)“

Example Usage:

$ adbping -u admin -p xxxxxxxx -w /home/opc/siraj/Wallet_db01 -c java -j /home/opc/siraj/jdk1.8.0_301 -s db01_low -d 30
+++Test Summary+++
Test Client: java
Number of concurrent threads: 1
Duration (secs): 30
SQL executed: select 1 from dual;
Pass: 27079 Fail: 0
Test start date: 2022-11-29 06:39:53.701708+00:00
Test end date: 2022-11-29 06:40:25.940934+00:00
Java connection pool Stats: Initsize:1, Maxsize:1, Pool setup time(ms):1713.559
SQL Execution Time(ms) : Min:0.423 Max:26.32 Avg:1.05 Median:0.819 Perc90:1.683 Perc95:2.156 Perc99:4.513
Connect + SQL Execution Time(ms) : Min:0.43 Max:26.34 Avg:1.07 Median:0.841 Perc90:1.698 Perc95:2.173 Perc99:4.528

Interpretation of the results
-----------------------------

1. Pass/Fail count: Indicates the total number of connections passed/failed in defined duration by the defined number of threads.

2. SQL execution time: Time taken to just execute the SQL. Connection time not included.
For sqlplus, this would be the elapsed time reported by sqlplus.

3. Connect + SQL Execution Time: Time taken to connect and execute SQL.
For sqlplus, this would be the time to connect and run the sql.
For java, it would be time taken to getConnection() and execute the query.

4. Java connection pool stats: Reports the time taken to setup the java connection pool and the initial and max size.
All query executions do a getConnection() and execute the SQL.

5. Perc90, Perc95, Perc99: This is the percentile value indicating 90%, 95% or 99% of the latencies are below the respective value.

Hope it helps.