Oracle Capacity Planning: How to Size UNDO, TEMP, Redo and Backup Storage

Almost every "the database is down" call I have taken at an unsociable hour traced back to space that nobody sized on purpose. An archived-redo destination filled and the archiver stalled; an undo tablespace was too small for a month-end report; a TEMP tablespace could not hold a quarter-end sort; a backup area ran out and jobs started failing. None of these were exotic bugs. They were capacity decisions made by default instead of on purpose. This guide gives you a practical, arithmetic-first way to size the parts of an Oracle database that actually run out — UNDO, TEMP, redo and archived redo, and RMAN backup / Fast Recovery Area — plus how to forecast datafile growth and set the monitoring that means you are never surprised again.

1. Why Capacity Planning Is a Reliability Problem, Not a Storage Chore

It is tempting to treat sizing as a one-time procurement question — "how much disk do we buy?" — and then forget it. But in Oracle the components that fill up do not fail politely. When the archived-redo destination fills, the database does not slow down; it stops, because Oracle refuses to overwrite an online redo log that has not been archived. When UNDO cannot honour retention, long reports die with ORA-01555. When TEMP is exhausted, a sort fails with ORA-01652 and the query — often an important one — simply cannot complete. Each of these is a reliability incident dressed up as a storage number.

That is why I treat capacity as part of availability engineering, alongside RAC and Data Guard. The good news: unlike many availability problems, capacity is highly predictable. You can measure the inputs, apply a formula, add headroom, and monitor the trend. Done once properly, it converts the worst kind of outage — the silent, slow-building one — into a routine, scheduled purchase.

2. The Five Things That Actually Run Out

Before the formulas, here is the map. Most Oracle space incidents fall into exactly one of these buckets, and each has its own way to measure, size and monitor:

Work through them in this order for a new system, and re-check them on a schedule for an existing one. The rest of this guide takes each in turn.

3. Sizing UNDO — Arithmetic, Not Art

UNDO holds the "before" image of changed data so that transactions can roll back and, far more often, so that long-running queries can see a consistent point-in-time view. Its size is governed by a clean formula:

UNDO size = UNDO_RETENTION (sec) × undo_blocks_per_sec × DB_BLOCK_SIZE

The only input you have to measure is the undo generation rate, and Oracle records it for you in ten-minute buckets:

-- Peak and average undo generation, and your longest query
SELECT ROUND(MAX(undoblks/((end_time-begin_time)*86400))) AS peak_ups,
       ROUND(AVG(undoblks/((end_time-begin_time)*86400))) AS avg_ups,
       MAX(maxquerylen)                                    AS longest_query_sec
FROM   v$undostat;

Set UNDO_RETENTION above your longest query with headroom, then size the tablespace so the arithmetic is actually honoured — if the tablespace is smaller than the formula demands, retention is a wish rather than a setting. For a full treatment of read consistency, ORA-01555 and the RETENTION GUARANTEE trade-off, see the dedicated guide on undo, redo and ORA-01555. For the number itself, drop your peak rate, retention and block size into the sizing calculator and read the answer.

4. Sizing TEMP — Size the Peak, Then Verify

TEMP is where Oracle spills work that will not fit in memory: large sorts, hash joins, ORDER BY and GROUP BY on big row sources, global temporary tables and index builds. It resists a single formula because its demand is entirely about concurrency — a database can run happily on a small TEMP for months, then exhaust it the one night three heavy reports overlap. The practical planning approach is to size for that overlap:

TEMP ≈ largest_work_area × concurrent_heavy_sessions × (1 + headroom)

Then verify against reality. During your real peak — month-end, quarter-end, the big overnight batch — watch who is actually using TEMP:

-- Live TEMP usage by session, largest first
SELECT s.sid, s.username, u.tablespace,
       ROUND(u.blocks * ts.block_size/1024/1024) AS mb_used,
       u.segtype
FROM   v$tempseg_usage u
JOIN   v$session       s  ON s.saddr = u.session_addr
JOIN   dba_tablespaces ts ON ts.tablespace_name = u.tablespace
ORDER  BY u.blocks DESC;

Two design rules matter as much as the number. First, configure TEMP with autoextend but a firm MAXSIZE, so one runaway sort cannot fill the disk and take the whole instance down with it. Second, remember that generous PGA_AGGREGATE_TARGET keeps more sorts in memory and reduces TEMP demand — sometimes the cheapest way to fix TEMP pressure is to tune PGA rather than buy disk. The calculator's TEMP tab gives you the starting number to validate against.

5. Sizing Redo and Archived Redo

Redo is the change log that makes recovery possible, and archived redo is the stream RMAN needs for point-in-time recovery. Two questions decide your sizing. First, are the online redo logs large enough that groups switch at a healthy cadence — roughly every 15 to 30 minutes at peak, not every 90 seconds? Constant switching means undersized logs and checkpoint pressure:

-- Log switches per hour over the last two days
SELECT TO_CHAR(first_time,'YYYY-MM-DD HH24') AS hour, COUNT(*) AS switches
FROM   v$log_history
WHERE  first_time > SYSDATE - 2
GROUP  BY TO_CHAR(first_time,'YYYY-MM-DD HH24')
ORDER  BY hour;

Second — and this is the capacity question — how much archived redo do you generate per day? That number drives both your archive destination and, as we will see, a big part of the backup area:

-- Archived redo volume per day (GB)
SELECT TO_CHAR(completion_time,'YYYY-MM-DD') AS day,
       ROUND(SUM(blocks*block_size)/1024/1024/1024,1) AS gb
FROM   v$archived_log
WHERE  completion_time > SYSDATE - 14
GROUP  BY TO_CHAR(completion_time,'YYYY-MM-DD')
ORDER  BY day;

Redo volume almost always exceeds the volume of changed data, because index maintenance, updates that touch many blocks, and reorganisations all generate redo out of proportion to the rows changed. That is why the backup estimate below treats archived redo as its own line, larger than the raw change rate.

6. Sizing RMAN Backup and the Fast Recovery Area

The backup area is where good intentions meet a hard floor: it has to physically hold everything required to satisfy your recovery window — the number of days back you must be able to restore to. Three pieces add up:

• Full backups. You need at least one full older than the window plus the current one, each shrunk by RMAN compression and by skipping empty blocks.
• Incremental backups. Roughly your daily changed data, compressed, accumulated across the window (with a weekly-full strategy).
• Archived redo. Everything generated since the oldest backup you retain — usually the largest single line, per the previous section.

A conservative planning estimate for a weekly-full-plus-daily-incremental strategy looks like this:

FRA ≈ (fulls_kept × compressed_full)
    + (daily_change × compression_factor × retention_days)
    + (daily_archive × compression_factor × retention_days)
    + a small margin for control-file / spfile autobackups

Set DB_RECOVERY_FILE_DEST_SIZE comfortably above that total, define a CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF n DAYS, and let DELETE OBSOLETE reclaim space automatically. The one number never to shave is the margin: an FRA that runs out stops the archiver and hangs the database. The complete backup-and-recovery methodology is in the RMAN backup and recovery guide; for the storage figure, the calculator's RMAN tab estimates all three lines from your database size, change rate and window.

7. Forecasting Datafile Growth

The four components above are about steady-state operation; datafile sizing is about the future. The mistake I see most is sizing datafiles for today's data and being surprised in eight months. The fix is to measure the growth trend and project it. A simple, reliable approach is to track total segment size over time and compute the monthly delta:

-- Total used size per tablespace (run and record monthly, or trend from AWR)
SELECT tablespace_name,
       ROUND(SUM(bytes)/1024/1024/1024,1) AS used_gb
FROM   dba_segments
GROUP  BY tablespace_name
ORDER  BY used_gb DESC;

If a schema grows 20 GB a month and you must not touch storage for a year, you need at least 240 GB of runway on top of today's footprint — plus headroom for the inevitable data-loading spikes. Configure datafiles with autoextend and a sane MAXSIZE so short-term spikes are absorbed automatically, but treat autoextend as a shock absorber, not a capacity plan: a datafile silently autoextending to fill a disk is just a slower version of the same outage. Plan the disk to the trend; let autoextend cover the noise.

8. Monitoring So You Are Never Surprised

Sizing is a snapshot; monitoring is what keeps it true. Three alerts prevent the large majority of space outages, and all three should fire with comfortable lead time — not at 99%. Watch tablespace usage, undo free space, and above all the FRA:

-- Fast Recovery Area usage — alert well before this climbs high
SELECT name,
       ROUND(space_limit/1024/1024/1024,1)                          AS limit_gb,
       ROUND(space_used/1024/1024/1024,1)                           AS used_gb,
       ROUND(space_used/NULLIF(space_limit,0)*100,1)                AS pct_used
FROM   v$recovery_file_dest;

-- Tablespace usage percentage
SELECT tablespace_name,
       ROUND(used_percent,1) AS pct_used
FROM   dba_tablespace_usage_metrics
ORDER  BY used_percent DESC;

The threshold that matters is lead time, not a percentage. If a tablespace grows 20 GB a month, an alert at 80% might give you weeks; if it can grow 20 GB in a bad afternoon of data loading, 80% gives you an afternoon. Set thresholds by how fast the thing can fill, and you will always get a purchase order signed before you get a pager alert. A regular database health check is the natural home for reviewing these trends.

9. A Worked Example: Sizing a New 800 GB Pharma ERP Database

Let me put it together the way I would for a real go-live. A pharmaceutical client is launching an ERP on an 800 GB (used) Oracle database, 8 KB block size, with a required 21-day recovery window and validated backups. Here is the reasoning end to end.

UNDO. Early load testing shows a peak undo rate around 400 blocks/sec and a longest reporting query near 50 minutes. I set UNDO_RETENTION to 4,800 seconds (80 minutes, with headroom) and size UNDO at 400 × 4,800 × 8 KB ≈ 15 GB, rounded to 20 GB for comfort and future growth.

TEMP. The heaviest month-end costing reports use roughly 4 GB of work area each, and up to six can overlap during close. That is 4 × 6 = 24 GB peak; with 50% headroom I provision a 40 GB TEMP tablespace, autoextend on, MAXSIZE capped below the disk limit.

Redo/archive. Change tracking during load testing suggests about 40 GB of archived redo per day at steady state, higher during data migrations, so I size the archive path and factor 40 GB/day into the backup math.

Backup / FRA. With weekly fulls plus daily incrementals, 40% compression saving, a 3% daily change rate and a 21-day window: three compressed fulls (≈480 GB each after compression → keep 3 ≈ … sized from the tool), incrementals across the window, and 21 days of compressed archived redo. The calculator lands this in the low terabytes; I set DB_RECOVERY_FILE_DEST_SIZE above it with margin and a 21-day recovery-window policy.

Datafiles. The business projects 25 GB/month of growth for year one. On top of 800 GB today, that is 300 GB of runway, so I provision datafiles and the underlying ASM storage for roughly 1.2 TB with autoextend as the shock absorber. Every one of these numbers is defensible in a validation document — which, in a regulated pharma environment, is not optional.

10. Common Capacity-Planning Mistakes

• Treating autoextend as the plan. Autoextend absorbs spikes; it does not decide how much disk you need. Left unbounded, it turns a tablespace problem into a full-disk problem that takes the whole instance down.
• Sizing UNDO or TEMP for the average. Both fail at the peak — the overlapping reports, the month-end sort. Size for the worst realistic concurrency, not the daily mean.
• Forgetting archived redo in the backup math. The FRA is usually dominated by archive, not by the full backup. Estimate it from V$ARCHIVED_LOG, not from a guess.
• Alerting at a fixed percentage. 90% means very different lead times on a slow-growing versus a spiky tablespace. Alert by time-to-full, not by percentage.
• Never re-checking. A plan sized at go-live is stale after the first new module or user surge. Trend growth monthly and re-size after major changes.

Every one of these is cheap to avoid up front and expensive to hit in production. Capacity is one of the few areas where a few hours of arithmetic genuinely buys you nights of uninterrupted sleep.

Frequently Asked Questions

What is Oracle capacity planning?

Oracle capacity planning is sizing the storage a database needs so it runs reliably without running out of space: the datafiles for data growth, the UNDO tablespace for read consistency, TEMP for sorts and hashes, redo and archived redo for recovery, and the RMAN backup area for your recovery window. Good planning is arithmetic from real numbers, not guesswork, and it is checked with monitoring so nothing surprises you.

How do I calculate UNDO tablespace size?

Multiply your undo retention target in seconds by the peak undo blocks generated per second and by the block size: UNDO size = UNDO_RETENTION × undo_blocks_per_sec × DB_BLOCK_SIZE. Get the peak rate from V$UNDOSTAT, add 15 to 30 percent headroom, and make sure the tablespace is large enough to honour the retention you set.

How big should TEMP tablespace be in Oracle?

There is no single formula because TEMP depends on concurrent sorts, hash joins and global temporary tables. A practical starting point is the largest work area a heavy query uses multiplied by how many such queries run at peak, plus headroom. Validate against V$TEMPSEG_USAGE and V$SORT_USAGE under real load, and use autoextend with a hard MAXSIZE.

How do I size RMAN backup storage and the FRA?

Add the full backups you must keep for your recovery window, the incrementals (roughly daily changed data), and the archived redo generated across the window, each reduced by compression. Set DB_RECOVERY_FILE_DEST_SIZE comfortably above that total and monitor V$RECOVERY_FILE_DEST so the archiver never stalls.

What happens if I run out of space in the FRA?

When the Fast Recovery Area fills, Oracle cannot archive redo, the archiver stalls, and the database hangs until space is freed — a classic 3 a.m. outage. Prevent it by sizing DB_RECOVERY_FILE_DEST_SIZE above your estimate, using a RECOVERY WINDOW retention policy with DELETE OBSOLETE, and alerting well before the FRA is full.

How often should Oracle capacity be reviewed?

Review growth trends monthly and re-check sizing after any major change: a new module, a data migration, a big jump in users, or a change to the backup retention policy. Continuous monitoring of tablespace, undo and FRA usage should run all the time so slow growth is caught long before it becomes an outage.