Oracle tnsping tips

The following is from the bestselling book Oracle Utilities: Using Hidden Programs, Import/Export, SQL Loader, oradebug, Dbverify, Tkprof and More today and receive 30% off.

The three main things to check for when diagnosing remote database connection problems are the machine, the listener, and the database. The utilities that can be used to test each one of these include ping, tnsping, and a database connection.

The ping utility is used to test the connectivity to a remote machine. ping will indicate whether a remote server is accessible and responding. If the ping command indicates that a machine cannot be accessed, the other connectivity tests will also fail.

The ping utility is usually found in /usr/sbin on UNIX machines and simply reports the health of the remote machine specified:

$ ping asgard
asgard is alive

Used with the 's option, ping will show the packets received and timing information.

oracle@asgard:/usr/sbin > ping -s grace

PING gracelan: 56 data bytes
64 bytes from grace.bmc.com (172.18.16.215): icmp_seq=0. time=0. ms

Once connectivity to the host is confirmed with ping, the next connection to test is the listener. The tnsping utility is used to determine whether or not an Oracle service can be successfully reached.

If a connection can be established from a client to a server (or server to server), tnsping will report the number of milliseconds it took to reach the remote service. If unsuccessful, a network error will be displayed. However, tnsping will only report if the listener process is up and provides no indication of the state of the database.

$ tnsping <net service name> <count>

The ?net service name? must exist in the tnsnames.ora file. This file is used by clients and database servers to identify server destinations. It stores the service names and database addresses. The ?count? parameter is optional and will show the number of times the command should try to connect to the specified service name.

$ tnsping GRACELANV8_GRA901m 5

TNS Ping Utility for Solaris: Version 9.2.0.1.0 - Production on 03-JAN-2003 14:47:09

Copyright (c) 1997 Oracle Corporation. All rights reserved.

Used parameter files:
/usr/oracle/9.2.0/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=gracelan)
(PORT=1525)) (CONNECT_DATA= (SID=GRA901m)))
OK (80 msec)
OK (10 msec)
OK (10 msec)
OK (0 msec)
OK (10 msec)

The result from the tnsping command above shows 80 milliseconds (ms) were required for the first ?ping?. During this time period, the alias GRACELANV8_GRA901m from the local tnsnames.ora file was retrieved, a DNS of the host ?gracelan? was resolved, and the TNS connect and refuse packets were transported. The second trip took only 10 ms because all of the connection information was already cached.