Enable Tracing for Oracle Net Components

You can enable tracing at several levels in Oracle Net Services, depending on what you are trying to troubleshoot. This lesson walks through the common tracing targets, when to use each one, and the mechanics of turning tracing on and back off again.

The underlying idea is the same across every component: pick the piece of the connection path you suspect is involved, invoke tracing just for that piece, reproduce the problem, and then turn the trace back off. Tracing every component at once, "just in case," mostly produces a large pile of output with nothing obviously useful in it; tracing the one component closest to where the symptom shows up is what actually narrows down a cause.

Listener tracing. Use this to diagnose problems with the listener itself: it fails to start, clients cannot connect to it, or service registration is not working. Enable it by setting TRACE_LEVEL_LISTENER as a top-level parameter in listener.ora, alongside the listener's own address description, not nested inside it:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
    )
  )

TRACE_LEVEL_LISTENER = SUPPORT

As with any listener parameter, lsnrctl help (or lsnrctl help trace) always reflects the exact syntax your installed release supports, and the general recommendation is to keep this parameter minimal day to day and lean on ADR's own diagnostics first, raising it only for a specific troubleshooting session; see the LSNRCTL Help Utility. You can also set the same trace level at runtime, without editing the file, from inside lsnrctl itself with set trc_level support, which is often faster for a one-off diagnostic session.

Client and server (TNS) tracing. Use this to capture the communication between a client and the server, for diagnosing connection-establishment problems (such as TNS-12541 or ORA-12154), network latency, and authentication errors. Enable it with TRACE_LEVEL_CLIENT in the client's sqlnet.ora, and TRACE_LEVEL_SERVER in the server's sqlnet.ora:

TRACE_LEVEL_CLIENT = SUPPORT

While you are in sqlnet.ora enabling tracing, it is also worth setting sane connection timeouts, so a stuck connection attempt fails fast enough to leave a useful trace entry rather than hanging indefinitely; see Setting TNS Timeout Values for the outbound, inbound, send, and receive timeout parameters.

Shared server and dispatcher tracing. Use this to troubleshoot dispatcher or shared server process problems: poor performance over shared server connections, dispatcher errors, or shared server process creation failures. Dispatchers and shared servers are background processes of the database instance itself, not of the listener, so they are not controlled through listener.ora, and they have nothing to do with the DBMS_SHARED_POOL package, which manages the shared pool's memory, an unrelated area. Each dispatcher and shared server process writes its own trace file into the database's standard ADR trace directory, using the same background-process naming convention as any other instance process, typically <SID>_d000_<pid>.trc for a dispatcher and <SID>_s000_<pid>.trc for a shared server process. Diagnosing a specific session routed through a shared server is usually done with DBMS_MONITOR session tracing rather than a blanket dispatcher-level setting.

Do not confuse shared server tracing with the SID_LIST_LISTENER section of listener.ora, which is a separate topic: static service registration. Most database services register themselves with the listener dynamically through PMON and need no entry in SID_LIST_LISTENER at all. A static entry is still required for services that cannot register themselves this way, the classic example being an external procedure agent:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/26.1.0/dbhome_1)
      (PROGRAM = extproc)
    )
  )

If you do need a TRACE_LEVEL_LISTENER setting while working with a listener that has a SID_LIST_LISTENER section, it still goes at the top level of listener.ora as shown earlier, not nested inside the SID_DESC entry. More broadly, prefer dynamic service registration over static SID_LIST_LISTENER entries wherever possible, and keep a static entry only for the specific services that genuinely require one; see How LSNRCTL and listener.ora Interact for more on how static and dynamic registration relate to each other.

Setting a trace level and confirming it is actually running are two different steps. For the listener, lsnrctl status reports the current trace level directly, so a quick status check confirms the change was picked up without needing to inspect any files. For client or server tracing under ADR, which is the default, use adrci rather than guessing at a directory:

adrci> show base -product client
adrci> set base -product client
adrci> show trace app_client.trc

If this returns nothing after you have reproduced the problem, the trace level most likely was not applied the way you expected, rather than nothing having happened on the network. Checking this before you spend time reproducing an intermittent problem saves you from doing it two or three times over for nothing.

Suppose a client application occasionally fails to connect with ORA-12170, a connect timeout, but only to one particular database service and only under load. The listener's own log shows nothing unusual for that service, which points away from the listener itself and toward the client side, the network path, or how the destination handles the request once it arrives.

Start with client tracing rather than listener or shared server tracing, since the symptom is specific to one client application: set TRACE_LEVEL_CLIENT = SUPPORT in that application's sqlnet.ora, confirm with adrci that output is being written, and reproduce the problem under load. If the trace shows the client waiting well past its configured outbound connect timeout with no response, the next question is whether the destination ever saw the request at all. That points toward either the listener's own log, to confirm whether a connection attempt arrived and was queued, or, if the service runs over Oracle Connection Manager, CMAN's own trace for the same window.

Once the point of delay is identified, whether that turns out to be the listener queuing requests under load, a shared server pool running out of available handlers, or something external to Oracle Net entirely, turn the client trace back off. A single, well-targeted trace comparing the client's view against the component on the other side of the suspected delay is usually enough; there is rarely a need to trace every component at once.

• Trace file location. Trace files are generated under diag/tnslsnr/<hostname>/listener/trace or diag/rdbms/<dbname>/<instance>/trace within the Automatic Diagnostic Repository (ADR).
• Performance impact. Tracing has a real performance cost, so use it judiciously and disable it as soon as you are done troubleshooting.
• Security. Trace files can contain sensitive information, including parts of connect strings and, in some configurations, credentials, so handle and store them the same way you would any other sensitive diagnostic output.
• Analyzing trace output. trcasst (Trace Assistant) was the classic tool for formatting raw Oracle Net trace files into a readable report; it has been desupported in current releases. tkprof is a different tool entirely: it formats SQL trace (event 10046) output for performance analysis and does not read Oracle Net trace files. For a current Oracle Net trace, plan on reading the trace file directly or working with Oracle Support, rather than reaching for tkprof.

None of this is specific to one Oracle release. The parameter names, the four trace levels, and the file-naming conventions behind them have been stable since the Net8 era; what has changed is where the output ends up by default, under ADR rather than a flat directory you have to remember, and how you confirm it is actually there.

The steps used to invoke tracing are the same regardless of which component you are tracing:

1. Choose the component to trace: client, server, listener, or Oracle Connection Manager.
2. Edit that component's configuration file: listener.ora, sqlnet.ora, or cman.ora. (names.ora applied only to the long-desupported Oracle Names method and has no current equivalent.)
3. Restart or reload the component. If the configuration file is modified while the component is already running, the new trace settings take effect the next time the component is invoked, reloaded, or restarted, depending on the component.
4. Turn tracing back off once you no longer need it for that specific diagnostic session.

The listener and Oracle Connection Manager both support a reload rather than a full restart, through lsnrctl reload and the equivalent cmctl command, so a trace-level change rarely needs to take an active listener or CMAN instance offline. The client and server read sqlnet.ora at connect time, so a new client-side trace setting applies to the next connection made, with no reload step at all.

Each component recognizes four trace levels, not three:

TRACE_LEVEL_CLIENT = off

off is the default: no trace output is generated.

TRACE_LEVEL_CLIENT = user

user is intended for DBAs without deep network expertise. It generates plain-language descriptions of high-level, significant networking events.

TRACE_LEVEL_CLIENT = admin

admin is intended for network administrators who understand network terms and concepts. It includes everything the user level reports, plus additional detail such as TNS addresses and configuration-level events. For the client, server, and listener, admin corresponds to numeric level 6; Oracle Connection Manager is the one component where admin corresponds to 10 instead.

TRACE_LEVEL_CLIENT = support

support is the most detailed level, intended for use with Oracle Support Services, and corresponds to numeric level 16.

Trace files at the user and support levels can be very large. Set this parameter only for a specific diagnostic reason, and delete the resulting files once they are no longer useful, so they do not consume disk space indefinitely.

Note: If a trace file from a previous session still holds information you need, rename it before starting a new trace; otherwise Oracle will overwrite it.

If you are having trouble pinging a server and need a detailed trace of the attempt itself, add the following parameters to sqlnet.ora to trace tnsping specifically:

TNSPING.TRACE_LEVEL
TNSPING.TRACE_DIRECTORY

These apply only to the tnsping utility's own diagnostic output, separate from client or server connection tracing.

A short recap of what invoking tracing actually involves:

• Pick the component closest to where the symptom shows up, listener, client, server, Oracle Connection Manager, or a shared server process, rather than tracing everything at once.
• Set the matching TRACE_LEVEL_* parameter in that component's own configuration file, as a top-level parameter, not nested inside an address or SID description.
• Reload or restart the component so the new setting takes effect, or set it at runtime through lsnrctl or cmctl for a one-off session.
• Confirm the trace is actually being written, with lsnrctl status or adrci, before relying on it to catch an intermittent problem.
• Turn tracing back off once you have what you need, and clean up any trace files you no longer need to keep.

The next lesson discusses the location of the log and trace files on an Oracle server.