Donald K. Burleson
Oracle Utilities Tips
tnsping can be used to test listener connectivity but not database
performance. While ping usually returns faster than tnsping, it gives no
indication whether or not SQL*Net is performing. The ping utility simply
uses IP to try to reach a destination, whereas tnsping uses TCP (a socket)
and transfers data between two nodes. As a result, the ping utility will
always be faster. A slow tnsping round trip could indicate any number of
problems, including a very active server or a slow network.
To reach an Oracle Names Server with tnsping, the command can be formatted
as shown below:
$ tnsping ‘(ADDRESS=(PROTOCOL=tcp)(HOST=onames-server)(PORT=1575))’
Once the host and listener connectivity have been verified, the final
connectivity test is the database itself. The host could be accessible and
the listener active, yet the database might still be inaccessible. Granted,
this final test could be performed first and the others (ping, tnsping)
performed only if this test fails, since if the database is accessible, so
are the database host and listener. Any type of database connection can be
used to confirm database connectivity (SQL*Plus, JDBC, ODBC, Pro*C).
The Java program (ConnectionTest.java) will parse a tnsnames.ora file and
display the connectivity status of every entry in the file. It does this by
establishing a JDBC connection with each entry in the file. The
tnsnames.ora file must be in the same directory as the Java program in
order to be found and parsed. In addition, the program requires that the
same username and password be used for all connections.
SEE CODE DEPOT
The Java program can be used to regularly monitor the availability of all
databases in the tnsnames.ora file. It requires that the JDBC driver be
listed in the CLASSPATH.
The tnsnames.ora entries are typically in the following format and are
easily parsed by ConnectionTest.java:
When the program is executed, it will display the status of each connection
listed in the file:
$java ConnectionTest system manager
Each entry in the file takes between 3 and 5 seconds to process. This may
or may not be feasible in environments with many entries to test.
To learn more about these techniques, see the book "Advanced
Oracle Utilities: The Definitive Reference".
You can buy it
directly from the
publisher and get instant access to the code depot of utilities scripts.
Download your Oracle scripts now:
definitive Oracle Script collection for every Oracle professional DBA