[Laskey99] Section 5.9. SQL*Net Troubleshooting

5.9. 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:

1. 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.

2. 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@
       host

   
   

   where 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
nodename

where 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:

tnsping
service_name

count

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).

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.

5.9.1. Some Common ORA Errors, Causes, and Corrective Actions

Table 5.3 summarizes the most common ORA errors, why they occur, and how you can correct them.

Table 5.3. Common ORA Errors

Error	Description	Corrective Action
ORA-12154	The service name could not be found in tnsnames.ora.	Check the service name.
ORA-12162	The connection description has an error.	Check tnanames.ora for syntax, especially mismatched parentheses.
ORA-12163	The connection description is too long.	Correct the problem by shortening the connection description.
ORA-12197	The connection description has an error.	Check tnsnames.ora for syntax errors.
ORA-12198	A path could not be found via interchanges.	Check tnsnav.ora for errors.
ORA-12203	Unable to connect to destination.	Check the underlying network problems, including physical problems or non-functioning routers.
ORA-12208	tnsnav.ora could not be found.	Make sure tnsnav.ora is in the correct directory.
ORA-12210	Improper configuration of tnsnav.ora.	Check tnsnav.ora syntax.
ORA-12500	Could not start a dedicated server process.	Check process count in the operating system and listener.ora parameters.
ORA-12504	SID could not be resolved.	Check CONNECT_DATA in tnsnames.ora and verify that SID is specified correctly.
ORA-12505	SID could not be resolved.	Check listener.ora on the server or run lsnrctl status to make sure a listener is running for the specified SID.
ORA-12510	No dispatcher available to accept a connection using the multithreaded server.	Increase the number of dispatchers available.
ORA-12511	Dispatchers are too busy using the multithreaded server.	Try again or increase the number of dispatchers available.

5.9.2. SQL*Net Troubleshooting Procedures

If, after eliminating any possible network or operating system problems, you still cannot connect an Oracle client to an Oracle server on your network, then you need to follow basic troubleshooting procedures. The steps listed in the following sections are intended as a guide, but we cannot anticipate every possible problem, so you may ultimately need to rely on the most basic diagnostic tool of all—your own intellect and experience.

5.9.2.1. Determine basic server operation

Ask these questions:

• Can any workstation or server connect to the server? If so, then the server is operating properly and you should diagnose the client.

• Is the database running? If not, start the database and retest the connection.

• Is the listener running? If not, start the listener and retest the connection.

• Can a loopback test be performed on the server? If so, you should diagnose the client. If not, the problem lies either in the SQL*Net configuration or in the network protocol stack on the server host.

• If you are using Oracle Names, is the Names server running and reachable? If not, start the Names server or correct any connectivity problem.

5.9.2.2. Determine basic client connectivity

Verify TCP/IP connectivity if you are using TCP/IP:

• Can the server be reached using the ping utility?

• If the tnsnames.ora file uses logical service names resolved by a DNS server, verify that the DNS server is properly resolving the name by using the logical name in the ping.

• If the logical name is not resolved by a DNS server, verify that the logical name is in the hosts file on the client.

• If the logical name cannot be resolved, try using the server's IP address in tnsnames.ora.

Verify IPX/SPX connectivity if you are using IPX/SPX or Novell networking:

• Can you perform a NetWare login to a Novell server on the network?

Verify named pipes connectivity if you are using named pipes in a Windows 95/98/NT environment:

• Using the Network Neighborhood icon, can you see other nodes on the local network?

Verify SQL*Net operation on the client:

• Is SQL*Net or Net8 installed on the client?

• Is the correct protocol adapter installed on the client?

• Is the correct tnsnames.ora file installed on the client, in the correct directory?

• Is the correct sqlnet.ora file installed on the client, in the correct directory?