Flash Recovery Area Space Usage (flash_space.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script provides comprehensive Flash Recovery Area analysis by:

• Showing FRA location and size limits
• Breaking down space usage by file type (archive logs, backups, etc.)
• Calculating actual used vs. reclaimable space
• Converting all measurements to MB for readability
• Helping identify space management opportunities

Script

 select a.name,
        a.space_limit/(1024*1024) space_limit,
     b.file_type,
        b.number_of_files,
        ((b.PERCENT_SPACE_USED/100)*a.SPACE_LIMIT)/(1024*1024) TOTAL_SPACE_USED,
         b.PERCENT_SPACE_USED TOTAL_PERCENT_SPACE_USED,
         ((b.PERCENT_SPACE_RECLAIMABLE/100)*a.SPACE_LIMIT)/(1024*1024) space_reclaimable,
         b.PERCENT_SPACE_RECLAIMABLE,
         (((b.PERCENT_SPACE_USED-b.PERCENT_SPACE_RECLAIMABLE)/100)*a.SPACE_LIMIT)/(1024*1024) space_used,
         b.PERCENT_SPACE_USED-b.PERCENT_SPACE_RECLAIMABLE PERCENT_SPACE_USED
  from V$RECOVERY_FILE_DEST a,
       V$FLASH_RECOVERY_AREA_USAGE b
 order by a.name, a.space_limit, b.file_type
;

Usage

SQL> @flash_space.sql

Required Privileges

• SELECT on V$RECOVERY_FILE_DEST
• SELECT on V$FLASH_RECOVERY_AREA_USAGE

Sample Output

NAME                    SPACE_LIMIT FILE_TYPE           NUMBER_OF_FILES TOTAL_SPACE_USED TOTAL_PERCENT_SPACE_USED SPACE_RECLAIMABLE PERCENT_SPACE_RECLAIMABLE SPACE_USED PERCENT_SPACE_USED
--------------------- ------------- ------------------- --------------- ---------------- ------------------------ ----------------- ------------------------- ---------- ------------------
/u01/app/oracle/fra          20,480 ARCHIVED LOG                    145        8,456.32                    41.28          2,344.67                     11.45      6,111.65              29.83
/u01/app/oracle/fra          20,480 BACKUP PIECE                     23        4,233.21                    20.67            455.23                      2.22      3,777.98              18.45
/u01/app/oracle/fra          20,480 CONTROL FILE                      2          126.45                     0.62              0.00                      0.00        126.45               0.62
/u01/app/oracle/fra          20,480 ONLINE REDO LOG                   6          512.00                     2.50              0.00                      0.00        512.00               2.50
/u01/app/oracle/fra          20,480 FLASHBACK LOG                    89        1,234.56                     6.03            234.56                      1.15      1,000.00               4.88

Key Output Columns

• NAME: Flash Recovery Area location path
• SPACE_LIMIT: Total FRA size limit in MB
• FILE_TYPE: Type of files (ARCHIVED LOG, BACKUP PIECE, etc.)
• NUMBER_OF_FILES: Count of files for each type
• TOTAL_SPACE_USED: Total space consumed by file type in MB
• TOTAL_PERCENT_SPACE_USED: Percentage of FRA used by file type
• SPACE_RECLAIMABLE: Space that can be reclaimed in MB
• PERCENT_SPACE_RECLAIMABLE: Percentage of space that’s reclaimable
• SPACE_USED: Actual non-reclaimable space used in MB
• PERCENT_SPACE_USED: Net percentage of FRA actually consumed

Understanding FRA File Types

ARCHIVED LOG

• Purpose: Archive log files for point-in-time recovery
• Reclaimable: After backup to tape or after retention period
• Management: Automatic with RMAN retention policy

BACKUP PIECE

• Purpose: RMAN backup files (datafile, controlfile, spfile backups)
• Reclaimable: Based on retention policy and redundancy settings
• Management: RMAN automatically manages based on configuration

CONTROL FILE

• Purpose: Current and backup control files
• Reclaimable: Usually none (current control file always needed)
• Management: Automatic multiplexing if configured

ONLINE REDO LOG

• Purpose: Current online redo log files
• Reclaimable: None (active redo logs)
• Management: Size and groups configured at database creation

FLASHBACK LOG

• Purpose: Flashback database logs for fast recovery
• Reclaimable: Based on flashback retention target
• Management: Automatic based on DB_FLASHBACK_RETENTION_TARGET

FRA Space Management

Space Pressure Indicators

• Total Usage > 80%: Warning threshold
• Total Usage > 90%: Critical threshold
• Low Reclaimable Space: May need policy adjustment

Immediate Actions for Space Issues

-- Check current FRA configuration
SELECT name, value FROM v$parameter
WHERE name IN ('db_recovery_file_dest', 'db_recovery_file_dest_size');

-- Force archive log deletion (if backed up)
RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';

-- Crosscheck and delete expired backups
RMAN> CROSSCHECK BACKUP;
RMAN> DELETE EXPIRED BACKUP;

Common Use Cases

1. Space Monitoring

   • Regular FRA utilization checks
   • Identify space consumption trends
   • Plan for space allocation needs

2. Backup Strategy Planning

   • Analyze backup piece storage requirements
   • Plan retention policy adjustments
   • Optimize backup frequency

3. Archive Log Management

   • Monitor archive log accumulation
   • Plan backup frequency to tape
   • Size FRA appropriately

Troubleshooting FRA Issues

ORA-19815: FRA Space Usage Warning

• Cause: FRA usage > 85%
• Action: Review reclaimable space and backup policies

ORA-19809: FRA Space Limit Exceeded

• Cause: FRA completely full
• Action: Immediate space cleanup or size increase

High Archive Log Accumulation

-- Check archive generation rate
SELECT TO_CHAR(first_time, 'YYYY-MM-DD HH24') hour,
       COUNT(*) archives_generated,
       ROUND(SUM(blocks * block_size)/1024/1024) mb_generated
FROM v$archived_log
WHERE first_time > SYSDATE - 1
GROUP BY TO_CHAR(first_time, 'YYYY-MM-DD HH24')
ORDER BY hour;

FRA Sizing Guidelines

Calculation Formula

FRA Size = (Archive logs per day × Retention days) +
           (Backup size × Backup copies) +
           (Flashback logs) +
           (20% buffer)

Recommended Sizing

-- Check average daily archive generation
SELECT ROUND(AVG(daily_mb)) avg_daily_archive_mb
FROM (
  SELECT TO_CHAR(first_time, 'YYYY-MM-DD') day,
         ROUND(SUM(blocks * block_size)/1024/1024) daily_mb
  FROM v$archived_log
  WHERE first_time > SYSDATE - 30
  GROUP BY TO_CHAR(first_time, 'YYYY-MM-DD')
);

Monitoring and Alerting

Create Monitoring Query

-- FRA utilization summary
SELECT
  name,
  ROUND(space_limit/1024/1024/1024, 2) size_gb,
  ROUND(space_used/1024/1024/1024, 2) used_gb,
  ROUND(space_reclaimable/1024/1024/1024, 2) reclaimable_gb,
  ROUND((space_used-space_reclaimable)/space_limit*100, 2) net_pct_used
FROM v$recovery_file_dest;

Alert Thresholds

• Warning: Net usage > 75%
• Critical: Net usage > 85%
• Emergency: Net usage > 95%

RMAN Integration

Backup Policy Optimization

-- Check current retention policy
SHOW RETENTION POLICY;

-- Optimize for FRA space
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

Automatic Cleanup

-- Enable automatic cleanup
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;

-- Manual cleanup when needed
DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-1';

Best Practices

1. Regular Monitoring

   • Check FRA usage daily
   • Monitor growth trends
   • Set up automated alerts

2. Proactive Management

   • Size FRA appropriately (3-5 days of archive logs minimum)
   • Configure proper retention policies
   • Regular backup to tape

3. Emergency Procedures

   • Document space cleanup procedures
   • Test space management commands
   • Plan for rapid FRA expansion

Related Scripts

• FRA Usage Details
• Archive Log Analysis
• RMAN Backup Status