ORA-15041: Diskgroup Space Not Sufficient - Free ASM Space

ORA-15041: Diskgroup Space Is Not Sufficient

Error Overview

Error Text: ORA-15041: diskgroup "string" space is not sufficient to complete the request

The ORA-15041 error occurs when an Oracle ASM diskgroup cannot satisfy a space allocation request. This can happen during file creation, datafile extension, RMAN backup, rebalance operations, or disk drops. A critical and frequently misunderstood aspect of this error is that ASM’s reported FREE_MB in V$ASM_DISKGROUP does not reflect actual usable space — the USABLE_FILE_MB column is the correct metric because it accounts for mirroring overhead and protection requirements. A diskgroup can show several gigabytes of FREE_MB while USABLE_FILE_MB is zero or negative.

Common Causes

1. Mirroring Overhead Underestimated

• NORMAL REDUNDANCY diskgroup keeps 2 copies of every extent — half the raw space is usable
• HIGH REDUNDANCY diskgroup keeps 3 copies — only one third of raw space is usable for data
• FREE_MB looks adequate but USABLE_FILE_MB is near zero after accounting for mirror copies
• Failure group imbalance causing ASM to protect more data than expected

2. Rapid Datafile Growth

• Autoextend enabled on datafiles without a MAXSIZE limit
• TEMP tablespace growing during a large sort or hash join operation
• RMAN backup pieces writing to an FRA diskgroup concurrently with production writes
• Redo log archival consuming space in the RECO/FRA diskgroup unexpectedly fast

3. Disk Drop Reducing Available Space

• Dropping a disk in a NORMAL REDUNDANCY group requires enough space to rebalance all extents to remaining disks
• An in-progress rebalance consuming temporary double-allocation during extent migration
• ORA-15032 (not all alterations performed) as a secondary consequence of ORA-15041

4. Imbalanced Failure Groups

• Failure groups of very different sizes causing ASM to leave space unusable for mirroring
• One failure group significantly smaller than others, acting as the bottleneck for usable space
• New disks added to only one failure group, creating an imbalance

5. Orphaned or Large ASM Files

• Stale RMAN backup pieces not deleted after a failed or superseded backup
• Orphaned temp files, log files, or control file copies consuming diskgroup space
• Old standby redo logs or archived logs not cleaned up on a Data Guard instance

6. ASM Metadata and Overhead

• Large diskgroup with thousands of files incurring significant ASM metadata overhead
• AU (Allocation Unit) fragmentation from many small files on a large-AU diskgroup
• Persistent buddy region overhead not visible in normal space queries

Diagnostic Queries

The Correct Space Calculation

-- PRIMARY SPACE QUERY: Always use USABLE_FILE_MB, not FREE_MB
-- FREE_MB = raw free space before mirroring
-- USABLE_FILE_MB = space available for new database files (accounts for mirroring)
SELECT
  name,
  type                                          AS redundancy,
  ROUND(total_mb        / 1024, 2)              AS total_gb,
  ROUND(free_mb         / 1024, 2)              AS raw_free_gb,
  ROUND(usable_file_mb  / 1024, 2)              AS usable_file_gb,
  ROUND((1 - free_mb / NULLIF(total_mb,0)) * 100, 1) AS pct_used,
  offline_disks,
  state
FROM v$asm_diskgroup
ORDER BY name;

-- Space breakdown per disk and failure group
SELECT
  dg.name          AS diskgroup,
  d.failgroup,
  d.name           AS disk_name,
  d.path,
  ROUND(d.total_mb / 1024, 2)  AS total_gb,
  ROUND(d.free_mb  / 1024, 2)  AS free_gb,
  ROUND((1 - d.free_mb / NULLIF(d.total_mb,0)) * 100, 1) AS pct_used,
  d.mode_status,
  d.state
FROM v$asm_disk d
JOIN v$asm_diskgroup dg ON d.group_number = dg.group_number
ORDER BY dg.name, d.failgroup, d.disk_number;

Identify the Largest Files in Each Diskgroup

-- Top 20 largest ASM files by diskgroup
SELECT *
FROM (
  SELECT
    dg.name                             AS diskgroup,
    f.type,
    ROUND(f.bytes / 1024 / 1024 / 1024, 3) AS size_gb,
    f.space / 1024 / 1024               AS allocated_mb,
    f.redundancy,
    f.striped,
    f.creation_date,
    f.modification_date
  FROM v$asm_file f
  JOIN v$asm_diskgroup dg ON f.group_number = dg.group_number
  ORDER BY f.bytes DESC
)
WHERE ROWNUM <= 20;

-- Space usage by file type within each diskgroup
SELECT
  dg.name                              AS diskgroup,
  f.type,
  COUNT(*)                             AS file_count,
  ROUND(SUM(f.bytes)/1024/1024/1024, 2) AS total_size_gb,
  ROUND(AVG(f.bytes)/1024/1024, 2)     AS avg_size_mb
FROM v$asm_file f
JOIN v$asm_diskgroup dg ON f.group_number = dg.group_number
GROUP BY dg.name, f.type
ORDER BY dg.name, SUM(f.bytes) DESC;

Check RMAN Backup Space Consumption

-- RMAN backup pieces consuming space in ASM diskgroups
SELECT
  bp.handle,
  bp.status,
  ROUND(bp.bytes / 1024 / 1024 / 1024, 3) AS size_gb,
  bp.completion_time,
  bs.backup_type,
  bs.incremental_level
FROM v$backup_piece bp
JOIN v$backup_set   bs ON bp.set_stamp = bs.set_stamp
WHERE bp.handle LIKE '+%'   -- ASM paths start with +
  AND bp.deleted = 'NO'
ORDER BY bp.bytes DESC;

-- Flash Recovery Area (FRA) usage if FRA is on ASM
SELECT
  name,
  space_limit      / 1024 / 1024 / 1024 AS limit_gb,
  space_used       / 1024 / 1024 / 1024 AS used_gb,
  space_reclaimable/ 1024 / 1024 / 1024 AS reclaimable_gb,
  ROUND(space_used / NULLIF(space_limit,0) * 100, 1) AS pct_used,
  number_of_files
FROM v$recovery_file_dest;

Failure Group Balance Check

-- Compare failure group sizes to identify imbalance
SELECT
  dg.name      AS diskgroup,
  d.failgroup,
  COUNT(*)                              AS disk_count,
  ROUND(SUM(d.total_mb) / 1024, 2)     AS total_gb,
  ROUND(SUM(d.free_mb)  / 1024, 2)     AS free_gb,
  ROUND(SUM(d.free_mb) / NULLIF(SUM(d.total_mb),0) * 100, 1) AS free_pct
FROM v$asm_disk d
JOIN v$asm_diskgroup dg ON d.group_number = dg.group_number
WHERE d.mount_status = 'CACHED'
GROUP BY dg.name, d.failgroup
ORDER BY dg.name, d.failgroup;

Orphaned or Stale File Detection

-- ASM files older than 30 days that may be orphaned
-- Cross-reference with DBA_DATA_FILES, DBA_TEMP_FILES, V$LOGFILE
SELECT
  dg.name                               AS diskgroup,
  f.type,
  ROUND(f.bytes / 1024 / 1024, 2)       AS size_mb,
  f.creation_date,
  f.modification_date,
  TRUNC(SYSDATE - f.modification_date)  AS days_since_modified
FROM v$asm_file f
JOIN v$asm_diskgroup dg ON f.group_number = dg.group_number
WHERE f.modification_date < SYSDATE - 30
  AND f.type NOT IN ('DATAFILE', 'CONTROLFILE', 'ONLINELOG', 'TEMPFILE')
ORDER BY f.bytes DESC;

Step-by-Step Resolution

Step 1: Confirm True Available Space

-- ALWAYS check USABLE_FILE_MB first
SELECT name, type, total_mb, free_mb, usable_file_mb
FROM v$asm_diskgroup
WHERE name = 'DATA';

If USABLE_FILE_MB is 0 or negative, the diskgroup is genuinely full with respect to its redundancy requirements. If FREE_MB appears large but USABLE_FILE_MB is small, you have a failure group imbalance or mirroring overhead issue.

Step 2: Add Disks to the Diskgroup

Adding disks is the most reliable fix and should be done with balanced failure groups:

-- Add disks to NORMAL REDUNDANCY diskgroup (add one per failure group for balance)
ALTER DISKGROUP data
  ADD FAILGROUP FG1 DISK '/dev/mapper/asm_data05' NAME DATA_0005
  ADD FAILGROUP FG2 DISK '/dev/mapper/asm_data06' NAME DATA_0006
  REBALANCE POWER 8;

-- Monitor the rebalance
SELECT operation, state, sofar, est_work, est_minutes
FROM v$asm_operation;

From ASMCMD (OS level):

# List diskgroups and check space from command line
asmcmd lsdg

# Add a disk from ASMCMD
asmcmd adddisk -G DATA /dev/mapper/asm_data05

# Check space after adding
asmcmd lsdg DATA

Step 3: Reclaim RMAN Backup Space

-- List obsolete RMAN backups (run from RMAN prompt)
-- RMAN> LIST EXPIRED BACKUP;
-- RMAN> LIST OBSOLETE;

-- Delete expired and obsolete backups
-- RMAN> DELETE NOPROMPT EXPIRED BACKUP;
-- RMAN> DELETE NOPROMPT OBSOLETE;

-- Check FRA reclaimable space
SELECT
  name,
  ROUND(space_reclaimable / 1024 / 1024 / 1024, 2) AS reclaimable_gb
FROM v$recovery_file_dest;

Step 4: Drop Unnecessary Database Files

-- Check for oversized or unnecessary TEMP tablespace files
SELECT
  tablespace_name,
  file_name,
  ROUND(bytes       / 1024 / 1024 / 1024, 2) AS size_gb,
  ROUND(maxbytes    / 1024 / 1024 / 1024, 2) AS max_gb,
  autoextensible
FROM dba_temp_files
ORDER BY bytes DESC;

-- Shrink a TEMP file that has grown unnecessarily large
ALTER TABLESPACE temp SHRINK SPACE KEEP 1G;

-- Drop an extra TEMP file if multiple exist
ALTER TABLESPACE temp DROP TEMPFILE '+DATA/orcl/tempfile/temp02.dbf';

-- Identify data tablespaces with significant free space that could release datafiles
SELECT
  tablespace_name,
  file_name,
  ROUND(bytes / 1024 / 1024 / 1024, 2)     AS file_size_gb,
  ROUND(user_bytes / 1024 / 1024 / 1024, 2) AS usable_gb
FROM dba_data_files
WHERE tablespace_name NOT LIKE 'SYS%'
  AND bytes > 10 * 1024 * 1024 * 1024  -- Files larger than 10 GB
ORDER BY bytes DESC;

-- Resize an overallocated datafile
ALTER DATABASE DATAFILE '+DATA/orcl/datafile/users01.dbf' RESIZE 5G;

Step 5: Resize Autoextend Limits

-- Identify datafiles with no MAXSIZE limit (uncapped autoextend)
SELECT
  tablespace_name,
  file_name,
  ROUND(bytes    / 1024 / 1024 / 1024, 2) AS current_gb,
  autoextensible,
  ROUND(maxbytes / 1024 / 1024 / 1024, 2) AS max_gb
FROM dba_data_files
WHERE autoextensible = 'YES'
  AND maxbytes = 0   -- 0 means UNLIMITED
ORDER BY bytes DESC;

-- Set a MAXSIZE to prevent uncontrolled growth
ALTER DATABASE DATAFILE '+DATA/orcl/datafile/soe01.dbf'
  AUTOEXTEND ON NEXT 512M MAXSIZE 32G;

Step 6: Force an Archivelog Cleanup (FRA Diskgroup)

# Delete archived logs older than 7 days from OS / RMAN
# Run from RMAN:
# RMAN> DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';

# Or from ASMCMD list and remove old archives
asmcmd ls -l +FRA/ORCL/ARCHIVELOG/

Prevention Strategies

1. Continuous Space Monitoring

-- Create a procedure to alert when usable space falls below threshold
CREATE OR REPLACE PROCEDURE check_asm_space AS
  v_threshold_pct NUMBER := 20; -- Alert when usable < 20% of total
BEGIN
  FOR r IN (
    SELECT
      name,
      type,
      total_mb,
      usable_file_mb,
      ROUND(usable_file_mb / NULLIF(total_mb, 0) * 100, 1) AS usable_pct
    FROM v$asm_diskgroup
    WHERE state = 'MOUNTED'
  ) LOOP
    IF r.usable_pct < v_threshold_pct THEN
      RAISE_APPLICATION_ERROR(-20003,
        'ASM diskgroup ' || r.name ||
        ' usable space is ' || r.usable_pct ||
        '% — add disks immediately. Usable: ' ||
        ROUND(r.usable_file_mb / 1024, 2) || ' GB');
    END IF;
  END LOOP;
END;
/

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'CHECK_ASM_SPACE',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'check_asm_space',
    repeat_interval => 'FREQ=HOURLY',
    enabled         => TRUE
  );
END;
/

2. RMAN Retention Policy

-- Enforce a retention policy to prevent backup accumulation (run in RMAN)
-- RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
-- RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

-- Verify current RMAN configuration from SQL
SELECT name, value
FROM v$rman_configuration
WHERE name LIKE '%RETENTION%'
   OR name LIKE '%ARCHIVELOG%';

3. Autoextend Governance

-- Audit all datafiles with UNLIMITED autoextend
SELECT
  tablespace_name,
  COUNT(*) AS file_count,
  SUM(CASE WHEN maxbytes = 0 AND autoextensible = 'YES' THEN 1 ELSE 0 END) AS unlimited_files
FROM dba_data_files
GROUP BY tablespace_name
HAVING SUM(CASE WHEN maxbytes = 0 AND autoextensible = 'YES' THEN 1 ELSE 0 END) > 0
ORDER BY tablespace_name;

4. Capacity Planning with Historical Trends

-- Measure daily diskgroup growth using AWR (requires Diagnostics Pack)
SELECT
  s.snap_id,
  TO_CHAR(s.begin_interval_time, 'YYYY-MM-DD') AS snap_date,
  dg.name                                       AS diskgroup,
  ROUND((dg.total_mb - dg.free_mb) / 1024, 2)  AS used_gb,
  ROUND(dg.free_mb / 1024, 2)                   AS free_gb
FROM dba_hist_snapshot        s
JOIN dba_hist_sga             sga ON s.snap_id = sga.snap_id  -- join placeholder
JOIN v$asm_diskgroup          dg  ON 1=1  -- pull live; use AWR if available
WHERE s.begin_interval_time > SYSDATE - 30
ORDER BY s.snap_id, dg.name;

5. Best Practices

• Always monitor USABLE_FILE_MB, not FREE_MB, for capacity planning
• Add disks in pairs (one per failure group for NORMAL REDUNDANCY, one per three failure groups for HIGH REDUNDANCY) to maintain balance and maximize usable space
• Set a hard MAXSIZE on all autoextend datafiles — unlimited growth on a shared ASM diskgroup is a common cause of ORA-15041
• Configure RMAN with a defined retention policy and schedule regular DELETE OBSOLETE operations
• Reserve at least 20% USABLE_FILE_MB headroom at all times to absorb rebalance operations triggered by disk failures or replacements
• Separate RECO/FRA diskgroup from DATA diskgroup to prevent backup growth impacting production datafiles

Related Errors

• ORA-15001 — Diskgroup does not exist or is not mounted
• ORA-15017 — Diskgroup cannot be mounted
• ORA-15025 — Could not open disk (disk access failure leading to usable space reduction)
• ORA-15032 — Not all alterations performed (ORA-15041 is a common secondary error)
• ORA-15040 — Diskgroup is incomplete
• ORA-15042 — ASM disk is missing from diskgroup
• ORA-15063 — ASM insufficient number of disks in diskgroup
• ORA-01652 — Unable to extend temp segment (related space exhaustion)
• ORA-01653 — Unable to extend table (datafile space exhaustion)

Emergency Response

Quick Space Recovery Steps

1. Confirm the diskgroup and how much space remains

   SELECT name, type, total_mb, free_mb, usable_file_mb
   FROM v$asm_diskgroup
   ORDER BY usable_file_mb;

2. Delete obsolete RMAN backups immediately (from RMAN prompt)

   RMAN> DELETE NOPROMPT OBSOLETE;
   RMAN> DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-3';

3. Shrink TEMP tablespace if it has grown large

   ALTER TABLESPACE temp SHRINK SPACE;

4. Add an emergency disk if space is critically low

   ALTER DISKGROUP data
     ADD DISK '/dev/mapper/asm_emergency01' NAME EMERGENCY_01
     REBALANCE POWER 11;

Post-Resolution Validation

-- Confirm space is restored and diskgroup is healthy
SELECT
  name,
  state,
  type,
  ROUND(total_mb       / 1024, 2) AS total_gb,
  ROUND(free_mb        / 1024, 2) AS raw_free_gb,
  ROUND(usable_file_mb / 1024, 2) AS usable_file_gb,
  offline_disks
FROM v$asm_diskgroup
ORDER BY name;

-- Confirm no active operations are running
SELECT group_number, operation, state, est_minutes
FROM v$asm_operation;

-- Check that the original failing operation (file creation / datafile extend) now succeeds
-- Re-run the SQL or DML that produced ORA-15041