[Laskey99] Section 5.9. SQL*Net Troubleshooting

来源：百度文库编辑：神马文学网时间：2024/04/28 12:07:33

5.9. SQL*Net Troubleshooting

Evenin the best planned network, problems can and do occur. There are fewthings more frustrating than to have what appear to be a properlyconfigured client and server that refuse to connect. Even in thesimplest network, there could be several potential causes of thisfailure, 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 logicallyanalyze the problem and confirm the proper operation of eachcomponent. 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 toolsand techniques provided by the host operating system as well as byOracle.

One easy way to determine whether SQL*Net is properly installed,configured, and running on a particular server is to perform aloopback test on the server host. This technique uses SQL*Net toconnect to the database, even though it is local on that machine. Toperform 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@    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 verifyproper operation of network components. In many cases, simply runninganother non-Oracle application that connects across the network canconfirm whether a network connection is functioning. Consider thefollowing 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 calledping, which will determine whether TCP/IPpackets are being properly transmitted between two nodes on anetwork. Typically, ping is invoked as:

pingnodename

where nodename is the name or IP address of themachine with which communication is desired. pingwill either respond with the length of time it takes for each testpacket to make a round trip, or fail with an error message indicatingthat communication has failed. No matter what the result, this simpletest provides valuable information, since it confirms whether or notbasic communication between machines is possible. Other operatingsystems and other network protocols provide their own diagnostictools similar to Unix's ping utility.

Oracle also provides a utility calledtnsping, which can help diagnose networkproblems. The tnsping utility was originallydistributed with some versions of Oracle 7.0, but was missing fromsome platforms and disappeared altogether from Oracle 7.3. It hasbeen included, however, in Oracle8, where it is now calledtnsping80. tnspingfunctions much like the Unix ping utility and isinvoked as:

tnspingservice_name
count

where service_name is the service name defined intnsnames.ora or Oracle Names, and count is thenumber 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 utilityalso provides a mechanism to determine whether or not a workstationcan reach a network listener process. To perform this test, start theNet8 Easy Configuration program, then select "Test" andenter an existing service name. A "ConnectionTest" window then appears, which willrequest a username and password. Once these are entered, you canclick the "Test" button, and the test result will bedisplayed.

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 systemproblems, you still cannot connect an Oracle client to an Oracleserver on your network, then you need to follow basic troubleshootingprocedures. The steps listed in the following sections are intendedas a guide, but we cannot anticipate every possible problem, so youmay ultimately need to rely on the most basic diagnostic tool ofall—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 usingTCP/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 usingIPX/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 aWindows 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?

Create Bookmark (Key: b)Create Bookmark
Create Note or Tag (Key: t)Create Note or Tag
Download (Key: d)Download
Email This Page (Key: e)Email This Page
PrintPrint

Html View (Key: h)Html View
Zoom Out (Key: -)Zoom Out
Zoom In (Key: +)Zoom In
Toggle to Full Screen (Key: f)
Previous (Key: p)Previous
Next (Key: n)Next