Recovery Diagnostics

Review the database role, open mode, logging mode and flashback status as the first recovery-readiness check.

SELECT
    name,
    db_unique_name,
    database_role,
    open_mode,
    log_mode,
    flashback_on
FROM v$database;

Identify data files currently reported as requiring media recovery and review the associated recovery change and time.

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

Review blocks reported as corrupt by Oracle to identify files and block ranges that may require further investigation or recovery.

SELECT
    file#,
    block#,
    blocks,
    corruption_change#,
    corruption_type
FROM v$database_block_corruption
ORDER BY file#, block#;

Review archive gaps reported by Oracle, which can be important when validating redo availability for recovery or standby operations.

SELECT
    thread#,
    low_sequence#,
    high_sequence#
FROM v$archive_gap
ORDER BY thread#;

Check recovery area capacity, current usage and reclaimable space to identify storage pressure that can affect recovery operations.

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;

Review recent RMAN operations, their status and execution time to identify backup or recovery activity that needs attention.

SELECT
    operation,
    status,
    start_time,
    end_time,
    ROUND(
        (end_time - start_time) * 24 * 60,
        1
    ) AS duration_minutes
FROM v$rman_status
WHERE start_time >= SYSDATE - 7
ORDER BY start_time DESC;