RMAN Recovery

Identify data files that require media recovery and review the associated error and starting SCN.

SELECT
    file#,
    error,
    online_status,
    change#,
    time
FROM v$recover_file
ORDER BY file#;

Review archived redo logs currently identified as required for media recovery.

SELECT
    thread#,
    sequence#,
    filename,
    first_change#,
    next_change#
FROM v$recovery_log
ORDER BY
    thread#,
    sequence#;

Check fast recovery area quota, current usage, reclaimable space and file count.

SELECT
    name,
    ROUND(space_limit / 1024 / 1024 / 1024, 2) AS limit_gb,
    ROUND(space_used / 1024 / 1024 / 1024, 2) AS used_gb,
    ROUND(space_reclaimable / 1024 / 1024 / 1024, 2) AS reclaimable_gb,
    number_of_files
FROM v$recovery_file_dest;

Identify which recovery-area file types consume the available quota.

SELECT
    file_type,
    ROUND(percent_space_used, 2) AS pct_used,
    ROUND(percent_space_reclaimable, 2) AS pct_reclaimable,
    number_of_files
FROM v$recovery_area_usage
ORDER BY percent_space_used DESC;

Check restore points, guaranteed flashback settings and storage associated with each point.

SELECT
    name,
    time,
    scn,
    guarantee_flashback_database,
    storage_size,
    preserved
FROM v$restore_point
ORDER BY time DESC;

Review the oldest available flashback SCN and time supported by the current flashback logs.

SELECT
    oldest_flashback_scn,
    oldest_flashback_time,
    retention_target,
    estimated_flashback_size
FROM v$flashback_database_log;