Backup Space

Review configured recovery area space, current usage, reclaimable space and the number of files stored in the destination.

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,
    number_of_files
FROM v$recovery_file_dest;

Break down recovery area usage by file type to identify where the available space is being consumed.

SELECT
    file_type,
    percent_space_used,
    percent_space_reclaimable,
    number_of_files
FROM v$recovery_area_usage
ORDER BY percent_space_used DESC;

Review RMAN backup piece sizes and completion times to identify large backup files and recent storage consumption.

SELECT
    completion_time,
    handle,
    bytes / 1024 / 1024 / 1024 AS size_gb,
    status
FROM v$backup_piece
WHERE deleted = 'NO'
ORDER BY completion_time DESC;

Review backup set sizes by completion time to understand how much storage individual RMAN backup sets require.

SELECT
    completion_time,
    backup_type,
    incremental_level,
    backup_size / 1024 / 1024 / 1024 AS size_gb
FROM v$backup_set
ORDER BY completion_time DESC;

Summarize archived redo volume by day to identify periods of high redo generation that can increase backup storage requirements.

SELECT
    TRUNC(completion_time) AS archive_day,
    ROUND(
        SUM(blocks * block_size) / 1024 / 1024 / 1024,
        2
    ) AS archived_redo_gb
FROM v$archived_log
WHERE completion_time IS NOT NULL
GROUP BY TRUNC(completion_time)
ORDER BY archive_day DESC;

Summarize recent RMAN backup piece volume by day to identify changes in backup storage requirements.

SELECT
    TRUNC(completion_time) AS backup_day,
    ROUND(
        SUM(bytes) / 1024 / 1024 / 1024,
        2
    ) AS backup_gb
FROM v$backup_piece
WHERE deleted = 'NO'
  AND status = 'A'
  AND completion_time >= SYSDATE - 30
GROUP BY TRUNC(completion_time)
ORDER BY backup_day DESC;