Why does LGWR write before DBWR in Oracle?
Short answer (core principle)
LGWR must write redo first to guarantee that every committed change can be recovered, even if dirty data blocks have not yet been written by DBWR.
This rule is called Write‑Ahead Logging (WAL) and it is non‑negotiable for any database that promises transactional durability.
LGWR (Log Writer): Writes redo entries from redo log buffer to online redo logs
DBWR (DB Writer): Writes dirty data blocks from buffer cache to datafiles
The problem Oracle must solve
Oracle must ensure ACID durability:
Once a user sees COMMIT successful, the change must survive:
- Instance crash
- Power failure
- OS crash
- Datafile not written yet
But Oracle uses:
- Deferred writes to datafiles (for performance)
- Memory caching of data blocks
So inevitably:
- Changed data blocks (dirty buffers) may sit in memory
- DBWR may write them minutes later
- Yet COMMIT must return immediately
This is where LGWR before DBWR becomes mandatory.
The WAL rule (the law of databases)
Redo describing a change must reach disk before the data blocks containing that change are allowed to be written.
Oracle enforces:
Redo on disk ⇒ Data block may be written
Redo not on disk ⇒ Data block must NOT be written
This means:
- LGWR always precedes DBWR in the timeline of durability
Internal sequence (step by step)
1. User modifies data
Internally:
- Data block modified in buffer cache
- Undo generated in undo segment
- Redo generated for:
- Data change
- Undo change
💡 Redo goes to redo log buffer (SGA)
2. User issues COMMIT
On COMMIT:
- LGWR is signaled
- LGWR writes:
- All redo entries up to the commit SCN
- Including the COMMIT record itself
Only after redo is safely written to disk:
Commit complete
✅ Durability now guaranteed
❌ Data block may still be only in memory
3. DBWR writes later (asynchronously)
DBWR writes dirty blocks due to:
- Checkpoints
- Buffer pressure
- Tablespace going offline
- Clean shutdown
Before DBWR can write a block:
- Oracle checks: Has corresponding redo been flushed?
- If not → LGWR is forced first
This is called a redo write barrier.
Why Oracle does NOT let DBWR write first
Scenario if DBWR wrote first (dangerous)
- DBWR writes dirty block to datafile
- LGWR has not flushed redo yet
- Instance crashes
After crash:
- Datafile shows new data
- Redo logs do not contain the change
➡️ Database corruption ➡️ Inconsistent datafiles ➡️ Recovery impossible
💥 This violates atomicity and durability
How Oracle enforces LGWR-first internally
1. Redo SCN tracking
Each dirty buffer tracks:
- Lowest redo SCN required
- DBWR checks redo availability before writing
2. Checkpoint mechanism
During checkpoint:
- CKPT signals:
- LGWR to flush redo up to checkpoint SCN
- DBWR to write all buffers <= that SCN
Sequence:
Crash recovery depends on this ordering
After instance crash
Oracle performs:
- Roll Forward
- Reads redo logs
- Reapplies changes to data blocks
- Roll Back
- Uses undo for uncommitted transactions
This only works if:
- Redo always exists on disk
- Even if data blocks were never written
Thus:
Redo is the source of truth after a crash, not datafiles
Performance benefit (secondary but important)
LGWR:
- Writes sequentially
- Very fast
- Small I/O
DBWR:
- Writes random blocks
- Expensive I/O
Letting LGWR commit fast while DBWR is deferred:
- Improves throughput
- Reduces IO contention
- Scales better on busy systems
Analogy (sticky note vs filing cabinet)
- Redo log = sticky note journal
- Datafiles = filing cabinet
Rule:
Write the sticky note first, then reorganize the cabinet later
If the office burns:
- Sticky notes let you reconstruct
- Cabinets alone mean lost intent
Key guarantees achieved
| Guarantee | Achieved by LGWR-before-DBWR |
|---|---|
| Durability | ✅ |
| Atomicity | ✅ |
| Crash recovery | ✅ |
| Fast commits | ✅ |
| Deferred writes | ✅ |
One-line takeaway (exam / interview gold)
LGWR writes before DBWR because redo must be safely on disk before any data block changes can be permanently stored, ensuring crash recovery and transactional durability under Oracle’s Write-Ahead Logging protocol.
No comments:
Post a Comment