SQL*NET Logging and tracing

Client server connection issues can be diagnosed by checking the log files at server and client level. Further tracing can be enabled to drill down the problems. All the client and server level logging/tracing parameters are defined in sqlnet.ora at server and client end.

Network Tracing

Tracing can be activated at different level depending upon the requirements.

Tracing Level	Meaning	Comments
0	OFF	Tracing is off
4	USER	USER level tracing used to diagnose user level issues.
6	ADMIN	ADMIN  level tracing used to diagnose installation issues.
16	SUPPORT	Used to collect Oracle Support specific information.

Enable Logging at Server Level

To enable logging at the database server level, we need to set the following parameters

1) LOG_DIRECTORY_SERVER = <PATH for log directory> . Default is $ORACLE_HOME/network/log

3) LOG_FILE_SERVER = <Log file name>. Default is sqlnet.log

Enable Tracing at Server Level

To enable tracing at the database server level, we need to set the following parameters.

1) TRACE_DIRECTORY_SERVER = <PATH for trace directory> . Default is $ORACLE_HOME/network/trace

2) TRACE_FILE_SERVER = <PATH for trace directory> . Default is svr_<pid>.trc

3) TRACE_LEVEL_SERVER = {OFF|USER|ADMIN|SUPPORT}. Default is OFF.

Enable Logging at client Level

1) LOG_DIRECTORY_CLIENT = <PATH for log directory> . Default is current working directory from where the connection is made with the server

2) LOG_FILE_CLIENT = <Log file name>. Default is sqlnet.log

Enable tracing at client Level

1) TRACE_DIRECTORY_CLIENT = <PATH for log directory> . Default is current working directory from where the connection is made with the server

2) TRACE_FILE_CLIENT = <Log file name>. Default is cli_<pid>.trc

3) TRACE_LEVEL_CLIENT = {OFF|USER|ADMIN|SUPPORT}. Default is OFF.

Apart from these parameters, we can also use these additional parameters

TRACE_TIMESTAMP_SERVER=ON – To add timestamps in trace files of server level tracing

TRACE_TIMESTAMP_CLIENT=ON - To add timestamps in trace files of client level tracing

Note:- The listener has to be bounced using START and STOP commands after making any changes in sqlnet.ora file on database server. The RELOAD command doesn’t read the changed sqlnet.ora.

Listener tracing

Tracing parameters at listener level can be defined in listener.ora of database server.

1) TRACE_DIRECTORY_LISTENER = <PATH for log directory> . Default is current working directory from where the connection is made with the server

2) TRACE_LEVEL_LISTENER = {OFF|USER|ADMIN|SUPPORT}. Default is OFF.

We can use SET and SHOW command on LSNRCTL utility to set the check the values of listener parameters.