Oracle Data Manipulation Internals: Undo, Redo, and Archiving

One of the things that makes Oracle's concurrency model so powerful — and so worth understanding — is how it uses two completely separate mechanisms working together: undo data and redo data. They're often mentioned in the same breath, and they're easy to conflate, but they serve opposite purposes. Once you understand each one on its own terms, the bigger picture of Oracle's consistency and recovery model becomes a lot clearer.

Undo and Redo: The Core Distinction

Before going deeper into either, it helps to have the contrast sharp in your mind:

| | Undo | Redo | |---|---|---| | Purpose | How to reverse a change | How to reproduce a change | | Used for | Rollback, read consistency, Flashback | Rolling forward during recovery | | Stored in | Undo segments (undo tablespace) | Redo log buffer → redo log files | | Protects against | Inconsistent reads in a multi-user system | Data loss |

Undo answers the question: what did this block look like before this transaction changed it? Redo answers the question: what changes need to be replayed to bring the database forward from a backup?

Multi-Version Concurrency Control (MVCC)

Oracle's read consistency model is built on MVCC — Multi-Version Concurrency Control — and undo data is the engine that makes it work.

When a query starts, Oracle notes the current System Change Number (SCN). As the query scans blocks, if it encounters a block that has been modified by a transaction that committed after the query began, Oracle doesn't block or return the modified version. Instead, it uses the undo data to reconstruct the version of that block as it existed at the query's SCN.

This is why in Oracle, readers never block writers and writers never block readers. The query gets a consistent view of the data as of its start time, regardless of concurrent modifications happening during its execution. The undo tablespace is what makes that time-travel possible.

Redo works in parallel with this. Every change written to a data block is also recorded in the redo log. If the instance crashes, Oracle uses redo to replay all committed changes that hadn't yet been written to datafiles — rolling the database forward. Undo then cleans up any transactions that were active at the time of the crash.

Undo Data in Detail

Where It's Stored

Undo data lives in undo segments, which are stored in a dedicated undo tablespace. Undo tablespaces have several important characteristics that set them apart from regular tablespaces:

• Used exclusively for undo segments — you can't create tables or other objects in them
• Only one undo tablespace can be the active (writable) tablespace for a given instance at any time
• Permanently locally managed — extent management is handled automatically by the database
• Associated with a single instance (relevant in RAC environments)
• Recovery of undo data can only happen when the instance is in mounted state
• In RDS Oracle, the undo tablespace is created as a bigfile tablespace

Undo segments work as circular buffers. Oracle writes new undo into the segment, and when space runs out, it can overwrite older undo — subject to retention rules.

Three Categories of Undo

At any given moment, undo data falls into one of three states:

• Active — belongs to an uncommitted transaction. Oracle will never overwrite this; doing so would break rollback.
• Unexpired — the transaction has committed, but the undo is still within the configured retention period. Oracle prefers not to overwrite this, as it supports flashback operations and long-running queries.
• Expired — committed and older than the retention period. Oracle can freely reuse this space.

Undo Retention

The UNDO_RETENTION parameter (specified in seconds) controls how long Oracle attempts to keep unexpired undo before allowing it to be overwritten. You must configure this explicitly when:

• The undo tablespace has AUTOEXTEND enabled
• You're working with LOBs that require undo retention
• You want to guarantee retention (see below)

The default is 900 seconds (15 minutes), which is often too short for environments doing Flashback operations or running long queries.

Guaranteeing Undo Retention

By default, if the undo tablespace runs low on space, Oracle can overwrite unexpired undo to make room for active transactions. This is generally acceptable — active transactions take priority — but it can cause long-running queries to fail with ORA-01555: snapshot too old, because the undo data they need to reconstruct a consistent read has been overwritten.

If your workload depends on Flashback or involves long-running queries, you can enable guaranteed retention:

ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

With this enabled, Oracle will never overwrite unexpired undo, even if it means a DML operation fails with a space error. This is a deliberate trade-off: you're choosing query consistency over write availability. It's disabled by default for good reason — misconfiguring it on an undersized undo tablespace will cause DML failures.

To check current retention mode:

SELECT tablespace_name, retention
FROM   dba_tablespaces
WHERE  contents = 'UNDO';

Managing Undo: What Goes Wrong

The two most common undo-related problems are:

ORA-01555 (Snapshot Too Old): A long-running query needed undo to reconstruct a consistent read, but the undo has been overwritten. Fix by increasing UNDO_RETENTION, sizing the undo tablespace larger, or enabling retention guarantee.

Space errors in the undo tablespace: The tablespace is full, usually because retention is set too high relative to the tablespace size, or a large transaction is generating more undo than anticipated. Fix by sizing the tablespace appropriately or adjusting retention.

Flashback Operations and Undo

The undo tablespace is what powers Oracle's Flashback features. Because Oracle retains old versions of blocks as undo data, you can query the past:

• Flashback Query — view a table as it existed at a specific SCN or timestamp
• Flashback Table — restore a table to a point in time
• DBMS_FLASHBACK — programmatic interface for flashback operations

-- Flashback Query: see the table as of 1 hour ago
SELECT * FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR);

-- Flashback Query using SCN
SELECT * FROM employees AS OF SCN 1234567;

RDS Note: On RDS Oracle, only Flashback Table, Flashback Query, and Flashback Transaction are supported. Flashback Database is not available in the managed environment.

Temporary Undo (12c+)

Oracle 12c introduced temporary undo, which stores undo for operations on temporary tables in the temp tablespace rather than the undo tablespace. Benefits:

• Reduces the volume of undo stored in the undo tablespace
• Reduces the amount of redo generated (undo writes generate redo — temporary undo bypasses this)
• Enables DML on temp tables in a physical standby database when using Active Data Guard

-- Enable for the current session
ALTER SESSION SET TEMP_UNDO_ENABLED = TRUE;

On RDS, you can enable this via a custom DB parameter group.

Redo Data and the LGWR Process

While undo handles consistency, redo handles durability. Every change made to the database — including changes to undo segments themselves — is recorded in the redo log. If the instance crashes, redo is what allows Oracle to replay committed work that hadn't yet been flushed to datafiles.

The Redo Log Buffer and LGWR

Redo is first written to the redo log buffer in the SGA — a circular buffer in memory. The LGWR (Log Writer) background process is responsible for flushing this buffer to the online redo log files on disk. LGWR writes under four conditions:

• A transaction commits
• The redo log buffer becomes one-third full
• LGWR has been idle for 3 seconds
• DBWR (Database Writer) needs to write dirty buffers to disk — LGWR must write first

The commit-triggered write is the critical one for durability. Oracle's default behavior is synchronous commit: when you commit, your session waits until LGWR confirms the redo has been written to disk before control returns. This is why redo log I/O is directly on the critical path of every transaction.

LGWR also handles:

• Writing the commit SCN record to the log file at commit time
• Updating datafile headers when a checkpoint occurs

Online Redo Log Groups

Oracle maintains multiple redo log groups, and LGWR writes to one group at a time. When the current group fills, a log switch occurs and LGWR moves to the next group. The groups operate in a circular fashion — when the last group fills, LGWR cycles back to the first.

Each group can contain multiple members (copies of the redo log file on different disks). This is called multiplexing, and Oracle strongly recommends it: if one member is lost due to a disk failure, the others continue. Losing an entire current redo log group — all members — is one of the most serious media failures Oracle can experience, because it means losing committed data that hasn't yet been written to datafiles.

RDS Note: RDS Oracle does not multiplex redo log groups. AWS manages the underlying storage redundancy at the infrastructure level instead.

Log Switches and Sequence Numbers

Every time a log switch occurs, Oracle assigns the new redo log file a unique log sequence number. This number is how Oracle tracks and orders redo log files during recovery. Each online redo log file, and each archived redo log file, is uniquely identified by its sequence number.

Key data dictionary views for redo log management:

-- Current log group status and sequence numbers
SELECT group#, sequence#, bytes/1024/1024 AS size_mb, status, archived
FROM   v$log;

-- Log switch history (useful for sizing)
SELECT first_time, sequence#, blocks * block_size / 1024 / 1024 AS size_mb
FROM   v$log_history
ORDER BY first_time DESC;

-- Archived log locations and status
SELECT name, sequence#, completion_time, archived
FROM   v$archived_log
ORDER BY sequence# DESC;

Other relevant views: V$DATABASE, V$ARCHIVE_DEST, V$ARCHIVE_PROCESSES, V$BACKUP_REDOLOG.

Sizing Redo Log Files

Getting redo log size wrong causes real performance problems. The goal is that LGWR writes continuously to a single redo log file until it's genuinely full — not switching frequently due to undersized files.

Undersized redo logs cause frequent log switches, which in turn trigger frequent checkpoints. Checkpoints generate I/O as DBWR flushes dirty buffers, and excessive checkpoint activity slows down the entire database. On high-update systems, tiny redo logs that switch every few minutes are a common hidden performance drain.

Oversized redo logs aren't really a problem for performance, but they do slow down instance recovery (more redo to replay) and can make archiving less granular.

For high-volume OLTP or systems generating significant redo before commits, redo log files in the range of tens of GB are not unusual.

RDS Note: On RDS, log switches are triggered every 5 minutes as part of the PITR (Point-in-Time Recovery) mechanism, regardless of whether the log file is full. Size your redo logs with this in mind — they should be large enough that the 5-minute switch, not a full file, is what triggers the switch in normal operation.

Tuning the Redo Log Buffer

The LOG_BUFFER parameter controls the size of the redo log buffer in the SGA. The default is usually adequate for most workloads, but you may need to increase it if you see significant redo log space request waits or if your system has high commit rates.

A common recommendation is to set LOG_BUFFER to at least 10MB on high-write systems. Increasing it reduces how frequently LGWR has to flush (because the buffer fills more slowly), but it doesn't reduce the total redo generated — it just smooths out the I/O pattern.

Log file sync waits (seen in V$SESSION_WAIT or AWR) indicate that sessions are waiting for LGWR to complete a commit write. Common causes:

• Slow disk I/O on redo log storage (put redo logs on your fastest, most dedicated storage)
• LGWR not getting enough CPU time
• Extremely high commit rates (consider batching commits where possible)
• LOG_BUFFER too small causing buffer pressure

Determining the Right Number of Redo Log Groups

Oracle requires a minimum of two redo log groups so that while LGWR is writing to one, the other is available. In practice, two groups is rarely enough — LGWR may finish writing a group and need to switch before the previous group has been archived (if in ARCHIVELOG mode) or checkpointed.

The right number of groups depends on your workload, archiving speed, and checkpoint frequency. The key test: LGWR should never have to wait because the next group isn't available. If you see log file switch (checkpoint incomplete) or log file switch (archiving needed) waits in AWR, add more groups or increase group size.

Two parameters limit the maximum configuration:

• MAXLOGFILES — maximum number of redo log groups
• MAXLOGMEMBERS — maximum number of members per group

Both are set at database creation time in the CREATE DATABASE statement and can be changed by rebuilding the controlfile.

ARCHIVELOG Mode

When LGWR needs to reuse a redo log group (because it's cycling back around), what happens to the redo data in that group depends on whether the database is in ARCHIVELOG mode.

NOARCHIVELOG mode: The filled redo log group is available for reuse as soon as its contents have been written to datafiles (checkpointed). The redo data is then gone. This simplifies operations but has serious implications:

• Only cold (offline) backups are possible
• No online tablespace backups
• Recovery from media failure is limited to restoring the last full backup and losing everything since
• No point-in-time recovery

ARCHIVELOG mode: Before a redo log group can be reused, the ARCn (Archiver) background process must copy it to the archive log destination. Only after both the checkpoint and the archive are complete can LGWR reuse the group. This means:

• Online backups (RMAN hot backups) are possible
• Full point-in-time recovery is possible
• Standby databases and Data Guard are possible
• Flashback Database is possible (though not on RDS)

The cost is storage, management overhead, and a modest performance overhead from the archive I/O.

The number of ARCn processes is controlled by LOG_ARCHIVE_MAX_PROCESSES. For busy databases that generate redo quickly, running multiple archiver processes prevents archiving from becoming a bottleneck.

-- Check current archive mode
SELECT log_mode FROM v$database;

-- Check archiver process status
SELECT process, status, log_sequence
FROM   v$archive_processes
WHERE  status != 'STOPPED';

Bringing It Together

The interplay between undo and redo is what gives Oracle its combination of high concurrency and strong durability guarantees. Redo ensures that committed changes survive instance failures by providing a complete record of every modification. Undo ensures that readers get a consistent view of data without blocking writers, by preserving old versions of changed blocks. Neither one alone is sufficient — you need both.

When things go wrong, the failure modes map cleanly back to this architecture: ORA-01555 means undo wasn't retained long enough; log file sync waits mean redo I/O is on the critical path; archiving bottlenecks mean redo is being generated faster than it can be archived. Understanding the underlying mechanisms makes these problems tractable rather than mysterious.

More Oracle internals as I work through them — next up will likely be checkpointing and the checkpoint process in more depth.