Locate the Alert Log and Trace Files with V$DIAG_INFO

calendar Aug 3, 2026 / · 11 min read · Oracle DBA V$DIAG_INFO Alert Log ADR Trace Files Automatic Diagnostic Repository Oracle Administration Diagnostic Data DIAGNOSTIC_DEST  ·
Share on: linkedin copy

Locate the Alert Log and Trace Files with V$DIAG_INFO

Purpose

Before Oracle 11g, finding the alert log meant knowing the value of BACKGROUND_DUMP_DEST and constructing the filename by hand. 11g replaced that parameter, along with USER_DUMP_DEST and CORE_DUMP_DEST, with a single DIAGNOSTIC_DEST parameter and moved every trace file, alert log, incident package, and core dump under the Automatic Diagnostic Repository (ADR) — a structured directory tree rooted at DIAGNOSTIC_DEST and organized by database name and instance name underneath it. That consolidation is a net improvement, but it introduces a new problem: a DBA connecting to an unfamiliar instance, a freshly handed-off production system, or a database with a non-default DIAGNOSTIC_DEST has no reliable way to guess the alert log's path from memory. Grepping the filesystem for alert_*.log works until the mount point is unfamiliar, the ORACLE_HOME is shared across several instances, or shell access isn't available at all.

V$DIAG_INFO answers this directly, from inside the database, in one query. It exposes the state of ADR as a set of NAME/VALUE pairs — not fixed columns, but rows that report the ADR base directory, the ADR home for this specific instance, and the exact subdirectory paths for trace output, the alert log, incidents, and core dumps. A DBA who has never logged into a given instance before can run one SELECT and have the alert log path, the trace directory, and the current session's own default trace file, without touching the operating system or knowing DIAGNOSTIC_DEST in advance.

This post covers the V$DIAG_INFO structure — the NAME/VALUE pairs it returns and what each one means — the RAC-aware GV$DIAG_INFO equivalent, and the practical shell pattern for turning a returned path into a live tail -f on the alert log. It is a discovery tool, not a tracing tool: it reports where diagnostic output already lands, and does not turn anything on.

Code

 1-- Query 1: full NAME/VALUE dump for the current instance
 2SELECT inst_id,
 3       name,
 4       value
 5FROM   v$diag_info
 6ORDER  BY name;
 7
 8-- Query 2: the four rows that answer "where is the alert log and trace output"
 9SELECT name,
10       value
11FROM   v$diag_info
12WHERE  name IN ('ADR Home', 'Diag Trace', 'Diag Alert', 'Default Trace File')
13ORDER  BY name;
14
15-- Query 3: RAC — every instance's ADR paths from a single connection
16SELECT inst_id,
17       name,
18       value
19FROM   gv$diag_info
20WHERE  name IN ('Diag Trace', 'Diag Alert')
21ORDER  BY inst_id, name;
22
23-- Query 4: confirm ADR is actually enabled before trusting the paths returned
24SELECT value
25FROM   v$diag_info
26WHERE  name = 'Diag Enabled';
27
28-- Query 5: a quick health check bundled into the same view
29SELECT name,
30       value
31FROM   v$diag_info
32WHERE  name IN ('Active Problem Count', 'Active Incident Count');

 1# Step 1: capture the alert directory path from V$DIAG_INFO into a shell variable
 2ADR_ALERT_DIR=$(sqlplus -s / as sysdba <<'EOF'
 3SET HEADING OFF FEEDBACK OFF PAGESIZE 0 TRIMSPOOL ON
 4SELECT value FROM v$diag_info WHERE name = 'Diag Alert';
 5EXIT
 6EOF
 7)
 8
 9# Step 2: tail the XML-formatted alert log using the captured path
10tail -f "${ADR_ALERT_DIR}/log.xml"
11
12# Step 3: same pattern for the plain-text alert log, which lives under Diag Trace
13ADR_TRACE_DIR=$(sqlplus -s / as sysdba <<'EOF'
14SET HEADING OFF FEEDBACK OFF PAGESIZE 0 TRIMSPOOL ON
15SELECT value FROM v$diag_info WHERE name = 'Diag Trace';
16EXIT
17EOF
18)
19tail -f "${ADR_TRACE_DIR}/alert_${ORACLE_SID}.log"

Code Breakdown

Query 1: the full NAME/VALUE dump

V$DIAG_INFO has four columns: INST_ID, NAME, VALUE, and CON_ID. NAME identifies a piece of ADR state — whether it's enabled, one of several directory paths, or a count of open problems and incidents. VALUE is the corresponding string for that row, sized as VARCHAR2(512) to hold a full filesystem path. Running the unfiltered query on a healthy 11g+ instance returns rows including Diag Enabled, ADR Base and ADR Home (the two top-level directories), Diag Trace, Diag Alert, Diag Incident, Diag Cdump, and Health Monitor (the ADR subdirectories), Default Trace File (the current session's own trace file), and Active Problem Count / Active Incident Count.

Query 2: filtering to the four rows that matter for this task

ADR Home is the instance-specific root — the directory that contains every other ADR subdirectory for this database and instance. Diag Trace is the directory holding background and foreground process trace files, including the plain-text copy of the alert log. Diag Alert is the directory holding the XML-formatted alert log. Default Trace File is the full path to the trace file the current session would write to if it generated one right now — useful for confirming where your own SQL*Plus session's diagnostic output lands, separate from any other process's trace file.

Query 3: GV$DIAG_INFO for RAC

GV$DIAG_INFO is the cluster-wide equivalent of V$DIAG_INFO, adding rows from every instance in a Real Application Clusters configuration, distinguished by INST_ID. Each instance in a RAC cluster has its own ADR home — they are not shared — so a query against GV$DIAG_INFO from any single instance returns the alert log and trace paths for every node in the cluster without needing to connect to each one separately.

Query 4: confirming ADR is enabled

Diag Enabled returns TRUE or FALSE. If ADR has been disabled at the instance level, the paths reported by the other rows may not reflect where diagnostic output is actually being written, since some legacy trace behavior can fall back to different locations. Checking this row before acting on the others is a cheap safeguard against acting on stale directory information.

Query 5: the bundled health check

Active Problem Count and Active Incident Count report how many ADR problems and incidents are currently considered active — either they occurred in the last 24 hours or they carry metadata marking them persistent, such as a disk corruption. This isn't the primary purpose of the view for this task, but it's a useful one-line addition: a nonzero count is a signal to also run adrci show incident or query DBA_OUTSTANDING_ALERTS before assuming the instance is quiet.

The shell pattern: turning a path into a live tail

The two-step shell pattern — capture the VALUE into a variable with a heredoc-fed sqlplus call, then use that variable in a normal shell command — avoids hardcoding any path. SET HEADING OFF FEEDBACK OFF PAGESIZE 0 TRIMSPOOL ON strips SQL*Plus's column headers, row-count feedback, and trailing whitespace, so the variable captures exactly the path string and nothing else. The XML alert log is named log.xml inside the Diag Alert directory; the plain-text alert log is named alert_<ORACLE_SID>.log and lives inside the Diag Trace directory, not the Diag Alert directory — a distinction that trips up anyone assuming both formats sit side by side.

Key Points

  • V$DIAG_INFO is a NAME/VALUE view, not a fixed-column table. New rows can appear across Oracle releases without any change to the view's own column structure, which is why filtering with WHERE name IN (...) is the right pattern rather than assuming a specific row count or order.
  • The text alert log and the XML alert log live in different directories. log.xml is under the path reported by Diag Alert. alert_<SID>.log, the plain-text copy, is under the path reported by Diag Trace — the same directory as every other background and session trace file.
  • Default Trace File reports the current session's own file, not a fixed instance-wide file. Run this from the session actually generating the trace you're chasing, not from an unrelated administrative connection.
  • RAC instances do not share an ADR home. Each instance's Diag Trace and Diag Alert paths are independent; GV$DIAG_INFO is the only way to see all of them from one connection.
  • CON_ID distinguishes container scope but the ADR paths themselves are instance-level. In a multitenant database, alert log entries are tagged with the originating PDB name inside the file, but the alert log and trace directories are not split per PDB — there is one ADR home for the whole CDB instance.
  • Check Diag Enabled first. A FALSE value means the paths reported by the other rows may not be where current diagnostic output is actually going.

Insights and Best Practices

Why this beats grepping for DIAGNOSTIC_DEST

Reading DIAGNOSTIC_DEST from V$PARAMETER only gives the top-level base directory. Reconstructing the full alert log path from there still requires knowing the exact diag/rdbms/<dbname>/<instance> naming convention and getting the database and instance name casing right. V$DIAG_INFO skips all of that reconstruction — Diag Alert and Diag Trace are already the complete, correct paths, built by Oracle itself rather than assembled by hand.

Build a standing alias, not a one-off query

For any instance a DBA touches regularly, wrapping the Step 1–3 shell pattern above into a small script or shell function — parameterized by ORACLE_SID — turns "where's the alert log on this box" into a single command instead of a repeated manual lookup. This is especially worth doing on hosts running multiple instances from a shared ORACLE_HOME, where each instance's ADR home differs even though the binaries are identical.

Don't assume every RAC node uses the same ADR base

Even within one cluster, differing local disk layouts or a node added after the others were provisioned can leave one instance's ADR Base pointing somewhere different from its siblings. Running the GV$DIAG_INFO query across the whole cluster before writing a monitoring script that assumes a single shared path catches this early, rather than after the script silently misses one node's alert log.

Pair with ADRCI for a friendlier read

V$DIAG_INFO is the fastest way to get the raw path from SQL, but once you have it, the adrci command-line utility — pointed at the same ADR home — gives commands like show alert and show incident that filter and format the XML alert log far more readably than a raw tail on log.xml. The two tools complement each other: V$DIAG_INFO for discovery from inside the database, adrci for interactive review once you're on the host.

Treat Active Problem Count as a cheap tripwire

Adding the Active Problem Count / Active Incident Count query to an existing health-check script costs one extra query and flags a class of issue — ORA-600 errors, ORA-7445 errors, block corruption — that a routine alert log tail can miss if the incident happened between checks. A nonzero count is the cue to follow up with adrci or DBA_OUTSTANDING_ALERTS, not a diagnosis on its own.

When to Run This

  • Connecting to an unfamiliar or freshly handed-off instance where DIAGNOSTIC_DEST and the ADR layout are not documented anywhere accessible.
  • Writing a monitoring script that needs to tail the alert log across several instances with different ADR bases, without hardcoding paths.
  • Confirming ADR is enabled and reachable before starting a deeper diagnostic session.
  • Troubleshooting a RAC cluster where the alert log path is needed for every node, not just the one currently connected.
  • Building a support-package or diagnostic-file inventory when there is no direct shell access to the database host.
  • Checking Default Trace File immediately before or after running a query, to confirm which trace file a given session actually wrote to.

Troubleshooting Common Issues

The query returns no rows. This almost always means the instance predates 11g and has no ADR — check the version with SELECT * FROM v$version and fall back to USER_DUMP_DEST and BACKGROUND_DUMP_DEST from V$PARAMETER instead.

Diag Enabled returns FALSE. Diagnostic output may not be landing in the paths the other rows report. Confirm with SHOW PARAMETER diagnostic_dest and check whether ADR was deliberately disabled for this instance before relying on the reported paths.

The path returned doesn't exist when you check it from the OS. This is usually a host mismatch — the query was run from a different node in a RAC cluster than the one whose filesystem you're checking, or the instance's ORACLE_BASE is mounted differently than expected on this particular host. Re-run the query from a session actually connected to the instance in question, or use GV$DIAG_INFO to confirm which INST_ID the path belongs to.

The alert log file isn't where you expected inside the directory. Remember the split: log.xml is under Diag Alert; alert_<SID>.log is under Diag Trace, not Diag Alert. Looking for the text file in the alert directory, or the XML file in the trace directory, is the most common cause of a "file not found" surprise here.

Default Trace File looks stale or blank. This row reflects the current session at the moment of the query. If the session hasn't generated any trace output yet in its current connection, the value may not point at a file that has actually been written. Re-check after the session has produced some trace activity.

References

Posts in this series