Oracle DBA Lesson 20A — Turn Workload Requirements Into a Creation Plan

The orders forecast is one number. The database you are about to create is several. 150 GiB of table data in two years, plus a 20 percent reserve, is 180 GiB. A 220 GiB volume can hold that data plan. It does not hold temp, undo, the Fast Recovery Area, or a second redo member unless those have their own budgets and their own disks.

Oracle DBA Lesson 20A Turn Workload Requirements Into a Creation Plan

Write the data number before you ask for a volume

Start from the workload, not from a LUN someone already carved. Orders data is 100 GiB today. The forecast is 150 GiB in two years. That is 50 GiB of growth, about 25 GiB a year. The reserve in this plan is 20 percent of the two-year size, not 20 percent of the growth: 150 × 1.2 = 180 GiB. The volume on offer is 220 GiB. 220 − 180 = 40 GiB left on that volume at the end of year two.

-- Orders data only. GiB = 1024^3. Oracle's G suffix is the same unit.
          -- 20% is of the two-year size, not of the 50 GiB of growth.
          -- 100 now
          -- 150 in 2 years
          -- 180 = 150 * 1.2  (the data plan)
          -- 220 volume - 180 data = 40 left on that volume
          -- 40 is not temp, undo, FRA, or redo.

Forty GiB is not a budget. It is the remainder after the data plan. Temp, undo, archived redo, backups, flashback logs, and the online redo members are other lines. If they land on the same 220 GiB volume, the data plan and those lines are competing for one pool.

Ask which 220 you were given. A vendor "220 GB" is often 220 × 10⁹ bytes, about 205 GiB. The spare then shrinks before Oracle creates a file. A Linux ext filesystem also holds back reserved blocks (the mkfs default is commonly 5 percent for root). Those blocks are not space the Oracle OS user can allocate. df on the mount, in bytes, is the number that enters the plan. The ticket title is not.

Plan the end state, not only today's free space. A file created at 100 GiB with room to grow to 180 GiB needs the volume to still have that headroom when the other consumers are at their own peaks. Free space on create day can be fine and still be a failed plan in year two.

Temp, undo, FRA, and memory are not inside the 180

Undo keeps read consistency, rollback, and flashback query. UNDO_RETENTION is a target in seconds. On an autoextending undo tablespace, Oracle tries to honor that target by growing the file, up to MAXSIZE and then up to free space. A fixed-size undo tablespace is different: Oracle tunes retention to fit the file and ignores UNDO_RETENTION unless the tablespace is RETENTION GUARANTEE. Guarantee means unexpired undo is kept, and DML fails with ORA-30036 (unable to extend a segment in the undo tablespace) instead of stealing that undo. Without guarantee, a long query can fail with ORA-01555 because the snapshot was overwritten. Size undo from V$UNDOSTAT (UNDOBLKS, MAXQUERYLEN, TUNED_UNDORETENTION), not from the orders table forecast.

Temp is sorts, hash joins, global temporary tables, and index builds that do not fit in PGA. The error is ORA-01652, unable to extend a temp segment. Read DBA_TEMP_FREE_SPACE (TABLESPACE_SIZE, ALLOCATED_SPACE, FREE_SPACE). A quiet month can hide a month-end close that needs tens of GiB for an hour. That peak is its own line.

PGA_AGGREGATE_TARGET is a target. PGA_AGGREGATE_LIMIT is the hard ceiling, from 12c on. The default limit is the greater of 2 GB, 200 percent of PGA_AGGREGATE_TARGET, and 3 MB times PROCESSES, and it will not exceed 120 percent of physical memory minus the total SGA. Cross the limit and Oracle raises ORA-04036 and can kill sessions. Undersized PGA does not stay in RAM. Workareas spill to temp, so a thin PGA budget shows up as a temp-file plan you did not write down.

SGA is host RAM, not a slice of the 180 GiB. SGA_TARGET cannot rise above SGA_MAX_SIZE. A 180 GiB database does not need a 180 GiB buffer cache. Size the cache from the hot working set. If the host uses HugePages, do not plan on MEMORY_TARGET. Automatic memory management does not use HugePages. Set SGA_TARGET and PGA_AGGREGATE_TARGET separately, and leave RAM for the operating system.

The Fast Recovery Area is a disk quota, DB_RECOVERY_FILE_DEST_SIZE, on a destination, DB_RECOVERY_FILE_DEST. The destination is refused if the size is not set. The quota is not the size of the filesystem. Set the quota larger than the volume and the filesystem fills first. Set it smaller and Oracle stops while df still looks comfortable. When the quota is exhausted, archived redo in the FRA can stall the instance: ORA-19809 (limit exceeded for recovery files) and then ORA-00257 from the archiver. V$RECOVERY_FILE_DEST splits SPACE_LIMIT, SPACE_USED, and SPACE_RECLAIMABLE. Reclaimable bytes are files Oracle is allowed to delete (obsolete backups, archived logs already backed up). They are not free disk, and they are not free if your retention still needs them.

Oracle's guidance is to put the FRA on a different disk from data files and online redo. The recommendation is also a string check: DB_RECOVERY_FILE_DEST should not be the same value as DB_CREATE_FILE_DEST or any DB_CREATE_ONLINE_LOG_DEST_n. Different directory strings on one volume pass that check and still die together.

-- FRA quota is its own budget. 400G is a placeholder, not 220-180.
          -- Replace it from backups + archived redo + flashback logs.
          -- G in an Oracle size is 1024^3.
          DB_CREATE_FILE_DEST=/u02/oradata
          DB_CREATE_ONLINE_LOG_DEST_1=/redo1
          DB_CREATE_ONLINE_LOG_DEST_2=/redo2
          DB_RECOVERY_FILE_DEST=/fra
          DB_RECOVERY_FILE_DEST_SIZE=400G

Online redo created as Oracle Managed Files, with no SIZE, defaults to 100 MB. That is a creation default, not a workload answer. Oracle's rough guide is to switch logs at most about once every twenty minutes at peak. Read the peak redo rate, pick a size, and set it. Members of one group must be the same size.

Every resource needs a limit, a trigger, and an owner

A number with nobody watching it is a postmortem. For each line, write three things: the usable limit (the byte count that stops work), the early trigger (the check that fires while you can still add space), and a named owner (the person who acts, not "the database team").

• Orders data. Limit: the sum of datafile MAXSIZE, and free bytes on that volume at the end state. Trigger: tablespace threshold, plus a volume check. Owner: the DBA sets MAXSIZE; storage owns the LUN.
• Temp. Limit: tempfile MAXSIZE and the temp volume. Trigger: DBA_TEMP_FREE_SPACE through the close, not at noon on a quiet Tuesday. Owner: the DBA.
• Undo. Limit: undo file size, and whether retention is guaranteed. Trigger: V$UNDOSTAT before ORA-30036 or ORA-01555. Owner: the DBA, with the longest query the application will run.
• FRA. Limit: DB_RECOVERY_FILE_DEST_SIZE and the FRA volume, whichever is smaller. Trigger: SPACE_USED against SPACE_LIMIT, after you subtract what is not really reclaimable. Owner: the DBA and the backup owner. Retention is a backup decision.
• Redo. Limit: member size, group count, and distinct failure domains. Trigger: switches faster than the twenty-minute guide, or two members whose paths resolve to one volume. Owner: the DBA for the files, storage for the LUNs.
• SGA and PGA. Limit: SGA_MAX_SIZE and PGA_AGGREGATE_LIMIT against host RAM. Trigger: free memory and swap, and temp spill. Owner: the DBA and the host owner.

A new database sets server-generated tablespace alerts at 85 percent warning and 97 percent critical for locally managed tablespaces. Free-space-remaining thresholds default to zero, which means off. Eighty-five percent of a 180 GiB max is an alert at about 153 GiB used, with 27 GiB left. At 25 GiB a year that is roughly a year. If growth jumps, it is late. For a large tablespace, Oracle documents the free-space threshold (kilobytes remaining) as the more useful one. Set it from the growth rate. Do not leave a zero and call the default an early trigger.

Those percent alerts are computed from the file maximum (the MAXSIZE you set, or the operating system file-size limit, whichever is smaller), not from "how full is the disk today." Oracle does that so a file that can still autoextend does not alert just because the current size is nearly used. The consequence: a volume at 99 percent full can sit there with no tablespace alert while MAXSIZE is still far above the bytes in the file. The volume check belongs to storage. DBA_FREE_SPACE will not do it. That view is free extents inside the files.

Autoextend still stops

AUTOEXTEND ON grows a file by NEXT until MAXSIZE. If you omit MAXSIZE, the default is UNLIMITED. Unlimited means Oracle was not given a ceiling. It does not mean the disk grows. The file stops at the Oracle file-size limit or at free space, whichever comes first. The session then errors. ORA-01653 is the usual one for a table that cannot get another extent. Autoextend does not order a LUN.

-- 100G is today's data. 180G is the plan, not a guess at the disk.
          -- NEXT and MAXSIZE are the plan. They are not Oracle defaults.
          CREATE BIGFILE TABLESPACE orders
            DATAFILE '/u02/oradata/ORDERS/orders.dbf'
              SIZE 100G
                AUTOEXTEND ON NEXT 1G MAXSIZE 180G;

A smallfile tablespace with an 8 KB block cannot put 180 GiB in one file. The maximum is just under 32 GiB (4,194,303 blocks times the block size). A bigfile tablespace is one file, up to 32 TB at 8 KB and 128 TB at 32 KB. Stay on smallfiles and you need at least six files for 180 GiB, each with its own MAXSIZE. MAXSIZE UNLIMITED on each of those six lets every file try for about 32 GiB. The sum is near 192 GiB, already past the data plan and large enough to crowd the 220 GiB volume. Set the ceilings so they add up to 180 GiB. Do not leave six unlimited files and hope the disk arbitrates.

-- MAXBYTES is the ceiling. 0 means the file will not autoextend.
          -- This does not show free bytes on the volume.
          SELECT file_name,
                 autoextensible,
                        ROUND(bytes    / 1024 / 1024 / 1024, 1) AS gib,
                               ROUND(maxbytes / 1024 / 1024 / 1024, 1) AS max_gib
                               FROM   dba_data_files
                               WHERE  tablespace_name = 'ORDERS'
                               ORDER  BY file_name;

MAXBYTES of 0 means AUTOEXTENSIBLE is NO. A huge MAXBYTES is UNLIMITED, stored as the largest size Oracle will record for that file. Neither column is df. After you create the files, read both the dictionary and the mount. On ASM, FREE_MB is not the planning number either. That comes later.

Two redo directories on Volume A are one disk

A multiplexed group is two or more members. LGWR writes the same redo to each member. Oracle's rule for the placement is physical disks: if one disk fails, another member has to remain. Two copies on the same disk still help when one file is corrupt or one write hits a bad sector. They do nothing when the disk, the LUN, or the array behind both paths is the thing that failed.

-- Paths Oracle was given. Not the volume, LUN, or failure group.
          SELECT group#, member, type, status
          FROM   v$logfile
          ORDER  BY group#, member;

/redo1/group1.log and /redo2/group1.log are two members. If both mount points sit on Volume A, V$LOGFILE still shows two rows. The view does not walk the mount table, LVM, or the storage array. Multiplexing is configured. Independent protection is not. The same trap hits control files: DB_CREATE_ONLINE_LOG_DEST_1 and DB_CREATE_ONLINE_LOG_DEST_2 place a redo member and a control file copy in each destination. Two destinations on Volume A means one failed volume takes the redo members and the control file copies together.

Prove the path before create. On Linux, findmnt and the underlying device for /redo1 and /redo2 have to name different failure domains. Two directories on one filesystem are not two disks. Two filesystems on one LUN are not two disks either.

An OMF name and a disk group still end at hardware

Oracle Managed Files hide the name. Set DB_CREATE_ONLINE_LOG_DEST_1 and DB_CREATE_ONLINE_LOG_DEST_2, omit the LOGFILE clause, and each group gets a generated member in each destination (o1_mf_... under the directory, or an ASM name). The parameters do not check that those directories are different volumes. They only check that you typed two strings.

ASM is the same fact with different labels. +DATA and +FRA are disk groups, not disks. A file such as +DATA/ORCL/DATAFILE/orders.258.905963653 tells you the group. It does not tell you the LUN. Read the disks:

-- USABLE_FILE_MB is what you can still allocate and keep redundancy.
          -- FREE_MB counts space a failure would need back.
          SELECT name,
                 type,
                        free_mb,
                               required_mirror_free_mb,
                                      usable_file_mb
                                      FROM   v$asm_diskgroup
                                      ORDER  BY name;

SELECT g.name AS diskgroup,
                 d.failgroup,
                        d.path,
                               d.total_mb,
                                      d.free_mb
                                      FROM   v$asm_disk d
                                      JOIN   v$asm_diskgroup g
                                        ON   g.group_number = d.group_number
                                        ORDER  BY g.name, d.failgroup, d.path;

NORMAL redundancy mirrors across failure groups. HIGH uses three. EXTERNAL does not mirror at all. Protection is then the array, and you still have to know which array. Two failure groups whose PATH values are LUNs on Volume A restore nothing when Volume A goes away. REQUIRED_MIRROR_FREE_MB is space ASM holds so it can restore redundancy after a failure group is lost. USABLE_FILE_MB is what you can allocate and still do that. Planning on FREE_MB overstates the disk group. A negative USABLE_FILE_MB means you have already spent the reserve.

Explicit names do not excuse the trace either. /u02/oradata/ORDERS/orders.dbf is only as independent as the device under /u02. Write the map into the creation plan: path, mount, volume, array, owner. ASM, OMF, and a hand-typed path all stop at that line.

Gotchas

• 180 GiB is orders data after a 20 percent reserve on 150, not on the 50 GiB of growth. 220 − 180 = 40 GiB left on that volume. Temp, undo, FRA, and redo are not paid for by the 40.
• A quoted "220 GB" may be decimal. 220 × 10⁹ bytes is about 205 GiB. Reserved blocks on the filesystem come off the top.
• AUTOEXTEND ON with no MAXSIZE defaults to UNLIMITED. The file still stops at the Oracle file-size limit or at free space. The error is ORA-01653 (or ORA-01652 for temp), not a new disk.
• One 8 KB smallfile tops out just under 32 GiB. Six unlimited smallfiles can try to reach about 192 GiB and walk past both the 180 GiB plan and the spare on a 220 GiB volume.
• Tablespace alerts at 85 and 97 percent use the file maximum, so they stay quiet while the volume is full. DBA_FREE_SPACE is free extents, not df.
• Autoextending undo grows to honor UNDO_RETENTION. A fixed undo tablespace ignores that parameter unless you set RETENTION GUARANTEE, which trades ORA-01555 for ORA-30036.
• DB_RECOVERY_FILE_DEST_SIZE is a quota. Larger than the volume, the disk wins. Smaller than the disk, Oracle stops early. SPACE_RECLAIMABLE is not free space you can give to data files.
• /redo1 and /redo2 on Volume A are two members and one failure domain. V$LOGFILE cannot see the LUN. Control file copies from the same DB_CREATE_ONLINE_LOG_DEST_n pair go with them.
• OMF redo with no SIZE is 100 MB. Matching parameter strings are what Oracle compares. Different strings on the same volume do not raise that warning.
• On ASM, allocate against USABLE_FILE_MB, not FREE_MB. EXTERNAL redundancy means the array is the mirror. Trace V$ASM_DISK.PATH and FAILGROUP anyway.

Quick quiz

1. Orders data is forecast at 150 GiB in two years, with a 20 percent reserve. The data volume is 220 GiB. What did you actually plan?

220 GiB covers data, temp, undo, FRA, and both redo members. 180 GiB is the orders data plan. The other 40 GiB on that volume is not temp, undo, FRA, or redo. The reserve is 20 percent of the 50 GiB of growth, so the plan is 160 GiB. 100 GiB today is the number to set as MAXSIZE.

2. A datafile is AUTOEXTEND ON with no MAXSIZE, and the volume is full. What stops the next extent?

Free space, or the Oracle file-size limit. UNLIMITED does not create disk. Nothing. AUTOEXTEND ON allocates past a full filesystem. The 85 percent tablespace alert extends the LUN. DBA_FREE_SPACE borrows extents from the FRA quota.

3. Group 1 has members on /redo1 and /redo2. Both directories are filesystems on Volume A. What protection do you have?

Loss of Volume A leaves one member, because the paths differ. V$LOGFILE rejects the second member when the device is the same. None. Two members on one volume are ignored by LGWR. Protection against one bad file. Not against losing Volume A.

4. The orders file is +DATA/ORCL/DATAFILE/orders.258.905963653. What do you still have to trace?

Nothing. A +DATA name is already a separate physical disk from +FRA. Only DBA_FREE_SPACE. ASM free bytes are the same column. The disk group to failure groups and PATH. Plan with USABLE_FILE_MB, not FREE_MB. The OMF 100 MB default. ASM files cannot grow past that.

AIOps Pro

https://www.apps-dba.com/ https://x.com/AIOpsPro https://www.youtube.com/@AIOpsPro