How to enable SQL*Net Client Side Tracing

Article
07/06/2010

How many times we have run into issues with client applications/SSIS Packages connecting to Oracle Databases and erroring out intermittently? Well, I have run into such scenarios on numerous occasions and the intermittent nature of the error makes it really difficult to figure out the root cause of such issues. In such cases I have found out Sql*Net client side tracing extremely useful in narrowing down the issue which often boils down to a simple Network Connectivity issue or a connectivity issue due to a configuration or protocol error on the Oracle side.
In plenty of cases we need to collaborate with Oracle support as the error might be coming from the Oracle Data Access Layer as well as the Connection/Command Timeout settings in the Oracle Configuration files. The purpose of this write up is NOT to discuss the possible issues and their troubleshooting methods but I would focus mainly on enabling the tracing (Detailed Tracing is turned OFF by default), discuss a few of the tracing switches available and their significances so that we are able to dig deep into the actual error message and consider possible resolution options.

The aim of this document is to overview SQL*Net, Net8, Oracle Net Services tracing and logging facilities. Although only basic information on how to enable and disable tracing and logging features is described, the document also serves as a quick reference. The document provides the reader with the minimum information necessary to generate trace and log files with a view to forwarding them to Oracle Support Services (OSS) for further diagnosis. The article does not intend to describe trace/log file contents or explain how to interpret them. For detailed information about all the switches and their implementations, please refer to: https://download.oracle.com/docs/cd/A57673_01/DOC/net/doc/NWTR23/ch3trc.htm

As I mentioned earlier, detailed Level of Sqlnet tracing is turned OFF by default. As a result, when a connection error occurs, we see entries similar to the following in the sqlnet.log file:

Fatal NI connect error 12560, connecting to: (DESCRIPTION=(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=oracle)(ARGV0=oracleORCL)(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))'))(CONNECT_DATA=(SID=ORCL)(CID=(PROGRAM=c:\windows\system32\inetsrv\w3wp.exe)(HOST=SFTNETAPP1)(USER=svcsys_qrmdms))))

VERSION INFORMATION:
TNS for 32-bit Windows: Version 11.1.0.7.0 – Production
Oracle Bequeath NT Protocol Adapter for 32-bit Windows: Version 11.1.0.7.0 – Production
Windows NT TCP/IP NT Protocol Adapter for 32-bit Windows: Version 11.1.0.7.0 – Production
Time: 03-MAR-2010 10:13:39
Tracing not turned on.
Tns error struct:
ns main err code: 12560

TNS-12560: TNS: protocol adapter error
ns secondary err code: 0
nt main err code: 530

TNS-00530: Protocol adapter error
nt secondary err code: 126
nt OS err code: 0

In the error description section we can see the program name (In my case I was connecting through a web App; so it is w3wp.exe). The error reported above is really kind of generic stuff and there could be numerous reasons for such an error. The solutions for such a generic error may vary on a wide range and could even include different types of TIMEOUT settings that could be configured in the Sqlnet.ora file. Notice the highlighted text in the above error message which says “Tracing not turned on”. So, the bottom line being, collecting a detailed Sqlnet trace is always a good option to track down the actual issue.

First of all, we need to choose the component we would be tracing. Of the various components available like Client, Server, Listener etc. we would only focus on Client Side tracing.
For any component, you can invoke tracing by editing the component configuration file that corresponds to the component traced. The component configuration files are SQLNET.ORA, LISTENER.ORA, and NAMES.ORA etc.; for client side tracing we would only concentrate on SQLNET.ORA file which is typically found in: “%ORACLE_HOME%\Network\Admin” directory.

Below are the steps to configure Sql*Net verbose Client side tracing:
(1) Open the SQLNET.ORA file typically found in the installation location (or simply do a Search):

(2) Add the following parameters at the end of the file:

#CLIENT-SIDE SQL*NET TRACE PARAMETERS
#====================================
TRACE_UNIQUE_CLIENT = ON
TRACE_LEVEL_CLIENT = 16
TRACE_DIRECTORY_CLIENT = C:\temp
TRACE_FILE_CLIENT = SQLNetTrace
TRACE_TIMESTAMP_CLIENT = ON
#TRACE_FILELEN_CLIENT = 2048
#TRACE_FILENO_CLIENT = 2

Parameters prefixed with“#” are interpreted as comments and will not affect tracing.

If you want to change the location where the trace file will be saved modify the following two parameters:

(a) TRACE_DIRECTORY_CLIENT

Set this parameter to the folder where you want the trace file to be saved.

WARNING: Do not end the path with a “\”. This is not necessary and may prevent the trace file from being generated.

(b) TRACE_FILE_CLIENT

Set this parameter to the filename of the trace file that will be created. The actual trace filename may contain additional information appended to the end of the filename such as

SQLNetTrace_ _<#>.trc

Where_ _ is the application’s process id while it was running and <#> distinguishes different client connections established from the same process id (or session) to the database. This occurs since the
parameter TRACE_UNIQUE_CLIENT is set to ON.

The parameter TRACE_LEVEL_CLIENT=16 ensures that maximum trace information is logged.

NOTE: If you think the size of the trace file will exceed the free space on the drive where TRACE_DIRECTORY_CLIENT is saving the file, then uncomment the last two parameters which will have the following effect:

(a) TRACE_FILELEN_CLIENT - will create trace files of the size specified in kilobytes (KB).

(b) TRACE_FILENO_CLIENT - will write to multiple trace files up to the maximum size specified by
TRACE_FILELEN_CLIENT and then cycle through the files again overwriting previously written trace information.

The default values specified above for these two parameters will cycle the trace output between two trace files with a maximum size of 2MB each.

Once you have made the appropriate changes to the SQLNET.ORA file be sure to SAVE the file.

NOTE: If the application you are tracing is running from inside a web server environment, such as Microsoft’s Internet Information Server (IIS), you will need to RESTART THE WEB SERVER at this
point in order for these changes to be seen by the web server. This is due to the fact that web servers may cache the SQLNET.ORA file.

(3) Immediately run the application that uses one of the programmatic interfaces and generate the error.

(4) Immediately go back to the SQLNET.ORA file and turn tracing off by using any of the following methods:

(a) Modify the following parameter:

TRACE_LEVEL_CLIENT = OFF

(b) Remove all the tracing parameters you added to this file or

Save the changes to the SQLNET.ORA file.

(5) Retrieve the “.trc” file(s) from the location “C:\temp\SQLNetTrace\” or wherever you have saved the file(s) based on what was specified in the parameters TRACE_DIRECTORY_CLIENT and TRACE_FILE_CLIENT. If you did not specify a location please check the location of the directory where the application was run from or search your hard drive for all “.trc” files. Chec(1) Open the SQLNET.ORA file typically found in the following location:

ORACLE_BASE\ORACLE_HOME\Network\Admin (7.3.x, 8.1.x or later)
ORACLE_BASE\ORACLE_HOME\Net80\Admin (8.0.x)

(2) Add the following parameters at the end of the file:

Parameters prefixed with a “#” are interpreted as comments and will not
affect tracing.

If you want to change the location where the trace file will be saved
modify the following two parameters:

(a) TRACE_DIRECTORY_CLIENT

Set this parameter to the folder where you want the trace file to be
saved.

WARNING: Do not end the path with a “\”. This is not necessary and may
prevent the trace file from being generated.

(b) TRACE_FILE_CLIENT

Set this parameter to the filename of the trace file that will be
created. The actual trace filename may contain additional information
appended to the end of the filename such as

SQLNetTrace_ _<#>.trc

where is the application’s process id while it was running and
<#> distinguishes different client connections established from the
same process id (or session) to the database. This occurs since the
parameter TRACE_UNIQUE_CLIENT is set to ON.

The parameter TRACE_LEVEL_CLIENT=16 ensures that maximum trace
information is logged.

NOTE: If you think the size of the trace file will exceed the free space
on the drive where TRACE_DIRECTORY_CLIENT is saving the file, then
uncomment the last two parameters which will have the following
affect:

(a) TRACE_FILELEN_CLIENT - will create trace files of the size
specified in kilobytes (KB)

(b) TRACE_FILENO_CLIENT - will write to multiple trace files up
to the maximum size specified by
TRACE_FILELEN_CLIENT and then cycle
through the files again overwriting
previously written trace information

The default values specified above for these two paramters will
cycle the trace output between two trace files with a maximum size
of 2MB each.

Once you have made the appropriate changes to the SQLNET.ORA file be
sure to SAVE the file.

NOTE: If the application you are tracing is running from inside a web
server environment, such as Microsoft’s Internet Information
Server (IIS), you will need to RESTART THE WEB SERVER at this
point in order for these changes to be seen by the web server.
This is due to the fact that web servers may cache the SQLNET.ORA
file.

(3) Immediately run the application that uses one of the programmatic
interfaces and generate the error.

(4) Immediately go back to the SQLNET.ORA file and turn tracing off by using
any of the following methods:

(a) Modify the following parameter:

TRACE_LEVEL_CLIENT = OFF

(b) Remove all the tracing parameters you added to this file or

Save the changes to the SQLNET.ORA file.

(5) Retrieve the “.trc” file(s) from the location “C:\temp\SQLNetTrace\” or
wherever you have saved the file(s) based on what was specified in the
parameters TRACE_DIRECTORY_CLIENT and TRACE_FILE_CLIENT. If you did not
specify a location please check the location of the directory where the
application was run from or search your hard drive for all “.trc” files.
Check the size of the “.trc” file(s) and make sure they do not have a
size of 0 bytes but do have the current time and date. Zip the file up
and upload it to support.

Check the size of the “.trc” file(s) and make sure they do not have a size of 0 bytes but do have the current time and date. Zip the file up and upload it to the respective support group for further diagnosis.

How to enable SQL*Net Client Side Tracing

Additional resources