Please see my other blog for Oracle EBusiness Suite Posts - EBMentors

Search This Blog

Note: All the posts are based on practical approach avoiding lengthy theory. All have been tested on some development servers. Please don’t test any post on production servers until you are sure.

Monday, December 20, 2010

SQLNet Logging/Tracing

Net Logging and Trace
Oracle Net logging and trace are configured in the sqlnet.ora typically found at ORACLE_HOME\network\admin

Logging

The following parameters can be set to configure Oracle Net logging in sqlnet.ora:

Parameter Description
LOG_DIRECTORY_CLIENT Specifies the directory for the client log file
LOG_FILE_CLIENT Specifies the name of the client log file
LOG_DIRECTORY_SERVER Specifies the directory for the server log file
LOG_FILE_SERVER Specifies the name of the server log file
By default both the client and server log file names default to sqlnet.log

Trace

The following parameters can be set to configure Oracle Net logging in sqlnet.ora:
Parameter Description
TRACE_DIRECTORY_CLIENT Specifies the directory for the client trace file
TRACE_FILE_CLIENT Specifies the name of the client trace file
TRACE_DIRECTORY_SERVER Specifies the directory for the server trace file
TRACE_FILE_SERVER Specifies the name of the server trace file
TRACE_FILELEN_CLIENT Specifies the size of each client trace file in kilobytes
TRACE_FILENO_CLIENT Specifies the number of client trace files
TRACE_FILELEN_SERVER Specifies the size of each server trace file in kilobytes
TRACE_FILENO_SERVER Specifies the number of server trace files
TRACE_LEVEL_CLIENT Specifies the level of detail for client trace
TRACE_LEVEL_SERVER Specifies the level of detail for server trace
TRACE_TIMESTAMP_CLIENT Includes a timestamp (to microseconds) for each event in the client trace
TRACE_TIMESTAMP_SERVER Includes a timestamp (to microseconds) for each event in the client trace
TRACE_UNIQUE_CLIENT Creates an individual client trace file for each process
For both the client and server trace files, the default directory is $ORACLE_HOME/network/trace.
For the client, the default trace file name is sqlnet.trc; for the server the default trace file name is svr_pid.trc

When both TRACE_FILELEN_CLIENT and TRACE_FILENO_CLIENT are set to non-zero values, the trace files are used cyclically. When one file is full, output continues in the next file; when all files are full output continues in the first file. A sequence number is included in the file name. For example if TRACE_FILE_CLIENT is client and TRACE_FILENO_CLIENT is 5 then the files will be:
client1_pid.trc
client2_pid.trc
client3_pid.trc
client4_pid.trc
client5_pid.trc
TRACE_FILELEN_SERVER and TRACE_FILENO_SERVER work in a similar way to TRACE_FILELEN_CLIENT and TRACE_FILENO_CLIENT.
For both TRACE_LEVEL_CLIENT and TRACE_LEVEL_SERVER, the parameter can take a numeric value between 0 and 16 where 0 is disabled and 16 is the most detailed. Alternatively these parameters can also take a scalar value was follows:
OFF 0 No tracing
USER 4 Include user errors
ADMIN 6 Include administrative errors
SUPPORT 16 Include packet contents
Level 16 (SUPPORT) is the most detailed trace level. Take care when enabling this level of detail as it will consume disk space very rapidly. Consider using the TRACE_FILELEN_SERVER and TRACE_FILENO_SERVER parameters to reduce the impact on the server If TRACE_UNIQUE_CLIENT is set to ON then a separate trace file will be created for each client. The pid is appended to the file name e.g. client_123.trc. Note that this appears to be the default behaviour in recent versions

Example:  on one of our server following for client side tracing

###################TRACING/LOGGING#########################
LOG_DIRECTORY_CLIENT=D:\Oracle\product\10.1.0\Client_1\networks\Log
LOG_FILE_CLIENT=sqlnet_log
TRACE_LEVEL_CLIENT = SUPPORT
TRACE_UNIQUE_CLIENT = on
TRACE_DIRECTORY_CLIENT = D:\Oracle\product\10.1.0\Client_1\network\trace
TRACE_FILE_CLIENT = CLIENT_4_24
TRACE_TIMESTAMP_ CLIENT = ON
###################################################
after the above mentioned change start sqlplus and observe that there are .trc files in the specified trace folder for analysis and investigation.

No comments: