Lesson 9 | Testing network and Oracle Net connectivity |
Objective | Check 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 the ping process 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
ping Command
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
If you are running multiple releases of Oracle client software on your PC, you may find yourself with several tnsnames.ora files.
For example, on one of the PCs that I am using to write this course, I have the following:
Legacy Connection Information
Oracle8i, release 8.1.5 | d:\oracle\network\admin\tnsnames.ora |
Oracle7 Personal Edition, release 7.3.4 | c:\orawin95\network\admin\tnsnames.ora |
Oracle8, release 8.0.4 | c:\orawin95\net80\admin\tnsnames.ora |
Oracle7, 16-bit, release 7.3.2 | c:\orawin\network\admin\tnsnames.ora |
All of these releases are independent of each other when it comes to tnsnames.ora files.
When I run a 16-bit client application, the tnsnames.ora file for the 16-bit version of Oracle is used. When I run an 8i client app, the tnsnames.ora file in
d:\oracle\network\admin
is used. When I run an 8.0.4 client app, the tnsnames file in c:\orawin95\net80\admin is used, and so forth.
I try to keep all these files in sync, but sometimes I get in a hurry, and I add an entry to one file, but not to the others.
When that happens, and I try to connect to the service in question using a different release of the client software, I get the dreaded "Failed to resolve service name" error. It is easy enough to fix, but you need to be aware that different releases of Oracle client software on the same machine use separate 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.