| Lesson 2 | An overview of log and trace files |
| Objective | Compare log files and trace files. |
Overview of Log and Trace Files
Let us compare log files with trace files so you fully understand their uses and differences. A client-side error is one line on the screen. The log file behind it holds a multi-layer error stack, the kind introduced in the previous lesson. The trace file, when one exists, holds the packet-by-packet exchange that produced that stack.
When logging is enabled, which it is by default, errors encountered in Oracle Net Services are written to a log file. The log file provides additional information for the DBA when the error message on the screen is inadequate to fully troubleshoot the failure. By recording the "error stack," the log file shows the state of the software at various layers, information that gives you the clues needed to solve the problem.
When logging is enabled, which it is by default, errors encountered in Oracle Net Services are written to a log file. The log file provides additional information for the DBA when the error message on the screen is inadequate to fully troubleshoot the failure. By recording the "error stack," the log file shows the state of the software at various layers, information that gives you the clues needed to solve the problem.
Why Oracle Net Keeps Logging and Tracing Separate
It is worth asking why Oracle Net bothers to maintain two distinct diagnostic facilities instead of one. The short answer is that logging and tracing solve different problems and carry very different costs, and collapsing them into a single always-on, maximum-detail facility would be worse at both jobs.
Logging exists to answer a narrow, recurring question cheaply: did this connection attempt succeed, and if not, which layer reported the failure and with what error code? That question comes up constantly, on every connection attempt a production system makes, so the facility that answers it has to be lightweight enough to run all the time without anyone noticing the overhead. Tracing exists to answer a much broader, open-ended question: exactly what happened, step by step, during this one specific transaction? That question only comes up when something has already gone wrong and the log's summary is not specific enough, so the facility that answers it is allowed to be expensive, because it only runs occasionally and only for as long as it takes to catch the problem in the act.
It helps to think of the relationship as a funnel rather than two unrelated tools. Logging is the wide end: it watches every connection attempt, all the time, and catches the great majority of problems with a single, cheap error code. Tracing is the narrow end: it exists for the minority of cases that make it through the log without a clear explanation, and it trades that cheapness for the detail needed to resolve them. Most DBAs go weeks between needing to open a trace file at all; almost none go a single day without an active, continuously written log.
Logging exists to answer a narrow, recurring question cheaply: did this connection attempt succeed, and if not, which layer reported the failure and with what error code? That question comes up constantly, on every connection attempt a production system makes, so the facility that answers it has to be lightweight enough to run all the time without anyone noticing the overhead. Tracing exists to answer a much broader, open-ended question: exactly what happened, step by step, during this one specific transaction? That question only comes up when something has already gone wrong and the log's summary is not specific enough, so the facility that answers it is allowed to be expensive, because it only runs occasionally and only for as long as it takes to catch the problem in the act.
It helps to think of the relationship as a funnel rather than two unrelated tools. Logging is the wide end: it watches every connection attempt, all the time, and catches the great majority of problems with a single, cheap error code. Tracing is the narrow end: it exists for the minority of cases that make it through the log without a clear explanation, and it trades that cheapness for the detail needed to resolve them. Most DBAs go weeks between needing to open a trace file at all; almost none go a single day without an active, continuously written log.
Oracle Net Tracing
The trace facility lets the DBA obtain far more information about the internal operations of an Oracle Net transaction than the log files alone provide. Tracing an operation produces a detailed, sequential record of events as they are executed. The DBA invokes the trace facility during or after an abnormal condition, specifically when the log file does not provide a clear enough indication of the cause.
This is a meaningful modernization from the Net8 and SQL*Net-era guidance this lesson was originally built around. Both of those names describe the same underlying facility; Oracle Net Services is simply the current umbrella term, and the mechanics of logging and tracing covered in this lesson apply unchanged regardless of which era's terminology a given document uses. Rather than relying solely on classic tracing to diagnose connectivity problems after the fact, current Oracle Net deployments often avoid a whole class of those problems up front by using Easy Connect Plus or an OCI-native connection method instead of legacy naming styles; see Oracle 23ai OCI Service Connection Methods for the current approach.
Note: Oracle tracing is an expensive operation. It requires a significant amount of disk space, since every detail of the transaction is written to the trace file, and it adds a real burden to the component doing the writing; database and network performance can suffer while a detailed trace is being captured. The prudent DBA uses tracing only as a last resort, at the lowest level that reveals the problem, and only for as long as it takes to reproduce the issue.
This is a meaningful modernization from the Net8 and SQL*Net-era guidance this lesson was originally built around. Both of those names describe the same underlying facility; Oracle Net Services is simply the current umbrella term, and the mechanics of logging and tracing covered in this lesson apply unchanged regardless of which era's terminology a given document uses. Rather than relying solely on classic tracing to diagnose connectivity problems after the fact, current Oracle Net deployments often avoid a whole class of those problems up front by using Easy Connect Plus or an OCI-native connection method instead of legacy naming styles; see Oracle 23ai OCI Service Connection Methods for the current approach.
Note: Oracle tracing is an expensive operation. It requires a significant amount of disk space, since every detail of the transaction is written to the trace file, and it adds a real burden to the component doing the writing; database and network performance can suffer while a detailed trace is being captured. The prudent DBA uses tracing only as a last resort, at the lowest level that reveals the problem, and only for as long as it takes to reproduce the issue.
Enabling and Disabling Each Facility
Because logging and tracing are controlled independently, you can leave one on and the other off, which is in fact the normal running state for a production system: logging on, tracing off. The listener, for example, can have its logging disabled entirely with the
To investigate a specific problem, you raise the listener's trace level, reproduce the issue, and then set it back:
The same pattern applies to
Setting
LOGGING_LISTENER parameter while its trace level stays at the default of off:
LOGGING_LISTENER = ON
TRACE_LEVEL_LISTENER = OFF
To investigate a specific problem, you raise the listener's trace level, reproduce the issue, and then set it back:
TRACE_LEVEL_LISTENER = ADMIN
The same pattern applies to
sqlnet.ora and cman.ora: logging stays on by default through ADR or the classic log file parameters, while each component's trace level starts at off and is raised only when you are actively diagnosing something. Oracle Connection Manager uses its own parameter names for the same idea, LOG_LEVEL and TRACING rather than a level-based trace parameter, along with LOG_DIRECTORY for where the resulting cman_pid.log is written:
LOG_LEVEL = SUPPORT
LOG_DIRECTORY = /oracle/network/log
TRACING = NO
Setting
TRACING = YES only while reproducing a problem, then switching it back to NO, keeps Oracle Connection Manager's overhead in line with the same enable-reproduce-disable pattern used for the listener and the client and server.
Logging vs. Tracing at a Glance
While logging reveals the state of the Oracle Net components at the moment of an error, tracing provides a description of all events as they occur and captures the state of things immediately before an error happens. In short, trace files complement the log files: they add detail the log cannot capture about each Oracle Net transaction.
The "default state" row is worth pausing on. Logging stays on continuously because its overhead is small and its output is compact: a few lines per connection attempt. Tracing stays off by default for the opposite reason: a
The previous lesson's Table 7-1 covered each component's default trace file name. The classic, non-ADR log file names follow a similar pattern and are worth knowing alongside it:
| Log Files | Trace Files | |
|---|---|---|
| Purpose | Error stacks, audit events, service registration, control commands | Packet flow and internal calls between Oracle Net layers |
| Default state | On (ADR log.xml, or classic listener.log / sqlnet.log) | Off |
| Data volume | Low | High, especially at the support level |
| Where configured | Logging parameters in sqlnet.ora, listener.ora, cman.ora | Trace parameters in the same three files, using named levels off, user (4), and support (16); admin is 6 for the listener and for the client/server, but 10 for Oracle Connection Manager |
| Typical file names | listener.log, sqlnet.log, ADR log.xml | listener.trc, sqlnet.trc, svr_pid.trc |
| When to use | First look at a TNS or connection error | When the log's error stack is not enough to show which layer or packet actually failed |
The "default state" row is worth pausing on. Logging stays on continuously because its overhead is small and its output is compact: a few lines per connection attempt. Tracing stays off by default for the opposite reason: a
support-level trace can log every internal call a component makes, which is invaluable when you are chasing a specific failure and wasted disk space and I/O when you are not. That asymmetry is exactly why the normal workflow is to read the log first and reach for tracing only when the log's error stack does not tell you enough on its own.
The previous lesson's Table 7-1 covered each component's default trace file name. The classic, non-ADR log file names follow a similar pattern and are worth knowing alongside it:
| Log File | Component |
|---|---|
cman_pid.log | Oracle Connection Manager |
listener.log | Listener |
sqlnet.log | Client and database server |
Reading a Log Entry Versus Reading a Trace Entry
It helps to see what each one actually buys you. Suppose a client reports ORA-12541, "TNS:no listener." The client's
A trace, by contrast, shows the socket-level outcome of the attempt: whether the connection was actively refused (the listener process exists but nothing is bound to that port) or whether it simply timed out with no response at all (more consistent with a firewall silently dropping the packets). That distinction, refused versus dropped, is precisely the kind of thing an error stack and a log entry cannot show you but a trace can, because it comes from watching the actual network exchange rather than the client's own summary of what it tried to do.
A second scenario illustrates the same point from a different angle. A connection that succeeds but takes far longer than expected will not show up as an error in any log at all, since nothing actually failed. The listener log confirms the connection was eventually accepted; it has no reason to flag anything as wrong. Only a trace, read alongside its timestamps, shows where the delay actually occurred: in DNS or naming resolution before the request ever reached the listener, in authentication negotiation once it got there, or in the handoff to a dispatcher or shared server process on the database side. Performance problems like this are exactly the case where logging alone, however well it is working, simply has nothing to tell you.
sqlnet.log confirms the attempt and the target address, which tells you the client built the connection string correctly and tried to reach it. That is useful, but it still leaves open whether the listener process is simply not running, is running on a different port than configured, or is running but being blocked by a firewall or an Oracle Connection Manager access control rule somewhere in between.
A trace, by contrast, shows the socket-level outcome of the attempt: whether the connection was actively refused (the listener process exists but nothing is bound to that port) or whether it simply timed out with no response at all (more consistent with a firewall silently dropping the packets). That distinction, refused versus dropped, is precisely the kind of thing an error stack and a log entry cannot show you but a trace can, because it comes from watching the actual network exchange rather than the client's own summary of what it tried to do.
A second scenario illustrates the same point from a different angle. A connection that succeeds but takes far longer than expected will not show up as an error in any log at all, since nothing actually failed. The listener log confirms the connection was eventually accepted; it has no reason to flag anything as wrong. Only a trace, read alongside its timestamps, shows where the delay actually occurred: in DNS or naming resolution before the request ever reached the listener, in authentication negotiation once it got there, or in the handoff to a dispatcher or shared server process on the database side. Performance problems like this are exactly the case where logging alone, however well it is working, simply has nothing to tell you.
Where Trace Parameters Are Configured
Parameters that control tracing, including the type and amount of information traced and the location where trace files are stored, are set in the configuration file of each network component, as shown in Table 7-2 below.
Table 7-2 Location of Trace Parameters
Each of these files uses its own set of
Table 7-2 Location of Trace Parameters
| Configuration File | |
cman.ora |
Oracle Connection Manager processes |
listener.ora |
Listener |
sqlnet.ora |
Client, database server, and the TNSPING utility |
Each of these files uses its own set of
TRACE_LEVEL_*, TRACE_DIRECTORY_*, and TRACE_FILE_* parameters (or, for cman.ora, the same parameters without a component suffix) to control where that component's trace output goes and how detailed it is. Log files follow the same configuration-file mapping, through the matching LOG_DIRECTORY_* and LOG_FILE_* parameters, so a component's log and trace output normally live in the same part of the configuration whether or not you ever raise that component's trace level.
sqlnet.ora is also where you set the connection timeout parameters that keep a stuck connection attempt from hanging indefinitely rather than failing fast enough to leave a useful log or trace entry behind. SQLNET.OUTBOUND_CONNECT_TIMEOUT is the key one on the client side: it bounds how long the client waits for the Oracle Net connection itself to be established, independent of any application-level query timeout. Leaving it unset means a client can hang far longer than expected against an unresponsive host, filling neither the log nor the trace with anything useful in the meantime, since nothing has failed yet, it has simply not finished. See Setting TNS Timeout Values for sane outbound, inbound, send, and receive timeout settings across the client, listener, and server.
Putting It Together: A Worked Example
Consider a report that a nightly batch job intermittently fails to connect to the database, but only on some nights. The log is the right first stop: the server's
The log, on its own, confirms the symptom and rules out several categories of cause, but it does not say why the handlers were exhausted on those particular nights. That is where tracing earns its keep: raising
sqlnet.log shows a handful of failed connection attempts over the past week, each reporting ORA-12520, "TNS:listener could not find available handler." That already narrows the problem considerably. ORA-12520 means the listener was reachable and recognized the service, but every shared server or dispatcher process was already busy when the request arrived, which points toward a capacity issue on the database side rather than a naming, firewall, or routing problem on the network side.
The log, on its own, confirms the symptom and rules out several categories of cause, but it does not say why the handlers were exhausted on those particular nights. That is where tracing earns its keep: raising
TRACE_LEVEL_SERVER to user during the batch window and correlating the resulting trace timestamps against the job schedule can confirm whether the failures line up with a separate reporting job competing for the same shared server pool, rather than requiring a blind guess at the cause. Once the overlapping job is identified, the trace level goes back to off, since its job, catching the event in the act, is already done.
Summary
Now that you have reviewed the basic differences between logging and tracing, and where their configuration lives, you are in a position to make the right first move when a connection problem is reported. A short recap of the key points:
- Logging is always-on and low-overhead; it records the error stack, not the full detail behind it.
- Tracing is off by default and expensive to run; it records the packet-level and internal-call detail that a log entry cannot.
- Both are configured per component, in
sqlnet.ora,listener.ora, andcman.ora, using parallelLOG_*andTRACE_*parameter families. - The normal workflow is log first, trace second: enable tracing only to catch a specific, still-unexplained problem in the act, then turn it back off.