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

2 thoughts on “Enabling SQL*Net Tracing with Oracle Instant Client

  1. Pingback: Intermittent Slow Oracle Net from Windows 7/Vista/2008 machines « Oleksandr Denysenko's Blog

  2. Pingback: Sqlnet trace | My2shoppe

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s