Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

Donald K. Burleson

Oracle Utilities Tips


Testing Connectivity

 

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, as depicted in Figure 7.1.

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
64 bytes from grace.bmc.com (172.18.16.215): icmp_seq=1. time=0. ms
64 bytes from grace.bmc.com (172.18.16.215): icmp_seq=2. time=2. ms
64 bytes from grace.bmc.com (172.18.16.215): icmp_seq=3. time=0. ms
64 bytes from grace.bmc.com (172.18.16.215): icmp_seq=4. time=0. ms
64 bytes from grace.bmc.com (172.18.16.215): icmp_seq=5. time=0. ms

----gracelan PING Statistics----
6 packets transmitted, 6 packets received, 0% packet loss
round-trip (ms) min/avg/max = 0/0/2


The ping command can also be executed at the DOS prompt on Windows machines to test client-to-server connectivity:

D:\> ping asgard

Pinging asgard.bmc.com [198.64.245.67] with 32 bytes of data:

Reply from 198.64.245.67: bytes=32 time<10ms TTL=254
Reply from 198.64.245.67: bytes=32 time<10ms TTL=254
Reply from 198.64.245.67: bytes=32 time<10ms TTL=254
Reply from 198.64.245.67: bytes=32 time<10ms TTL=254


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.
 


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


��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.