Identify the Location of Log and Trace Files

Finding where Oracle actually writes its log and trace files is the first practical skill behind everything covered so far in this module. Knowing that TRACE_LEVEL_SERVER = SUPPORT will produce a detailed trace is only useful if you can then put your hands on the file it produces. This lesson walks through the system views and commands that locate log and trace files on an Oracle 26ai server, for the database itself, for individual sessions, and for the listener.

The short version is that almost everything now routes through the Automatic Diagnostic Repository, so the single most useful habit to build is querying V$DIAG_INFO rather than remembering a directory path by heart. The sections below cover that view in detail, then work through the handful of places, redo logs, archived redo logs, and the listener, that still have their own lookup method.

The modern starting point, for a database instance on any ADR-based release including 26ai, is V$DIAG_INFO:

SELECT name, value
FROM v$diag_info
WHERE name IN ('ADR Home','Diag Trace','Diag Alert','Default Trace File');

• ADR Home: the root of this instance's diagnostic data under DIAGNOSTIC_DEST, typically $ORACLE_BASE/diag/rdbms/<db_unique_name>/<instance>/.
• Diag Trace: the directory holding background and foreground .trc files, and the text-format alert log, alert_<SID>.log.
• Diag Alert: the directory holding the XML-format alert log, log.xml.
• Default Trace File: the trace file your own current session is writing to, if tracing is active.

You can also confirm the diagnostic root directly:

SHOW PARAMETER diagnostic_dest

Two older initialization parameters, USER_DUMP_DEST and BACKGROUND_DUMP_DEST, controlled trace file placement before Oracle Database 11g. They are now deprecated and silently ignored whenever ADR is active, which it is by default, so querying them no longer tells you where trace files actually go; use V$DIAG_INFO or DIAGNOSTIC_DEST instead. If you come across either parameter referenced in an older script or a forum post, treat it as a historical pointer to the same general idea, "where does this instance keep its trace files," rather than something to actually set.

It is worth running the V$DIAG_INFO query once on a server you work with regularly and simply noting the ADR Home value, since every other path in this lesson, the background trace directory, the alert log, a specific session's trace file, is a subdirectory underneath it.

To list every trace file currently known under this instance's ADR home, query V$DIAG_TRACE_FILE rather than hunting through a directory listing by hand:

SELECT trace_filename
FROM v$diag_trace_file
ORDER BY trace_filename;

To find the trace file for one particular session, join V$SESSION to V$PROCESS; the trace file name itself is a column on V$PROCESS, not on V$SESSION:

SELECT s.username, s.sid, s.serial#, p.tracefile
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND s.username IS NOT NULL;

To make a session's own trace file easier to pick out by name, tag it before you start:

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'my_trace';

The resulting file name will include my_trace, so you can find it without opening every file in the directory to check.

These two approaches suit different situations. Use the V$SESSION/V$PROCESS join when you already know which session you care about, its SID, its username, or the application that opened it, and you want that one connection's trace file specifically. Use V$DIAG_TRACE_FILE when you are not sure which file you want yet, for example after enabling tracing broadly and reproducing a problem, and need to see everything that landed in the trace directory around that time so you can pick out the right one by timestamp.

Online redo log member locations come from V$LOGFILE, not V$LOG; V$LOG tracks group-level status and sequence numbers but has no file-path column, while V$LOGFILE has the actual member path:

SELECT group#, member
FROM v$logfile;

For archived redo logs, query V$ARCHIVE_DEST for the configured destination paths:

SELECT destination
FROM v$archive_dest
WHERE status = 'VALID';

Both of these views answer a slightly different question than V$DIAG_INFO does: they are about where the database stores its redo stream, not about diagnostic trace output, which is why they are queried separately rather than folded into the ADR-based views above. If you only remember one distinction from this section, make it this one: V$LOG tells you how many redo log groups exist and which one is current, while V$LOGFILE tells you where each group's actual files live on disk. Confusing the two is an easy mistake to make, since both views have "LOG" in the name and both are commonly queried together when investigating redo-related issues.

The listener and, if one is in the path, Oracle Connection Manager each have their own ADR home, separate from the database's. From adrci, SHOW BASE reports the current ADR_BASE for a given product, and SET BASE points the session at it:

adrci> show base -product listener
adrci> set base -product listener
adrci> show homes

The same pattern works with -product client for a client installation's own ADR home, and is the most reliable way to confirm a diagnostic path rather than assuming a directory layout from memory. On a server running more than one listener or more than one Oracle Connection Manager instance, each gets its own home under the shared ADR base, distinguished by instance name, so SHOW HOMES after setting the base is worth running even when you are fairly sure which directory you are looking for.

Once you have the right home, the same adrci session can go on to browse its contents directly, with SHOW ALERT for that component's alert log and SHOW TRACE for a specific trace file, rather than switching to a plain directory listing and a text editor. For a one-off lookup, that is a minor convenience; for a host with several listeners, several Oracle Connection Manager instances, and a database all sharing one ADR base, it is usually faster than remembering which subdirectory belongs to which component.

The easiest way to see the location of server-side listener log and trace files, without writing any SQL at all, is the Listener Control status command. Here is a current example:

dbhost01 $ lsnrctl status

LSNRCTL for Linux: Version 26.1.0.0.0 - Production on 04-OCT-2026 10:05:12

Copyright (c) 1991, 2026, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=fred))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 26.1.0.0.0 - Production
Start Date                20-SEP-2026 08:12:04
Uptime                    14 days 1 hr. 53 min. 8 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/26.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/dbhost01/listener/alert/log.xml
Services Summary...
  lamp          has 1 service handler(s)
  i8itall       has 1 service handler(s)

Notice that the Listener Log File line points at the ADR path, alert/log.xml, rather than a classic flat network/log/listener.log location; that reflects ADR being enabled by default.

To view client-side parameters, display the local sqlnet.ora file, or, for the files it actually produces, query V$DIAG_INFO as a client-side session the same way you would on the server. While you are looking at sqlnet.ora, it is also worth checking that sane connection timeouts are set, since a stuck connection attempt that never fails leaves nothing useful in either the log or the trace; see Setting TNS Timeout Values for the outbound, inbound, send, and receive timeout parameters.

Suppose a report comes in that a particular application's connections were slow between 2:00 and 2:15 this morning, with no error reported, just slowness. There is no log entry to start from, since nothing failed, so the task is purely one of locating the right files to compare.

Start on the database side with V$DIAG_INFO to confirm the ADR Home and Diag Trace locations for the instance, then check whether TRACE_LEVEL_SERVER tracing happened to be active during that window; if not, this is really a job for the next time the problem occurs, with tracing enabled in advance. If a trace does exist for that window, V$DIAG_TRACE_FILE lists it alongside every other current trace file, so you are not left guessing at a file name from a directory listing. On the listener side, SHOW BASE -product listener followed by SHOW HOMES confirms exactly which ADR home holds that listener's own trace and log output, which matters on a host running more than one listener.

With both locations confirmed, the actual comparison, client trace against listener log for the same fifteen-minute window, is the easy part; the time-consuming part, if you skip the steps in this lesson, is usually spent hunting for the right directory rather than reading what is in it.

A short recap of where to look:

• V$DIAG_INFO is the starting point for a database instance's own ADR Home, trace directory, alert log, and current session trace file.
• V$DIAG_TRACE_FILE lists every trace file currently under that ADR home, and a V$SESSION/V$PROCESS join finds one specific session's file.
• V$LOGFILE, not V$LOG, holds redo log member paths; V$ARCHIVE_DEST holds archived redo log destinations.
• adrci's SHOW BASE and SHOW HOMES locate the listener's and Oracle Connection Manager's own ADR homes, separate from the database's.
• lsnrctl status remains the fastest single command for confirming the listener's own log file location without writing any SQL at all.

Now that you understand the basic file locations and their purposes, the next lesson looks at the uses of these files.

Before moving on to the next lesson, click the Exercise button to evaluate a situation requiring the use of the log and trace parameters.
Log Location Trace Files - Exercise