Managing Users  «Prev  Next»

Lesson 9 Testing network and Oracle Net connectivity
ObjectiveCheck your connectivity to the server.

Testing Oracle Net Connectivity

Testing Underlying Network Connectivity

If your underlying protocol is TCP/IP, it is very easy to test your connectivity to the database server. Simply use the ping[1] utility.

View the Code below to see what this looks like.

C:\>ping prodsvr 
Pinging prodsvr [10.11.49.239] with 32 bytes of data:
Reply from 10.11.49.239: bytes=32 time=1ms TTL=128
Reply from 10.11.49.239: bytes=32 time<10ms TTL=128
Reply from 10.11.49.239: bytes=32 time=1ms TTL=128
Reply from 10.11.49.239: bytes=32 time=1ms TTL=128
Ping statistics for 10.11.49.239:
    Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
    Minimum = 0ms, Maximum =  1ms, Average =  0ms

The ping command works from both Windows and Unix, and is issued from the command prompt. Pass your database server name as an argument to the ping command. The ping command shown above was successful. You can tell that because you got replies back from the server. Had the ping failed to get a response from the server, you would see several "request timed out" messages. If you get timeouts, or do not get replies, the first things that you should check are the host name and TCP/IP address. Make sure that you are pinging the correct host. If you have the correct host name, and pinging still fails, then you have a network issue and should probably go see your local area network administrator for more help. Before you do that however, be sure the network cable is physically connected to your PC.
Go ahead and try the ping command on your own database.

Testing Oracle Net Connectivity

Assuming that you can ping the database server, the next logical thing to check is the Oracle Net connectivity. Oracle supplies a utility named tnsping[2] for this purpose. With it, you can test communication between your PC and the listener that is running on the server. You run tnsping from the command prompt, and you pass a Oracle Net Service name as an argument. Here is an example showing how to use tnsping to verify connectivity for the COIN service:

C:\>tnsping coin
TNS Ping Utility for 32-bit Windows:
Version 8.1.5.0.0 - Production on 16-MAY-99
 13:53:10
(c) Copyright 1997 Oracle Corporation.
All rights reserved.
Attempting to contact (ADDRESS=(PROTOCOL=TCP)
(Host=10.11.49.239)(Port=1521))
OK (180 msec)

Tnsping simply verifies that it can contact the Oracle listener process running on the host machine. In this case, it was successful, and displayed the brief message "OK" to tell you that. If the listener process cannot be contacted, you will get an error message like this:

Attempting to contact (ADDRESS=(PROTOCOL=TCP)
(Host=10.11.49.239)(Port=1521))
TNS-12541: TNS:no listener

This is a good indication that the listener may not be running on the server. You may need to start it. This message might also indicate a problem with the underlying network connectivity. That iswhy you should run ping first--to rule that out.
Here is an example showing tnsping's response to what is perhaps the most common Oracle Net configuration error:

C:\>tnsping coin
TNS Ping Utility for 32-bit Windows:
Version 8.1.5.0.0 - Production on 16-MAY-99
 14:00:26
(c) Copyright 1997 Oracle Corporation.
All rights reserved.
TNS-03505: Failed to resolve name

This is probably the most common error that people encounter. This is telling you that Oracle Net was unable to find an entry in the tnsnames.ora file for a service named coin. To resolve this, double-check the tnsnames.ora entry, and make certain that what you are typing matches what is in the tnsnames.ora file. If you have multiple Oracle products installed on your PC, you may want to read this sidebar on multiple tnsnames.ora files.
Now, try the tnsping command on your own database. You should also check the value for the default domain, and should verify that the syntax of the tnsnames.ora entry is correct. You will learn about checking the default domain in the next lesson.

[1]ping: A utility used to verify TCP/IP connectivity between two nodes on a network.
[2]tnsping: A Oracle Net utility used to verify connectivity between a client and a remote listener.