Note that some of the material on this page was covereed by Metalink Note 111916.1 SQLNET.ORA Logging and Tracing parameters. However at the time of writing, several of the parameters described below were missing from the note. There is more information in the documentation on this particular topic.
Oracle Net logging and trace are configured in the sqlnet.ora
On Unix, Oracle checks for sqlnet.ora in $ORACLE_HOME/network/admin unless this location has been overridden using the $TNS_ADMIN environment variable.
You can enable logging, trace or both on both the client and server using sqlnet.ora
| Logging |
| Trace |
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
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:
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.