What is the Oracle REDO Log? Complete Guide with Diagrams: Mechanism, Role, and Recovery

REDO logs are critical components of the Oracle Database architecture, essential for data recovery and transaction durability. This article explains the purpose, internal structure, usage during recovery, and operational behavior of REDO logs, with textual diagrams and examples.

---

What is a REDO Log?

A REDO log is a set of files that record all changes made to the database — before they are written to the data files. This ensures that even if the database crashes, committed transactions can be recovered.

┌──────────────┐
│ User Action  │ (INSERT/UPDATE/DELETE)
└──────┬───────┘
       │
       ▼
┌──────────────┐
│   REDO Log   │ ← Change recorded here
└──────┬───────┘
       │
       ▼
┌──────────────┐
│  Data Files  │ ← Changes written later
└──────────────┘

---

Role of the REDO Log

🔁 Records Transaction Activity

Each DML operation (INSERT/UPDATE/DELETE) is recorded in the REDO log buffer, and later written to REDO log files.

💾 Ensures Durability (ACID)

Even if the database fails, the committed transactions can be recovered because the changes exist in the REDO logs.

🔧 Supports Crash Recovery

Oracle can restore the database to a consistent state by replaying REDO logs during instance or media failure.

---

REDO Log Architecture

Oracle REDO logs consist of log groups, each containing one or more log members (files).

┌─────────────┐    ┌─────────────┐
│　　　　Group 1 　　　    │    │　　　　Group 2   　　　  │
├─────┬───────┤    ├─────┬───────┤
│　log1a　 │　　log1b　   │    │　log2a　 │　　log2b 　  │  ← Multiple members 
└─────┴───────┘    └─────┴───────┘　 　(mirroring)

• Oracle writes to only one group at a time.
• When a log switch occurs, Oracle starts writing to the next group.

---

When are REDO Logs Written?

Timing	Event
COMMIT	REDO is flushed to log file
Every 3 seconds (default)	Log Writer (LGWR) flushes buffer
Buffer full	LGWR writes to redo log file

---

Recovery Using REDO Logs

In the event of failure, Oracle uses REDO logs to restore consistency.

Crash → REDO logs replayed → Committed transactions restored

If a datafile is lost but the REDO logs are intact, Oracle can restore from backup and apply REDO to bring the data current.

---

Key Views and Commands

-- Check REDO log status
SELECT group#, status, archived FROM v$log;

-- Check log file members
SELECT * FROM v$logfile;

---

Summary

• REDO logs record all database changes before writing to data files
• They are crucial for ensuring durability and enabling recovery
• Log switches and archiving are part of regular operations
• Views like v$log and v$logfile help manage REDO logs

---

[reference]
11 Managing the Redo Log
12 Managing Archived Redo Log Files