SQL*Net Troubleshooting
Even in the best planned network, problems can and do occur. There are few things more frustrating than to have what appear to be a properly configured client and server that refuse to connect. Even in the simplest network, there could be several potential causes of this failure, including:
A problem with a SQL*Net configuration parameter
A non-functioning network protocol
A failure in a logical network component, like a router
A failure in the physical network, like a broken wire or unplugged connection
The key to diagnosing network connectivity problems is to logically analyze the problem and confirm the proper operation of each component. You must determine if the failure is due to a problem in:
The Oracle software
The network protocol layers
The physical network
In diagnosing the problem, you may be able to take advantage of tools and techniques provided by the host operating system as well as by Oracle.
One easy way to determine whether SQL*Net is properly installed, configured, and running on a particular server is to perform a loopback test on the server host. This technique uses SQL*Net to connect to the database, even though it is local on that machine. To perform a loopback test using SQL*Plus:
Verify that the database is up and running by connecting locally using SQL*Plus. For example, issue the command line:
sqlplus scott/tiger
to establish a connection to the database.
Connect to the database using a SQL*Net loopback connection by specifying a SQL*Net connect string. If you are still logged into SQL*Plus, just enter:
connect scott/tiger@
hostwhere host is the SQL*Net connect string for the local database, as defined in tnsnames.ora. Note that the loopback test has the following requirements:
The SQL*Net listener must be configured and running on the host.
The tnsnames.ora file must exist and must contain an entry for the database running on this host.
If tnsnames.ora requires TCP/IP name resolution, the appropriate entry must exist either in an accessible DNS or in the local hosts file.
Most modern operating systems provide diagnostic tools to verify proper operation of network components. In many cases, simply running another non-Oracle application that connects across the network can confirm whether a network connection is functioning. Consider the following situation:
A simple TCP/IP network running on a 10BaseT topology
One Oracle server running on a Unix host with the SQL*Net TCP/IP protocol
One Oracle client workstation running on Windows 98 with the Microsoft TCP/IP protocol stack
The Unix operating system provides a simple utility called ping , which will determine whether TCP/IP packets are being properly transmitted between two nodes on a network. Typically, ping is invoked as:
ping nodenamewhere nodename is the name or IP address of the machine with which communication is desired. ping will either respond with the length of time it takes for each test packet to make a round trip, or fail with an error message indicating that communication has failed. No matter what the result, this simple test provides valuable information, since it confirms whether or not basic communication between machines is possible. Other operating systems and other network protocols provide their own diagnostic tools similar to Unix’s ping utility.
Oracle also provides a utility called tnsping , which can help diagnose network problems. The tnsping utility was originally distributed with some versions of Oracle 7.0, but was missing from some platforms and disappeared altogether from Oracle 7.3. It has been included, however, in Oracle8, where it is now called tnsping80. tnsping functions much like the Unix ping utility and is invoked as:
tnspingservice_namecount
where service_name is the service name defined in tnsnames.ora or Oracle Names, and count is the number of times the tnsping program will attempt to reach the server (the default is 1).
Tip
tnsping is of limited use, since it does not actually establish a connection but merely verifies the existence of a listener process on the server side. On the other hand, knowing whether SQL*Net can communicate with the host computer is often valuable.
The Net8 Easy Configuration utility also provides a mechanism to determine whether or not a workstation can reach a network listener process. To perform this test, start the Net8 Easy Configuration program, then select “Test” and enter an existing service name. A “Connection Test" window then appears, which will request a username and password. Once these are entered, you can click the “Test” button, and the test result will be displayed.