Design a Storage Layout for OLTP, Analytics, Temp, Undo, Recovery, and Growth

Learning outcomes

ServiceHub’s first two chapters created a safe database and explained runtime internals; this chapter mapped persistent storage. The final task is not to memorize a “best” tablespace layout. It is to turn workload, capacity, recovery, and operational evidence into a storage decision record that can survive growth. A useful plan separates mechanisms—data, TEMP, undo, redo, recovery area, lower storage—without pretending that putting every object in its own tablespace creates performance.

1. Start from workload classes and failure objectives

A storage layout should answer operational questions. Which data grows fastest? Which SQL can spill heavily to TEMP? How much undo history is needed for the longest reads/flashback goals? How much redo does peak DML generate? What backup/restore throughput and recovery-time objective (RTO) are required? What recovery-point objective (RPO) is required? Which failures must not remove both primary data and recovery copies?

These questions create boundaries. OLTP tables/indexes need predictable latency and concurrency. Analytical/batch work may create large scans and TEMP demand. Undo follows transaction and consistent-read history. Redo is latency-sensitive durability infrastructure. Archived redo and RMAN backups belong to recovery capacity, not ordinary application tablespace headroom.

2. Do not create tablespaces merely to mirror the object model

“One tablespace per table” sounds organized but usually multiplies files, quotas, backup/reporting metadata, and operational decisions without creating a real I/O or recovery boundary. A tablespace is useful when objects share a lifecycle, security/encryption policy, availability/recovery need, transport/migration strategy, block-size requirement, growth policy, or administrative boundary.

Separating table and index segments into different tablespaces can be justified for lifecycle, placement, or recovery administration. It is not automatically a performance optimization when both tablespaces sit on the same physical disks. Modern ASM/storage can stripe files across the same devices, making cosmetic file separation irrelevant to I/O isolation.

3. Autoextend is a guardrail, not capacity planning

AUTOEXTEND ON can prevent an avoidable outage when a file reaches its current size, but unlimited or unmonitored autoextend can simply move the outage to the filesystem/ASM/cloud-volume layer. A robust policy records current file size, growth increment, maximum size, lower-storage free/usable capacity, alert thresholds, and who responds before the maximum is reached.

SELECT tablespace_name, file_id, file_name,       ROUND(bytes/1024/1024,1) AS size_mb,       autoextensible,       ROUND(maxbytes/1024/1024,1) AS max_mb,       increment_byFROM   dba_data_filesORDER  BY tablespace_name, file_id;SELECT tablespace_name, file_id, file_name,       ROUND(bytes/1024/1024,1) AS size_mb,       autoextensible,       ROUND(maxbytes/1024/1024,1) AS max_mb,       increment_byFROM   dba_temp_filesORDER  BY tablespace_name, file_id;

INCREMENT_BY is stored in blocks, so translate it using the file/tablespace block size when you need bytes. Do not label it “MB” directly.

4. Capacity needs at least four layers of headroom

Thin provisioning introduces another apparent-capacity layer. A virtual volume can report logical free space while the backing pool is constrained. Production monitoring must follow allocation to the real failure point.

5. Redo and recovery areas are not ordinary tablespaces

Online redo log members are physical recovery structures, not tablespace files. Their size/count and log-switch behavior affect durability/recovery and should be designed from redo generation and recovery requirements. Archived redo, RMAN backups, flashback logs, and related recovery files can be managed in a Fast Recovery Area (FRA) when configured. The FRA is a recovery-file destination with a quota/space-management policy, not a tablespace.

SELECT group#, thread#, sequence#, bytes/1024/1024 AS size_mb,       members, archived, statusFROM   v$logORDER  BY group#;SELECT group#, member, type, statusFROM   v$logfileORDER  BY group#, member;SELECT name, valueFROM   v$parameterWHERE  name IN ('db_recovery_file_dest','db_recovery_file_dest_size')ORDER  BY name;

Chapter 14 covers redo mechanics and Chapter 15 covers RMAN/recovery engineering. Here the design rule is simpler: data growth and recovery growth must not surprise each other. If DATA and recovery copies share one failure/capacity domain, a full or failed storage pool can affect both primary operation and recovery capability.

6. Build the current Free-lab capacity card

SHOW CON_NAMEWITH df AS (  SELECT tablespace_name,         SUM(bytes) bytes,         SUM(CASE WHEN autoextensible='YES' THEN maxbytes ELSE bytes END) potential_bytes  FROM   dba_data_files  GROUP  BY tablespace_name), fs AS (  SELECT tablespace_name, SUM(bytes) free_bytes  FROM   dba_free_space  GROUP  BY tablespace_name)SELECT df.tablespace_name,       ROUND(df.bytes/1024/1024,1) AS current_file_mb,       ROUND(NVL(fs.free_bytes,0)/1024/1024,1) AS free_extent_mb,       ROUND(df.potential_bytes/1024/1024,1) AS configured_ceiling_mbFROM   dfLEFT JOIN fs ON fs.tablespace_name=df.tablespace_nameORDER  BY df.tablespace_name;SELECT owner, segment_name, segment_type, tablespace_name,       ROUND(bytes/1024/1024,2) AS allocated_mbFROM   dba_segmentsWHERE  owner='SERVICEHUB_OWNER'ORDER  BY bytes DESC;

This is intentionally not a production “percent used” formula. Maxbytes can be constrained by block/file type/platform limits, and the Free license’s 12-GB user-data limit is a separate ceiling. Lower storage may run out before an Oracle-configured max size. The evidence card must record all applicable ceilings.

7. Deliberately wrong design: unlimited growth plus fixed alert percentages

Assume every datafile is AUTOEXTEND ON MAXSIZE UNLIMITED and operations page only when the tablespace is 95% used. This can fail in several ways: “unlimited” is bounded by implementation/platform limits; the lower filesystem/ASM pool can fill first; a growth increment can be too large for remaining space; a rapidly growing workload can cross the last 5% before anyone responds; and a bigfile percentage can represent terabytes of remaining or consumed capacity.

The repair is a rate-aware policy: bounded maxima, absolute and percentage headroom, growth velocity, forecast horizon, lower-layer usable capacity, and a response owner. Alert thresholds are SLO/runbook inputs, not universal numbers copied from a blog.

8. Workload-driven production layout example

9. Backup/recovery implications belong in the storage design

Large files change backup/restore work distribution. Many small files increase file-count administration. Bigfile tablespaces can use RMAN sectioning to parallelize work on a large file. Autoextend changes backup volume. TEMP can usually be recreated according to recovery procedures, while permanent datafiles require recovery. Undo is part of database consistency/recovery. Redo and archived redo determine recovery continuity. These differences should be written into the storage runbook before an incident.

Likewise, ASM redundancy and filesystem RAID do not prove restore capability. A credible plan includes RMAN backup validation plus periodic restore/recovery drills. Chapter 15 will turn this requirement into executable recovery engineering.

10. Capstone lab: write a storage decision record

Create a short storage record with two columns: Free-lab observation and production assumption/decision. Include:

• Oracle AI Database product/RU, CDB/PDB, and tablespace default/type evidence;
• ServiceHub segment/tablespace placement and current file sizes;
• datafile/tempfile autoextend, increments, and maximums;
• undo configuration and recent workload evidence;
• redo group/member layout and recovery-area configuration;
• filesystem versus ASM lower-storage model and failure domains;
• absolute + percentage + growth-rate monitoring signals;
• backup/restore, RPO/RTO, and maintenance implications;
• licensing/topology assumptions for any optional Partitioning, compression, ASM/Grid, RAC, Data Guard, Exadata, or cloud capability;
• rollback/expansion procedure and responsible owner.

The record should explicitly reject any rule that lacks a mechanism or measurable objective—for example “indexes always need a separate tablespace,” “bigfile is always faster,” or “autoextend means capacity is automatic.”

11. Chapter summary and bridge to Chapter 04

Chapter 03 connected Oracle logical objects to physical files and lower storage, separated permanent/TEMP/undo roles, explained locally managed/ASSM/HWM reclamation, introduced ASM without pretending the Free lab includes Grid Infrastructure, and finished with workload-driven capacity/recovery planning. Chapter 04 returns upward to logical design: Oracle users versus schemas, object ownership, data types, keys/constraints, sequences, identity columns, defaults, virtual/invisible columns, and integrity boundaries.

Authoritative references

• Oracle AI Database Administrator’s Guide — Managing Tablespaces — file growth, autoextend, bigfile/smallfile, and tablespace administration
• Oracle AI Database Backup and Recovery User’s Guide — FRA, RMAN, backup/restore and recovery planning
• Oracle AI Database Concepts — Physical Storage Structures — datafiles, redo logs, control files and physical storage
• Oracle Automatic Storage Management Administrator’s Guide — ASM capacity, redundancy and disk-group architecture
• Oracle AI Database Free Licensing Restrictions — Free CPU/RAM/user-data limits for the lab baseline