Lesson 5	Contents of Alert log
Objective	Identify the different types of content in the alert log in Oracle 23ai

Alert Log Contents

The alert log is Oracle’s primary narrative of what the database instance is doing. In Oracle 23ai it is written into the Automatic Diagnostic Repository (ADR) and captured in both a human-readable alert log and structured diagnostic metadata (incidents, trace files, dumps). When you learn to recognize common alert log “shapes,” you can diagnose most operational problems quickly—often before users even notice.

This lesson focuses on identifying and categorizing the most common classes of alert log content in Oracle 23ai. You will see routine operational entries (which are usually safe to ignore) alongside entries that signal performance pressure or genuine instability. Along the way, we’ll connect the alert log to neighboring tools you use in real DBA work: ADRCI, dynamic performance views (the V$ views), and trace/incident packaging.

Where the Oracle 23ai alert log lives

In 23ai, the alert log is managed under ADR. The exact path depends on platform and database name, but the pattern is consistent:


$ORACLE_BASE/diag/rdbms/<db_name>/<instance_name>/trace/alert_<instance_name>.log

In practice, you’ll rarely “hunt” for the file manually. You’ll usually tail it, query it via ADRCI, or let a monitoring system ingest it.

Core alert log content types you should recognize

Redo log switches and checkpoint pressure
Redo log switching is routine: as redo fills, Oracle advances to the next log group. It becomes noteworthy when a switch is delayed by checkpoint lag (dirty buffers not flushed fast enough). The alert log tells you when redo allocation is blocked, and it often points to storage throughput, redo sizing, or checkpoint tuning.
Checkpoint and archive activity
Log switches are frequently followed by archiving actions (especially in ARCHIVELOG mode). These lines are useful in recovery investigations: they help you correlate sequences, threads, and timelines when you’re restoring or validating backups.
Startup and shutdown lifecycle events
Clean startup/shutdown entries are expected during maintenance. Unexpected shutdowns or repeated startup sequences, however, can be a major signal: instance crashes, host reboots, watchdog restarts, or configuration drift.
Crash/instance recovery
After an abnormal termination, Oracle performs instance recovery (rolling forward committed work from redo and rolling back uncommitted work). The alert log provides a high-level summary that’s extremely helpful when you need to distinguish “a one-off reboot” from “a recurring stability problem.”
Physical structure and configuration changes
Structural changes—tablespaces, datafiles, redo logs, control file updates, parameter changes—are recorded in the alert log. These entries become valuable during recovery and audit work because they establish “what changed and when,” which is exactly what you need when a restore does not behave as expected.
Errors, warnings, incidents, and trace references
Some errors are routine and self-contained; others are urgent. In 23ai, internal errors (for example ORA-00600) typically create incidents and point you to trace files under ADR. These are the entries you do not hand-wave away—especially if they repeat.

How DBAs actually work with the alert log

Tail the most recent activity: you’re usually looking for the few lines immediately before an error, a log switch stall, a shutdown, or a recovery event.
Correlate with time: “a user reported a failure at 14:37” is enough to focus you on a narrow window.
Pivot from the alert log to detail: the alert log tells you what category of issue occurred; the trace file, incident, and V$ views tell you why.

Examples of common alert log entry types (Oracle 23ai style)

The examples below illustrate the most common alert log entry types you will see in Oracle 23ai. Most entries are routine; what matters is learning which patterns indicate normal operation versus performance pressure or instability.


2026-02-04T11:02:19.441832+00:00
Thread 1 cannot allocate new log, sequence 28416
Checkpoint not complete

2026-02-04T11:02:21.918442+00:00
Thread 1 advanced to log sequence 28413
  Current log group: 1
  Members:
    /u01/oradata/coin/redo01a.log
    /u01/oradata/coin/redo01b.log

2026-02-04T11:04:37.552118+00:00
Thread 1 advanced to log sequence 28414
  Current log group: 2
  Members:
    /u01/oradata/coin/redo02a.log
    /u01/oradata/coin/redo02b.log

2026-02-04T11:07:01.220419+00:00
Thread 1 advanced to log sequence 28415
  Current log group: 3
  Members:
    /u01/oradata/coin/redo03a.log
    /u01/oradata/coin/redo03b.log

Redo log switches and checkpoint pressure. Log switches are routine, but “cannot allocate new log / checkpoint not complete” indicates Oracle is waiting for dirty buffers to be flushed before it can reuse or advance redo. If this pattern repeats, treat it as a performance signal (redo sizing, number of log groups, and I/O throughput are common levers).


2026-02-04T11:24:51.317882+00:00
Thread 1 cannot allocate new log, sequence 29412
Checkpoint not complete

2026-02-04T11:24:53.902114+00:00
Checkpoint lag detected
Waiting for DBWR to flush dirty buffers

Redo Thread 1 advanced to log sequence 29411
  Current log group: 3
  Members:
    /u01/oradata/coin/redo03a.log
    /u01/oradata/coin/redo03b.log

Fast-start checkpoint initiated
Target MTTR: 300 seconds

2026-02-04T11:25:07.441009+00:00
Checkpoint completed
Redo allocation resumed

Checkpoint backlog with fast-start checkpoint behavior. This is the “expanded” version of checkpoint pressure: the alert log explicitly shows lag and a fast-start checkpoint attempt. Frequent occurrences suggest redo logs are switching too quickly or storage cannot keep up with DBWR flush rates.


2026-02-04T09:17:12.441318+00:00
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - Production
Version 23.3.0.24.01

Operating System: Linux x86_64
Kernel Version: 5.15.0-105-generic
CPU Architecture: x86_64
CPU Cores: 8

Starting ORACLE instance (normal)

System parameters with non-default values:
  processes                 = 300
  sga_target                = 4G
  pga_aggregate_target      = 1G
  shared_pool_size          = 1G
  db_block_size             = 8192
  compatible                = '23.0.0'

Memory Target enabled:
  SGA Target                = 4G
  PGA Target                = 1G

Background process startup initiated
PMON (Process Monitor) started with pid=4121
SMON (System Monitor) started with pid=4123
DBW0 (Database Writer) started with pid=4125
LGWR (Log Writer) started with pid=4127
CKPT (Checkpoint) started with pid=4129
MMON (Manageability Monitor) started with pid=4131
MMNL (Manageability Monitor Light) started with pid=4133
ARC0 (Archiver) started with pid=4135

Database mounted in EXCLUSIVE mode
Database opened

Completed: ALTER DATABASE OPEN

Startup lifecycle entries. Startup messages identify the database version, platform context, non-default parameters, and background process activation. These entries are routine during maintenance windows; unexpected startup sequences can indicate instance crashes or host restarts.


2026-02-04T09:42:18.903112+00:00
Beginning crash recovery of 1 redo thread(s)

Instance recovery initiated
Redo Thread 1 enabled
Rolling forward using online redo logs

Recovering redo log:
  Thread 1
  Group 2
  Sequence 15634

Reading redo members:
  Member 0: /u01/oradata/coin/redo02a.log
  Member 1: /u01/oradata/coin/redo02b.log

Checkpoint completed
Crash recovery completed successfully

Crash/instance recovery summary. After an abnormal shutdown, Oracle rolls forward changes from redo and brings the database back to a consistent state. These entries are expected after a crash; repeated recovery events are a strong signal that you should investigate stability (OS, storage, memory pressure, patches, or application behavior).


2026-02-04T10:03:27.114902+00:00
ALTER DATABASE: structural change detected

DDL execution initiated
CREATE TABLESPACE users
  DATAFILE '/u01/oradata/coin/users01.dbf'
  SIZE 5M
  AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED

Redo Thread 1 advanced to sequence 28491
  Current log group: 1
  Members:
    /u01/oradata/coin/redo01a.log
    /u01/oradata/coin/redo01b.log

Tablespace users successfully created
Completed: CREATE TABLESPACE users

Database structure change messages. Physical changes (tablespaces, datafiles, redo, control file updates) are recorded in the alert log and become invaluable during recovery and audit work. This example shows a tablespace creation with an associated redo advance.


2026-02-04T10:21:44.672381+00:00
Errors in file /u01/app/oracle/diag/rdbms/coin/coin1/trace/coin1_ora_40821.trc:

ORA-05004: error occurred at recursive SQL level 1
ORA-00984: column not allowed here
ORA-06512: at line 2

2026-02-04T10:21:47.019553+00:00
Errors in file /u01/app/oracle/diag/rdbms/coin/coin1/trace/coin1_ora_40829.trc:

ORA-00600: internal error code, arguments:
  [25499], [1], [147], [], [], [], []

Incident ID: 483921

Error messages and trace/incident references. The alert log often provides the first indicator of an error and points you to trace files under ADR. Standard ORA errors should be reviewed for impact and recurrence. Internal errors (for example ORA-00600) typically create incidents and warrant deeper analysis (and often Oracle Support) if repeatable.

Many messages recorded in the alert log are routine and do not require special action. Routine entries (log switches, successful checkpoints, background process chatter) help establish timelines and can support recovery investigations. Errors should always be reviewed—especially internal errors (such as ORA-00600) and any error pattern that repeats.

Handling Alert Log Errors

In day-to-day DBA work, you do not always chase every single one-time alert log message. A practical approach is to prioritize patterns: does the error repeat, does it correlate with user impact, and does it coincide with other signs of instability (restarts, recovery activity, I/O stalls)?

If a message appears once and never returns, you may document it and move on. If it repeats—or if users report problems—use the alert log to narrow the time window and then pivot into deeper diagnostics: associated trace files under ADR, incident details, and supporting V$ views.

A common real-world scenario is a developer remembering “something failed around mid-afternoon” without remembering the error code. The alert log gives you a reliable timeline: you can search around that time and identify what happened, which session activity it correlates with, and what component (SQL parsing, I/O, memory, recovery, archiving) is involved.

In the next lesson, you will practice housekeeping tasks by deleting and renaming old alert log entries as part of your monitoring workflow.

Practical tools for parsing the alert log (Oracle 23ai)

Tail the log: use standard OS tools for quick triage (for example, tail -f on Linux).
Filter by pattern: search for ORA-, Incident, Beginning crash recovery, checkpoint not complete, and version/startup markers.

Use ADRCI for structured access:


adrci> show alert -tail 100
adrci> show incident
adrci> ips create package incident 483921

By learning to categorize alert log content, you can diagnose problems faster, reduce downtime, and build the operational habit of correlating symptoms (errors, stalls, restarts) with concrete evidence in Oracle 23ai diagnostics.