How to enable Oracle SQL*Net Client, Server and Listener Tracing

To enable tracing make sure TNS_ADMIN environment variable should be set and add below tracing parameters in sqlnet.ora. Pls note that any trace settings apply to all server processes so you need to careful when making changes on Prod, I would always suggest to test before applying changes on PRD.
Enable SQL*Net server and Client side tracing.
TRACE_LEVEL_CLIENT = SUPPORT
TRACE_UNIQUE_CLIENT = on
TRACE_LEVEL_SERVER = SUPPORT
TRACE_DIRECTORY_CLIENT = \tmp
TRACE_FILE_CLIENT = Client_Trace.log
TRACE_DIRECTORY_SERVER = \tmp
TRACE_FILE_SERVER = Server_Trace.log
TRACE_TIMESTAMP_ CLIENT = ON
DIAG_ADR_ENABLED= OFF

Starting from 11g and higher to enable client, server trace you need to add DIAG_ADR_ENABLED= OFF

SQL*Net Listener tracing
TRACE_FILE_LISTENER = Listener_Trace.log
TRACE_DIRECTORY_LISTENER = \tmp
TRACE_LEVEL_LISTENER = SUPPORT
DIAG_ADR_ENABLED_LISTENER_<LISTENER> = OFF

To start Listener Trace you need to set DIAG_ADR_ENABLED_<listener name> = OFF (must set to OFF)

To enable Tnsping Trace
TNSPING.TRACE_LEVEL = SUPPORT
TNSPING.TRACE_DIRECTORY = <Trace file path>

Enabling Dynamic Listener Tracing
LSNRCTL> set current_listener listener_ORCL
Current Listener is listener_ORCL
LSNRCTL> set trc_level 16
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
LISTENER parameter "trc_level" set to support
The command completed successfully
LSNRCTL> set trc_directory /oracle/diag/tnslsnr/host1/listener_orcl/trace
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
LISTENER parameter "trc_directory" set to /oracle/diag/tnslsnr/host1/listener_orcl/trace

Turn off dynamic tracing using:
LSNRCTL>set current_listener listener
LSNRCTL>set trc_level OFF


Thanks

Satishbabu Gunukula, Oracle ACE

http://www.oracleracexpert.com