Listener and sqlnet trace

Listener and sqlnet trace

Posted by decipherinfosys on June 5, 2007

At times, when troubleshooting connectivity issues or performance issues, you might need to run listener trace (server side) and/or a sqlnet trace (client side trace).  Oracle support staff will also ask for these files when you enlist their help in troubleshooting these issues.  Here are the steps on how you can do that:

To enable sqlnet trace (client side trace):

Add the following to the sqlnet.ora file on the application server(s) from where the connections are being made to the Oracle database:

Trace_level_client=16
Trace_directory_client=<path_to_the_trace_directory> # use the full path to the trace directory
Trace_unique_client=on
Trace_timestamp_client=on

To enable listener trace (server side):

Add the following to the listener.ora file on the db server before starting the listener:

TRACE_TIMESTAMP_<listener_name>=on
TRACE_DIRECTORY_< listener_name>=<path_to_the_trace_directory>

Here <listener_name> is the name of the listener. Then run lsnrctl to turn on the listener trace:

lsnrctl> set current_listener <listener_name>
lsnrctl> set trc_level support

You can run both trace at the same time for 15 minutes or so in order to get some meaningful data set.

To stop the sqlnet trace:

Remove the following entries from sqlnet.ora or comment them out by using the # sign:

Trace_level_client=16
Trace_directory_client=<path_to_the_trace_directory> # use the full path to the trace directory
Trace_unique_client=on
Trace_timestamp_client=on

To stop the listener trace:

lsnrctl> set trc_level off

For getting more information on the trace levels and the different parameters for these traces, please refer metalink or otn.