Complete Guide to the Oracle Database Alert Log

ADR_en

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.

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形式（log.xml）
            └─ trace/   ← テキスト形式：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

# 例：パスをそのまま指定（置き換えてください）
tail -f /u01/app/oracle/diag/rdbms/ORCL/ORCL/trace/alert_ORCL.log

# 直近の ORA- を10行ずつ確認
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)

-- 管理者権限（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.)

-- 直近24時間のアラートログを時系列で
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     -- 目的のホームを選択
ADRCI> show alert -tail -f                     -- 追尾表示（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表領域の情報 SELECT tablespace_name, status, contents FROM dba_tablespaces WHERE contents = 'UNDO'; -- UNDO使用状況（代表例） 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 -- 自セッションのトレースファイル位置（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使用率 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; -- 内訳（どの種別が使っているか） 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

[起動時の典型]
Starting ORACLE instance
 → Database mounted.
 → Database opened.

[構成変更例]
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" -- 直近のトレースを素早く確認
  • Sizing/retention (automatic/manual)
    • Automatic policy: SET CONTROL (size and retention policies)
    • Manual purge (units are minutes): ADRCI> purge -age 10080 -type ALERT -- 7日より古いアラートログ関連を対象 ADRCI> purge -age 43200 -type TRACE -- 30日より古いトレースを対象
    • 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

コメント

Copied title and URL