Oracle Network Products Getting Started for Windows Platforms

Library

Product

Contents

Index

Troubleshooting

Resolving Common Error Messages and Codes
Diagnosing SQL*Net
SQL*Net Logging and Tracing
SQL*Net Troubleshooting Hints and Tips from the Field
Contacting Oracle Worldwide Customer Support

Resolving Common Error Messages and Codes

The most common error messages are detailed below:

ORA-12154: Could not resolve service name

Cause: SQL*Net could not find the connect descriptor specified in the TNSNAMES.ORA file.

Action: After verifying that the database is turned on, check the following:

If connecting from a login box, do not begin the field for the connect string with an @ sign. Only use the @ sign if specifying the connect string in command line mode:
```
SQLPLUS SCOTT/TIGER@service_name
```
Make sure you have a TNSNAMES.ORA on the client.
Check for multiple copies of the TNSNAMES.ORA file.
By default, TNSNAMES.ORA is located in ORACLE_HOME\NETWORK\ADMIN.
Verify the configuration files were created with either SQL*Net Easy Configuration or Oracle Network Manager.
If these tools were not used, many syntax errors may exist. The solution is to re-create the configuration files using SQL*Net Easy Configuration or Network Manager.
In the TNSNAMES.ORA file, verify:
- the service name you are specifying in the connect string matches the one in the TNSNAMES.ORA file.
- there are no syntax errors, especially stray characters and mismatched parentheses.
If you are not using the MultiProtocol Interchange (MPI), remove the TNSNAV.ORA file created by Oracle Network Manager.
Make sure that there are no duplicate copies of SQLNET.ORA.
Turn on client tracing and re-execute the operation.
The client trace file shows a secondary error code. To turn on client tracing, add to (or increase) the variable TRACE_LEVEL_CLIENT in the ORACLE_HOME\NETWORK\ADMIN\SQLNET.ORA file.
If you are using domain names, check your SQLNET.ORA file for the parameter NAME.DEFAULT_ZONE.
The domain name must be specified in the connect string if no parameter exists in the SQLNET.ORA file. If you are not using domain names, then comment out this parameter in the SQLNET.ORA file:
```
#NAME.DEFAULT_ZONE=domain     
```
If you comment out this parameter, you must also comment out the NAMES.DEFAULT_DOMAIN parameter:
```
#NAMES.DEFAULT_DOMAIN=domain
```
For Windows, the ORA-12154 error can result from:

ORA-12203:TNS:unable to connect to destination on Windows

This message can appear of the following scenarios:

Cause: An invalid TNS service name was supplied in the connect string.

Action: Verify that the TNS service name supplied in your connect string exists in your TNSNAMES.ORA file and the ADDRESS information for that TNS service name is valid:

Is the HOST or SERVICE name correct?
Is the PORT specified correct?

Cause: The destination system's listener is not listening.

Action: Verify that the remote system's SQL*Net listener is running. Enter:

LSNRCTL

LSNRCTL>STATUS [listener_name]

where listener is the name of the listener defined in the server's LISTENER.ORA file. It is not necessary to identify the listener if you are using the default listener, named LISTENER.

If the output indicates the listener is not running, try starting it with the command:

LSNRCTL>START [listener_name]

Cause: There are underlying network transport problems.

Action: Verify with utilities supplied with the networking protocol being used that the protocol itself is functional. For example, with TCP/IP, try to PING the remote system.

Cause: TNSNAMES.ORA file is not located in the ORACLE_HOME\NETWORK\ADMIN directory.

Action: Make sure the Windows workstation has its TNSNAMES.ORA file located in ORACLE_HOME\NETWORK\ADMIN.

Replace the ORACLE_HOME string with the value that is present in your workstation's ORACLE.INI parameter named ORACLE_HOME.

If your workstation's ORACLE.INI file has a parameter named TNS_ADMIN, ensure that it points to the directory in which the TNSNAMES.ORA file is located.

Cause: The incorrect Oracle Protocol Adapter for the selected networking protocol is installed.

Action: Ensure the correct DLL is installed by viewing the RGS file in the ORACLE_HOME\ORAINST directory:

File Operating System
WINDOWS.RGS Windows 3.1x
WIN95.RGS Windows 95
NT.RGS Windows NT

A missing protocol adapter driver usually produces the following errors in the SQLNET.LOG or any client trace file:

ORA-12203
ORA-12538
ORA-00508

Cause: The (HOST=host_name) for TCP/IP and (SERVICE=tns_application) for SPX are not consistent on the clients and server workstations.

Action: Ensure the (HOST=host_name) for TCP/IP and (SERVICE=tns_application) for SPX are the same on the server and client workstations.

For TCP/IP setups, make sure that the HOST parameter in the LISTENER.ORA on the server and the TNSNAMES.ORA file on the client point to the same name, or at least to names that are then translated to the same IP address by each system. This is especially important for servers with multiple IP addresses assigned to the various network interfaces on the server.

For SPX setups, the name must be the same on the server and client workstations.

Cause: The alias descriptor in the TNSNAMES.ORA file for the LU6.2 Protocol Adapter does not have the value for PLU_LA in upper case. This is irrespective of the case used in the SIDEINFO.NSD file for the symbolic destination name. For example:

 os2=

       (DESCRIPTION=

           (ADDRESS=(PROTOCOL=LU62)

                    (PLU_LA=os2)))

results in this error.

Action: Change the value to uppercase. For example:

os2=

       (DESCRIPTION=

           (ADDRESS=(PROTOCOL=LU62)

                    (PLU_LA=OS2)))

where os2 is defined as the SYMBOLIC DESTINATION NAME in the SIDEINFO.NSD file.

ORA-12203: TNS: unable to connect to destination and
ORA-12154: TNS: could not resolve service name Using 16-bit Applications on Windows NT

Cause: SQL*Net could not find the connect descriptor specified in the TNSNAMES.ORA file.

Action: After verifying that the database is running, check the following:

Verify the SQL*Net listener is running. Enter:
```
LSNRCTL
```
```
LSNRCTL>STATUS [listener_name]
```
where listener is the name of the listener defined in the LISTENER.ORA file. It is not necessary to identify the listener if you are using the default listener, named LISTENER.
If the output indicates the listener is not running, try starting it with the command:
```
LSNRCTL>START [listener_name]
```
Ensure the TNSNAMES.ORA file is in the correct location.
By default, TNSNAMES.ORA is located in ORACLE_HOME\NETWORK\ADMIN.
Verify that configuration files were created with either SQL*Net Easy Configuration or Oracle Network Manager.
If they these tools were not used, many syntax errors may exist. The solution is to re-create the configuration files using SQL*Net Easy Configuration or Network Manager.
Verify you are not using 16-bit SQL*Net SPX on Windows NT, as it is not supported by ensuring the NTS.DLL file does not exist.
If connecting from a login box, make sure you are not placing an @ symbol in front of your connect service name. For example, if your valid service name is ORCL, it actually looks for @ORCL in your TNSNAMES.ORA file.

ORA-3121: No interface driver connection - function not performed on Windows NT

Cause: This is caused from using a SQL*Net version 1 prefix in the connect string.

Action: Do not use the following prefixes in the connect string.

Diagnosing SQL*Net

If you just completed installing and configuring your SQL*Net product and an attempt to make a basic peer-to-peer (single protocol/single community network) connection returns an Oracle error, this section may help you diagnose the cause of the problem. Any underlying fault, noticeable or not, is reported by SQL*Net with an error number or message that is not always indicative of the actual problem.

This section helps you determine which parts of SQL*Net do function properly rather than the parts that do not work. This section helps you determine if the problem is:

Oracle software
other network layers

A problem can be identified if you slowly test each network layer.

For more information on specific error messages or technical bulletins on errors received when performing these diagnostics test, please check the following resources available to you:

Bulletins through the GSX Problem/Solution Database Oracle Metalink Web Site at http://support.oracle.com. If this is your first visit:

Select Registration.

Select OracleMercury or OracleMetalink.

Select Technical Support Knowledge Base.

Select only the Problem/Solution Database.
Oracle Network Products Messages Manual
Oracle Network Products Troubleshooting Guide
Oracle Worldwide Customer Support

Understanding Proper SQL*Net Installation

SQL*Net is Oracle Corporation's remote data access software. It enables both client-server and server-server communication (with applications residing on different machines communicating as peer applications) across any network.

The architecture of TNS is comprised of two software components that need to be installed on both the server and all client machines:

SQL*Net Client and Server version 2.3
Protocol Specific Adapter (If you are using TCP/IP, install the Oracle TCP/IP Protocol Adapter, etc.)

Note: A supported third party network transport layer must also be installed and tested.

To verify proper installation:

Follow the instructions in "Verifying Oracle Network Products Setup".

Server Diagnostics

Note: You may need assistance from the server administrator to follow the instructions in this section.

Answer the questions below:

Are there any other machines (workstations/servers) connecting to the server using SQL*Net?
There have been no changes made to the server, database, or SQL*Net listener recently?
Your server has been up and running for at least several days?

If you are able to answer yes to any of the above questions/statements, you skip this skip and continue to "Client Diagnostics" in this appendix. If you are not sure or answered no to any of the above questions, please continue.

Diagnosing SQL*Net on the server involves:

Verifying the Database Is Running

To check that the database is up:

Log into the database using SQL*Plus or Server Manager and connect with a valid username/password. For example:
SQLPLUS SYSTEM/MANAGER
A Connected message appears.
If you receive the following errors, have your server administrator assist you:
- ORA-1017: invalid U/P
- ORA-1034: Oracle not available

Performing a Loopback Test

In order to perform a loopback test:

You must have one of each of these files: LISTENER.ORA, SQLNET.ORA, and TNSNAMES.ORA exist in the ORANT\NETWORK\ADMIN directory.
In the SQLNET.ORA file, set the AUTOMATIC_IPC parameter to off (AUTOMATIC_IPC=OFF) in order to turn off IPC.
If it is on, the loopback is performed through IPC (Interprocess Communication) instead of the connection going out of the network card with the protocol adapter, which defeats the purpose of the loopback test.

To perform a loopback test:

Follow the instructions in "Testing the Configuration on the Client" to start the listener and perform a loopback test.
If the loopback test continues to fail, continue to Step 3.
If the loopback test passes, skip to Step 4.
Check the Problem/Solution Database Web Site at http://support.oracle.com for more specific information on the error received or contact Oracle Worldwide Support.
Continue to the next section, "Client Diagnostics," in this appendix.

Client Diagnostics

At this point, you assume or know the server's SQL*Net listener is functioning properly because you answered yes to one or more of the following statements:

I verified a loopback test on the server and the connection worked.
I have other machines (servers/workstation) connecting to this same Oracle7 Server using SQL*Net.
Connections from this workstation worked until we made changes to this machine (that is, installed new products, modified the network configuration, and so on).

To perform diagnostics on the client:

Check that the underlying network layer software is installed and a supported version is approved for use with the Oracle Protocol Adapter of choice.

On the client, Oracle has numerous supported vendors, including several versions that we have certified.

Additional Information See:

Oracle Networking Products Manual for protocol adapter requirements

Problem/Solution database for bulletins on Available Networking Products for your operating system, using search words "Available," "Networking," or "Products."

If you have newer versions of the underlying network layer, then it might still work if the vendors' API has not changed.

For TCP/IP, sometimes vendors provide two interfaces, a proprietary one and a Winsock compliant one. Winsock is a standard that provides a common set of network calls. In theory, any vendor who supplies a Winsock compliant interface is suitable for Oracle software. In practice, the quality of the implementations varies from supplier to supplier. Therefore, make sure that the TCP/IP layer is a proper copy and not a less expensive clone, as the API can be different. Also, there are many shareware versions that are Winsock compliant that Oracle does not support, but which may work.

If you are using a Winsock compliant vendor, verify there are no duplicate copies of the WINSOCK.DLL file are on the system.

Check base connectivity for underlying network transport. You may need a networking administrator's assistance.

SQL*Net technology is layers above the underlying network transport stack and are dependent on the underlying network for a successful connection.

Protocol Connectivity Instructions
TCP/IP Check that you can use file transfer or terminal emulation utilities (FTP, TELNET, and PING) from the workstation to the server where the Oracle listener and database reside.
SPX Check that you can do a Netware login to the machine that the database is on. Ensure you can map drives or use other Novell services such as Print Servers and File Servers on the Network. Check that the Oracle listener service is broadcasting by doing a DISPLAY SERVERS from the Novell server or any Novell file server on the SPX/IPX network.
DECnet Check that you can use file transfer and terminal sessions (NFT and SETHOST). Also, check the DECnet network control program, which is a database of connectable nodes:

NCP SHOW KNOWN NODES

NCP LIST KNOWN NODE

Named Pipes Check that you can see other computers or servers on the MSFT network. Ensure you are able to share drives within the MSFT network.

Ensure that the SQL*Net Client and appropriate Protocol Adapter have been installed by following the instructions in section, "Understanding Proper SQL*Net Installation," in this appendix.
Ensure the client machine has the following two files: TNSNAMES.ORA and SQLNET.ORA in the ORACLE_HOME\NETWORK\ADMIN directory or in any of the following situations:
The search order for SQLNET.ORA and TNSNAMES.ORA is as follows:
If you have any other working client machines connecting to your selected Oracle7 Server using SQL*Net, back up your existing files and copy both the working TNSNAMES.ORA and SQLNET.ORA from the working machine onto the non-working client workstations. This eliminates the possibility of errors in the files.
Test the Oracle SQL*Net layer with SQL*Plus or Server Manager.
It is advised not to use TNSPING. TNSPING works just like the TCP/IP ping utility. A socket is never created and open. The TNSPING never connects with the Oracle7 Server listener. It just checks to make sure a TNS listener is present on the server side. Therefore, a working TNSPING can be misleading.
If the connection still fails:

SQL*Net Logging and Tracing

Both SQLNET.ORA and LISTENER.ORA log and trace files are available for you to use in troubleshooting your network problems.

Log files are by default located in ORACLE_HOME\NETWORK\ADMIN \LOG and trace files are by default located in ORACLE_HOME\NETWORK\ADMIN \TRACE.

Logging

All errors encountered in Oracle network products are logged to a log file for evaluation by a network or database administrator. The log file provides additional information for an administrator when the error message on the screen is inadequate to understand the failure. The log file, by way of the error stack, shows the state of the software at various layers.

Tracing

SQL*Net tracing is used to track and examine application connections across the network. Tracing can be used to examine and diagnose application connections across the network. The trace facility allows a network or database administrator to obtain more information on the internal operations of the components of an Oracle application network than is provided in a log file. Tracing an operation produces a detailed sequence of statements that describe the events as they are executed. All trace output is directed to trace output files that can be evaluated to identify the event that led to an error.

The Difference Between Logging and Tracing

Logging reveals the state of the Oracle components at the time of an error; that is, when are error occurs, it is logged to the log file. However, tracing describes all software events as they occur; that is, even when an error is not occurring. Information is posted into the trace file to show what is happening in the software. Thus, tracing provides additional information about events whether or not there is an error.

Additional Information For more specific details about Oracle SQL*Net logging and tracing, please refer to the following documentation

Oracle Network Products Troubleshooting Guide
Understanding SQL*Net
GSX entry 1011114.6 - SQL*NET V2 Tracing

To turn on tracing:

Edit the SQLNET.ORA and LISTENER.ORA files and set the following:

TRACE_LEVEL_CLIENT=16

TRACE_DIRECTORY_CLIENT= <Directory where trace is written>

LOG_DIRECTORY_CLIENT = <Directory where log file is written>

The default names for the trace files are SQLNET.TRC and LISTENER.TRC and the default names for the log files are SQLNET.LOG and LISTENER.LOG.

Oracle Trace for SQL*Net

SQL*Net release 2.3 includes a new, optional source of SQL*Net tracing that uses the Oracle Trace product. Oracle Trace provides a standard, supported, tracing facility for Oracle products.

Oracle Trace is a general-purpose data collection product that has been introduced with the Oracle Enterprise Manager systems management product family. Oracle Trace allows Oracle products to collect data for a variety of uses, such as performance monitoring, diagnostics, and auditing. In addition to its use in SQL*Net release 2.3, Oracle Trace is also used to capture data for the Oracle Server release 7.3.

Additional Information See Understanding SQL*Net and Oracle Trace User's Guide contained in the Oracle Enterprise Manager Performance Pack documentation set for information on using Oracle Trace.

SQL*Net Troubleshooting Hints and Tips from the Field

Below are some SQL*Net tips you may find helpful when you are unable to diagnose a problem:

Try using the node or network address during configuration instead of the name of the server machine.This eliminates any internal lookup problems (and makes the connection slightly faster).

Protocol	Node Address Instructions
TCP/IP	Use the internet protocol address, for example, 198.32.3.5 Change the `(HOST = server_name)` line in TNSNAMES.ORA to the internet protocol address; for example, `(HOST=198.32.3.5)`.
DECnet	Use the node address, for example, 198.43. The Network Control Program (NCP) can provide this information from the command LIST NODE node_name. Change the `(NODE = node_name)` line in the TNSNAMES.ORA file to the node number, for example, `(NODE = 193.43)`

Ensure the VSL.INI file is present before making a 16-bit SQL*Net TCP/IP for Windows connection.
Understand the SPX connection issues.
The workstation that is requesting a connection to be made with a remote Oracle SPX listener must first learn the location of that SPX service in the NetWare IPX network.
The client workstation issues a NetWare Core Protocol (NCP) lookup request to the server it is currently attached to with its primary connection. This does not require the workstation to be logged into that NetWare server as a registered NetWare user. It simply requires that a workstation NCP connection has been established with the server.
When SQL*Net SPX requests a connection with a remote Oracle SPX listener, the workstation provides the following items of information to the NetWare server when requesting this lookup:

Object Type
259 DEC (0x103 hex)

Object Name
Oracle SPX listener name

Field Requested
Network Address

If this combination of information is not present in the Server Information Table, an error is sent back to the workstation.
Perform a loopback test on the server and FTP the files TNSNAMES.ORA and SQLNET.ORA to the client PC.
Comment out unnecessary SQLNET.ORA parameters.
The SQLNET.ORA file may have parameters required for more enhanced uses, such as Dead Connection Detection. Older SQLNET.ORA files can have ANO parameters that do not work for SQL*Net. If these features are not fully configured, a basic connection can fail. The following parameters can be commented out:
SQLNET.AUTHENTICATION_SERVICES (ANO Parameter)
SQLNET.EXPIRE_TIME (Dead Connection Detection Parameter)
SQLNET.CRYPTO_SEED (ANO Parameter)
Set the AUTOMATIC_IPC parameter to OFF in the client SQLNET.ORA file to reduce the errors in the trace file. IPC (interprocess communication) is only an option on the machine where the Oracle Server is running, but SQL*Net by default attempts to use the IPC driver if AUTOMATIC_IPC is set to ON in the SQLNET.ORA file.
Check what is between you and the server. If it is a wide area network, identify any PCs that are not working correctly. If all machines are fine, the problem may be a timing issue. Timing issues are associated with ORA-12203, ORA-12535, or ORA-12547 errors in the client log files.
To resolve this, try speeding up the connection by using exact addresses instead of names and increase the CONNECT_TIMEOUT_LISTENER parameter in the LISTENER.ORA file. The default value for this parameter is 10 seconds.
Determine which Oracle applications are failing. SQL*Plus may work, but CASE tools might not. If you determine the problem is a data volume issue, try to transfer a large (5MB) file with the base connectivity.

Questions to Ask When Troubleshooting

Below are some questions to ask yourself when diagnosing a problem:

Do all machines have a problem or is it just one?
If one machine works and another does not, and you are confident that the same software (Oracle and third party products) is installed, swap the network cables (if they are close enough) to see if the problem moves. This indicates the problem is something between the client-server and not locally on the PC.
What kind of links are between the client and the server, for example, X.25, Integrated Services Digital Network (ISDN), Token Ring, or leased line?
Sniffers and LAN Analyzers are useful for intermittent failing connections or detecting time-outs and resent packets. You can also see what side of the conversation is waiting for a response.
Does the third-party application fail, but Oracle applications work?

Contacting Oracle Worldwide Customer Support

How to Contact Oracle Worldwide Customer Support

Oracle Worldwide Customer Support can be contacted as follows:

Location	Contact
United States of America	Telephone (1) (415) 506-1500
Europe	Telephone (1) (44) (1344) 860-160
Outside the United States and Europe	Your Oracle sales representative
Worldwide	Visit: http://www.oracle.com/support/

For further information on how to contact Oracle Worldwide Customer Support please refer to the included Customer Support Information Booklet.

Before You Call for Assistance

If after reading this appendix, you still cannot resolve your problems, call Oracle Worldwide Customer Support to report the error. Please have the following information at hand:

The hardware and operating system, release number on which your application(s) is running.
The release numbers (up t o five release levels) of all Oracle networking products involved in the current problem.
The third-party vendor and version you are using.
The kind of links that are between the client and server.
A description of what does work.
The exact error message.
A SQL*Net trace if possible. If not, the log file is sufficient.

Prev Next

Library

Product

Contents

Index

File	Operating System
WINDOWS.RGS	Windows 3.1x
WIN95.RGS	Windows 95
NT.RGS	Windows NT

Protocol	Connectivity Instructions
TCP/IP	Check that you can use file transfer or terminal emulation utilities (FTP, TELNET, and PING) from the workstation to the server where the Oracle listener and database reside.
SPX	Check that you can do a Netware login to the machine that the database is on. Ensure you can map drives or use other Novell services such as Print Servers and File Servers on the Network. Check that the Oracle listener service is broadcasting by doing a DISPLAY SERVERS from the Novell server or any Novell file server on the SPX/IPX network.
DECnet	Check that you can use file transfer and terminal sessions (NFT and SETHOST). Also, check the DECnet network control program, which is a database of connectable nodes: NCP SHOW KNOWN NODES NCP LIST KNOWN NODE
Named Pipes	Check that you can see other computers or servers on the MSFT network. Ensure you are able to share drives within the MSFT network.

Object Type	259 DEC (0x103 hex)
Object Name	Oracle SPX listener name
Field Requested	Network Address

Troubleshooting

Resolving Common Error Messages and Codes

ORA-12154: Could not resolve service name

ORA-12203:TNS:unable to connect to destination on Windows

ORA-12203: TNS: unable to connect to destination and ORA-12154: TNS: could not resolve service name Using 16-bit Applications on Windows NT

ORA-3121: No interface driver connection - function not performed on Windows NT

Diagnosing SQL*Net

Understanding Proper SQL*Net Installation

Server Diagnostics Note: You may need assistance from the server administrator to follow the instructions in this section.

Verifying the Database Is Running

Performing a Loopback Test

Client Diagnostics

SQL*Net Logging and Tracing

Logging

Tracing

The Difference Between Logging and Tracing

Oracle Trace for SQL*Net

SQL*Net Troubleshooting Hints and Tips from the Field

Questions to Ask When Troubleshooting

Contacting Oracle Worldwide Customer Support

How to Contact Oracle Worldwide Customer Support

Before You Call for Assistance

ORA-12203: TNS: unable to connect to destination and
ORA-12154: TNS: could not resolve service name Using 16-bit Applications on Windows NT

Server Diagnostics

Note: You may need assistance from the server administrator to follow the instructions in this section.