ORACLE DATABASE ARCHITECTURE — BUFFER CACHE AND REDO LOG BUFFER


12. Database Buffer Cache

The Database Buffer Cache is one of the most important components of the Oracle SGA.

It is a memory area that stores copies of data blocks read from datafiles. Oracle uses the buffer cache to reduce physical I/O because accessing memory is much faster than repeatedly reading blocks from disk.

                  Oracle Session
                       |
                       v
                  SQL Statement
                       |
                       v
              Database Buffer Cache
                  /           \
                 /             \
          Block Found       Block Not Found
             (Hit)             (Miss)
               |                 |
               v                 v
         Read from Cache    Read from Datafile
                                 |
                                 v
                         Buffer Cache
How Buffer Cache Works

Suppose we execute:

SQL
SELECT * FROM employees;

Oracle first needs the required data blocks.

The simplified process is:

SQL Query
   |
   v
Check Buffer Cache
   |
   +---- Block available? ---- YES ----> Read from Memory
   |
   NO
   |
   v
Read Block from Datafile
   |
   v
Place Block into Buffer Cache
   |
   v
Return Data

If the required block is already in memory, Oracle can avoid a physical disk read.

Logical Read vs Physical Read

This is an important concept for Oracle DBAs.

Logical Read — A logical read occurs when Oracle reads a block from the buffer cache.

Buffer Cache → Oracle Session

Physical Read — A physical read occurs when Oracle has to read the block from a datafile because the required block is not available in the buffer cache.

Datafile → Buffer Cache → Oracle Session
📌 Remember: Logical read = memory access. Physical read = disk/storage access.

Reducing unnecessary physical I/O is an important part of database performance tuning.


Buffer Cache Block States

Database blocks in the buffer cache can be in different states.

1. Free Buffer

A free buffer is available to hold a new database block.

FREE
 |
 v
Can be reused

Oracle can use this buffer when a new block needs to be loaded into memory.

2. Clean Buffer

A clean buffer contains a copy of a database block that has not been modified since it was read from the datafile.

Example:

Datafile Block
     |
     v
Buffer Cache
     |
   CLEAN

The contents in memory are consistent with the corresponding block in the datafile.

3. Dirty Buffer

A dirty buffer contains a database block that has been modified in memory but the modified block has not yet been written to the datafile.

Example:

Datafile
   |
   v
Buffer Cache
   |
   v
UPDATE
   |
   v
DIRTY BUFFER

For example:

SQL
UPDATE employees
SET salary = salary + 500
WHERE employee_id = 101;

Oracle modifies the block in the buffer cache. The corresponding datafile block may still contain the old value. That buffer is therefore called a dirty buffer. Later, DBWn writes the modified block to the datafile.

4. Pinned Buffer

A pinned buffer is a buffer that is currently being accessed and cannot be immediately reused.

Buffer
  |
  v
Currently in use
  |
  v
PINNED

This prevents Oracle from replacing a buffer while it is actively being used.


Database Buffer Cache — Write Process

A common misconception is:

⚠️ Misconception: "Whenever a transaction performs UPDATE, Oracle immediately writes the changed block to the datafile." This is not correct.

Oracle normally changes the block in the buffer cache first. For example:

SQL
UPDATE employees
SET salary = salary + 1000
WHERE employee_id = 101;

The simplified flow is:

UPDATE
  |
  v
Find block
  |
  v
Buffer Cache
  |
  v
Modify block
  |
  v
Dirty Buffer
  |
  v
DBWn
  |
  v
Datafile

The DBWn process writes dirty buffers to the datafiles when required by Oracle's internal write conditions.

Buffer Cache and LRU

Oracle uses algorithms to manage the buffer cache and determine which buffers are suitable for reuse.

Historically, the concept is commonly explained using LRU — Least Recently Used.

💡 Basic idea: Buffers that have not been used recently are better candidates for replacement than frequently used buffers.

Modern Oracle versions use more sophisticated buffer management algorithms, so LRU is best understood as a conceptual model rather than the complete implementation.

Buffer Cache and Full Table Scan

Suppose a table is larger than the available buffer cache. Oracle may need to read many blocks from the datafiles.

Large Table
    |
    v
Datafiles
    |
    v
Physical Reads
    |
    v
Buffer Cache

Whether a full table scan is efficient depends on many factors, including table size, selectivity, storage performance, optimizer decisions, and caching behavior.

📌 Key point: A full table scan is not automatically a bad thing.

Useful Buffer Cache Views

Oracle DBAs can use dynamic performance views to investigate buffer cache activity.

SQL
SELECT name, block_size, current_size, buffers
FROM v$buffer_pool;

You can also check instance-level statistics:

SQL
SELECT name, value
FROM v$sysstat
WHERE name IN (
    'db block gets',
    'consistent gets',
    'physical reads'
);

These statistics can help in understanding logical and physical I/O activity.


13. Redo Log Buffer

The Redo Log Buffer is an SGA memory structure that temporarily stores redo entries generated by database changes.

Redo is one of the most important concepts in Oracle because it allows Oracle to recover database changes after failures.

                   SQL Operation
                        |
              +---------+---------+
              |         |         |
            INSERT    UPDATE    DELETE
              |         |         |
              +---------+---------+
                        |
                        v
                   REDO ENTRY
                        |
                        v
                REDO LOG BUFFER
                        |
                        v
                      LGWR
                        |
                        v
              ONLINE REDO LOG FILES
What is Redo?

Redo is information that Oracle generates to describe changes made to the database. For example:

SQL
UPDATE employees
SET salary = 50000
WHERE employee_id = 101;

Oracle generates redo information associated with this change. The redo information helps Oracle reproduce changes during recovery.

Redo Entries

Changes generated by database operations are recorded as redo entries.

Common operations that generate redo include:

  • INSERT
  • UPDATE
  • DELETE
  • DDL operations
  • Index modifications
  • Other database changes

Redo is generated even when the corresponding database block is currently in the buffer cache.

Redo Log Buffer Flow

The simplified architecture is:

                  User Process
                       |
                       v
                 Server Process
                       |
                       v
               Database Change
                       |
              +--------+--------+
              |                 |
              v                 v
        Buffer Cache        Redo Entry
              |                 |
              v                 v
       Dirty Buffer       Redo Log Buffer
              |                 |
              |                 v
              |               LGWR
              |                 |
              |                 v
              |        Online Redo Log
              |
              v
             DBWn
              |
              v
           Datafile
📌 Key principle: Redo and database blocks are managed separately.

Redo Log Buffer and COMMIT

One of the most important DBA concepts is what happens when a transaction issues:

SQL
COMMIT;

Oracle must make the transaction's redo durable according to the configured commit behavior.

The simplified flow is:

DML
 |
 v
Redo generated
 |
 v
Redo Log Buffer
 |
 v
COMMIT
 |
 v
LGWR writes redo
 |
 v
Online Redo Log
 |
 v
COMMIT acknowledged
💡 Important point: LGWR writes the necessary redo to the online redo log before Oracle acknowledges a normal commit to the user, subject to Oracle's configured commit semantics.

Why Doesn't LGWR Wait for DBWn?

This is a very common Oracle DBA interview question. Suppose:

SQL
UPDATE employees
SET salary = salary + 1000
WHERE employee_id = 101;

COMMIT;

The changed data block may still be in the buffer cache. Oracle does not need to wait for DBWn to write that data block to the datafile before completing the normal commit. Instead, LGWR writes the required redo to the online redo log.

            COMMIT
               |
               v
         Redo Log Buffer
               |
               v
              LGWR
               |
               v
        Online Redo Log
               |
               v
          COMMIT ACK

Later:

Dirty Buffer
     |
     v
    DBWn
     |
     v
 Datafile

This design allows Oracle to provide transaction durability without requiring every changed data block to be immediately written to the datafile.

Write-Ahead Logging Principle

Oracle follows a fundamental recovery principle commonly described as write-ahead logging.

📌 The idea: The redo describing a change must be safely written to the redo log before the corresponding modified data block is required to be written to the datafile.

This is one of the key reasons Oracle can recover committed transactions after an instance failure.

LGWR — Log Writer

LGWR is the Oracle background process responsible for writing redo entries from the redo log buffer to the online redo log files.

LGWR can write redo under several circumstances, including:

  • When a transaction commits
  • When the redo buffer needs to be flushed
  • Periodically
  • Before DBWn writes certain modified blocks to datafiles
  • During other internal database operations

Simplified flow:

Redo Log Buffer
       |
       v
      LGWR
       |
       v
Online Redo Log Group

Redo Log Buffer vs Database Buffer Cache

These two memory areas are often confused.

Database Buffer CacheRedo Log Buffer
Stores database blocksStores redo entries
Contains copies of data blocksContains information describing changes
Used for reading and modifying database blocksUsed for recovery information
DBWn eventually writes dirty blocks to datafilesLGWR writes redo to online redo logs
Part of SGAPart of SGA
📌 A simple way to remember: Buffer Cache = Data Blocks. Redo Log Buffer = Change Information.

DBWn vs LGWR

This is one of the most important Oracle DBA interview questions.

DBWnLGWR
Database WriterLog Writer
Writes dirty buffersWrites redo entries
Writes to datafilesWrites to online redo logs
Works with Database Buffer CacheWorks with Redo Log Buffer
Writes database blocksWrites recovery information
             ORACLE SGA
                 |
       +---------+---------+
       |                   |
       v                   v
 Buffer Cache        Redo Log Buffer
       |                   |
       v                   v
     DBWn                LGWR
       |                   |
       v                   v
   Datafiles         Online Redo Logs

Important Interview Scenario

Question

A user executes:

SQL
UPDATE employees
SET salary = 60000
WHERE employee_id = 101;

COMMIT;

What happens internally?

Simplified Answer
1. Oracle finds the required block.
             |
             v
2. Block is loaded into Buffer Cache if required.
             |
             v
3. Oracle modifies the block in memory.
             |
             v
4. The block becomes DIRTY.
             |
             v
5. Oracle generates REDO.
             |
             v
6. Redo is placed in Redo Log Buffer.
             |
             v
7. User issues COMMIT.
             |
             v
8. LGWR writes required redo to Online Redo Log.
             |
             v
9. COMMIT is acknowledged.
             |
             v
10. DBWn later writes the dirty block to Datafile.

This is the basic relationship between: Buffer Cache + Redo Log Buffer + LGWR + DBWn + Datafiles + Online Redo Logs.

Easy Way to Remember Oracle Memory

SGA
 |
 +-- Shared Pool
 |      |
 |      +-- Library Cache
 |      +-- Data Dictionary Cache
 |      +-- Result Cache
 |
 +-- Database Buffer Cache
 |      |
 |      +-- Data Blocks
 |      +-- Clean Buffers
 |      +-- Dirty Buffers
 |
 +-- Redo Log Buffer
 |      |
 |      +-- Redo Entries
 |
 +-- Large Pool
 |
 +-- Java Pool
 |
 +-- Streams Pool

And the two most important write paths are:

DATA PATH
=========
Buffer Cache
     |
     v
   DBWn
     |
     v
 Datafiles


REDO PATH
=========
Redo Log Buffer
     |
     v
    LGWR
     |
     v
Online Redo Logs
📌 Key DBA Takeaway:
  • DBWn protects the database blocks by writing modified buffers to datafiles.
  • LGWR protects transaction durability by writing redo information to online redo logs.
Both processes work together, but they have different responsibilities and different destinations.