Setting up Oracle Net Services Tracing on the Client and Server

Tracing the Oracle Net Services provides detailed information to aid in diagnosing connectivity issues. This document will detail the parameters used in tracing Oracle Net Services along with providing an example of tracing a connection to a database.

Oracle Database 11gR1 introduced the Automatic Diagnostic Repository (ADR) which disables many of the parameters detailed in this document. In order to enable many of these parameters ADR will need to be disabled with the DIAG_ADR_ENABLED=OFF in the ORACLE_HOME/network/admin/sqlnet.ora or with DIAG_ADR_ENABLED_< listener_name >=OFF in the ORACLE_HOME/network/admin/listener.ora.

Oracle Net Services Tracing Parameters

TRACE_LEVEL_[ CLIENT | SERVER | < listener_name > ]– This parameter is used to set the level of detail included in the trace file. The higher the level the greater the amount of data included in the trace. Keep in mind that trace files can grow extremely fast and can have impact on the performance of the system.

Valid values:
OFF (0) – no tracing
USER (4) – traces to identify user induced errors conditions.
ADMIN (6) – traces to identify installation specific problems.
SUPPORT (16) – provides trace information used by Oracle Support

Set in:
CLIENT- ORA_CLIENT_HOME/network/admin/sqlnet.ora
SERVER- ORACLE_HOME/network/admin/sqlnet.ora
< listener_name >- ORACLE_HOME/network/admin/listener.ora

TRACE_FILE_[ CLIENT | SERVER | < listener_name > ]– This parameter is used to set the name of the trace file. By default the trace file name is svr_pid.trc for SERVER trace files, sqlnet.trc for CLIENT trace files and listener.trc for listener trace files.

Set in:
CLIENT- ORA_CLIENT_HOME/network/admin/sqlnet.ora
SERVER- ORACLE_HOME/network/admin/sqlnet.ora
< listener_name >- ORACLE_HOME/network/admin/listener.ora

TRACE_DIRECTORY_[ CLIENT | SERVER | < listener_name > ]-This parameter is used to set the destination directory for the trace output. Default location is ORACLE_HOME/network/trace.

Set in:
CLIENT- ORA_CLIENT_HOME/network/admin/sqlnet.ora
SERVER- ORACLE_HOME/network/admin/sqlnet.ora
< listener_name >- ORACLE_HOME/network/admin/listener.ora

TRACE_UNIQUE_[ CLIENT | SERVER | < listener_name > ]– When set this parameter appends the process identifier to the name of the trace file generated. When not set data from a new trace session overwrites the existing file.

Valid values:
TRUE (ON) – appends the process id to the file name
FALSE (OFF) default – does not append the process id to the file name

Set in:
CLIENT- ORA_CLIENT_HOME/network/admin/sqlnet.ora
SERVER- ORACLE_HOME/network/admin/sqlnet.ora
< listener_name >- ORACLE_HOME/network/admin/listener.ora

TRACE_TIMESTAMP_[ CLIENT | SERVER | < listener_name > ]– This parameter adds a timestamp in the form of dd-mon-yyyy hh:mi:ss:mil for every event in the trace file.

Valid Values:
TRUE (ON) – appends a timestamp to each event in the trace file
FALSE (OFF) default – no timestamp is appended

Set in:
CLIENT- ORA_CLIENT_HOME/network/admin/sqlnet.ora
SERVER- ORACLE_HOME/network/admin/sqlnet.ora
< listener_name >- ORACLE_HOME/network/admin/listener.ora

TRACE_FILELEN_[ CLIENT | SERVER | < listener_name > ]– This parameter is used to set the size of trace file in bytes. Once the trace file reaches this size trace information is written to a new file.

Set in:
CLIENT- ORA_CLIENT_HOME/network/admin/sqlnet.ora
SERVER- ORACLE_HOME/network/admin/sqlnet.ora
< listener_name >- ORACLE_HOME/network/admin/listener.ora

TRACE_FILENO_[ CLIENT | SERVER | < listener_name > ]– This parameter set number of files to be used in conjunction with TRACE_FILELEN_[ CLIENT | SERVER | < listener_name > ]. Trace data is written to the files in a cyclical fashion. When the last file is filled the first file is overwritten.

Set in:
CLIENT- ORA_CLIENT_HOME/network/admin/sqlnet.ora
SERVER- ORACLE_HOME/network/admin/sqlnet.ora
< listener_name >- ORACLE_HOME/network/admin/listener.ora

LOG_FILE_[ CLIENT | SERVER | < listener_name > ]– This parameter sets the log file name.

Set in:
CLIENT- ORA_CLIENT_HOME/network/admin/sqlnet.ora
SERVER- ORACLE_HOME/network/admin/sqlnet.ora
< listener_name >- ORACLE_HOME/network/admin/listener.ora

LOG_DIRECTORY_[ CLIENT | SERVER | < listener_name > ]– This parameter set location in which log file are files are to be written.

Set in:
CLIENT- ORA_CLIENT_HOME/network/admin/sqlnet.ora
SERVER- ORACLE_HOME/network/admin/sqlnet.ora
< listener_name >- ORACLE_HOME/network/admin/listener.ora

LOGGING_LISTENER – This parameter enables or disables Listener logging.

Valid values:
ON – Listener logging enabled (default)
OFF – Listener logging disabled

Set in ORACLE_HOME/network/admin/listener.ora

Setting Client Tracing

Below is an example of setting up client side tracing. The goal is to trace client sessions at the USER level. Trace data is to be written to 6 files each with a size of 51200 bytes (50MB). Each trace event should have a times tamp. We also want a unique trace file per connection and all trace files and logs are to be written to the directory /home/oracle/client_trace. In order to meet the above stated goal the following parameters need to be set in the ORA_CLIENT_HOME/network/admin/sqlnet.ora file.

# sqlnet.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

TRACE_LEVEL_CLIENT=USER
TRACE_FILENO_CLIENT=6
TRACE_FILELEN_CLIENT=51200
TRACE_UNIQUE_CLIENT=ON
TRACE_TIMESTAMP_CLIENT=ON
TRACE_DIRECTORY_CLIENT=/home/oracle/client_trace
LOG_DIRECTORY_CLIENT=/home/oracle/client_trace
DIAG_ADR_ENABLED=OFF

ADR_BASE = /u01/app/oracle

Note the parameter DIAG_ADR_ENABLED. It is necessary to set this parameter to OFF for 11gR1 and higher in order for some of the parameters set above to be enabled.

With the above parameters set we next attempt a connection the database.

[oracle@ora2 client_trace]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 30 08:33:40 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SQL> connect hr@proddb
Enter password: 
Connected.
SQL> host
[oracle@ora2 client_trace]$ ls -al
total 16
drwxr-xr-x  2 oracle oinstall 4096 Jun 30 08:33 .
drwx------ 20 oracle oinstall 4096 Jun 30 08:33 ..
-rw-r-----  1 oracle oinstall 5879 Jun 30 08:33 cli1_30048.trc
-rw-r-----  1 oracle oinstall    0 Jun 30 08:33 cli2_30048.trc
-rw-r-----  1 oracle oinstall    0 Jun 30 08:33 cli3_30048.trc
-rw-r-----  1 oracle oinstall    0 Jun 30 08:33 cli4_30048.trc
-rw-r-----  1 oracle oinstall    0 Jun 30 08:33 cli5_30048.trc
-rw-r-----  1 oracle oinstall    0 Jun 30 08:33 cli6_30048.trc
[oracle@ora2 client_trace]$

Notice that 6 trace (trc) files were created but only one is not empty. This is due to the TRACE_FILENO_CLIENT parameter being set to 6. In the file containing the trace data you should see a section in the middle to end of the file that looks like the following after all of the configuration lines.

(3086878400) [000001 30-JUN-2010 08:33:47:768] niotns: Calling address: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=prod)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=proddb)(CID=(PROGRAM=sqlplus)(HOST=ora2.localdomain)(USER=oracle))))
(3086878400) [000001 30-JUN-2010 08:33:47:768] nscall: connecting...
(3086878400) [000001 30-JUN-2010 08:33:47:768] nsc2addr: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.130)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=proddb)(CID=(PROGRAM=sqlplus)(HOST=ora2.localdomain)(USER=oracle))))
(3086878400) [000001 30-JUN-2010 08:33:47:769] nttbnd2addr: using host IP address: 192.168.56.130
(3086878400) [000001 30-JUN-2010 08:33:47:769] nsopen: opening transport...
(3086878400) [000001 30-JUN-2010 08:33:47:770] nttcni: Tcp conn timeout = 60000 (ms)
(3086878400) [000001 30-JUN-2010 08:33:47:770] nttcni: trying to connect to socket 13.
(3086878400) [000001 30-JUN-2010 08:33:47:773] nttcni: connected on ipaddr 192.168.56.11
(3086878400) [000001 30-JUN-2010 08:33:47:773] nttcon: set TCP_NODELAY on 13
(3086878400) [000001 30-JUN-2010 08:33:47:773] nsopen: transport is open
(3086878400) [000001 30-JUN-2010 08:33:47:774] nsnainit: inf->nsinfflg[0]: 0x41 inf->nsinfflg[1]: 0x41
(3086878400) [000001 30-JUN-2010 08:33:47:774] nsopen: global context check-in (to slot 0) complete
(3086878400) [000001 30-JUN-2010 08:33:47:776] nscon: doing connect handshake...
(3086878400) [000001 30-JUN-2010 08:33:47:776] nscon: sending NSPTCN packet
(3086878400) [000001 30-JUN-2010 08:33:47:858] nscon: got NSPTRS packet
(3086878400) [000001 30-JUN-2010 08:33:47:859] nscon: sending NSPTCN packet
(3086878400) [000001 30-JUN-2010 08:33:47:861] nscon: got NSPTAC packet
(3086878400) [000001 30-JUN-2010 08:33:47:862] nscon: connect handshake is complete
(3086878400) [000001 30-JUN-2010 08:33:47:863] nscon: nsctxinf[0]=0x41, [1]=0x41
(3086878400) [000001 30-JUN-2010 08:33:47:863] nsnainconn: inf->nsinfflg[0]: 0x41 inf->nsinfflg[1]: 0x41
(3086878400) [000001 30-JUN-2010 08:33:47:864] nsnasend: bytes to send: 146
(3086878400) [000001 30-JUN-2010 08:33:47:865] nsdo: 146 bytes to NS buffer
(3086878400) [000001 30-JUN-2010 08:33:47:866] nsnareceive: buffer address: 0xbfe94ad8 bytes wanted: 2048
(3086878400) [000001 30-JUN-2010 08:33:47:866] nsnareceive: calling NS to receive 2048 bytes into address 0xbfe94ad8
(3086878400) [000001 30-JUN-2010 08:33:47:870] nsdo: 117 bytes from NS buffer
(3086878400) [000001 30-JUN-2010 08:33:47:870] nsnareceive: received 117 bytes
(3086878400) [000001 30-JUN-2010 08:33:47:870] nsnareceive: no more data to receive - returning
(3086878400) [000001 30-JUN-2010 08:33:47:870] nsnareceive: total bytes received: 117

The above trace segment shows the steps taken to successfully make a connection to the database. Had there been an error in the connection it would have been detailed in the trace and a SQLNET.LOG file would have been created with the error stack.

Setting Server and Listener Tracing

The setting for sever and listener tracing are nearly the same as for the client. Keep in mind that when server tracing is enabled all connections into the server will be traced. This could lead to problems on the database server due to the number and size of the trace files.

When tracing the listener the parameter to disable ADR is DIAG_ADR_ENABLED_< listener_name >. The listener will need to be reloaded or restarted after adding parameters to the LISTENER.ORA file.

Tracing can be used to aid in tracking down problems with Oracle Net Services.