11
Troubleshooting Net8

Net8 provides methods for understanding and resolving network problems through the use of log and trace files. These files keep track of the interaction between network components as errors occur. Evaluating this information will help you to diagnose and troubleshoot even the most complex network problems.

This chapter describes common network errors and outlines procedures for resolving them. It also describes methods for logging and tracing error information to diagnose and troubleshoot more complex network problems. This chapter contains the following sections:

• Diagnosing Net8

• Resolving the Most Common Error Messages

• Troubleshooting Tips from the Field

• Troubleshooting Network Problems Using Log and Trace Files

• Logging Error Information

• Tracing Error Information

• Contacting Oracle Customer Support

Diagnosing Net8

If you have just completed installing and configuring Net8 and an attempt to make a basic peer-to-peer (single protocol network) connection returns an ORA ERROR, this section may help you diagnose the cause of the problem.

Any underlying fault, noticeable or not, is reported by Net8 with an error number or message that is not always indicative of the actual problem. This section helps you determine which parts of Net8 do function properly rather than the parts that do not work. It also helps you to decide in which of the following categories the fault belongs:

• Oracle software

• Operating system layer

• Other network layers

Testing the various network layers progressively should in most cases uncover any problem.

Server Diagnostics

Answer the questions below: (You may need assistance from your server administrator to follow the instructions in this section.)

• Is any other system (workstations/servers) able to connect to the server using Net8?

• Has the server, database, or listener configuration remained the same for some time?

If you answered YES to any of the above questions/statements, skip this section and continue to "Client Diagnostics".

If you are unsure, or answered NO to any of the above questions, please continue.

Diagnosing Net8 on the server involves the following tasks:

• Task 1: Verify the Database Is Running

• Task 2: Perform a Loopback Test

Task 1: Verify the Database Is Running

To check that the database is up:

• Log onto the database and connect with a valid user name and password. For example:

  sqlplus system/manager 
      
  
           

A message appears, confirming that you are connected with the database. If you receive the following errors, ask your Database Administrator to assist you:

• ORA-1017: invalid U/P

• ORA-1034: Oracle not available

Task 2: Perform a Loopback Test

To perform a loopback test:

1. Check that the LISTENER.ORA, TNSNAMES.ORA, and SQLNET.ORA files exist in $ORACLE_HOME/network/admin on UNIX and ORACLE_HOME\network\admin on Windows NT.

2. Follow the instructions in "Testing Configuration on the Server" to perform a loopback test.
   • If the loopback test continues to fail, continue to the next step.

   • If the loopback test passes, skip to "Client Diagnostics" below.

3. Check the Problem/Solution Database website at http://support.oracle.com for more specific information on the error received, or contact Oracle Worldwide Support.

Client Diagnostics

At this point, you know the Net8 server side listener works properly, because you could verify at least one of the following statements:

• The server passed a loopback test, showing that the connection worked

• Other machines (servers, workstation) connect also using Net8 to this same Oracle Server.

• Connections from this workstation worked previous to making changes on this machine (such as the installation of a new product or a modification to the network configuration).

To perform diagnostics on the client:

1. Check that you have installed the same Oracle protocol(s) as were installed on the server. On UNIX, you can run the adapters program to verify this. Run adapters at $ORACLE_HOME/bin.

   Output similar to the following appears:

   Installed Net8 Tranport Protocols are:
   
       IPC
       TCP/IP
       BEQueath
       SSL
       RAW
   ...
   

2. Check base connectivity for underlying network transport. (You may need help from your network administrator to verify this.) Net8 technology depends on the underlying network for a successful connection.

   Protocol	Verify that you can...
   TCP/IP	Use file transfer or terminal emulation utilities (FTP, TELNET, and PING) from the workstation to the server where the listener and database reside.
   SPX	Perform a Netware log in to the machine on which the database is running. Ensure you can map drives or use other Novell services such as Print Servers and File Servers on the Network. Check that the listener service is broadcasting by doing a DISPLAY SERVERS from the Novell Server or any Novell File Server on the SPX network.
   Named Pipes	See other computers or servers on the MSFT network. Ensure you are able to share drives within the MSFT network.

3. Verify that all Net8 software has been installed to ensure that both the Net8 Client and the appropriate protocol are present.

4. Ensure that the client machine has the TNSNAMES.ORA and the SQLNET.ORA files in $ORACLE_HOME/network/admin on UNIX and ORACLE_HOME\network\admin on Windows platforms.

   The search order for SQLNET.ORA and TNSNAMES.ORA follows:

   1. Current working directory from where an application is executed

   2. TNS_ADMIN environment variable

      If the TNS_ADMIN environment variable is not defined on Windows platforms, TNS_ADMIN it may be in the registry

   3. If TNS_ADMIN is not defined, $ORACLE_HOME/network/admin on UNIX and ORACLE_HOME\network\admin on Windows platforms

   If you have any other working client machines connecting to your selected Oracle database using Net8, back up your existing files and copy both the working TNSNAMES.ORA and SQLNET.ORA files from the working machine onto the non-working client workstations. This eliminates the possibility of errors in the files.

   Test the Net8 layer, as described in "Testing Network Connectivity from the Client".

   Note: Do not to use TNSPING. TNSPING works just like the TCP/IP PING utility and will not create and open a socket, nor does it connect with the listener. It just ensures listener is present at the server side.

   If the connection still fails:

   • Use tracing as described in the following section "Troubleshooting Network Problems Using Log and Trace Files".

   • Check the Problem/Solution Database website at http://support.oracle.com for a specific diagnostics bulletin on the error received.

   • Contact Oracle Worldwide Support.

Resolving the Most Common Error Messages

Due to the complexity of network communications, network errors may originate from a variety of sources, for a variety of reasons. If an error occurs, applications such as SQL*Plus and SQL*Forms, which depend on network services from Net8, will normally generate an error message.

A list of the most common network error messages follows:

• ORA-12154: TNS:could not resolve service name

• ORA-12198: TNS:could not find path to destination

• ORA-12203 TNS:unable to connect to destination

• ORA-12224:TNS:no listener

• ORA-12500: TNS:listener failed to start a dedicated server process

• ORA-12533: TNS:illegal ADDRESS parameters

• ORA-12545: TNS:name lookup failure

• ORA-12560: TNS:protocol adapter error

• ORA-3113: TNS:End of file on communication channel

• ORA-3121: No interface driver connection - function not performed

Error #: Message	Description/Troubleshooting Procedures
ORA-12154: TNS:could not resolve service name	Cause: Net8 could not locate the net service name specified in the TNSNAMES.ORA configuration file. Actions: Verify that a TNSNAMES.ORA file exists and that it is accessible. Verify that there are not multiple copies of the TNSNAMES.ORA file. In your TNSNAMES.ORA file, verify that the net service name specified in your connect string is mapped to a connect descriptor in the TNSNAMES.ORA file. Also, verify that there are no syntax errors in the file. Verify that there are no duplicate copies of the SQLNET.ORA file. If you are using domain names, verify that your SQLNET.ORA file contains a NAMES.DEFAULT_DOMAIN parameter. If this parameter does not exist, you must specify the domain name in your connect string. If you are not using domain names, and this parameter exists, delete it or disable it by commenting it out. If you are connecting from a login dialog box, verify that you are not placing an "@" symbol before your connect net service name. Activate client tracing and re-execute the operation.
ORA-12198: TNS:could not find path to destination ORA-12203 TNS:unable to connect to destination	Cause: The client is not able to find the desired database. Actions: Verify that you have entered the net service name you wish to reach correctly. Verify that the net service name ADDRESS parameters in the connect descriptor of your TNSNAMES.ORA file are correct. Verify that your TNSNAMES.ORA file is stored in the correct directory. Verify that the listener on the remote node has started and is running. If not, start the listener by using the Listener Control Utility. If you are connecting from a login box, verify that you are not placing an "@" symbol before your connect net service name.
ORA-12203 TNS:unable to connect to destination	ORA-12203 error is a generic error that often shields secondary errors. For this reason, check the latest SQLNET.LOG file located in $ORACLE_HOME/network/log on UNIX and ORACLE_HOME\network\log on Windows platforms for secondary ORA messages. If after analyzing the log file you determine there are no secondary errors, determine if the problem may be caused by one the following scenarios: Cause: The incorrect Oracle protocol for the selected networking protocol is installed. A missing protocol support driver usually produces the following errors in the SQLNET.LOG or any client trace file: ORA-12203 ORA-12538 ORA-00508 Action: Check that you have installed the appropriate Oracle protocol. On UNIX, you can run the adapters program to verify this. Run adapters at $ORACLE_HOME/bin. Output similar to the following appears: Installed Net8 Tranport Protocols are: IPC TCP/IP BEQueath SSL RAW ...
ORA-12203 continued	Cause: An invalid net service name was supplied in the connect string. Action: Verify that the net service name supplied in your connect string exists in your TNSNAMES.ORA file and the ADDRESS information for that net service name is valid. Ask yourself the following questions: Is the HOST or SERVICE name correct? Is the PORT specified correct?
ORA-12203 continued	Cause: Net8 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 listener is running. Enter: lsnrctl lsnrctl> status listener_name where listener_name 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, $ORACLE_HOME/network/admin on UNIX and ORACLE_HOME\network\admin on Windows platforms.
ORA-12203 continued	Cause: The destination system's listener is not listening. Action: Verify that the remote system's listener is running. Enter: lsnrctl lsnrctl> status listener_name where listener_name 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
ORA-12203 continued	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.
ORA-12203 continued	Cause: TNSNAMES.ORA file is not located in the proper directory. Action: Make sure the TNSNAMES.ORA file is located in $ORACLE_HOME/network/admin on UNIX and ORACLE_HOME\network\admin on Windows platforms or an alternative path, as explained in "Client Diagnostics".
ORA-12203 continued	Cause: The (HOST=server_name) parameter for TCP/IP addresses or (SERVICE=tns_application) parameters for SPX addresses is not consistent on the clients and server machines. Action: Ensure the values for these parameter are the same on the server and client. For TCP/IP setups, make sure that the HOST parameter in LISTENER.ORA on the server and in 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.
ORA-12224:TNS:no listener	Cause: The connection request could not be completed because the listener is not running. Actions: Ensure that the supplied destination address matches one of the addresses used by the listener. Verify also that this is not a version compatibility problem.
ORA-12500: TNS:listener failed to start a dedicated server process	Cause: The listener was unable to start a process connecting the user to the database server. Actions: Verify that the SID_LIST section of the LISTENER.ORA file and the system identifier (SID) in the CONNECT DATA section of the TNSNAMES.ORA file are correct. Check that the program name specified by the PROGRAM parameter in the SID_LIST section exists in the /bin directory or specify the path for the program name. Verify that the user has adequate privileges to access the database.
ORA-12533: TNS:illegal ADDRESS parameters	Cause: The protocol specific parameters in the ADDRESS section of the designated connect descriptor in your TNSNAMES.ORA file are incorrect. Action: For more information about protocol specific keywords, refer to the Oracle operating system specific documentation for your platform.
ORA-12545: TNS:name lookup failure	Cause: The listener on the remote node cannot be contacted. Actions: Verify that the ADDRESS in the TNSNAMES.ORA file or the LISTENER.ORA file is correct. Verify that the listener on the remote node has been started. You may check its status with the STATUS command of the Listener Control Utility, and start it with the START command if necessary.
ORA-12560: TNS:protocol adapter error	Cause: The listener was unable to start a process connecting the user to the database server. Actions: Turn on tracing and re-execute the operation. Evaluate the contents of the trace file to diagnose the problem.
ORA-3113: TNS:End of file on communication channel	Cause: An unexpected end of file was processed on the communication channel. This may be an indication that the communications link may have gone down at least temporarily; it may indicate that the server has gone down. Action: You may need to modify your re-transmission count. For more information about troubleshooting this error, refer to the appropriate Oracle operating system specific documentation.
ORA-3121: No interface driver connection - function not performed	Cause: A SQL*Net version 1 prefix was erroneously used in the connect string. Action: Do not use the following prefixes in the connect string. T: X: P: Cause: Only the user name and password were specified from a client machine that had no local Oracle database installed. Action: Specify a connect string.

Troubleshooting Tips from the Field

Below are some tips you may find helpful when you are having difficulty diagnosing network problems:

• Use the node or network address during configuration instead of the name of the server machine--This eliminates any internal lookup problems and make the connection slightly faster.

  TCP/IP--Use the internet address rather than the host name in TCP/IP addresses, for example, 198.32.3.5. Change the
  (HOST =server_name) line in the TNSNAMES.ORA file with the internet address, for example (HOST=198.32.3.5).

• Consider possible SPX connection issues--The workstation requesting a connection be made with a remote listener must first learn the location of that SPX service in the NetWare IPX network.

  The client workstation issues a lookup request for the SPX service. If the service cannot be found, an error is sent back to the workstation.

• Perform a loopback test--Perform a loopback test on the server, as described in "Testing Configuration on the Server". If the test passes, FTP the TNSNAMES.ORA and SQLNET.ORA files to the client.

• Check what is between you and the server--If it is a wide area network (WAN), identify any intermediate systems that may not work correctly. If all machines are fine, the problem may be a timing issue.

• Verify Whether or Not There Is 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 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 may not. If you determine the problem is a data volume issue, try to transfer a large (5 MB) 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 out the network cables, if they are close enough, to see if the problem moves. If it does move, it indicates that the problem has something to do with the client-server connection and is not local to the PC.

• What kind of links exist between the client and the server, for example, X.25, 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?

Troubleshooting Network Problems Using Log and Trace Files

Net8 provide detailed information about the source and context of problems as they arise. This information is generated and stored in log and trace files. The process of logging and tracing error information will help you to diagnose and resolve network problems.

For server and listener, log files are by default located in $ORACLE_HOME/network/log on UNIX and ORACLE_HOME\network\log on Windows platforms, and trace files are by default located in $ORACLE_HOME/network/trace on UNIX and ORACLE_HOME\network\trace on Windows platforms. For client, log and trace files are by default located in the current working directory.

Logging Error Information

All errors encountered in Oracle network products are appended 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.

To ensure that all errors are recorded, logging cannot be disabled on clients or Names Servers. Furthermore, only an administrator may replace or erase log files. The log file for the listener also includes Audit Trail information about every client connection request, as well as most listener control commands.

This section covers the following topics:

• Error Stacks

• Log File Names

• Understanding and Setting Log Parameters

• Setting Log Settings During Runtime of Control Utilities

• Using Log Files

• Listener's Log Audit Trail

• Understanding Oracle Connection Manager Logs

Error Stacks

Log files provide information contained in an error stack. An error stack refers to the information that is produced by each layer in an Oracle communications stack as the result of a network error.

Figure 11-1 depicts the relationship among Oracle network products as they might appear in an error stack:

Figure 11-1 Network Products and Error Stack Component

The layers in Figure 11-1 are as follows:

NI	Net8 Interface Layer
NR	Network Routing
NN	Network Naming (Oracle Names)
NS	Network Session (main and secondary layers)
NA	Native Services includes Network Authentication (NA) and Network Encryption (NAE)
NT	Network Transport (main, secondary, and operating system layers)

Your network may or may not include all of these components.

Error Example

As an example, suppose that a user of a client application tries to establish a connection with a database server using Net8 and TCP/IP, and the user enters:

sqlplus scott/tiger@hrserver.com 

The SQL*Plus banner is displayed on the screen, and the following error is displayed:

ORA-12203: TNS:Unable to connect to destination

This message indicates that the connection to the server failed because the database could not be contacted. 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. On the client-side, a log file called SQLNET.LOG, contains an error stack corresponding to the ORA-12203 error as follows:

Figure 11-2 Typical Error Stack

***********************************************************

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:
Oracle Bequeath NT Protocol Adapter for SunOS:
Unix Domain Socket IPC NT Protocol Adaptor for SunOS: 
TCP/IP NT Protocol Adapter for SunOS:
  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

Log File Names

Each Net8 component produces its own log file. The table below provides the default file names and a description of the information they contain:

Log File	Contains Error Information about the...
SQLNET.LOG	Client and/or server
LISTENER.LOG	Listener
NAMES.LOG	Oracle Names Server
CMAN_PID.TRC	Oracle Connection Manager CMGW gateway process
CMADM_PID.TRC	Oracle Connection Manager CMADMIN administrative process

Understanding and Setting Log Parameters

Parameters that control logging, including the type and amount of information logged, as well as the location where the files are stored, are set in the configuration file of each network component as follows:

These log parameters corresponding to the...	...are set in the following Configuration Files
client	SQLNET.ORA
server	SQLNET.ORA
listener	LISTENER.ORA
Oracle Names Server	NAMES.ORA
Oracle Connection Manager processes	CMAN.ORA

This section cover the following topics:

• SQLNET.ORA Parameters

• LISTENER.ORA Parameters

• NAMES.ORA Parameters

• CMAN.ORA Parameters

• Setting Log Parameters in Configuration Files

For further information about the parameters described in this section, see Appendix C, "Configuration Parameters".

SQLNET.ORA Parameters

The following parameters settings can be set in SQLNET.ORA:

SQLNET.ORA Parameter	Net8 Assistant Option	Description
LOG_DIRECTORY_CLIENT	Client Information: Log Directory	Establishes the destination directory for the client log file. By default, the client directory is the current working directory.
LOG_FILE_CLIENT	Client Information: Log File	Sets the name of the log file for the client. By default the log name is SQLNET.LOG.
LOG_DIRECTORY_SERVER	Server Information: Log Directory	Establishes the destination directory for the server log files. By default the server directory is $ORACLE_HOME/network/log on UNIX and ORACLE_HOME\network\log on Windows NT.
LOG_FILE_SERVER	n/a	Sets the name of the log file for the server. By default the log name is SQLNET.LOG.

LISTENER.ORA Parameters

The following log parameters can be set in LISTENER.ORA:

LISTENER.ORA Parameter	Net8 Assistant Option	Description
LOG_DIRECTORY_listener_name LOG_FILE_listener_name	Log File	Establishes the destination directory and file for the log file that is automatically generated for listener events. By default the directory is $ORACLE_HOME/network/log on UNIX and ORACLE_HOME\network\log on Windows NT, and the file name is LISTENER.LOG.

NAMES.ORA Parameters

The following log parameters can be set in NAMES.ORA:

NAMES.ORA Parameter	Net8 Assistant Option	Description
NAMES.LOG_DIRECTORY	Log Directory	Establishes the destination directory for log files. By default, the directory is $ORACLE_HOME/network/log on UNIX and ORACLE_HOME\network\log on Windows NT.
NAMES.LOG_FILE	Log File	Sets the name of the log file for the client. By default the log name is NAMES.LOG.

CMAN.ORA Parameters

The following log parameter can be set in CMAN.ORA:

CMAN.ORA Parameter

Description

LOG_LEVEL

Establishes the level of logging: range is 0 to 4: level 0 - no logging level 1 - basic reporting level 2 - RULE_LIST matching lookup reporting level 3 - relay blocking reporting level 4 - relay I/O counts reporting The CMGW gateway process creates a log file called CMAN_PID.LOG, and the CMADMIN administrative process creates a log file called CMADM_PID.LOG. The log files are located in $ORACLE_HOME/network/log on UNIX and ORACLE_HOME\network\log on Windows NT.

Setting Log Parameters in Configuration Files

SQLNET.ORA, LISTENER.ORA and NAMES.ORA logging parameters can be set with the Net8 Assistant. CMAN.ORA logging parameter must be set manually in CMAN.ORA, as described in "Oracle Connection Manager Parameters (CMAN.ORA)". To set logging parameters:

1. Start the Net8 Assistant:
   • On UNIX, run netasst at $ORACLE_HOME/bin.

   • On Windows NT, choose Start > Programs > Oracle - HOME_NAME > Network Administration > Net8 Assistant.

2. Specify the log parameters:

   For this log file...	Set logging parameters here...
   SQLNET.LOG	Click the Profile icon. Choose General from the drop-down list box. Click the Logging tab. Specify the settings.
   LISTENER.LOG	Click the Listeners folder. Select a listener. Choose General Parameters from the drop-down list box. Click the Logging and Tracing tab. Specify the settings.
   NAMES.LOG	Click the Oracle Names Servers folder. Select an Oracle Names Server. Choose Configure Server from the drop-down list box. Click the Advanced tab. Specify the log directory and file name.

3. Choose Save Network Configuration from the File menu.

4. Choose Exit from the File menu.

   The Net8 Assistant application exits.

Setting Log Settings During Runtime of Control Utilities

Logging can be set during a runtime of a control utilities. Note that setting logging with a control utility will not set parameters in the *.ORA files; the setting is only valid for the session of the control utility:

• For the listener, use the SET LOG_FILE and SET LOG_DIRECTORY commands from the LSNRCTL control utility, as described in "Listener Control Utility (LSNRCTL)".

• For an Oracle Names server, use the SET LOG_FILE_NAME command from the NAMESCTL control utility, as described in "Oracle Names Control Utility (NAMESCTL)", or set it through the Net8 Assistant:
  1. Start the Net8 Assistant:

     -On UNIX, run netasst at $ORACLE_HOME/bin.

     -On Windows NT, choose Start > Programs > Oracle - HOME_NAME > Network Administration > Net8 Assistant.

  2. Click the Oracle Names Servers folder.

  3. Select an Oracle Names Server.

  4. Choose Manage Server from the drop-down list box.

  5. Click the Logging tab.

  6. Specify the log directory and file name.

     Note:: For Oracle Connection Manager, logging can only be set from the Oracle Connection Manager configuration file, as described in "Oracle Connection Manager Parameters (CMAN.ORA)".

  7. Choose Save Network Configuration from the File menu.

  8. Choose Exit from the File menu.

     The Net8 Assistant application exits.

Using Log Files

To use a log file to diagnose a network error:

1. Review the log file for the most recent error number you received from the application. Note that this is almost always the last entry in the log file.

2. Starting from the bottom of the file, locate the first non-zero entry in the error report. This is usually the actual cause.

3. If that error does not provide the desired information, review the next error in the stack until you locate the correct error information.

4. If the cause of the error is still not clear, turn on tracing and re-execute the statement that produced the error message.

Listener's Log Audit Trail

The listener log file contains Audit Trail information that allows you to gather and analyze network usage statistics, as well as information indicating the following:

• a client connection request

• a start, stop, status, reload or service command issued by the Listener Control Utility

Note that you cannot turn this feature off.

Format of the Listener's Log Audit Trail

The Audit Trail formats text into the following fields: Timestamp, Connect Data, Application, [Protocol Info], event, [SID | service], Return Code. Properties of the Audit Trail are as follows:

• Each field is delimited by an asterisk (*)

• PROTOCOL INFO and SID | SERVICE appear only when a connection is attempted

• A successful connection or command returns a code of 0

• A failure produces a code that maps to an error message

Typical output to the log file upon a reload request is as follows:

Figure 11-3 Typical Audit Trail Information for Successful Reload Request

28-dec-98 14:12:18 * (connect_data=(service=sales.com)(cid=
(program=)(host=sales=pc)(user=system)) (command=reload) (arguments=64) 
(service=listener) (version=(version=135282688))* reload * 0

Typical output to the log file upon a connection request is as follows:

Figure 11-4 Typical Audit Trail Information for Successful Connection Request

28-dec-98 14:16:21 * (connect_data=(service=sales.com)(cid=
(program=c:\ora81\bin\sqlplus.exe)
(host=windowspc)(user=dsteiner))) * (address=(protocol=tcp)
(host=144.25.23.246)(port=3366))
* establish * sales.com * 0

Notice that the user ID is recorded as well as the platform, protocol, and software used to make the connection.

Using Audit Trail Information

You can use Audit Trail information to view trends and user activity by first storing it in a table and then collating it into a report format. To import the data into a table, use an import utility such as SQL*Loader.

Understanding Oracle Connection Manager Logs

The Oracle Connection Manager CMGW gateway process creates a log file called CMAN_PID.LOG, and the CMADMIN administrative process creates a log file called CMADM_PID.LOG. The log files are located in $ORACLE_HOME/network/log on UNIX and ORACLE_HOME\network\log on Windows NT.

Figure 11-5 CMAN_PID.LOG

(TIMESTAMP=30-DEC-98 18:03:10)(EVENT=10)(VERSION=8.1.4.0.0)
(TIMESTAMP=30-DEC-98 18:03:10)(EVENT=36)(rule_list= 
(rule=(src=spcstn)(dst=x)(srv=x)(act=accept)))
(TIMESTAMP=30-DEC-98 18:03:10)(EVENT=32)(PARAMETER_LIST=(MAXIMUM_
RELAYS=1024)(RELAY_STATISTICS=no)(AUTHENTICATION_LEVEL=0)(LOG_LEVEL=1)(SHOW_TNS_
INFO=no)(ANSWER_TIMEOUT=0)(MAXIMUM_CONNECT_DATA=1024)(USE_ASYNC_
CALL=yes)(TRACING=no)(TRACE_DIRECTORY=default)(MAX_FREELIST_BUFFERS=0))
(TIMESTAMP=30-DEC-98 18:03:10)(EVENT=34)(ADDRESS_LIST= 
(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1610)(QUEUESIZE=32)))
(TIMESTAMP=30-DEC-98 18:03:12)(EVENT=38)(COMMAND=2)
(TIMESTAMP=30-DEC-98 
18:03:27)(EVENT=26)(RLYNO=0)(SRC=(ADDRESS=(PROTOCOL=tcp)(HOST=spcstn.us.oracle.c
om)(PORT=34758)))(DST=(ADDRESS=(PROTOCOL=tcp)(HOST=144.25.187.89)(PORT=1581)))
(TIMESTAMP=30-DEC-98 18:03:43)(EVENT=28)(RLYNO=0)(SINCE=30-DEC-98 
18:03:27)(STATISTICS=(IN=(BYTES=0)(PACKETS=0)(DCDS=0)(OUT=(BYTES=0)(PACKETS=0)(D
CDS=0)))

Figure 11-6 CMADM_PID.LOG

(TIMESTAMP=30-DEC-98 18:03:09)(EVENT=Sent Admin Status to UI)
(TIMESTAMP=30-DEC-98 18:03:10)(EVENT=CMan Registration)

CMAN_PID.LOG Event Codes

The CMAN_PID.LOG reports events using event codes numbers. This section explains what each of these event codes represents dependent on the log level that was The event code reported is dependent upon the log level set with the LOG_LEVEL parameter in CMAN.ORA:

Log Level 1 Events

Code	Description
10	Gateway is starting up
12	Gateway is shutting down
14	Listening on TNS address(es)
18	Answer failed Additional Information: See "Reasons for Event Code 18".
20	Refusing in-coming call Additional Information: See "Reasons for Event Code 20".
26	Relay is now open
28	Relay is now closed
30	Statistics report
32	<parameter_list>
34	<address_list>
36	<parameter_list>
38	CMCTL command
40	CMCTL command refused because the gateway is busy
42	Dead connection detected
44	Relay has timed out
11	Bad <address_list> argument
13	Bad <parameter_list> argument
15	Bad <rule_list> argument
23	Bad CMCTL record
25	Command line argument is too long
27	Memory allocation failure
29	TNS error
31	TNS error doing CMCTL

Reasons for Event Code 18

The answer can fail due to the following:

Code	Description
1	Timed out
2	Connect data buffer is too small
3	Refused by TNS
4	TNS packet checksum error

Reasons for Event Code 20

The incoming call can be refused if:

Code	Description
1	Gateway is shutting down
3	No connect data on in-coming call
4	Bad connect data on in-coming call
5	All relays are in use
6	Unable to get relay buffers
7	Fatal TNS error
8	No available ASO service
9	Reject" from filter rule
10	Out-going call failed
11	Refused by Net8/TNS
12	Listener is not running
13	Listener is not reachable
14	Host name lookup failure
15	Protocol adapter (and probably the protocol stack) not loaded
16	No SOURCE_ROUTE set in TNSNAMES.ORA or Oracle Names server
17	"reject" from rule or bad connect string data

Log Level 2 Events

Code	Description
102	Answering in-coming call
104	Making out-going call
105	Accepting in-coming call
106	Rule match report

Log Level 3 Events

Code	Description
202	Call will block (no asynchronous TNS support)
204	Relay blocked Additional Information: See "Reasons for Event Code 204".
206	Buffer contains leftover data

Reasons for Event Code 204

Code	Description
1	Waiting for writer to be ready
2	Waiting for writer to clear backlog
3	EWOULDBLOCK error on recovery
4	TNS packet checksum error
5	EWOULDBLOCK or EPARTIAL on send

Log Level 4 Events

Code	Description
302	Read this many bytes
304	Wrote this many bytes
306	Wrote this many bytes on retry

Tracing Error Information

Tracing produces a detailed sequence of statements that describe network events as they are executed. Tracing an operation allows you to obtain more information on the internal operations of the components of Net8 than is provided in a log file. This information is output to files that can be evaluated to identify the events that led to an error.

This section covers the following topics:

• Trace File Names

• Understanding and Setting Trace Parameters

• Setting Trace Settings During Runtime of Control Utilities

• Evaluating Net8 Traces

• Using the Trace Assistant to Examine Your Trace Files

Trace File Names

Each Net8 component produces its own trace file. The table below provides the default file names and a description of the information they contain:

Log File	Contains Error Information about the...
SQLNET.TRC	Client
SVR_PID.TRC	Server
LISTENER.TRC	Listener
NAMES.TRC	Oracle Names Server
CMAN_PID.TRC	Oracle Connection Manager CMGW gateway process
CMADM_PID.TRC	Oracle Connection Manager CMADMIN administrative process

Understanding and Setting Trace Parameters

Parameters that control tracing, including the type and amount of information logged, as well as the location where the files are stored, are set in the configuration file of each network component as follows:

These trace parameters corresponding to the...	...are set in the following Configuration Files
client	SQLNET.ORA
server	SQLNET.ORA
listener	LISTENER.ORA
Oracle Names Server	NAMES.ORA
Oracle Connection Manager processes	CMAN.ORA

This sections cover the following topics:

• SQLNET.ORA Parameters

• LISTENER.ORA Parameters

• NAMES.ORA Parameters

• CMAN.ORA Parameters

• Setting Trace Parameters in Configuration Files

For further information about the parameters described in this section, see Appendix C, "Configuration Parameters".

SQLNET.ORA Parameters

The following parameters settings can be set in SQLNET.ORA:

SQLNET.ORA Parameter	Net8 Assistant Option	Description
TRACE_LEVEL_CLIENT	Client Information: Trace Level	Indicates the level of detail the trace facility records for the client. The trace level value can either be a value within the range of 0 to 16 (where 0 is no tracing and 16 represents the maximum amount of tracing) or a value of OFF, ADMIN, USER, or SUPPORT. OFF (equivalent to 0) provides no tracing. USER (equivalent to 4) traces to identify user-induced error conditions. ADMIN (equivalent to 6) traces to identify installation-specific problems. SUPPORT (equivalent to 16) provides trace information for troubleshooting information for support.
TRACE_DIRECTORY_CLIENT	Client Information: Trace Directory	Establishes the destination directory for the client trace file. By default, the client directory is $ORACLE_HOME/network/trace on UNIX and ORACLE_HOME\network\trace on Windows NT.
TRACE_FILE_CLIENT	Client Information: Trace File	Sets the name of the log file for the client. By default the log name is SQLNET.TRC.
TRACE_UNIQUE_CLIENT	Client Information: Unique Trace File Name	Determines whether or not a unique trace file is created. By default, the value is OFF. The same trace file name is used for every client. If the value is OFF, when a new trace file is created for a client, it overwrites the existing file. If the value is set to ON, a process identifier is appended to the name of each trace file generated so that several can coexist. This parameter creates unique trace files named SQLNETPID.TRC
TRACE_LEVEL_SERVER	Server Information: Trace Level	Indicates the level of detail the trace facility records for the server. The trace level value can either be a value within the range of 0 to 16 (where 0 is no tracing and 16 represents the maximum amount of tracing) or a value of OFF, ADMIN, USER, or SUPPORT. OFF (equivalent to 0) provides no tracing. USER (equivalent to 4) traces to identify user-induced error conditions. ADMIN (equivalent to 6) traces to identify installation-specific problems. SUPPORT (equivalent to 16) provides trace information for troubleshooting information for support.
TRACE_DIRECTORY_SERVER	Server Information: Trace Directory	Establishes the destination directory for the server trace file. By default, the server directory is $ORACLE_HOME/network/trace on UNIX and ORACLE_HOME\network\trace on Windows NT.
TRACE_FILE_SERVER	Server Information: Trace File	Sets the name of the trace file for the client. By default the log name is SVR_PID.TRC.

You can also manually add the following optional tracing parameters for the TNSPING utility to SQLNET.ORA. TNSPING determines whether or not a service (such as a database, an Oracle Names Server, or other TNS services) on a Net8 network can be successfully reached.

SQLNET.ORA Parameter	Description
TNSPING.TRACE_LEVEL	Indicates the level of detail the trace facility records for the TNSPING utility. The trace level value can either be a value within the range of 0 to 16 (where 0 is no tracing and 16 represents the maximum amount of tracing) or a value of OFF, ADMIN, USER, or SUPPORT. OFF (equivalent to 0) provides no tracing. USER (equivalent to 4) traces to identify user-induced error conditions. ADMIN (equivalent to 6) traces to identify installation-specific problems. SUPPORT (equivalent to 16) provides trace information for troubleshooting information for support.
TNSPING.TRACE_DIRECTORY	Establishes the destination directory for TNSPING trace file. By default, the directory is $ORACLE_HOME/network/trace on UNIX and ORACLE_HOME\network\trace on Windows NT.

LISTENER.ORA Parameters

The following trace parameters can be set in LISTENER.ORA:

LISTENER.ORA Parameter	Net8 Assistant Option	Description
TRACE_LEVEL_listener_name	Trace Level	Indicates the level of detail the trace facility records for the listener. The trace level value can either be a value within the range of 0 to 16 (where 0 is no tracing and 16 represents the maximum amount of tracing) or a value of ADMINISTRATOR, USER, or SUPPORT. USER (equivalent to 4) traces to identify user-induced error conditions. ADMINISTRATOR (equivalent to 6) traces to identify installation-specific problems. SUPPORT (equivalent to 16) provides trace information for troubleshooting information for support.
TRACE_DIRECTORY_listener_name TRACE_FILE_listener_name	Trace File	Establishes the destination directory and file for the trace file. By default the directory is $ORACLE_HOME/network/trace on UNIX and ORACLE_HOME\network\trace on Windows NT, and the file name is LISTENER.TRC.

NAMES.ORA Parameters

The following trace parameters can be set in NAMES.ORA:

NAMES.ORA Parameter	Net8 Assistant Option	Description
NAMES.TRACE_DIRECTORY	Trace Directory	Establishes the destination directory for trace files. By default, the directory is $ORACLE_HOME/network/trace on UNIX and ORACLE_HOME\network\trace on Windows NT.
NAMES.TRACE_FILE	Trace File	Sets the name of the trace file. By default the trace name is NAMES.TRC.
NAMES.TRACE_UNIQUE	Make Trace File Unique	If set to ON, creates a unique file name for each trace session, allowing multiple trace files to coexist. A process identifier is appended to the name of the trace file generated. This parameter creates unique trace files named NAMESPID.TRC.

CMAN.ORA Parameters

The following trace parameters can be set in CMAN.ORA:

CMAN.ORA Parameter	Description
TRACING	Sets tracing ON or OFF.
TRACE_DIRECTORY	Establishes the destination directory for trace file. By default, the directory is $ORACLE_HOME/network/trace on UNIX and ORACLE_HOME\network\trace on Windows NT.

Setting Trace Parameters in Configuration Files

SQLNET.ORA, LISTENER.ORA and NAMES.ORA logging parameters can be set with the Net8 Assistant. CMAN.ORA logging parameter must be set manually, as described in "Oracle Connection Manager Parameters (CMAN.ORA)". To set logging parameters:

1. Start the Net8 Assistant:
   • On UNIX, run netasst at $ORACLE_HOME/bin.

   • On Windows NT, choose Start > Programs > Oracle - HOME_NAME > Network Administration > Net8 Assistant.

2. Specify the trace parameters:

   For this trace file...	Set tracing parameters here...
   SQLNET.TRC (for the client SVR_PID.TRC (for the server)	Click the Profile icon. Choose General from the drop-down list box. Click the Tracing tab. Specify the settings:
   LISTENER.TRC	Click the Listeners folder. Select a listener. Choose General Parameters from the drop-down list box. Click the Logging and Tracing tab. Specify the settings:
   NAMES.TRC	Click the Oracle Names Servers folder. Select an Oracle Names server. Choose Configure Server from the drop-down list box. Click the Logging tab. Specify the settings.

3. Choose Save Network Configuration from the File menu.

4. Choose Exit from the File menu.

   The Net8 Assistant application exits.

Setting Trace Settings During Runtime of Control Utilities

Tracing can be set during a runtime of a control utilities. Note that setting tracing with a control utility will not set parameters in the *.ORA files; the setting is only valid for the session of the control utility:

• For the listener, use the SET TRC_FILE, SET TRC_DIRECTORY and SET TRC_LEVEL commands from the LSNRCTL control utility, as described in "Listener Control Utility (LSNRCTL)".

• For an Oracle Names server, use the SET TRACE_FILE_NAME and SET TRACE_LEVEL command from the NAMESCTL control utility, as described in "Oracle Names Control Utility (NAMESCTL)", or set it through the Net8 Assistant:
  1. Start the Net8 Assistant:

     -On UNIX, run netasst at $ORACLE_HOME/bin.

     -On Windows NT, choose Start > Programs > Oracle - HOME_NAME > Network Administration > Net8 Assistant.

  2. Click the Oracle Names Servers folder.

  3. Select an Oracle Names Server.

  4. Choose Manage Server from the drop-down list box.

  5. Click the Logging tab.

  6. Specify the trace level, directory and file name.

     Note:: For Oracle Connection Manager, tracing can only be set from the Connection Manager configuration file, as described in "Oracle Connection Manager Parameters (CMAN.ORA)".

  7. Choose Save Network Configuration from the File menu.

  8. Choose Exit from the File menu.

     The Net8 Assistant application exits.

Evaluating Net8 Traces

Evaluating trace files either manually, or by using the Trace Assistant tool will help you to diagnose and troubleshoot network problems by giving you a better understanding of the following:

• the flow of packets between network nodes

• which component of Net8 is failing

• pertinent error codes

Understanding the Flow of Data Packets Between Network Nodes

Net8 performs its functions by sending and receiving data packets.By specifying a trace level of SUPPORT, you can view the actual contents of the Net8 packet in your trace file. The order of the packet types sent and received will help you to determine how your connection was established.

Understanding Data Packet Formats

Each line in the trace file begins with a procedure followed by a message. Following each procedure is a line of hexadecimal data representing actual data. The actual data that flows inside the packet is sometimes viewable to the right of the hexadecimal data.

Below is a list of the Net8 packet keywords and describes the types of packets they represent:

For example, the following line describes a procedure called "nscon" sending a NSPTCN packet over the network:

nscon: sending NSPTCN packet

Each packet has a keyword that denotes the packet type. All packet types begin with the prefix "NSP". It is helpful to remember this when reviewing trace files for specific packet information

Figure 11-7 provides typical packet information:

Figure 11-7 Packet Information

nscon: entry
nscon: doing connect handshake...
nscon: sending NSPTCN packet
nspsend: entry
nspsend: plen=187, type=1
nspsend: 187 bytes to transport
nspsend:packet dump
nspsend:00 BB 00 00 01 00 00 00  |........|
nspsend:01 33 01 2C 0C 01 08 00  |.3.,....|
nspsend:7F FF 7F 08 00 00 00 01  |........|
nspsend:00 99 00 22 00 00 08 00  |..."....|
nspsend:01 01 28 44 45 53 43 52  |..(DESCR|
nspsend:49 50 54 49 4F 4E 3D 28  |IPTION=(|
nspsend:43 4F 4E 4E 45 43 54 5F  |CONNECT_|
nspsend:44 41 54 41 3D 28 53 49  |DATA=(SI|
nspsend:44 3D 61 70 33 34 37 64  |D=ap347d|
nspsend:62 31 29 28 43 49 44 3D  |b1)(CID=|
nspsend:28 50 52 4F 47 52 41 4D  |(PROGRAM|
nspsend:3D 29 28 48 4F 53 54 3D  |=)(HOST=|
nspsend:61 70 32 30 37 73 75 6E  |ap207sun|
nspsend:29 28 55 53 45 52 3D 6D  |)(USER=m|
nspsend:77 61 72 72 65 6E 29 29  |warren))|
nspsend:29 28 41 44 44 52 45 53  |)(ADDRES|
nspsend:53 5F 4C 49 53 54 3D 28  |S_LIST=(|
nspsend:41 44 44 52 45 53 53 3D  |ADDRESS=|
nspsend:28 50 52 4F 54 4F 43 4F  |(PROTOCO|
nspsend:4C 3D 74 63 70 29 28 48  |L=tcp)(H|
nspsend:4F 53 54 3D 61 70 33 34  |OST=ap34|
nspsend:37 73 75 6E 29 28 50 4F  |7sun)(PO|
nspsend:52 54 3D 31 35 32 31 29  |RT=1521)|
nspsend:29 29 29 00 00 00 00 00  |))).....|
nspsend: normal exit
nscon: exit (0)

Understanding Pertinent Error Output

Every time a problem occurs with the connection in Net8, the error code is logged in the trace file with the prefix of <ERROR> or <FATAL>. Figure 11-8 depicts typical trace file error output.

Figure 11-8 Trace File Error Output

nspsend: entry
nspsend: plen=244, type=6
ntpwr: entry
ntpwr: exit
-<ERROR>- nspsend: transport write error
nspsend: error exit
nserror: entry
-<ERROR>- nserror: nsres: id=0, op=65, ns=12541, ns2=12560; nt[0]=511, 
nt[1]=61,nt[2]=0
-<ERROR>- nsopen: unable to open transport
nricdt: Call failed...
nricdt: exit
-<ERROR>- osnqper:  error from nricall
-<ERROR>- osnqper:  nr err code: 12203
-<ERROR>- osnqper:  ns main err code: 12541
-<ERROR>- osnqper:  ns (2)  err code: 12560
-<ERROR>- osnqper:  nt main err code: 511
-<ERROR>- osnqper:  nt (2)  err code: 61
-<ERROR>- osnqper:  nt OS   err code: 0
osnqme: entry
osnqme:  reporting nr (1) error: (12203) as rdbms err (12203)
osnqme: exit
-<ERROR>- onstns: Couldn't connect, returning 12203
nricall: Exiting NRICALL with following termination result -1
nricall: exit
osnqme: entry
osnqme:  reporting nr (1) error: (12203) as rdbms err (12203)
osnqme: exit
-<ERROR>- onstns: Couldn't connect, returning 12203
-<ERROR>- osnqper:  error from nricall

The most efficient way to evaluate error codes is to find the most recent NS error code logged. This is because the session layer controls the connection. The most important error messages are the ones at the bottom of the file. They are the most recent errors and the source of the problem with your connection.

For information about the specific return codes, use the Oracle UNIX error tool oerr. Use the oerr tool to discover more information about Net8 return codes, by entering the following at any command line prompt:

oerr tns error_number

Using the Trace Assistant to Examine Your Trace Files

Net8 provides a tool called the Trace Assistant to help you understand the information provided in your trace files by converting existing lines of trace file text into a more readable paragraph. Note that the Trace Assistant runs against only a level 16 (SUPPORT) SQL*Net or Net8 trace file.

To run the Trace Assistant, enter the following at any command line prompt:

trcasst [options] filename

The options are described below.

Option	Description
-o	Displays connectivity and Two Task Common (TTC) information. After the -o the following options may be used: c (for summary connectivity information) d (for detailed connectivity information) u (for summary TTC information) t (for detailed TTC information) q (displays SQL commands enhancing summary TTC information)
-p	Oracle Internal Use Only
-s	Displays statistical information
-e	Enables display of error information After the -e, zero or one error decoding level may follow: 0 or nothing (translates the NS error numbers dumped from the nserror function plus lists all other errors) 1 (displays only the NS error translation from the nserror function) 2 (displays error numbers without translation)
If no options are provided, then the default is -odt -e -s, providing detailed connectivity, detailed Two-Task Common, error level 0, and statistics.

Figure 11-9 depicts how Trace Assistant converts trace file information into a more readable format.

Figure 11-9 Typical Trace Assistant Conversion

nsc2addr: normal exit
nsopen: entry
nsmal: 404 bytes at 
0x10d5a48
nsopen: opening 
transport...
-<ERROR>- ntus2err: sd=13, 
op=1, resnt[0]=511, 
resnt[1]=2, resnt[2]=0
-<ERROR>- nserror: nsres: 
id=0, op=65, ns=12541, 
ns2=12560; nt[0]=511, 
nt[1]=2, nt[2]=0
-<ERROR>- nsopen: unable 
to open transport

However, other errors may also exist within the trace file that were not logged from the nserror function.

Understanding Information Traversing the Network in Net8 Packets

Trace Assistant also allows you to view data packets from both the Net8 and Two Task Common communication layers. Trace Assistant offers you two options to view these packets:

• summary connectivity (using option -oc)

• detailed connectivity (using option -od)

Net8 Packet Examples

The following examples depict how Trace Assistant presents various packets as they are sent to and from the Net8 layer in a variety of transactions:

• bequeathed connection

• redirected connection

• data packet

Note that the packets being sent or received have a prefix of "---> Send nnn bytes" or "<--- Received nnn bytes" showing that this node is sending or receiving a packet of a certain type and with nnn number of bytes. This prefix enables you to determine if the node is the client or the server. The connection request is always sent by the client, but received by the server (or listener).

Figure 11-10 Summary Data Packets Sent in a Bequeathed Connection

--->  Send     192 bytes - Connect packet
        Connect data length: 142
(description=(address=(protocol=tcp)(host=dlsun)(port=1521))(connect_
data=(sid=db1)(cid=(program=)(host=dlsun)(user=use1))))
<---  received 24 bytes - accept packet
        Accept data length: 0

Figure 11-11 Detailed Data Packets Sent in a Bequeathed Connection

--->  Send     50 bytes - Connect packet
Current NS version number is: 309.
Lowest NS version number can accommodate is: 300.
Global options for the connection:
        can receive attention
        no attention processing
        Don't care
        Maximum SDU size: 2048
        Maximum TDU size: 5120
        NT protocol characteristics:
                Test for more data
                Spawner is running
                Hang on to Listener connection
                Full duplex I/O
                Urgent data support
                Generate SIGURG signal
                Handoff connection to another
        Line turnaround value: 0
        Connect data length: 234
        Connect data offset: 50
        Connect data maximum size: 2048
                Native Services wanted
                Native Services wanted
Cross facility item 1: 0
        Cross facility item 2: 0
        Connection id: Ox0000000000000000
        Packet data is in the following data packet
--->  Send     244 bytes - Data packet
(DESCRIPTION=(ADDRESS=(PROTOCOL=beq)(PROGRAM=/private/oracle/bin/oracle)(ARGV0=oracle)(A
RGS='(DESCRIPTION=(LOCAL=YES) (ADDRESS=(PROTOCOL=beq))))(DETACH=NO))(CONNECT_
DATA=(CID=(PROGRAM=)(HOST=dlsun)(USER=use1))))
<---  Received 24 bytes - Accept packet
        Accepted NS version number is: 307.
Global options for the connection:
        no attention processing
        Don't care
        Accepted maximum SDU size: 2048
        Accepted maximum TDU size: 4096
        Connect data length: 0
                Native Services wanted
                Native Services wanted

Figure 11-12 Summary Data Packets Sent in a Redirected Connection

--->  Send     187 bytes - Connect packet
        Connect data length: 153
(DESCRIPTION=(CONNECT_
DATA=(SID=ap347db1)(CID=(PROGRAM=)(HOST=apsun)(USER=use2)))(ADDRESS_
LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=apsun)(PORT=1521))))
<---  Received 8 bytes - Resend packet
--->  Send     187 bytes - Connect packet
        Connect data length: 153
(DESCRIPTION=(CONNECT_DATA=(SID=apdb1)(CID=(PROGRAM=)(HOST=apsun)(USER=use2)))(ADDRESS_
LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=apsun)(PORT=1521))))
<---  Received 24 bytes - Accept packet
        Accept data length: 0

Figure 11-13 Data Packet

--->  Send     30 bytes - Data packet
<---  Received 201 bytes - Data packet
--->  Send     439 bytes - Data packet
<---  Received 400 bytes - Data packet

Two Task Common Packet Examples

Two-Task Common handles requests such as open cursor, select rows, and update rows that are directed to the database. All requests are answered by the server. If you request to logon, a response is returned from the database that the request was completed. Figure 11-14 through Figure 11-16 show the type of information you can expect.

Summary information for Two-Task Common is different from other displays in that it shows two packets on each line, rather than one. This is done to mirror the request/response pairings process by which Two-Task Common operates.

Figure 11-14 Two Task Common Summary Information

(O3LOGA)

1st half of challenge-response logon

80

78

(O3LOGON) 

2nd half of challenge-response logon

97

59 

(OOPEN)

# 1 

21

16

(OPARSEX)

# 1 

245

 59

(OCLOSE) 

# 1 

17

11

(OVERSION)

29

16

(OOPEN) 

# 2

21

16

(OALL7) 

# 2 Parse Can Defn=2 Exec Fetch "SELECT A.V

268

100

(OOPEN) 

# 3 

21

16

(OALL7) 

# 3 Parse Exec=1 "SELECT USER FROM SYS.DUAL

152

70

(OALL7) 

# 3 Defn=1 Fetc

117

88

(OCLOSE)

# 3

17

11

Figure 11-15 Two Task Common Summary Information

start of user function (TTIFUN)
        1st half of challenge-response logon (O3LOGA)
          Username: applsys
          Terminal: ttyp5
          Machine: ap207sun
          System User: mwarren
          Process: 24459
          Program: aiap45@ap207sun (TNS interface)
return opi parameter (TTIRPA)
        OPI parameter: 3309B1A977A62A3C
start of user function (TTIFUN)
        2nd half of challenge-response logon (O3LOGON)
          Username: applsys
          Terminal: ttyp5
          Machine: ap207sun
          System User: mwarren
          Process: 24459
          Program: aiap45@ap207sun (TNS interface)
ORACLE function complete (TTIOER)
start of user function (TTIFUN)
        Open a cursor
return opi parameter (TTIRPA)
        Cursor #: 1
start of user function (TTIFUN)
        Parse and Execute (OPARSEX) Cursor # 1 
alter session set nls_language= 'AMERICAN' nls_territory= 'AMERICA' nls_currency= 
'$' nls_iso_currency= 'AMERICA' nls_numeric_characters= '.,' nls_date_format= 
'DD-MON-YY' nls_date_language= 'AMERICAN' nls_sort= 'BINARY'
ORACLE function complete (TTIOER)
start of user function (TTIFUN)
        Close cursor (OCLOSE) Cursor # 1 
V6 Oracle func complete (TTISTA)
        Succeeded

Figure 11-16 Detailed SQL information on top of summary Two-Task

(O3LOGA)

1st half of challenge-response logon

180

78

(O3LOGON) 

2nd half of challenge-response logon

197

59 

(OOPEN)

# 1 

21

16

(OPARSEX)

# 1 alter session set nls_language= 'AMERICAN' nls_
territory= 'AMERICA' nls_currency= '$' nls_iso_
currency= 'AMERICA' nls_numeric_characters= '.,' nls_
date_format= 'DD-MON-YY' nls_date_language= 
'AMERICAN' nls_sort= 'BINARY'

245

 59

(OCLOSE) 

# 1 

17

11

(O71SESOPN)

(get session ID) 

47

18    

(OOPEN)

# 1

21 

16   

(OVERSION)

Oracle7 Server Release 8.1.3.0.0 - Production Release 
with the distributed and parallel query optionsPL/SQL 
Release 2.1.4.0.0 - Production

29

157

(O71SESOPN)

(get session ID) 

47

18

Analyze the Data Collected into Appropriate Statistics

The type of statistics gathered is on the order of how many calls (TTC), packets and bytes were sent and received between the network partners. The following example depicts typical trace file statistics:

Figure 11-17 Typical Trace File Statistics

======================================================================
     Trace File Statistics: 
    ----------------------------------------------------------------------
     SQL*Net:
          Total Calls:       466 sent,       491 received,       423 upi
          Total Bytes:    119214 sent,     86614 received
        Average Bytes:       255 sent,       176 received
        Maximum Bytes:      2048 sent,      2048 received 
     GRAND TOTAL PACKETS  sent:    466     received:    491

Trace File Example

The following example shows a full trace file decoded. This example was created using the Oracle client tool SVRMGRL with the request:

connect scott/tiger@june

The message ORA-12154: TNS:could not resolve service name was displayed on the screen.

Figure 11-18 Trace File Example

-- TRACE CONFIGURATION INFORMATION FOLLOWS ---
New trace stream is "C:\ORANT\network\trace\sqlnet7.trc"
New trace level is 16
--- TRACE CONFIGURATION INFORMATION ENDS ---

nnfotran: tnsname.ora entry for name "june.com" not found
nnftqnm: Error querying june.com of attribute A.SMD errcode 
406
nnfgrwsp: Query unsuccessful, skipping to next adapter

nnfgrwsp: Switching to ONAMES adapter
nnfgrwsp: Original name: june
nnfgrwsp: Qualified name: june.com
nngsget_get_stream: looking for "(DESCRIPTION=(CONNECT_
DATA=(RPC=ON))(ADDRESS=(PROTOCOL=tcp)(HOST=oranamesrvr0)(PORT
=1575)))"
nngsget_get_stream: cache miss, opening new stream
nngsnad_new_stream_addr: "(DESCRIP TION=(CONNECT_
DATA=(RPC=ON))(ADDRESS=(PROTOCOL
=tcp)(HOST=oranamesrvr0)(PORT=1575)))"
nngsget_get_stream: no caller address will be sent to callee

nricall: entry
nric2a: entry
nric2a: Getting local community information
nriglp: entry
nriglp: Looking for local addresses setup by nrigla
nriglp: No addresses in the preferred address list
nriglp: exit
nric2a: TNSNAV.ORA is not present. No local communities 
entry.
nrigla: entry
nrigla: Getting local address information
nrigla: Simple address...
nrigla: No community component so just use straight address
nrigla: exit
nridst: entry
nridst: Resolving address to use to call destination or next 
hop
nridst: Found destination address
nridst: Local address
nridst: Local destination community found
nridst: exit
nric2a: This is a local community access
nric2a: exit
nricall: Got routable address information

nricall: Making call with following address information: 
(DESCRIPTION=(CONNECT_
DATA=(RPC=ON))(ADDRESS=(PROTOCOL=tcp)(HOST=oranamesrvr0)(PORT
=1575)))
nricdt: entry
nricdt: Calling with outgoing connect data 
(DESCRIPTION=(CONNECT_
DATA=(RPC=ON))(ADDRESS=(PROTOCOL=tcp)(HOST=oranamesrvr0)(PORT
=1575)))

nscall: entry
nscall: connecting...
nsc2addr: entry
nsc2addr: (DESCRIPTION=(CONNECT_
DATA=(RPC=ON))(ADDRESS=(PROTOCOL=tcp)(HOST=oranamesrvr0)(PORT
=1575)))

nttbnd2addr: entry
nttbnd2addr: port resolved to 1575
nttbnd2addr: looking up IP addr for host: oranamesrvr0
nttbnd2addr: exitnsopen: entry
nsmal: entry
nsmal: 330 bytes at 0x30d76e74
nsmal: normal exit
nsopen: opening transport...
nttcon: entry
nttcon: toc = 1
nttcnp: entry
nttcnp: creating a socket.
nttcnp: exit
nttcni: entry
nttcni: trying to connect to socket 1.
ntt2err: entry

-<ERROR>- ntt2err: soc 1 error - operation=1, ntresnt[0]=511, 
ntresnt[1]=61 ntresnt[2]=0
ntt2err: exit
nttcni: exit
nttcon: exit
nserror: entry

  -<ERROR>- nserror: nsres: id=0, op=65, ns=12541, ns2=12560; 
nt[0]=511, nt[1]=61,nt[2]=0
-<ERROR>- nsopen: unable to open transport
nsmfr: entry
nsmfr: 330 bytes at 0x30d76e74
nsmfr: normal exit
nsopen: error exit
nscall: error exit
nricdt: Call failed...
nricfg: entry
nricfg: exit
nricdt: Call made to destination
nricdt: exit

nricall: Failed to copy originating community name value 
binding
nricall: Exiting NRICALL with following termination result -1
nricall: exit
nngsfad_free_stream_addr: "(DESCRIPTION=(CONNECT_
DATA=(RPC=ON))(ADDRESS=(PROTOCOL=tcp)(HOST=oranamesrvr0)(PORT
=1575)))"
-<ERROR>- nngsget_get_stream: open failure, error stack 
follows

TNS-12224: TNS:no listener
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
nnfgrwsp: Query unsuccessful, skipping to next adapter

nnfun2a: address for name "june" not found
nngsfad_free_stream_addr: "(DESCRIPTION = (CONNECT_DATA= 
(RPC=ON)) (ADDRESS=(PROTOCL=tcp) (HOST=oranamesrvr0) 
(PORT=1575)))"
nngtdei_deinit_msg: free message pool block
nngtfms_free_msg: message ID -10429
nngtfms_free_msg: message was a request
nngtfms_free_msg: message free, type 100
nngtfoa_free_objarr: free message object array
nngtfmt_free_msg_type: type-specific message free, type 100
nngtfoa_free_objarr: free message object array
nngtfms_free_msg: message ID 0
nngtfms_free_msg: message was a response
nngtfms_free_msg: message free, type 0
nngsdei_deinit_streams: deinit
nngscls_close_stream: UID 11 not established, ignored
nngscls_close_stream: UID 0 not established, ignored
osnqrn: Return code from nnfsn2a is 409

-<ERROR>- onstns: Couldn't connect, returning 12154
onstns: exit
osnqtg: Count in the OSN global area is now 0
rigbd: entry
nrigbd: exit
osnqtg: Count in the NL global area is now 0

Trace File Example Summary

This trace file provides a summary of what occurs with Net8 when you encounter the error ORA-12154: Could not resolve service name. In this example, a client is unsuccessful in making a connection to net service name JUNE. This is because a NAMES.DEFAULT_DOMAIN = COM parameter exists in the SQLNET.ORA file. This parameter adds the ".COM" extension to all net service names requested, including the net service name JUNE. Unfortunately, this net service name is defined in neither the client's TNSNAMES.ORA file, nor an Oracle Names Server. To troubleshoot this problem, the user should:

• edit the SQLNET.ORA file to remove the NAMES.DEFAULT_DOMAIN configuration parameter; or

• request a connection to JUNE.COM instead of JUNE

Contacting Oracle Customer Support

If you are still unable to resolve your problems or if you are requested to contact Oracle 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 up-to-five-digit release number of all the Oracle networking products involved in the current problem

• The third-party vendor and version you are using

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

• The kind of links that exist between the client and server

• A description of what does work

• The exact error message, if there is one

• A Net8 trace, if possible; if not, the log file is sufficient