Oracle RMAN Backup Verification

Purpose

Verifies Oracle RMAN backup completeness and validity:

1. List recent backup sets and their status
2. Validate backup pieces are accessible and uncorrupted
3. Check retention policy compliance
4. Report any expired or obsolete backups

Required Permissions

SYSDBA or SYSBACKUP privilege to access the RMAN recovery catalog or control file backup metadata.

Risk Level

Low. Verification reads backup pieces and validates checksums — it does not modify backups or database files.

Note: VALIDATE BACKUPSET reads all backup pieces and performs checksum verification. On large backup sets, this adds I/O load to the backup destination. Run during off-peak windows on storage-sensitive environments.

Script

-- ============================================================
-- Oracle RMAN Backup Verification
-- Connect: rman target / (or rman target sys/password@tnsalias)
-- Permissions: SYSDBA or SYSBACKUP
-- Risk: Low (read + checksum verification)
-- ============================================================

-- 1. List completed backups (last 7 days)
LIST BACKUP COMPLETED AFTER 'SYSDATE-7' SUMMARY;

-- 2. List backup sets with status detail
LIST BACKUPSET SUMMARY;

-- 3. Check for expired backup pieces
--    (pieces that RMAN thinks exist but are not found on disk/tape)
CROSSCHECK BACKUP;

-- 4. List any expired backups found by crosscheck
LIST EXPIRED BACKUP;

-- 5. Validate the most recent full database backup
--    (reads backup pieces and verifies checksums; I/O intensive)
VALIDATE BACKUPSET
    (SELECT bs.recid FROM v$backup_set bs
     WHERE bs.backup_type = 'D'
     AND bs.completion_time = (SELECT MAX(completion_time)
                               FROM v$backup_set
                               WHERE backup_type = 'D'));

-- 6. Check retention policy compliance
--    (shows what is obsolete under the configured retention policy)
REPORT OBSOLETE;

-- 7. Show current RMAN configuration
SHOW ALL;

Equivalent SQL*Plus queries (no RMAN session required)

-- Recent backup history from control file
SELECT
    bs.recid,
    bs.start_time,
    bs.completion_time,
    bs.elapsed_seconds,
    bs.backup_type,
    bs.status,
    bs.pieces,
    bs.device_type,
    bs.compressed
FROM v$backup_set bs
WHERE bs.completion_time > SYSDATE - 14
ORDER BY bs.completion_time DESC;

-- Backup piece locations
SELECT
    bp.recid,
    bp.set_stamp,
    bp.piece#,
    bp.tag,
    bp.status,
    bp.handle,
    bp.device_type,
    bp.completion_time
FROM v$backup_piece bp
WHERE bp.completion_time > SYSDATE - 14
ORDER BY bp.completion_time DESC;

Expected Output

1. LIST BACKUP SUMMARY — table of recent backup sets with start/end time, type, status, and size
2. CROSSCHECK — each piece is marked AVAILABLE or EXPIRED
3. LIST EXPIRED — empty if all pieces are accessible; non-empty requires investigation
4. VALIDATE BACKUPSET — reports any corrupted blocks found; should show 0 errors
5. REPORT OBSOLETE — lists backups that can be deleted under the retention policy

Interpreting Results

• EXPIRED pieces: The backup piece file is missing from the expected location. If the backup was moved intentionally, update the catalog. If it is genuinely missing, you have a gap in your recovery capability.
• VALIDATE errors: Block corruption in a backup piece means that backup cannot be used for recovery. Immediately take a new backup and investigate the cause.
• REPORT OBSOLETE with many entries: The retention policy may not be enforced by a scheduled DELETE OBSOLETE job. Verify the RMAN maintenance schedule.

Safe Execution Guidance

Connect to RMAN as target database owner (SYSDBA or SYSBACKUP). Run verification checks weekly at minimum, and always before a planned failover or database upgrade.

Rollback Steps

Not applicable — read-only/verification script.

The DELETE EXPIRED and DELETE OBSOLETE RMAN commands are separate, destructive operations and are not included in this script.