Logging | Tracing   « Prev  Next »

Lesson 10

Logging, Tracing, and Troubleshooting Oracle Net Services Summary

This module started with a single idea: a one-line network error like ORA-12154 or ORA-12541 is only the top of a much longer story, and logging and tracing exist to show you the rest of it. Nine lessons later, that idea has been built out into a complete, practical skill set, from understanding what logging and tracing actually record, through configuring them on each Oracle Net Services component, to locating the resulting files and reading them, with or without specialized tools.

What follows is a synthesis of that material, organized around the themes that tie the nine lessons together rather than repeating each one lesson by lesson. Several of these lessons began life as much older course material, written when Net8 and SQL*Net were the current terminology and a level-16 trace, read by hand, was often the only diagnostic option available. Part of what this synthesis does is separate what has genuinely changed in the decades since from what, perhaps surprisingly, has not.

Foundations: Why Two Separate Facilities Exist

Lesson 1 and Lesson 2 established the vocabulary the rest of the module builds on. Logging is always-on, cheap, and answers a narrow question: did this connection succeed, and if not, which layer reported the failure? Tracing is off by default, expensive, and answers a much broader question: exactly what happened, step by step, during one specific transaction? Both ideas trace back to the Net8 and SQL*Net era in the original course material; the mechanics have barely changed since, even though the name has, Oracle Net Services is the current umbrella term, and current deployments more often reach for Easy Connect Plus or an OCI-native connection method than classic Net8-style naming. Lesson 1 also introduced the NI/NS/NT error stack, the layered detail behind every terse ORA- message, and Oracle's seven-row trace file naming table, confirmed unchanged from the Net8 era all the way through Oracle 26ai.

Configuration: The Same Four Places, Every Time

Lesson 3 and Lesson 4 showed that configuring logging and tracing comes down to the same small set of moves repeated across four configuration points: the database's DIAGNOSTIC_DEST and the Automatic Diagnostic Repository (ADR), listener.ora for the listener, sqlnet.ora on both the client and server, and cman.ora if Oracle Connection Manager sits in the connection path. Lesson 4 also traced the parameter history back further still, to the pre-ADR init.ora world of background_dump_dest and user_dump_dest. That file format is worth a clarification many students assume incorrectly: a classic init.ora text parameter file (a PFILE) is not deprecated. It remains fully supported; it is simply less convenient for ongoing administration than a server parameter file (SPFILE), which supports persistent ALTER SYSTEM changes without manual file editing. See Additional Database Parameters for that distinction in full.

Whichever parameter file style is in use, Oracle Database 26ai centralizes diagnostic output in ADR by default, and sqlnet.ora remains the one file that comes up constantly across this module, for client and server tracing, for logging, and for the connection timeout parameters that keep a stuck attempt from hanging indefinitely rather than failing fast enough to leave a useful log or trace entry; see Setting TNS Timeout Values for the outbound, inbound, send, and receive settings worth configuring alongside tracing itself.

Turning Tracing On, Deliberately and Temporarily

Lesson 5 and Lesson 6 turned configuration into practice: setting TRACE_LEVEL_CLIENT, TRACE_LEVEL_SERVER, or TRACE_LEVEL_LISTENER, reloading or restarting the affected component, reproducing the problem, and turning tracing back off once the diagnostic has what it needs. The same discipline applies to every component covered in this module, including dispatcher and shared server processes, which write their own trace files into the database's standard ADR trace directory rather than through listener.ora. A recurring practical point across both lessons: confirm a trace is actually being written, with lsnrctl status or adrci, before relying on it to catch an intermittent problem. Finding out after the fact that ADR quietly wrote somewhere other than the directory you were watching is a common, avoidable way to lose a diagnostic window.

Finding and Reading What Tracing Produces

Lesson 7 and Lesson 8 covered the other half of the problem: once a log or trace exists, where is it, and what does it actually tell you? V$DIAG_INFO is the starting point for a database instance's own ADR Home, trace directory, and alert log; V$DIAG_TRACE_FILE lists every trace file currently under that home; and adrci's SHOW BASE and SHOW HOMES commands locate the listener's and Oracle Connection Manager's own ADR homes, which are separate from the database's.

The single most useful modernization point in this half of the module is CONNECTION_ID. Starting with Oracle Database 21c, Oracle Net generates an actual connection identifier that is written into the logs and traces of every network element a connection passes through, client, Connection Manager, and server alike. On 21c and later, including 26ai, that one value lets you follow a single connection end to end by searching for it directly, rather than matching timestamps and connect data across three separate logs by eye, which is still the only option on releases before 21c.

When a Log Isn't Enough: Trace Assistant

Lesson 9 closed the module with the Trace Assistant (trcasst) utility, Oracle Net Services' tool for converting a dense, level-16 trace file into readable connectivity, Two-Task Common (TTC), error, and statistics sections. It is worth being precise here: trcasst remains a documented, current part of Oracle Net Services through Oracle 26ai. It has not been replaced or desupported; it simply solves a narrower, specific problem than the ADR-era tools covered in Lesson 8. adrci and Oracle Trace File Analyzer are the right tools for locating and browsing whatever ADR has already collected, which is how almost every environment actually runs by default. Trace Assistant is the right tool for the specific case where you have deliberately captured a level-16 trace with DIAG_ADR_ENABLED=OFF, because you need packet-and-TTC-level detail that ADR's own browsing commands do not format for you. As Lesson 9 put it plainly, most troubleshooting never reaches that second case at all; reading a raw trace file, even with Trace Assistant's help, stays a comparatively rare activity even for an experienced DBA.

What Changed Since the Net8 Era, and What Didn't

It is worth being explicit about the dividing line this module kept drawing, because it is easy to assume either that nothing has changed or that everything has.

What changed: diagnostic output now defaults to the Automatic Diagnostic Repository rather than a flat directory you had to remember or configure by hand; a connection that hops through Oracle Connection Manager can now be followed end to end with a single CONNECTION_ID value instead of matched by timestamp, a capability that did not exist before Oracle Database 21c; TLS 1.3, introduced in Oracle Database 23ai, is now the default negotiated protocol version between current clients and servers; and Easy Connect Plus has made file-based naming optional for many common connection scenarios, which Lesson 5 and Lesson 8 both touched on, in place of or alongside classic Net8-style tnsnames.ora configuration.

What did not change: the four-level trace scale (off, user, admin, support), the parameter naming pattern across sqlnet.ora, listener.ora, and cman.ora, the seven-row trace file naming table from Lesson 1, and the Trace Assistant utility itself, all remain in place, documented, and functionally the same as they were described in material written for a much older release. A DBA who learned this material from a Net8-era textbook and has not touched Oracle Net Services since would recognize almost everything in this module except where the output now lives by default and the existence of CONNECTION_ID.

The One Number Worth Memorizing Differently

If this module has a single recurring trap, it is the admin trace level's numeric value. Across listener.ora and sqlnet.ora, off, user, admin, and support map to 0, 4, 6, and 16. Oracle Connection Manager is the one exception: in cman.ora, admin maps to 10, not 6. Every other parameter name, trace level keyword, and file-naming convention in this module has stayed essentially stable since the Net8 era; this is the one place a value genuinely differs by component, and it is worth double-checking explicitly rather than assuming a single number applies everywhere.

A Practical Checklist for the Next Connection Problem

None of the nine lessons in this module are especially complicated on their own; what makes Oracle Net Services troubleshooting feel hard in practice is usually not knowing which of the nine applies to the problem in front of you. Pulling the module together into a single sequence is meant to solve exactly that:
  1. Get the exact error number from the end user, and look it up with oerr if its meaning is not already obvious.
  2. Confirm basic reachability with tnsping, and check the listener's status with lsnrctl status and lsnrctl services.
  3. Read the relevant log first, listener, client, or server, and use CONNECTION_ID on 21c and later to follow one specific connection across multiple components.
  4. If the log's error stack does not explain the problem, raise tracing on the single component closest to the suspected failure, reproduce the problem, and confirm the trace actually captured something before reading further.
  5. For a level-16, non-ADR trace, use Trace Assistant, starting with -s for a quick statistical overview, then -la and -li to narrow in on one connection.
  6. Turn tracing back off once the cause is identified, and keep the change small, documented, and easily reversible, the same configuration-management discipline this module has applied to every component from the first lesson to the last.

Lessons in This Module


Logging and tracing are not tools you reach for every day, and this module has said so more than once, in Lesson 2's observation that most DBAs go weeks between opening a trace file, and again in Lesson 9's closing note that reading a raw trace stays rare even for an experienced DBA. That infrequency is exactly why the material tends to get rusty, or get learned once from outdated documentation and never revisited. But when a connection problem does resist the obvious fixes, tnsping and a listener status check, the skills covered here, reading an error stack, locating the right ADR home, correlating a connection across components with CONNECTION_ID, and formatting a trace when nothing else will do, are what separate a quick diagnosis from hours of guessing at an unfamiliar error code. The next module moves on from logging and tracing to the next stage of Oracle network configuration management.

Oracle Logging Tracing - Quiz

Before moving on to the next module, click the Quiz link to check your mastery of logging and tracing.
Logging Tracing - Quiz
SEMrush Software 10 SEMrush Banner 10