Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

Donald K. Burleson

Oracle Utilities Tips


Testing Connectivity

 

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:

ASGARDV8_ASG817t= (DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=asgard)
(PORT=1525)
)
(CONNECT_DATA=
(SID=ASG817t)
)
)


When the program is executed, it will display the status of each connection listed in the file:

$java ConnectionTest system manager

FENRISV8_FEN920wa Successful
MINOTAURV8_MIN817wa Unsuccessful
MINOTAURV8_MIN8173x Successful
NT817LEE Successful
ANGUSV8_ANG817us Successful
JARAIXV8_AIX817r Successful
ASGARDV8_ASG806r Successful


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.


 

  ”call


Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 
   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.