Using Log and Trace Files to Analyze and Troubleshoot Network Problems

Because network communication has several layers, a network error can originate from a variety of sources for a variety of reasons. When something goes wrong, an application that depends on Oracle Net Services will normally surface an error message, usually a terse one. Older material for this lesson talked about Net8 and the standalone Trace Assistant (trcasst) utility; both names have since given way to Oracle Net Services and ADR-based tooling, covered below, though the underlying skill of reading a log or trace file has not really changed. The purpose of this lesson is to learn how to read Oracle Net logs and traces well enough to turn a terse message into an actual cause, and resolve the connectivity problem.

The information in log and trace files helps you understand:

1. The flow of packets between network nodes.
2. Which Oracle Net Services component is failing (listener, shared server, client, Connection Manager).
3. The specific error codes and messages involved, and which layer reported each one.

As covered in the previous lesson, V$DIAG_INFO is the starting point on the database side, and adrci locates the listener's and Oracle Connection Manager's own ADR homes separately. In outline, under the ADR default:

• Listener: $ADR_BASE/diag/tnslsnr/<host>/<listener>/trace/listener.log (text) and alert/log.xml (XML).
• Client/server: $ADR_BASE/diag/clients/... or diag/rdbms/..., holding sqlnet.log and trace files under trace/.

1. Reproduce the error.
2. Open the newest listener.log or sqlnet.log (or log.xml).
3. Find the timestamp and the error stack, for example TNS-12541 or ORA-12514. The stack shows which layer failed: address resolution, service name, protocol, or authentication.
4. Correlate the same connection across client, listener, and server logs.

That last step used to mean matching timestamps and connection details by eye, which gets difficult the moment a connection passes through more than one hop, such as client to Oracle Connection Manager to server. Starting with Oracle Database 21c, Oracle Net generates an actual connection identifier, CONNECTION_ID (with a related CONNECTION_ID_PREFIX), that is written into the logs and traces of every network element a connection passes through. On 21c and later, including 26ai, searching for that one value across the client, Connection Manager, and server logs is a direct way to follow a single connection end to end. On releases before 21c, you are still matching by timestamp and connect data, since the identifier does not exist yet.

A listener log entry that includes a connection identifier looks roughly like this, among the other audit-trail fields on the line:

(CONNECTION_ID=4VIdFEpcSe3gU+FoRmR0aA==)

That same string appears in the corresponding client and, if one is in the path, Oracle Connection Manager log and trace output for the identical connection attempt, which is what makes it searchable across all three rather than something you have to reconstruct from timestamps.

1. Set TRACE_LEVEL_CLIENT, TRACE_LEVEL_SERVER, or TRACE_LEVEL_LISTENER to SUPPORT, then reload the listener if that is the component you changed.
2. Reproduce the problem.
3. Open the .trc file in the ADR trace directory, or list it with V$DIAG_TRACE_FILE as covered in the previous lesson.
4. Search for the error, or for the CONNECTION_ID value if you already have it from a log.
5. Turn tracing back off (OFF) once you are done.

Oracle's original Trace Assistant, trcasst, formatted raw Net8-era trace files into a readable summary of packets and TTC calls. It has been desupported in current releases, so plan on one of the following instead:

• adrci, the ADR Command Interpreter, is the most direct modern replacement for routine trace review:

  
  adrci> show homes
  adrci> show alert
  adrci> show trace listener.log
  

  SHOW HOMES locates the right ADR home, SHOW ALERT reviews a component's alert log, and SHOW TRACE opens a specific trace file, all without leaving the command line or remembering a directory path.
• Oracle Trace File Analyzer (TFA), part of the Oracle Database Support Tools Bundle, automatically collects, organizes, and correlates trace files, logs, and diagnostic data across multiple hosts and components, which is particularly useful when a problem spans more than one server.

A few other tools come up in Oracle diagnostics generally but are not primarily network troubleshooting tools, so they are worth knowing about rather than reaching for first here: SQLT and Oracle Real-Time SQL Monitoring focus on SQL execution and tuning, and the Diagnostics Pack (AWR and ADDM) provides automated, system-wide performance analysis under a separate license. All three can be useful once a network problem turns out to be a performance problem on the database side, but none of them read an Oracle Net trace file directly.

Suppose an application team reports intermittent ORA-12170 connect timeouts, but only from one particular application server, and only under load. The client's own sqlnet.log confirms the symptom and gives a CONNECTION_ID for one of the failed attempts, which is the thread to pull on.

Because the service runs behind Oracle Connection Manager, the next step is searching for that same CONNECTION_ID in CMAN's own log and trace output rather than guessing whether the delay happened before or after CMAN. If the identifier never appears there, the request never reached Connection Manager at all, which points the investigation back toward the client or the network path in front of it, not toward anything on the database side. If it does appear, comparing the timestamp it was received against the timestamp it was forwarded on narrows the delay down to whichever hop actually introduced it, the client-to-CMAN leg or the CMAN-to-listener leg, rather than leaving "somewhere in the network" as the only available answer.

This is the practical payoff of having a connection identifier at all: on a pre-21c system, the same investigation would mean lining up timestamps across three separate logs by hand and hoping nothing else connected in the same second.

An end user typically receives a generic message such as ORA-12154, "TNS: could not resolve service name." On its own, that message means nothing to the end user, who calls the DBA. A DBA who recognizes ORA-12154 already knows it points to a bad or missing entry in tnsnames.ora (or, for Easy Connect-style connections, a malformed connect string), which usually narrows the search considerably. A predictable naming and distribution strategy for tnsnames.ora, tested with tnsping after every change, catches most of these before an end user ever sees the error; see Creating and Maintaining the tnsnames.ora File.

The most common Oracle Net Services problem overall is the loss of the listener process itself, whether it crashed or simply stopped accepting new connections. When a large number of users report an inability to connect at the same time, the listener is the first thing to check.

A general diagnostic sequence:

1. Get the exact error number from the end user. If they did not write it down, have them reproduce the error and read it back. The error number points to the problem far more often than not. oerr displays the full error text for a given code:

   
   oerr ora 12154
   

2. Confirm basic reachability from the client with tnsping:

   
   tnsping dilbert
   

3. If tnsping succeeds, confirm the listener is actually accepting connections by connecting from the server itself:

   
   sqlplus system/<password>@fred
   

4. If that also succeeds, the server side is healthy, and the remaining question is whether the client can reach the server end to end using the same connect identifier, which is what the client's own connection attempt is actually testing.

ORA-12154 is just as real for cloud-hosted and Autonomous Databases as it is on-premises, since both still rely on Oracle Net Services configuration, whether through tnsnames.ora, Easy Connect or Easy Connect Plus, or a wallet-based connection. Common causes in a cloud-enabled environment include:

• A connect identifier in the client application that does not match any entry in tnsnames.ora.
• Incorrect Easy Connect syntax, for example a missing or malformed host:port/service_name component.
• Network configuration blocking connectivity: firewall rules, network security groups, or virtual cloud network (VCN) settings.
• A missing or misconfigured wallet, common with Autonomous Database and other wallet-based cloud connections.
• A service name and host that do not match what the cloud console actually shows for that database.
• A hostname in the connect identifier that cannot be resolved via DNS.

To troubleshoot: verify the tnsnames.ora entry or Easy Connect string matches what the database actually expects; confirm firewall, NSG, and VCN rules allow traffic to the database port (typically 1521); confirm the wallet location and configuration in sqlnet.ora if one is in use, the same file worth double-checking for sane outbound, inbound, send, and receive timeouts while you are in there (see Setting TNS Timeout Values); check the connection details shown in the cloud console directly against what the client is using; and test a direct connection from the client:

sqlplus user/password@//hostname:port/service_name

If the problem persists after checking all of this, tnsping and Oracle Net tracing, covered earlier in this lesson, are the next step toward isolating the root cause.

Regardless of the specific error, the same general sequence applies to almost any Oracle Net Services problem:

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.
3. If tnsping succeeds, confirm the listener itself is accepting connections by connecting directly from the server.
4. If the server side checks out, attempt the same connection from the client using the actual TNS service name or connect string the application uses, to confirm whether the problem is specific to the client's own configuration.
5. If the problem is still not clear at this point, move on to the log and trace analysis covered earlier in this lesson.

A short recap of how to turn a terse error message into a cause:

• Start with the log, not the trace; it is already being collected and usually narrows the problem considerably on its own.
• On Oracle Database 21c and later, use CONNECTION_ID to follow one specific connection across client, Connection Manager, and server logs instead of matching timestamps by hand.
• Reach for a trace, at the component closest to the suspected failure, only when the log's error stack does not explain the problem on its own.
• adrci and TFA are the current tools for reading trace output; trcasst is desupported.
• Most Oracle Net Services problems trace back to a handful of recurring causes: a stopped listener, a bad tnsnames.ora entry, a blocked port, or a mismatched wallet or service name in cloud environments.

Now that you have reviewed the general steps for resolving Oracle Net Services connectivity problems, the next lesson concludes this module.