Oracle Network Products Troubleshooting Guide

Library

Product

Contents

Index

Troubleshooting Techniques

What Is Logging?
What Is Tracing?
What Is the Difference Between Logging and Tracing?
Sorting Out Networking Error Messages
What Is the Client Status Monitor?
What Is the Trace Route Utility?
Calling Oracle Customer Support

This chapter describes methods you can use to resolve problems that may arise when you use SQL*Net and other Oracle Network Products. The methods are described briefly in this chapter, and in more detail in subsequent chapters. These methods include:

reading log files

using Audit Trail information

reading trace files

using the Client Status Monitor

using the Trace Route Utility

This release of SQL*Net includes three new analysis tools: Audit Trail, Client Status Monitor, and the Trace Route Utility. These tools enable you to gather information that can help you in general network administration, as well as in troubleshooting network problems.

What Is Logging?

When logging is enabled, 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 for you to understand the failure. The log file, by way of the error stack, shows the state of the software at various layers. The properties of the log file are:

Error information is appended to the log file when an error occurs.

The log file for a listener includes Audit Trail information about every connection request from a client and most listener control commands. (This feature is new in SQL*Net 2.3.)

Generally, a log file can be replaced or erased only by an administrator, although client log files can be deleted by the user whose application created them. (Note that in general it is bad practice to delete these files while the program using them is still actively logging.)

Logging of errors for the client, server, listener, and Names Server cannot be disabled. This essential feature ensures all errors are recorded.

The Navigator and Connection Manager components of the MultiProtocol Interchange may have logging turned on or off. If on, logging includes connection statistics. The Navigator may optionally log additional information for process or audit control of operations. You set logging parameters using Oracle Network Manager.

Audit Trail

The Audit Trail is a new utility in SQL*Net release 2.3 that enables a system or network administrator to gather and analyze network usage statistics from the log file for the listener. In addition to containing errors, the listener log file contains information about every connection request from a client and most commands sent by the Listener Control Utility. The format of this information makes it easy to use in reports. For example, you might use Audit Trail information in a report about what departments in your company access certain databases most frequently.

For detailed information about log files and the Audit Trail, see Chapter 2, "Logging".

What Is Tracing?

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 events that led to an error. The user or administrator typically invokes the trace utility during or after the occurrence of an abnormal condition, when the log file does not provide a clear indication of the cause.

Attention: The trace facility uses a large amount of disk space and may have a significant impact upon system performance. Therefore, you are cautioned to turn the trace facility on only as part of a diagnostic procedure and to turn it off promptly when it is no longer necessary.

Components that can be traced using the trace facility are:

Network listener

SQL*Net version 2 components

SQL*Net on the client

SQL*Net on the server

MultiProtocol Interchange components

the Connection Manager and pumps

the Navigator

Oracle Names

Names Server

Names Control Utility

TNSPING utility

The trace facility can be used to identify the following types of problems:

difficulties in establishing connections

abnormal termination of established connections

fatal errors occurring during the operation of transparent network substrate (TNS) network components

For detailed information about using the trace facility, see Chapter 3, "Tracing".

What Is the Difference Between Logging and Tracing?

While logging reveals the state of the Oracle components at the time of an error, tracing provides a description of all software events as they occur, and therefore provides additional information about events prior to an error. The three levels of diagnostics, each providing more information than the previous level, are as follows:

1. The reported error from Oracle7 or tools. This is the single error message that is commonly returned to the user.

. This file can often uncover low-level errors in interaction with the underlying protocols.

3. The trace file containing English statements describing what the TNS software has done from the time the trace session was initiated until the failure is re-created. When an error occurs, a simple error message is displayed and a log file is generated. Optionally, a user can generate a trace file for more information. (Remember, however, that using the trace facility has an impact on your system performance.)

In the following example, the user failed to use Oracle Network Manager to create a configuration file, and misspelled the word "PORT" as "POT" in the connect descriptor. It is not important that you understand in detail the contents of each of these results; this example is intended only to provide a comparison between the three levels of diagnostics.

Reported Error

On the screen in SQL*Forms:

ERROR: ORA-12533: TNS:illegal ADDRESS parameters

Logged Error

In the log file, SQLNET.LOG:

****************************************************************
Fatal OSN connect error 12533, connecting to:
 (DESCRIPTION=(CONNECT_DATA=(SID=trace)(CID=(PROGRAM=)(HOST=lala)
	(USER=ginger)))(ADDRESS_LIST=(ADDRESS=(PROTOCOL=ipc)
	(KEY=bad_port))(ADDRESS=(PROTOCOL=tcp)(HOST=lala)(POT=1521))))

  VERSION INFORMATION:
	TNS for SunOS: Version 2.0.14.0.0 - Developer's Release
	Oracle Bequeath NT Protocol Adapter for SunOS: Version
	2.0.14.0.0 - Developer's Release
	Unix Domain Socket IPC NT Protocol Adaptor for SunOS: Version
   	2.0.14.0.0 - Developer's Release
	TCP/IP NT Protocol Adapter for SunOS: Version 2.0.14.0.0 -
	Developer's Release
  Time: 07-MAY-93 17:38:50
  Tracing to file: /home/ginger/trace_admin.trc
  Tns error struct:
    nr err code: 12206
    TNS-12206: TNS:received a TNS error while doing navigation
    ns main err code: 12533
    TNS-12533: TNS:illegal ADDRESS parameters
    ns secondary err code: 12560
    nt main err code: 503
    TNS-00503: Illegal ADDRESS parameters
    nt secondary err code: 0
    nt OS err code: 0

Example of Trace of Error

The trace file, SQLNET.TRC at the USER level, contains the following information:

--- TRACE CONFIGURATION INFORMATION FOLLOWS ---
New trace stream is "/private1/oracle/trace_user.trc"
New trace level is 4
--- TRACE CONFIGURATION INFORMATION ENDS ---

--- PARAMETER SOURCE INFORMATION FOLLOWS ---
Attempted load of system pfile source /private1/oracle/network/admin/sqlnet.ora
Parameter source was not loaded
Error stack follows:
NL-00405: cannot open parameter file

Attempted load of local pfile source /home/ginger/.sqlnet.ora
Parameter source loaded successfully

 -> PARAMETER TABLE LOAD RESULTS FOLLOW <-
Some parameters may not have been loaded
See dump for parameters which loaded OK
 -> PARAMETER TABLE HAS THE FOLLOWING CONTENTS <-
  TRACE_DIRECTORY_CLIENT = /private1/oracle
  trace_level_client = USER
  TRACE_FILE_CLIENT = trace_user
--- PARAMETER SOURCE INFORMATION ENDS ---

--- LOG CONFIGURATION INFORMATION FOLLOWS ---
Attempted open of log stream "/tmp_mnt/home/ginger/sqlnet.log"
Successful stream open
--- LOG CONFIGURATION INFORMATION ENDS ---

Unable to get data from navigation file tnsnav.ora
local names file is /home/ginger/.tnsnames.ora
system names file is /etc/tnsnames.ora
-<ERROR>- failure, error stack follows
-<ERROR>- NL-00427: bad list
-<ERROR>-   NOTE: FILE CONTAINS ERRORS, SOME NAMES MAY BE MISSING

Calling address: (DESCRIPTION=(CONNECT_DATA=(SID=trace)(CID=(PROGRAM=)(HOST=lala)(USER=ginger)))(ADDRESS_LIST=(ADDRESS=(PROTOCOL=ipc)(KEY=bad_port))(ADDRESS=(PROTOCOL=tcp)(HOST=lala)(POT=1521))))
Getting local community information
Looking for local addresses setup by nrigla
No addresses in the preferred address list
TNSNAV.ORA is not present. No local communities entry.
Getting local address information
Address list being processed...
No community information so all addresses are "local"
Resolving address to use to call destination or next hop
Processing address list...
No community entries so iterate over address list
This a local community access
Got routable address information
Making call with following address information: (DESCRIPTION=(EMPTY=0)(ADDRESS=(PROTOCOL=ipc)(KEY=bad_port)))
Calling with outgoing connect data (DESCRIPTION=(CONNECT_DATA=(SID=trace)(CID=(PROGRAM=)(HOST=lala)(USER=ginger)))(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=lala)(POT=1521))))
(DESCRIPTION=(EMPTY=0)(ADDRESS=(PROTOCOL=ipc)(KEY=bad_port)))
KEY = bad_port
connecting...
opening transport...
-<ERROR>- sd=8, op=1, resnt[0]=511, resnt[1]=2, resnt[2]=0
-<ERROR>- unable to open transport
-<ERROR>- nsres: id=0, op=1, ns=12541, ns2=12560; nt[0]=511, nt[1]=2, nt[2]=0
connect attempt failed
Call failed...
Call made to destination
Processing address list so continuing
Getting local community information
Looking for local addresses setup by nrigla
No addresses in the preferred address list
TNSNAV.ORA is not present. No local communities entry.
Getting local address information
Address list being processed...
No community information so all addresses are "local"
Resolving address to use to call destination or next hop
Processing address list...
No community entries so iterate over address list
This a local community access
Got routable address information
Making call with following address information: (DESCRIPTION=(EMPTY=0)(ADDRESS=(PROTOCOL=tcp)(HOST=lala)(POT=1521)))
Calling with outgoing connect data (DESCRIPTION=(CONNECT_DATA=(SID=trace)(CID=(PROGRAM=)(HOST=lala)(USER=ginger)))(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=lala)(POT=1521))))
(DESCRIPTION=(EMPTY=0)(ADDRESS=(PROTOCOL=tcp)(HOST=lala)(POT=1521)))

-<FATAL?>- failed to recognize: POT

-<ERROR>- nsres: id=0, op=13, ns=12533, ns2=12560; nt[0]=503, nt[1]=0, nt[2]=0
Call failed...
Exiting NRICALL with following termination result -1
-<ERROR>-  error from nricall
-<ERROR>-    nr err code: 12206
-<ERROR>-    ns main err code: 12533
-<ERROR>-    ns (2)  err code: 12560
-<ERROR>-    nt main err code: 503
-<ERROR>-    nt (2)  err code: 0
-<ERROR>-    nt OS   err code: 0
-<ERROR>- Couldn't connect, returning 12533

In the trace file, note that unexpected events are preceded with an -<ERROR>- stamp. These events may represent serious errors, minor errors, or merely unexpected results from an internal operation. More serious and probably fatal errors are stamped with the -<FATAL?>- prefix.

In this sample trace file, you can see that the root problem, the misspelling of "PORT," is indicated by the trace line: -<FATAL?>- failed to recognize: POT

Sorting Out Networking Error Messages

Error messages may originate from many sources, especially in distributed applications in which many components interact. Applications such as SQL*Plus, SQL*Forms, or Pro*C applications, which depend on network services from network applications such as SQL*Net, display a single message for errors encountered. This concise error message is often insufficient to identify the specific cause of a network error. A network error may originate at any of several different layers within the TNS products, but the user application reports only the highest level error. The actual cause of the error can be found in the error stack produced by all the layers of Oracle network products such as SQL*Net and Oracle Protocol Adapters.

Error Stacks

The relationships among Oracle network products as they might appear in an error stack is shown in Figure 1 - 1:

Figure 1 - 1. Network Products and Error Stack Components

The layers shown in this figure have the following meanings:

OSN

SQL*Net Interface Layer

Network Routing (MultiProtocol Interchange )

Network Naming (Oracle Names )

Network Session (main and secondary layers)

Native Services includes Network Authentication (NAU) and Network Encryption (NAE)

Network Transport (main, secondary, and operating system layers )

Note: Your network may not include all of these components.

When a network error occurs, each layer contributes to the error stack. Layers that know nothing about the error report nothing, while other layers report what they know about the error. For example, suppose that a user of a client application tries to establish a connection with a database server using SQL*Net version 2 and TCP/IP, and the user enters:

sqlplus scott/tiger@hrserver.world

After the banner of SQL*Plus appears on the screen, the following error is displayed:

ORA-12203: TNS:Unable to connect to destination

This message indicates that the error message file on the Oracle server could not be opened, because the connection to the server failed. However, although the application displays only a one-line error message, an error stack that is much more informative is recorded in the log file by the network layer, if it is able to locate the appropriate message files on the client machine. This error stack can also be captured in trace files by invoking the trace facility and trying the connection again.

In the client-side log output file, SQLNET.LOG, an error stack corresponding to the SQL*Plus ORA-12203 error contains the following message :

***********************************************************
Fatal OSN connect error 12203, connecting to:
 (DESCRIPTION=(CONNECT_DATA=(SID=trace)(CID=(PROGRAM=)
   (HOST=lala)(USER=sviavant)))(ADDRESS_LIST=(ADDRESS=
   (PROTOCOL=ipc)(KEY=trace))(ADDRESS=(PROTOCOL=tcp)
   (HOST=lala)(PORT=1521))))

VERSION INFORMATION:
	TNS for SunOS: Version 2.1.3.0.0 - 
	Oracle Bequeath NT Protocol Adapter for SunOS: Version
    2.1.3.0.0
	Unix Domain Socket IPC NT Protocol Adaptor for SunOS:
    Version 2.1.3.0.0 - 
	TCP/IP NT Protocol Adapter for SunOS: Version 2.1.3.0.0
  Time: 07-FEB-94 17:36:38
  Tracing to file: /home/sviavant/trace_admin.trc
  Tns error struct:
    nr err code: 12203
    TNS-12203: TNS:unable to connect to destination
    ns main err code: 12541
    TNS-12541: TNS:no listener
    ns secondary err code: 12560
    nt main err code: 511
    TNS-00511: No listener
    nt secondary err code: 61
    nt OS err code: 0

Each of the six middle layers shown in Figure 1-1 contributes an error status to the error stack. In this example, the actual cause of the error is introduced and reported at the "nt main" layer. When the error number 511 is propagated to the upper layers, the same error is interpreted by the ns secondary, ns main, and nr layers to be errors 12560, 12541, and 12203 respectively. These error messages are all listed of this guide, along with a cause and suggested corrective action for each.

Oracle Networking Error Prefixes

Oracle network product error messages are identified by the following prefixes:

The prefix "TNS" means that the error message is generated by an Oracle network product. For example, an application may display the following error:

      TNS-00103: Parameter file initialization error.

The prefix "ORA" is the generic prefix returned by all Oracle applications, but within that category network product-specific errors can be identified by the "TNS:" prefix at the beginning of the error string. Some network product error messages may appear with either the ``ORA-" or the ``TNS-" prefix, depending on the source of the error message. In these cases, the message text is the same regardless of whether the message is looked up in the ORA or the TNS message files.

In addition, Secure Network Services messages have the ``ORA-" prefix.

      ORA-12203: TNS: Unable to connect to destination

The prefix "NMC" indicates that an error message is generated by the Oracle Network Manager. The prefix "NMO" indicates error messages generated by the network object layer of the Network Manager. The prefix "NMR" is for messages generated from the ROS file level of the Network Manager. For example, the Network Manager may display the following error:

      NMC-00010:  Resource file cannot be opened

The prefix "NNO" precedes error messages generated by a Names Server. The prefix "NNC" is for error messages generated by an Oracle Names client, and those common to both server and client. The prefix "NNL" is for error messages generated by the Oracle Names Control Utility.

The "NMP" prefix is for error messages generated by the Network Management Protocol, the part of the Names Server that handles SNMP queries. The "NPL" prefix precedes error messages generated by the Network Presentation Layer component of the Names Server. (Network Management Protocol and Network Presentation Layer error messages are typically not visible to the user.)

For example, a Names Server may display the following error:

      NNO-00052:  invalid domain description list

The prefix "NMS" precedes error messages related to Oracle SNMP Support. Chapter 9 explains the error and offers a recommended action.

The prefix "NNF" precedes error messages related to native naming adapters. Chapter 10 explains the error and offers a recommended action.

Error Ranges

Oracle network product error messages are listed in Chapter 6 through Chapter 10. All error messages are identified by error message numbers. The error messages are organized in ascending numerical order and are separated into sections based on the error message prefix. Within each section, the error messages are organized into subsections based on the component that reported the error. The prefixes, error message numbers, and the product components where they are generated are shown in Table 1-1:

Table 1 - 1. Error Message Prefix, Numbers, and Components

Prefix Error Number Component Type of Error
TNS 1 to 500 NR (routing) MultiProtocol Interchange
TNS 501 to 1000 NT (transport) Protocol Adapter
TNS 1001 to 2500 Listener Control Program Listener Control Program
TNS 2501 to 3500 NA Internal Messages NAU (Authentication) and NAE (Encryption)
ORA/TNS 12150 to 12195 SQL*Net Oracle SQL*Net
ORA/TNS 12196 to 12285 NR (routing) TNS
ORA/TNS 12500 to 12530 Listener Listener to client
ORA/TNS 12531 to 12629 NS (session) TNS
ORA/TNS 12630 to 12699 NA (Native Services) Native Services (Authentication and Encryption)
NMC 00001 to 11000 Network Manager Oracle Network Manager
NMO 01001 to 01300 Object Layer Oracle Network Manager Object Store
NMR 00001 to 00500 Resource Layer Oracle Network Manager
NNO 00050 to 00711 NN (Network Naming) Oracle Names Server
NNC 00001 to 00501 NN (Network Naming) Oracle Names client and server
NNL 00001 to 01073 NN (Network Naming) Oracle Names Control Utility
NMP 00001 to 00011 NMP (Network Management Protocol) Oracle Names client and server
NPL 00100 to 00420 NPL (Network Presentation Layer) Network Presentation Layer
NMS 00001 to 00275 NMS (Oracle SNMP Support) Oracle SNMP Support
NNF 00001 to 04999 NNF (Native Naming Adapters) Native Naming Adapters

Error Messages and Documentation

Error messages you may encounter while using Oracle network products fall into the categories shown in Table 1-2:

Table 1 - 2. Error Messages and Related Documentation

Type of Error Message Where to Find Information
Application Application reference manual
Operating system Operating system documentation
Network protocol Protocol-specific documentation
Oracle network software This guide
SQL*Net Version 2.x
MultiProtocol Interchange
TNS
Secure Network Services
Oracle Protocol Adapters
Network Manager
Oracle Names

SNMP Support This guide
Native Naming

What Is the Client Status Monitor?

The Client Status Monitor is a new utility in SQL*Net release 2.3 that provides client information which can be useful for troubleshooting. The tool can be used by decentralized users to provide useful information to the system administrator or to Oracle Customer Support, thus enabling faster problem-solving assistance.

The Client Status Monitor also provides a SQLNET.ORA Editor that enables users to edit some of the optional parameters in the SQLNET.ORA file on the client. The editing tool is useful because it enables you to change the configuration parameters of a single client, without having to use Oracle Network Manager or to distribute new files from a central location.

For further information, see Chapter 4, "Client Status Monitor".

What Is the Trace Route Utility?

The Trace Route Utility (TRCROUTE) is a new feature of SQL*Net release 2.3. It enables administrators to discover what path or route a connection takes from a client to a server. It TRCROUTE encounters a problem, it returns an error stack to theclient instead of a single error. These additional error messages make troubleshooting easier.

Note: TRCROUTE does not function on nodes using releases of SQL*Net earlier than release 2.3.

For more information about TRCROUTE, see Chapter 5, "Trace Route Utility".

Calling Oracle Customer Support

Some error messages recommend calling Oracle Customer Support to report the error. When you call Oracle Customer Support, please have the following information at hand:

The hardware, operating system, and release number of the operating system on which your application(s) is running.

Example: "My client application runs on a Sun workstation running SUN OS4.1.3 and the server application runs on a VAX machine running VMS version 5.4. The protocol is TCP/IP."

The release numbers of all the Oracle networking products involved in the current problem.

Example: "We are running SQL*Net release 2.0.14 at the client and the server, with release 2.0.14 Oracle TCP/IP Adapters."

If you encountered one or more error codes or messages, the exact code numbers and message texts in the order they appeared.

The problem severity according to the following codes:

1 = Program not usable. Critical impact on operations.

2 = Program usable. Operations severely restricted.

3 = Program usable with limited functions. Not critical to overall operations.

4 = Problem circumvented by customer. Minimal effect, if any, on operations.

A description of the problem, including any unusual conditions.

You will also be expected to provide your:

name

company's name

company's Oracle customer support ID (CSI) number

phone number

Prev Next

Library

Product

Contents

Index

*Prefix*	*Error Number*	*Component*	*Type of Error*
TNS	1 to 500	NR (routing)	MultiProtocol Interchange
TNS	501 to 1000	NT (transport)	Protocol Adapter
TNS	1001 to 2500	Listener Control Program	Listener Control Program
TNS	2501 to 3500	NA Internal Messages	NAU (Authentication) and NAE (Encryption)
ORA/TNS	12150 to 12195	SQL*Net	Oracle SQL*Net
ORA/TNS	12196 to 12285	NR (routing)	TNS
ORA/TNS	12500 to 12530	Listener	Listener to client
ORA/TNS	12531 to 12629	NS (session)	TNS
ORA/TNS	12630 to 12699	NA (Native Services)	Native Services (Authentication and Encryption)
NMC	00001 to 11000	Network Manager	Oracle Network Manager
NMO	01001 to 01300	Object Layer	Oracle Network Manager Object Store
NMR	00001 to 00500	Resource Layer	Oracle Network Manager
NNO	00050 to 00711	NN (Network Naming)	Oracle Names Server
NNC	00001 to 00501	NN (Network Naming)	Oracle Names client and server
NNL	00001 to 01073	NN (Network Naming)	Oracle Names Control Utility
NMP	00001 to 00011	NMP (Network Management Protocol)	Oracle Names client and server
NPL	00100 to 00420	NPL (Network Presentation Layer)	Network Presentation Layer
NMS	00001 to 00275	NMS (Oracle SNMP Support)	Oracle SNMP Support
NNF	00001 to 04999	NNF (Native Naming Adapters)	Native Naming Adapters

*Type of Error Message*	*Where to Find Information*
Application	Application reference manual
Operating system	Operating system documentation
Network protocol	Protocol-specific documentation
Oracle network software	This guide
SQL*Net Version 2.x
MultiProtocol Interchange
TNS
Secure Network Services
Oracle Protocol Adapters Network Manager Oracle Names


SNMP Support	This guide
Native Naming