Complete Guide to the Oracle Database Alert Log

The alert log is Oracle’s chronological record of instance health and incidents. If you can read the alert log fast, your root-cause isolation and first response improve dramatically. This article covers where it lives and how to read it, representative inspection commands, how to interpret common errors, and basic housekeeping—with explanations that are friendly to beginners.

🇯🇵日本語版はこちら (Japanese version) >

What is the alert log? (Role and location)
[oracle 19c] Quickly verify the alert log location and structure
How to read common messages and what to check first
Grasping the “flow” of startup and configuration messages
Trace file output and management
Ready-to-use daily health checks
Wrap-up (how to read in the real world)

What is the alert log? (Role and location)

Role: Records key events such as instance start/stop, configuration changes, background process issues, and ORA- errors.
Location: Under the ADR (Automatic Diagnostic Repository).

$ORACLE_BASE/
└─ diag/
   └─ rdbms/
      └─ <db_unique_name>/
         └─ <instance_name>/
            ├─ alert/   ← XML format（log.xml）
            └─ trace/   ← Text format：alert_<db_unique_name>.log

In many environments the file name is alert_<DB_UNIQUE_NAME>.log (on single-instance systems, it can appear similar to the SID).

[oracle 19c] Quickly verify the alert log location and structure

1) Read it directly on OS

# Example: Specify the path as is (replace it)
tail -f /u01/app/oracle/diag/rdbms/ORCL/ORCL/trace/alert_ORCL.log

# Check the most recent ORA- by 10 lines
grep -n "ORA-" /u01/app/oracle/diag/rdbms/ORCL/ORCL/trace/alert_ORCL.log | tail -n 10

Notes for beginners

tail -f follows the file in real time. Stop with CTRL+C.
grep -n "ORA-" extracts error lines. Read the surrounding timestamped lines to identify the change point.

2) Pinpoint “where” via SQL*Plus (V$DIAG_INFO)

-- With administrator privileges (e.g. SYSDBA)
COL name  FORMAT A20
COL value FORMAT A100
SELECT name, value
FROM   v$diag_info
WHERE  name IN ('ADR Base','ADR Home','Diag Alert','Diag Trace','Default Trace File')
ORDER  BY name;

Beginner-friendly explanation

V$DIAG_INFO returns exact directories for diagnostic components.
- ADR Base … ADR root
- ADR Home … Diagnostic home for this instance
- Diag Alert … Directory of the XML alert log
- Diag Trace … Directory containing the text alert log and various trace files
- Default Trace File … Your session’s trace file

3) Query the alert log content (19c: V$DIAG_ALERT_EXT)

From 12.2 onward, V$DIAG_ALERT_EXT exposes alert-log-equivalent content as rows. Filtering and sorting are easy.
(The view is a table-like interface to the XML alert.)

-- Alert logs for the last 24 hours in chronological order
COL originating_timestamp FORMAT A30
COL message_text FORMAT A100 WORD_WRAPPED
SELECT originating_timestamp, message_text
FROM   v$diag_alert_ext
WHERE  originating_timestamp > SYSTIMESTAMP - INTERVAL '1' DAY
ORDER  BY originating_timestamp;

Explanation

originating_timestamp … Event timestamp
message_text … Message body (including lines starting with ORA-)

Only errors

SELECT originating_timestamp, message_text
FROM   v$diag_alert_ext
WHERE  message_text LIKE 'ORA-%'
ORDER  BY originating_timestamp DESC
FETCH FIRST 50 ROWS ONLY;

4) Use ADRCI for powerful search/follow (official utility)

adrci
ADRCI> show homes
ADRCI> set homepath diag/rdbms/ORCL/ORCL     -- Select the desired home
ADRCI> show alert -tail -f                     -- Tracking display (stop with CTRL+C)

Filter example (only ORA-600 family)

ADRCI> show alert -p "MESSAGE_TEXT LIKE '%ORA-600%'" -term

Spool to file

ADRCI> spool /tmp/my_alert.log
ADRCI> show alert -term
ADRCI> spool off

show alert options such as -p, -tail, -f, and -term are documented and very handy.

How to read common messages and what to check first

In practice, always pair surrounding lines with recent changes. Below are “axes for reading.” Execute changes only after proper assessment—they vary by environment.

ORA-01555: snapshot too old

Meaning:
Consistent-read blocks for SELECT have aged out of UNDO.
Reading axes:
Long-running SELECT/batch, write concurrency, balance between UNDO_RETENTION and UNDO tablespace size.
SQL to inspect
-- Undo tablespace information
SELECT tablespace_name, status, contents FROM dba_tablespaces WHERE contents = 'UNDO';
-- UNDO usage status (typical example)
SELECT name, value FROM v$parameter WHERE name LIKE 'undo%';
Suggestion: Split long queries, reduce unnecessary sorts, reassess UNDO tablespace size and UNDO_RETENTION. Prepare impact evaluation and rollback plans before changes.

ORA-00060: deadlock detected while waiting for resource

Meaning:
Mutual (cyclic) blocking; Oracle rolls one side back automatically.
Reading axes:
Check the deadlock graph in the relevant session’s trace file. Review update ordering and index/selectivity design.
Navigation
-- Trace file location for current session（Default Trace File）
SELECT value AS default_trace_file FROM v$diag_info WHERE name = 'Default Trace File';
Suggestion: Standardize update order, improve WHERE clause selectivity, and consider app-side retries with exponential backoff.

ORA-00257: Archiver error.

Meaning:
FRA (Fast Recovery Area) is full; archiving has stopped.
Inspection SQL
-- FRA usage rate
SELECT name, space_limit/1024/1024 AS mb_limit, space_used/1024/1024 AS mb_used, space_reclaimable/1024/1024 AS mb_reclaimable FROM v$recovery_file_dest;
-- Breakdown (which types are used)
SELECT file_type, percent_space_used, percent_space_reclaimable FROM v$flash_recovery_area_usage ORDER BY percent_space_used DESC;
Suggestion: Remove old archives/backups (via RMAN), expand the FRA, or reduce archive generation (within business/DR constraints). Always confirm operations policy and recovery posture first.

Grasping the “flow” of startup and configuration messages

[Typical startup]
Starting ORACLE instance
 → Database mounted.
 → Database opened.

[Configuration change example]
ALTER SYSTEM SET ...
Tablespace ... added.
Switch logfile ...

Reading tips

Look for the immediately preceding event: Do errors follow startup, a log switch, or a tablespace addition?
Repetition matters: A warning repeating periodically hints at resource shortages or misconfiguration.

Trace file output and management

Destination:
Diag Trace (verify with V$DIAG_INFO).
Common names:
*_ora_<pid>.trc (per-process), *_ora_<pid>.trm (metadata).
Search with ADRCI
adrci ADRCI> set homepath diag/rdbms/ORCL/ORCL ADRCI> show tracefile -t "last" -- Quickly check recent traces
Sizing/retention (automatic/manual)
- Automatic policy:
  SET CONTROL (size and retention policies)
- Manual purge (units are minutes):
  ADRCI> purge -age 10080 -type ALERT -- Targets alert log related items older than 7 days
  ADRCI> purge -age 43200 -type TRACE -- For traces older than 30 days
- If you also use Linux log rotation, make sure operations agree on the scheme and recovery procedures are documented.

Ready-to-use daily health checks

# 1) Look for recent critical errors (ORA-00600/07445)
adrci exec="set homepath diag/rdbms/ORCL/ORCL; show alert -p \"message_text like '%ORA-600%' or message_text like '%ORA-7445%'\" -term"

# 2) Get a quick count of ORA- messages over the last 24 hours (SQL)
sqlplus -s / as sysdba <<'SQL'
SET PAGES 100 LINES 200
SELECT TO_CHAR(originating_timestamp, 'YYYY-MM-DD HH24:MI') AS ts,
       COUNT(*) AS cnt
FROM   v$diag_alert_ext
WHERE  message_text LIKE 'ORA-%'
AND    originating_timestamp > SYSTIMESTAMP - INTERVAL '1' DAY
GROUP  BY TO_CHAR(originating_timestamp, 'YYYY-MM-DD HH24:MI')
ORDER  BY ts;
SQL

Explanation

(1) Quickly tells you if severe internal errors occurred.
(2) Visualizes time-of-day clustering—useful for batch windows and traffic peaks.

Wrap-up (how to read in the real world)

First, locate it precisely (V$DIAG_INFO / directory layout).
Reading order: change point → error → surrounding context.
Combine ADRCI / SQL / OS methods and share repeatable procedures.
For housekeeping, combine policy (automatic) and scheduled purge (manual).

This article targets Oracle Database 19c (other versions may differ in screens or defaults).

[reference]
Oracle Database Database Error Messages, 19c