Enabling SQL*Net Tracing with Oracle Instant Client

Just a quick note for myself about Enabling SQL*Net Tracing with Oracle Instant Client.
There are absolutely nothing special for Instant Client, except the note that clients starting with version 11.1 use Automatic Diagnostic Repository (ADR) for storing log and trace data, so for tracing clients up to 10.2 we use next parameters in SQLNET.ORA:

TRACE_LEVEL_CLIENT=(OFF | USER | ADMIN | SUPPORT)
TRACE_DIRECTORY_CLIENT=D:\ORACLE\PRODUCT ($ORACLE_HOME/network/trace | %ORACLE_HOME%\network\trace)
TRACE_FILE_CLIENT=client_trace.trc (sqlnet.trc)
TRACE_TIMESTAMP_CLIENT=(ON | true | off | false)
TRACE_UNIQUE_CLIENT=(ON | true | off | false)

TRACE_FILELEN_CLIENT=max size of the client trace files in kilobytes
TRACE_FILENO_CLIENT=1

Starting from Oracle version 11.1 there are two possible destinations for trace and log files: Automatic Diagnostic Repository (ADR) and non-ADR(old pre 11.1-style).
For ADR-destination we specify:

TRACE_LEVEL_CLIENT=(OFF | USER | ADMIN | SUPPORT)
DIAG_ADR_ENABLED=ON
ADR_BASE=D:\ORACLE\LOG

(default for ?nux:  ~/oradiag_username/clients/user_username/host*/trace

for windows %systemdrive%\Documents and Settings\%username%\oracle\oradiag_%username%\diag\clients\user_%username%\host*\trace)
TRACE_TIMESTAMP_CLIENT=(ON | true | off | false)

For non-ADR(old style – pre 11.1) destination we specify:

TRACE_LEVEL_CLIENT=(OFF | USER | ADMIN | SUPPORT)
DIAG_ADR_ENABLED=OFF
TRACE_DIRECTORY_CLIENT=D:\ORACLE\PRODUCT
TRACE_FILE_CLIENT=client_trace.trc (sqlnet.trc)
TRACE_UNIQUE_CLIENT=(ON | true | off | false)
TRACE_TIMESTAMP_CLIENT=(ON | true | off | false)

TRACE_FILELEN_CLIENT=max size of the client trace files in kilobytes
TRACE_FILENO_CLIENT=1

PS: for Oracle Instant Client preferred locations for SQLNET.ORA are:

  • current directory
  • location specified by TNS_ADMIN variable

References:

  • 454927.1 Using and Disabling the Automatic Diagnostic Repository (ADR) with Oracle Net for 11g
  • 219968.1 SQL*Net & Oracle Net Services – Tracing and Logging at a Glance
  • 834822.1 Oracle Net Diagnostics
  • 1076022.1 Examples of Troubleshooting Slow Oracle Net Connections
Advertisements