Oracle Undo vs Redo Explained - and How to Actually Fix ORA-01555

Ask ten database people the difference between undo and redo and you will get some confident wrong answers, because the two sound similar and both involve "recovering" something. Yet this pair of mechanisms is the heart of how Oracle stays consistent and survives crashes - and misunderstanding them is why the classic ORA-01555 "snapshot too old" error keeps haunting long-running reports decade after decade. This guide explains both mechanisms in plain language, shows how read consistency really works, and gives you a practical, non-guesswork method to size undo and make ORA-01555 go away.

1. Two Different Jobs: Protect the Past vs Protect the Future

The cleanest way to keep undo and redo straight is by the question each answers.

Redo answers: "how do I re-do committed work after a failure?" Every change Oracle makes - to tables, indexes, even to undo itself - generates redo records, written sequentially to the online redo logs by LGWR. If the server dies, Oracle replays redo at startup to bring the datafiles forward to the moment of the crash. Redo exists so committed transactions are never lost.

Undo answers: "how do I reverse a change, or see data as it used to be?" Before Oracle modifies a row, it copies the old values into an undo segment. That old image serves two masters: a transaction that rolls back, and - far more often - other sessions that need to read the row as it looked before an uncommitted or recent change.

A memorable summary I give juniors: redo replays history, undo rewinds it. Redo is written for the crash you hope never happens; undo is used constantly, every second, by ordinary queries.

2. Read Consistency: Undo's Real Day Job

Oracle's crown jewel is that readers never block writers, and writers never block readers. The mechanism is multi-version read consistency, and it runs on undo.

When your query starts at 10:00:00, Oracle promises it a view of the database exactly as of 10:00:00. If another session updates a row at 10:00:05 - even commits it - your query must NOT see that new value. So when your query reaches that block, Oracle notices the block changed after your query began, goes to undo, and reconstructs the 10:00:00 version of the block just for you. This is called a consistent read.

Now the crucial detail: undo segments are a circular buffer. Once a transaction commits, its undo is no longer required and becomes eligible to be overwritten when space is needed. Oracle keeps it around as long as it can - governed by UNDO_RETENTION - but under pressure, old undo gets recycled.

3. So What Exactly Is ORA-01555?

Put the two facts together. A long-running query needs old block images from undo. Undo gets recycled over time. If your query runs for 45 minutes and, at minute 40, needs an undo record that was overwritten at minute 25 - Oracle cannot build the consistent picture it promised. Rather than silently give you wrong data, it raises:

ORA-01555: snapshot too old: rollback segment number ... too small

Three things follow from understanding this. First, ORA-01555 is not corruption and not a bug - nothing is damaged. Second, the error hits the reader (usually a long report or export), but the cause is the combination of reader duration and writer activity recycling undo. Third, the fix is about retention: keeping undo long enough for your longest reader.

A special variant worth knowing: delayed block cleanout. A block changed by a huge committed transaction may still carry old lock information; the next reader must consult undo to confirm the commit, and if that undo is gone, ORA-01555 can appear even though "nothing was running." Rare, but it explains the mystery cases after batch loads.

4. Diagnosing with V$UNDOSTAT - the Honest Witness

Everything you need to know is recorded, in ten-minute buckets, in V$UNDOSTAT:

-- How much undo is being generated, the longest query, and 1555s per bucket
SELECT TO_CHAR(begin_time,'DD-MON HH24:MI') AS began,
       undoblks                      AS undo_blocks,
       txncount                      AS txns,
       maxquerylen                   AS longest_query_sec,
       ssolderrcnt                   AS ora_1555_count,
       nospaceerrcnt                 AS space_errors,
       tuned_undoretention           AS auto_tuned_retention
FROM   v$undostat
ORDER  BY begin_time DESC FETCH FIRST 24 ROWS ONLY;

-- Current settings and undo tablespace size
SHOW PARAMETER undo
SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024/1024,1) AS gb
FROM   dba_data_files WHERE tablespace_name LIKE 'UNDO%'
GROUP  BY tablespace_name;

Two columns decide your fix. MAXQUERYLEN is the longest-running query Oracle observed (in seconds) - your retention must exceed this. SSOLDERRCNT counts actual ORA-01555s, so you can see exactly when they cluster (usually during the nightly batch window, when heavy writes recycle undo underneath long reports).

5. The Sizing Method (No Guessing)

Undo sizing is arithmetic, not art. The space needed is roughly:

undo space = (undo blocks generated per second) x (retention seconds) x block size

-- Get your real undo generation rate from history:
SELECT ROUND(MAX(undoblks/((end_time-begin_time)*86400))) AS peak_undo_blocks_per_sec,
       ROUND(AVG(undoblks/((end_time-begin_time)*86400))) AS avg_undo_blocks_per_sec
FROM   v$undostat;

1. Find your longest query - MAX(MAXQUERYLEN) from V$UNDOSTAT. Say it is 5,400 seconds (90 minutes).
2. Set retention above it - with headroom: ALTER SYSTEM SET undo_retention = 7200; (2 hours).
3. Size the tablespace to honour it - peak rate x retention x block size. For example, 300 blocks/sec x 7,200 sec x 8 KB ≈ 17 GB. If the undo tablespace is smaller than the arithmetic demands, retention is a wish, not a setting - Oracle will steal expired undo early and 1555s continue.

With UNDO_MANAGEMENT=AUTO (the default for two decades), Oracle also auto-tunes retention upward when space allows - the TUNED_UNDORETENTION column shows what it actually achieved. Your job is mainly to give it enough disk to work with.

6. RETENTION GUARANTEE: the Strong Option, With Teeth

By default, retention is a target, not a promise: under space pressure Oracle will sacrifice unexpired undo to let writers proceed. If your business genuinely cannot tolerate ORA-01555 - say, a regulated end-of-day report - you can make retention a hard guarantee:

ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;
-- confirm
SELECT tablespace_name, retention FROM dba_tablespaces
WHERE  tablespace_name LIKE 'UNDO%';

Understand the trade you just made: if undo space runs out, Oracle will now fail writers (ORA-30036) rather than break the retention promise to readers. A guarantee without a properly sized tablespace converts report errors into transaction errors - strictly worse. Guarantee only after the sizing arithmetic is honoured, and monitor free undo space as a first-class alert.

7. Fixes on the Query Side

Sometimes the cheapest fix is not more undo but a shorter exposure window.

• Speed up the reader. A report tuned from 90 minutes to 12 needs seven times less retention. Often the 1555 is really a tuning problem wearing a disguise - the methodology in the performance tuning guide applies directly.
• Schedule around the writers. Move the long report out of the heavy batch window, or the batch out of the reporting window. Half the 1555s I have fixed were calendar fixes.
• Stop committing inside loops. The old "fetch across commit" pattern - a cursor loop that commits every N rows while still fetching - practically manufactures ORA-01555 against its own undo. Restructure to commit at the end, or process in independent chunks.
• For huge extracts, consider flashback-consistent methods or Data Pump with a consistent snapshot early, rather than a 3-hour open cursor - see the Data Pump guide.

8. And Redo? The Other Half in Two Minutes

Since we defined both: your redo health checklist is short but non-negotiable. Redo logs must be sized so groups switch roughly every 15-30 minutes at peak (constant switching means undersized logs and checkpoint pressure - check V$LOG_HISTORY). Never run production without ARCHIVELOG mode, because archived redo is what point-in-time recovery replays - the foundation of everything in the RMAN guide. And log file sync waits at commit time usually mean slow redo storage or an application committing far too often.

Undo, for its part, is also what powers Flashback Query and Flashback Table - one more reason generous undo sizing pays for itself beyond just killing 1555s.

9. A Real Case: the Month-End Report That Always Died at 90 Minutes

A manufacturing client had a month-end costing report that failed with ORA-01555 like clockwork - but only on the nights the inventory revaluation batch also ran. V$UNDOSTAT told the whole story in one query: MAXQUERYLEN around 5,800 seconds, retention set to 900 (the old default), and SSOLDERRCNT spiking exactly in the revaluation window, when undo generation jumped tenfold and recycled everything within minutes.

The fix was threefold: retention raised to 7,200; the undo tablespace grown from 4 GB to 20 GB (the arithmetic demanded 17); and the report re-scheduled to start after the revaluation finished rather than alongside it. The error never returned, and as a bonus the bigger undo pool extended the usable Flashback Query window - which the same client later used to recover from a bad UPDATE without a restore. One diagnosis, two problems solved.

Frequently Asked Questions

What is the difference between undo and redo in Oracle?

Redo records every change so committed work can be replayed after a crash - it protects the database going forward. Undo records the old values before each change so transactions can roll back and queries can see consistent old versions of data - it rewinds. Redo replays history; undo rewinds it.

What causes ORA-01555 snapshot too old?

A long-running query needed the old image of a changed block, but the undo holding that old image had already been overwritten. It typically happens when long reports run concurrently with heavy write activity and UNDO_RETENTION or the undo tablespace is too small for the combination. Nothing is corrupted.

How do I fix ORA-01555?

Measure first: V$UNDOSTAT shows your longest query (MAXQUERYLEN) and when the errors occur (SSOLDERRCNT). Then set UNDO_RETENTION above the longest query with headroom, size the undo tablespace to honour it (undo rate x retention x block size), and where possible shorten the query or move it away from heavy write windows.

What does UNDO_RETENTION actually do?

It is the target time, in seconds, that Oracle tries to keep committed undo available for consistent reads and Flashback. By default it is a target, not a promise - under space pressure Oracle recycles unexpired undo. With RETENTION GUARANTEE on the undo tablespace it becomes a hard promise, at the cost that writers can fail when space runs out.

Should I enable RETENTION GUARANTEE?

Only after sizing the undo tablespace properly. Guarantee means Oracle will fail new transactions (ORA-30036) rather than break retention, so on an undersized tablespace it converts report errors into transaction failures. Use it when a critical long-running process genuinely cannot tolerate ORA-01555, and monitor undo free space closely.