database_recovery_manager.sql
-- ------------------------------------------------------------------------------------- File Name : database_recovery_manager.sql-- Author : Pierre Montbleau-- Description : Oracle database recovery and restore management utility-- Purpose : Manage database recovery operations, validate backups, and test restore procedures-- Call Syntax : @F:\DBA\Scripts\database_recovery_manager.sql (operation) (recovery_type) (target_time)-- Parameters : operation - Recovery operation (VALIDATE, RESTORE, RECOVER, TEST, REPORT)-- recovery_type - Type of recovery (COMPLETE, TABLESPACE, DATAFILE, UNTIL_TIME)-- target_time - Target time for point-in-time recovery (YYYY-MM-DD HH24:MI:SS)-- Last Modified: 2024-01-15-- -----------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------- INITIALIZATION: Set environment and validate parameters-- -----------------------------------------------------------------------------------
-- Set substitution variablesset verify OFF
CLEAR SCREENCOLUMN "_USER" NEW_VALUE _USER NOPRINTCOLUMN "_CONNECT_IDENTIFIER" NEW_VALUE _CONNECT_IDENTIFIER NOPRINTCOLUMN DATE1 NEW_VALUE DATE1 NOPRINTCOLUMN TIME1 NEW_VALUE TIME1 NOPRINT
SELECT USER AS "_USER", SYS_CONTEXT('USERENV', 'DB_NAME') AS "_CONNECT_IDENTIFIER", TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS DATE1, TO_CHAR(SYSDATE, 'HH24:MI:SS') AS TIME1FROM DUAL;
DEFINE DATE1 = &DATE1DEFINE TIME1 = &TIME1
-- Generate unique filename for output redirectionCOLUMN output_filename NEW_VALUE output_filename NOPRINTSELECT 'F:\DBA\Scripts\AI\Reports\DB_recovery_manager_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '_' || '&1' || '_' || '&2' || '_' || '&3' || '.log' AS output_filenameFROM DUAL;
DEFINE output_filename = &output_filename
SET VERIFY OFFSET FEEDBACK ONSET LINESIZE 200SET PAGESIZE 1000SET SERVEROUTPUT ON
-- Redirect output to unique fileSPOOL &output_filename
PROMPT Starting Database Recovery Manager...PROMPT ======================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Operation: &1PROMPT Recovery Type: &2PROMPT Target Time: &3PROMPT Timestamp: &DATE1 &TIME1PROMPT
-- Validate parametersDECLARE v_operation VARCHAR2(20) := UPPER('&1'); v_recovery_type VARCHAR2(20) := UPPER('&2'); v_target_time VARCHAR2(20) := '&3';BEGIN IF v_operation NOT IN ('VALIDATE', 'RESTORE', 'RECOVER', 'TEST', 'REPORT') THEN RAISE_APPLICATION_ERROR(-20001, 'Operation must be VALIDATE, RESTORE, RECOVER, TEST, or REPORT'); END IF; IF v_operation IN ('RESTORE', 'RECOVER') AND v_recovery_type NOT IN ('COMPLETE', 'TABLESPACE', 'DATAFILE', 'UNTIL_TIME') THEN RAISE_APPLICATION_ERROR(-20002, 'Recovery type must be COMPLETE, TABLESPACE, DATAFILE, or UNTIL_TIME'); END IF; IF v_recovery_type = 'UNTIL_TIME' AND v_target_time IS NULL THEN RAISE_APPLICATION_ERROR(-20003, 'Target time required for UNTIL_TIME recovery'); END IF; IF v_recovery_type = 'UNTIL_TIME' THEN BEGIN -- Validate timestamp format IF TO_DATE(v_target_time, 'YYYY-MM-DD HH24:MI:SS') > SYSDATE THEN RAISE_APPLICATION_ERROR(-20004, 'Target time cannot be in the future'); END IF; EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20005, 'Invalid target time format. Use: YYYY-MM-DD HH24:MI:SS'); END; END IF; DBMS_OUTPUT.PUT_LINE('Parameter validation successful');EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- BACKUP VALIDATION STATUS: Verify backup availability and integrity-- -----------------------------------------------------------------------------------
PROMPT PROMPT 1. BACKUP VALIDATION STATUS-- Prompt ===========================
COLUMN validation_check FORMAT A40COLUMN status FORMAT A20COLUMN details FORMAT A50COLUMN recommendation FORMAT A60
SELECT 'Recent Full Database Backup' as validation_check, CASE WHEN MAX(end_time) > SYSDATE - 1 THEN 'AVAILABLE' WHEN MAX(end_time) > SYSDATE - 7 THEN 'AGING' ELSE 'MISSING' END as status, NVL(TO_CHAR(MAX(end_time), 'YYYY-MM-DD HH24:MI:SS'), 'No backup found') as details, CASE WHEN MAX(end_time) > SYSDATE - 1 THEN 'Backup current' WHEN MAX(end_time) > SYSDATE - 7 THEN 'Schedule new backup soon' ELSE 'IMMEDIATE BACKUP REQUIRED' END as recommendationFROM v$rman_backup_job_detailsWHERE status = 'COMPLETED' AND input_type = 'DB FULL' -- Changed from backup_type to input_typeUNION ALLSELECT 'Archive Log Backups', CASE WHEN COUNT(*) > 0 THEN 'AVAILABLE' ELSE 'MISSING' END, TO_CHAR(COUNT(*)) || ' backups found in last 24 hours', CASE WHEN COUNT(*) > 0 THEN 'Archive log backup active' ELSE 'Enable archive log backups' ENDFROM v$rman_backup_job_detailsWHERE status = 'COMPLETED' AND input_type = 'ARCHIVELOG' -- Changed from backup_type to input_type AND end_time > SYSDATE - 1 -- Changed from completion_time to end_timeUNION ALLSELECT 'Flash Recovery Area Usage', CASE WHEN (SUM(r.SPACE_USED) / NULLIF(MAX(TO_NUMBER(p.value)), 0)) < 0.8 THEN 'HEALTHY' WHEN (SUM(r.SPACE_USED) / NULLIF(MAX(TO_NUMBER(p.value)), 0)) < 0.9 THEN 'WARNING' ELSE 'CRITICAL' END, ROUND((SUM(r.SPACE_USED) / NULLIF(MAX(TO_NUMBER(p.value)), 0)) * 100, 2) || '% used', CASE WHEN (SUM(r.SPACE_USED) / NULLIF(MAX(TO_NUMBER(p.value)), 0)) < 0.8 THEN 'Adequate free space' WHEN (SUM(r.SPACE_USED) / NULLIF(MAX(TO_NUMBER(p.value)), 0)) < 0.9 THEN 'Monitor space usage' ELSE 'IMMEDIATE CLEANUP REQUIRED' ENDFROM v$recovery_file_dest r, v$parameter pWHERE r.name = 'db_recovery_file_dest_size'GROUP BY r.nameORDER BY 4;
-- ------------------------------------------------------------------------------------- RECOVERY WINDOW ANALYSIS: Determine available recovery points-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. RECOVERY WINDOW ANALYSISPROMPT ============================
COLUMN recovery_point FORMAT A30COLUMN timestamp FORMAT A20COLUMN type FORMAT A15COLUMN recoverable FORMAT A5
SELECT 'Earliest Recoverable Time' as recovery_point, TO_CHAR(MIN(first_time), 'YYYY-MM-DD HH24:MI:SS') as timestamp, 'ARCHIVELOG' as type, 'YES' as recoverableFROM v$archived_logWHERE first_time IS NOT NULLUNION ALLSELECT 'Latest Recoverable Time', TO_CHAR(MAX(first_time), 'YYYY-MM-DD HH24:MI:SS'), 'ARCHIVELOG', 'YES'FROM v$archived_logWHERE first_time IS NOT NULLUNION ALLSELECT 'Current Time', TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'), 'LIVE', 'N/A'FROM dualUNION ALLSELECT 'Oldest Backup Available', TO_CHAR(MIN(completion_time), 'YYYY-MM-DD HH24:MI:SS'), 'BACKUP', 'YES'FROM v$backup_pieceWHERE status = 'A'ORDER BY 2;
-- ------------------------------------------------------------------------------------- DATAFILE BACKUP STATUS: Individual datafile backup information-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. DATAFILE BACKUP STATUSPROMPT ==========================
COLUMN file_name FORMAT A70COLUMN tablespace_name FORMAT A30COLUMN last_backup FORMAT A20COLUMN backup_status FORMAT A15COLUMN recovery_priority FORMAT A10
SELECT df.name as file_name, ts.name as tablespace_name, TO_CHAR(bp.completion_time, 'YYYY-MM-DD HH24:MI:SS') as last_backup, CASE WHEN bp.completion_time > SYSDATE - 1 THEN 'CURRENT' WHEN bp.completion_time > SYSDATE - 7 THEN 'RECENT' ELSE 'STALE' END as backup_status, CASE ts.name WHEN 'SYSTEM' THEN 'CRITICAL' WHEN 'SYSAUX' THEN 'HIGH' WHEN 'UNDOTBS1' THEN 'HIGH' ELSE 'MEDIUM' END as recovery_priorityFROM v$datafile dfJOIN v$tablespace ts ON df.ts# = ts.ts#LEFT JOIN ( SELECT bd.file#, MAX(bp.completion_time) as completion_time FROM v$backup_datafile bd JOIN v$backup_piece bp ON bd.set_stamp = bp.set_stamp AND bd.set_count = bp.set_count WHERE bp.status = 'A' GROUP BY bd.file#) bp ON df.file# = bp.file#ORDER BY 5,4;
-- ------------------------------------------------------------------------------------- RESTORE VALIDATION: Simulate restore operations without actual restore-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. RESTORE VALIDATION SIMULATIONPROMPT =================================
BEGIN IF UPPER('&1') = 'VALIDATE' THEN DECLARE v_validate_result VARCHAR2(100); BEGIN DBMS_OUTPUT.PUT_LINE('Starting restore validation...'); -- Validate backups are restorable FOR backup_rec IN ( SELECT DISTINCT bp.handle FROM v$backup_piece bp WHERE bp.status = 'A' AND ROWNUM <= 5 -- Limit for demonstration ) LOOP BEGIN -- Note: Actual VALIDATE command would be executed via RMAN -- This is a simulation of the validation process DBMS_OUTPUT.PUT_LINE('Validating backup piece: ' || backup_rec.handle); v_validate_result := 'SUCCESS'; EXCEPTION WHEN OTHERS THEN v_validate_result := 'FAILED: ' || SQLERRM; END; DBMS_OUTPUT.PUT_LINE('Result: ' || v_validate_result); END LOOP; DBMS_OUTPUT.PUT_LINE('Restore validation completed'); DBMS_OUTPUT.PUT_LINE('Note: Full validation requires RMAN VALIDATE command'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error during restore validation: ' || SQLERRM); END; ELSE DBMS_OUTPUT.PUT_LINE('Restore validation skipped (operation: ' || UPPER('&1') || ')'); END IF;END;/
-- ------------------------------------------------------------------------------------- RECOVERY SCENARIO ANALYSIS: Analyze recovery requirements-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. RECOVERY SCENARIO ANALYSISPROMPT ==============================
COLUMN scenario FORMAT A30COLUMN recovery_type FORMAT A20COLUMN estimated_time FORMAT A15COLUMN complexity FORMAT A15COLUMN prerequisites FORMAT A60
SELECT 'Complete Database Loss' as scenario, 'COMPLETE' as recovery_type, '2-8 hours' as estimated_time, 'HIGH' as complexity, 'Full backup + all archivelogs' as prerequisitesFROM dualUNION ALLSELECT 'Tablespace Corruption', 'TABLESPACE', '1-2 hours', 'MEDIUM', 'Tablespace backup + archivelogs since backup'FROM dualUNION ALLSELECT 'Single Datafile Loss', 'DATAFILE', '30-60 min', 'LOW', 'Datafile backup + archivelogs since backup'FROM dualUNION ALLSELECT 'Point-in-Time Recovery', 'UNTIL_TIME', '2-4 hours', 'HIGH', 'Full backup + archivelogs to target time'FROM dualORDER BY 4;
-- ------------------------------------------------------------------------------------- RECOVERY READINESS CHECKLIST: Pre-recovery verification-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. RECOVERY READINESS CHECKLISTPROMPT =================================
COLUMN checklist_item FORMAT A50COLUMN status FORMAT A15COLUMN verification FORMAT A50
SELECT 'Database in ARCHIVELOG mode' as checklist_item, CASE WHEN log_mode = 'ARCHIVELOG' THEN 'PASS' ELSE 'FAIL' END as status, 'Required for point-in-time recovery' as verificationFROM v$databaseUNION ALLSELECT 'Recent full database backup exists', CASE WHEN EXISTS ( SELECT 1 FROM v$rman_backup_job_details WHERE input_type = 'DB FULL' -- Changed from backup_type to input_type AND status = 'COMPLETED' AND end_time > SYSDATE - 7 -- Changed from completion_time to end_time ) THEN 'PASS' ELSE 'FAIL' END, 'Backup within last 7 days'FROM dualUNION ALLSELECT 'Archive log backups current', CASE WHEN EXISTS ( SELECT 1 FROM v$rman_backup_job_details WHERE input_type = 'ARCHIVELOG' -- Changed from backup_type to input_type AND status = 'COMPLETED' AND end_time > SYSDATE - 1 -- Changed from completion_time to end_time ) THEN 'PASS' ELSE 'FAIL' END, 'Archive logs backed up today'FROM dualUNION ALLSELECT 'Flash Recovery Area has free space', CASE WHEN (SELECT (1 - (SUM(SPACE_USED) / NULLIF(MAX(TO_NUMBER(value)), 0))) * 100 FROM v$recovery_file_dest r, v$parameter p WHERE r.name = 'db_recovery_file_dest_size') > 20 THEN 'PASS' ELSE 'FAIL' END, 'At least 20% free space in FRA'FROM dualUNION ALLSELECT 'Critical datafiles backed up', CASE WHEN NOT EXISTS ( SELECT 1 FROM v$datafile df WHERE df.name LIKE '%system%' AND NOT EXISTS ( SELECT 1 FROM v$backup_datafile bd WHERE bd.file# = df.file# AND bd.completion_time > SYSDATE - 7 ) ) THEN 'PASS' ELSE 'FAIL' END, 'SYSTEM tablespace has backup within 7 days'FROM dualORDER BY 2;
-- ------------------------------------------------------------------------------------- RECOVERY TIME ESTIMATION: Estimate recovery duration-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. RECOVERY TIME ESTIMATIONPROMPT ============================
COLUMN recovery_component FORMAT A30COLUMN size_gb FORMAT 999,999.99COLUMN transfer_rate_mbps FORMAT 999,999COLUMN estimated_time FORMAT A15
SELECT 'Datafile Restore' as recovery_component, ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) as size_gb, 100 as transfer_rate_mbps, -- Assumed transfer rate TO_CHAR(ROUND((SUM(bytes) / 1024 / 1024) / (100 * 60), 2)) || ' min' as estimated_timeFROM v$datafileUNION ALLSELECT 'Archive Log Application', ROUND(SUM(blocks * block_size) / 1024 / 1024 / 1024, 2), 200, -- Faster for sequential reads TO_CHAR(ROUND((SUM(blocks * block_size) / 1024 / 1024) / (200 * 60), 2)) || ' min'FROM v$archived_logWHERE first_time > SYSDATE - 1 -- Last 24 hoursUNION ALLSELECT 'Redo Log Recovery', 1.0, -- Estimated average 500, -- Very fast for redo '5-15 min'FROM dualORDER BY 1;
-- ------------------------------------------------------------------------------------- RECOVERY PROCEDURE GENERATION: Generate recovery commands-- -----------------------------------------------------------------------------------
PROMPT PROMPT 8. RECOVERY PROCEDURE GENERATIONPROMPT =================================
BEGIN IF UPPER('&1') IN ('RESTORE', 'RECOVER', 'TEST') THEN DECLARE v_recovery_type VARCHAR2(20) := UPPER('&2'); v_target_time VARCHAR2(20) := '&3'; BEGIN DBMS_OUTPUT.PUT_LINE('Generating recovery procedure for: ' || v_recovery_type); DBMS_OUTPUT.PUT_LINE('================================'); CASE v_recovery_type WHEN 'COMPLETE' THEN DBMS_OUTPUT.PUT_LINE('-- Complete Database Recovery Procedure'); DBMS_OUTPUT.PUT_LINE('-- 1. Shutdown database immediate'); DBMS_OUTPUT.PUT_LINE('-- 2. Startup mount'); DBMS_OUTPUT.PUT_LINE('-- 3. RMAN> RESTORE DATABASE;'); DBMS_OUTPUT.PUT_LINE('-- 4. RMAN> RECOVER DATABASE;'); DBMS_OUTPUT.PUT_LINE('-- 5. ALTER DATABASE OPEN;'); WHEN 'TABLESPACE' THEN DBMS_OUTPUT.PUT_LINE('-- Tablespace Recovery Procedure'); DBMS_OUTPUT.PUT_LINE('-- 1. Take tablespace offline:'); DBMS_OUTPUT.PUT_LINE('-- ALTER TABLESPACE tablespace_name OFFLINE IMMEDIATE;'); DBMS_OUTPUT.PUT_LINE('-- 2. RMAN> RESTORE TABLESPACE tablespace_name;'); DBMS_OUTPUT.PUT_LINE('-- 3. RMAN> RECOVER TABLESPACE tablespace_name;'); DBMS_OUTPUT.PUT_LINE('-- 4. Bring tablespace online:'); DBMS_OUTPUT.PUT_LINE('-- ALTER TABLESPACE tablespace_name ONLINE;'); WHEN 'DATAFILE' THEN DBMS_OUTPUT.PUT_LINE('-- Datafile Recovery Procedure'); DBMS_OUTPUT.PUT_LINE('-- 1. Take datafile offline:'); DBMS_OUTPUT.PUT_LINE('-- ALTER DATABASE DATAFILE ''file_name'' OFFLINE;'); DBMS_OUTPUT.PUT_LINE('-- 2. RMAN> RESTORE DATAFILE file_number;'); DBMS_OUTPUT.PUT_LINE('-- 3. RMAN> RECOVER DATAFILE file_number;'); DBMS_OUTPUT.PUT_LINE('-- 4. Bring datafile online:'); DBMS_OUTPUT.PUT_LINE('-- ALTER DATABASE DATAFILE ''file_name'' ONLINE;'); WHEN 'UNTIL_TIME' THEN DBMS_OUTPUT.PUT_LINE('-- Point-in-Time Recovery Procedure'); DBMS_OUTPUT.PUT_LINE('-- Target time: ' || v_target_time); DBMS_OUTPUT.PUT_LINE('-- 1. Shutdown database immediate'); DBMS_OUTPUT.PUT_LINE('-- 2. Startup mount'); DBMS_OUTPUT.PUT_LINE('-- 3. RMAN> RESTORE DATABASE;'); DBMS_OUTPUT.PUT_LINE('-- 4. RMAN> RECOVER DATABASE UNTIL TIME "' || v_target_time || '";'); DBMS_OUTPUT.PUT_LINE('-- 5. ALTER DATABASE OPEN RESETLOGS;'); END CASE; DBMS_OUTPUT.PUT_LINE('================================'); DBMS_OUTPUT.PUT_LINE('Note: These are sample procedures. Actual commands may vary.'); DBMS_OUTPUT.PUT_LINE('Always test recovery procedures in a non-production environment.'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error generating recovery procedure: ' || SQLERRM); END; ELSE DBMS_OUTPUT.PUT_LINE('Recovery procedure generation skipped (operation: ' || UPPER('&1') || ')'); END IF;END;/
-- ------------------------------------------------------------------------------------- RECOVERY TESTING RECOMMENDATIONS: Best practices for recovery testing-- -----------------------------------------------------------------------------------
PROMPT PROMPT 9. RECOVERY TESTING RECOMMENDATIONSPROMPT ====================================
COLUMN test_scenario FORMAT A30COLUMN frequency FORMAT A15COLUMN test_method FORMAT A60COLUMN success_criteria FORMAT A60
SELECT 'Full Database Recovery' as test_scenario, 'QUARTERLY' as frequency, 'Restore to test environment using production backups' as test_method, 'Database opens successfully, critical data accessible' as success_criteriaFROM dualUNION ALLSELECT 'Tablespace Recovery', 'MONTHLY', 'Restore individual tablespace in test environment', 'Tablespace recovers, objects accessible'FROM dualUNION ALLSELECT 'Point-in-Time Recovery', 'SEMI-ANNUAL', 'Recover to specific timestamp in test environment', 'Database recovers to specified time, data consistent'FROM dualUNION ALLSELECT 'Datafile Recovery', 'MONTHLY', 'Simulate datafile loss and recovery', 'Datafile recovers, no data loss'FROM dualORDER BY 2;
-- ------------------------------------------------------------------------------------- COMPLETION: Recovery management summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT ======================================PROMPT DATABASE RECOVERY MANAGEMENT COMPLETEDPROMPT ======================================PROMPT Operation: &1PROMPT Recovery Type: &2PROMPT Target Time: &3PROMPT Completion Time: &DATE1 &TIME1PROMPT PROMPT Key Recovery Insights:PROMPT - Validate backup integrity regularlyPROMPT - Test recovery procedures frequentlyPROMPT - Document recovery time objectivesPROMPT - Maintain recovery documentation updatedPROMPT
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment and stop spooling-- -----------------------------------------------------------------------------------
SPOOL OFF
CLEAR COLUMNSSET VERIFY ONSET TIMING OFF
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------- INITIALIZATION: Set environment and validate parameters-- -----------------------------------------------------------------------------------
-- Set substitution variablesset verify OFF
CLEAR SCREENCOLUMN "_USER" NEW_VALUE _USER NOPRINTCOLUMN "_CONNECT_IDENTIFIER" NEW_VALUE _CONNECT_IDENTIFIER NOPRINTCOLUMN DATE1 NEW_VALUE DATE1 NOPRINTCOLUMN TIME1 NEW_VALUE TIME1 NOPRINT
SELECT USER AS "_USER", SYS_CONTEXT('USERENV', 'DB_NAME') AS "_CONNECT_IDENTIFIER", TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS DATE1, TO_CHAR(SYSDATE, 'HH24:MI:SS') AS TIME1FROM DUAL;
DEFINE DATE1 = &DATE1DEFINE TIME1 = &TIME1
-- Generate unique filename for output redirectionCOLUMN output_filename NEW_VALUE output_filename NOPRINTSELECT 'F:\DBA\Scripts\AI\Reports\DB_recovery_manager_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '_' || '&1' || '_' || '&2' || '_' || '&3' || '.log' AS output_filenameFROM DUAL;
DEFINE output_filename = &output_filename
SET VERIFY OFFSET FEEDBACK ONSET LINESIZE 200SET PAGESIZE 1000SET SERVEROUTPUT ON
-- Redirect output to unique fileSPOOL &output_filename
PROMPT Starting Database Recovery Manager...PROMPT ======================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Operation: &1PROMPT Recovery Type: &2PROMPT Target Time: &3PROMPT Timestamp: &DATE1 &TIME1PROMPT
-- Validate parametersDECLARE v_operation VARCHAR2(20) := UPPER('&1'); v_recovery_type VARCHAR2(20) := UPPER('&2'); v_target_time VARCHAR2(20) := '&3';BEGIN IF v_operation NOT IN ('VALIDATE', 'RESTORE', 'RECOVER', 'TEST', 'REPORT') THEN RAISE_APPLICATION_ERROR(-20001, 'Operation must be VALIDATE, RESTORE, RECOVER, TEST, or REPORT'); END IF; IF v_operation IN ('RESTORE', 'RECOVER') AND v_recovery_type NOT IN ('COMPLETE', 'TABLESPACE', 'DATAFILE', 'UNTIL_TIME') THEN RAISE_APPLICATION_ERROR(-20002, 'Recovery type must be COMPLETE, TABLESPACE, DATAFILE, or UNTIL_TIME'); END IF; IF v_recovery_type = 'UNTIL_TIME' AND v_target_time IS NULL THEN RAISE_APPLICATION_ERROR(-20003, 'Target time required for UNTIL_TIME recovery'); END IF; IF v_recovery_type = 'UNTIL_TIME' THEN BEGIN -- Validate timestamp format IF TO_DATE(v_target_time, 'YYYY-MM-DD HH24:MI:SS') > SYSDATE THEN RAISE_APPLICATION_ERROR(-20004, 'Target time cannot be in the future'); END IF; EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20005, 'Invalid target time format. Use: YYYY-MM-DD HH24:MI:SS'); END; END IF; DBMS_OUTPUT.PUT_LINE('Parameter validation successful');EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- BACKUP VALIDATION STATUS: Verify backup availability and integrity-- -----------------------------------------------------------------------------------
PROMPT PROMPT 1. BACKUP VALIDATION STATUS-- Prompt ===========================
COLUMN validation_check FORMAT A40COLUMN status FORMAT A20COLUMN details FORMAT A50COLUMN recommendation FORMAT A60
SELECT 'Recent Full Database Backup' as validation_check, CASE WHEN MAX(end_time) > SYSDATE - 1 THEN 'AVAILABLE' WHEN MAX(end_time) > SYSDATE - 7 THEN 'AGING' ELSE 'MISSING' END as status, NVL(TO_CHAR(MAX(end_time), 'YYYY-MM-DD HH24:MI:SS'), 'No backup found') as details, CASE WHEN MAX(end_time) > SYSDATE - 1 THEN 'Backup current' WHEN MAX(end_time) > SYSDATE - 7 THEN 'Schedule new backup soon' ELSE 'IMMEDIATE BACKUP REQUIRED' END as recommendationFROM v$rman_backup_job_detailsWHERE status = 'COMPLETED' AND input_type = 'DB FULL' -- Changed from backup_type to input_typeUNION ALLSELECT 'Archive Log Backups', CASE WHEN COUNT(*) > 0 THEN 'AVAILABLE' ELSE 'MISSING' END, TO_CHAR(COUNT(*)) || ' backups found in last 24 hours', CASE WHEN COUNT(*) > 0 THEN 'Archive log backup active' ELSE 'Enable archive log backups' ENDFROM v$rman_backup_job_detailsWHERE status = 'COMPLETED' AND input_type = 'ARCHIVELOG' -- Changed from backup_type to input_type AND end_time > SYSDATE - 1 -- Changed from completion_time to end_timeUNION ALLSELECT 'Flash Recovery Area Usage', CASE WHEN (SUM(r.SPACE_USED) / NULLIF(MAX(TO_NUMBER(p.value)), 0)) < 0.8 THEN 'HEALTHY' WHEN (SUM(r.SPACE_USED) / NULLIF(MAX(TO_NUMBER(p.value)), 0)) < 0.9 THEN 'WARNING' ELSE 'CRITICAL' END, ROUND((SUM(r.SPACE_USED) / NULLIF(MAX(TO_NUMBER(p.value)), 0)) * 100, 2) || '% used', CASE WHEN (SUM(r.SPACE_USED) / NULLIF(MAX(TO_NUMBER(p.value)), 0)) < 0.8 THEN 'Adequate free space' WHEN (SUM(r.SPACE_USED) / NULLIF(MAX(TO_NUMBER(p.value)), 0)) < 0.9 THEN 'Monitor space usage' ELSE 'IMMEDIATE CLEANUP REQUIRED' ENDFROM v$recovery_file_dest r, v$parameter pWHERE r.name = 'db_recovery_file_dest_size'GROUP BY r.nameORDER BY 4;
-- ------------------------------------------------------------------------------------- RECOVERY WINDOW ANALYSIS: Determine available recovery points-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. RECOVERY WINDOW ANALYSISPROMPT ============================
COLUMN recovery_point FORMAT A30COLUMN timestamp FORMAT A20COLUMN type FORMAT A15COLUMN recoverable FORMAT A5
SELECT 'Earliest Recoverable Time' as recovery_point, TO_CHAR(MIN(first_time), 'YYYY-MM-DD HH24:MI:SS') as timestamp, 'ARCHIVELOG' as type, 'YES' as recoverableFROM v$archived_logWHERE first_time IS NOT NULLUNION ALLSELECT 'Latest Recoverable Time', TO_CHAR(MAX(first_time), 'YYYY-MM-DD HH24:MI:SS'), 'ARCHIVELOG', 'YES'FROM v$archived_logWHERE first_time IS NOT NULLUNION ALLSELECT 'Current Time', TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'), 'LIVE', 'N/A'FROM dualUNION ALLSELECT 'Oldest Backup Available', TO_CHAR(MIN(completion_time), 'YYYY-MM-DD HH24:MI:SS'), 'BACKUP', 'YES'FROM v$backup_pieceWHERE status = 'A'ORDER BY 2;
-- ------------------------------------------------------------------------------------- DATAFILE BACKUP STATUS: Individual datafile backup information-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. DATAFILE BACKUP STATUSPROMPT ==========================
COLUMN file_name FORMAT A70COLUMN tablespace_name FORMAT A30COLUMN last_backup FORMAT A20COLUMN backup_status FORMAT A15COLUMN recovery_priority FORMAT A10
SELECT df.name as file_name, ts.name as tablespace_name, TO_CHAR(bp.completion_time, 'YYYY-MM-DD HH24:MI:SS') as last_backup, CASE WHEN bp.completion_time > SYSDATE - 1 THEN 'CURRENT' WHEN bp.completion_time > SYSDATE - 7 THEN 'RECENT' ELSE 'STALE' END as backup_status, CASE ts.name WHEN 'SYSTEM' THEN 'CRITICAL' WHEN 'SYSAUX' THEN 'HIGH' WHEN 'UNDOTBS1' THEN 'HIGH' ELSE 'MEDIUM' END as recovery_priorityFROM v$datafile dfJOIN v$tablespace ts ON df.ts# = ts.ts#LEFT JOIN ( SELECT bd.file#, MAX(bp.completion_time) as completion_time FROM v$backup_datafile bd JOIN v$backup_piece bp ON bd.set_stamp = bp.set_stamp AND bd.set_count = bp.set_count WHERE bp.status = 'A' GROUP BY bd.file#) bp ON df.file# = bp.file#ORDER BY 5,4;
-- ------------------------------------------------------------------------------------- RESTORE VALIDATION: Simulate restore operations without actual restore-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. RESTORE VALIDATION SIMULATIONPROMPT =================================
BEGIN IF UPPER('&1') = 'VALIDATE' THEN DECLARE v_validate_result VARCHAR2(100); BEGIN DBMS_OUTPUT.PUT_LINE('Starting restore validation...'); -- Validate backups are restorable FOR backup_rec IN ( SELECT DISTINCT bp.handle FROM v$backup_piece bp WHERE bp.status = 'A' AND ROWNUM <= 5 -- Limit for demonstration ) LOOP BEGIN -- Note: Actual VALIDATE command would be executed via RMAN -- This is a simulation of the validation process DBMS_OUTPUT.PUT_LINE('Validating backup piece: ' || backup_rec.handle); v_validate_result := 'SUCCESS'; EXCEPTION WHEN OTHERS THEN v_validate_result := 'FAILED: ' || SQLERRM; END; DBMS_OUTPUT.PUT_LINE('Result: ' || v_validate_result); END LOOP; DBMS_OUTPUT.PUT_LINE('Restore validation completed'); DBMS_OUTPUT.PUT_LINE('Note: Full validation requires RMAN VALIDATE command'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error during restore validation: ' || SQLERRM); END; ELSE DBMS_OUTPUT.PUT_LINE('Restore validation skipped (operation: ' || UPPER('&1') || ')'); END IF;END;/
-- ------------------------------------------------------------------------------------- RECOVERY SCENARIO ANALYSIS: Analyze recovery requirements-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. RECOVERY SCENARIO ANALYSISPROMPT ==============================
COLUMN scenario FORMAT A30COLUMN recovery_type FORMAT A20COLUMN estimated_time FORMAT A15COLUMN complexity FORMAT A15COLUMN prerequisites FORMAT A60
SELECT 'Complete Database Loss' as scenario, 'COMPLETE' as recovery_type, '2-8 hours' as estimated_time, 'HIGH' as complexity, 'Full backup + all archivelogs' as prerequisitesFROM dualUNION ALLSELECT 'Tablespace Corruption', 'TABLESPACE', '1-2 hours', 'MEDIUM', 'Tablespace backup + archivelogs since backup'FROM dualUNION ALLSELECT 'Single Datafile Loss', 'DATAFILE', '30-60 min', 'LOW', 'Datafile backup + archivelogs since backup'FROM dualUNION ALLSELECT 'Point-in-Time Recovery', 'UNTIL_TIME', '2-4 hours', 'HIGH', 'Full backup + archivelogs to target time'FROM dualORDER BY 4;
-- ------------------------------------------------------------------------------------- RECOVERY READINESS CHECKLIST: Pre-recovery verification-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. RECOVERY READINESS CHECKLISTPROMPT =================================
COLUMN checklist_item FORMAT A50COLUMN status FORMAT A15COLUMN verification FORMAT A50
SELECT 'Database in ARCHIVELOG mode' as checklist_item, CASE WHEN log_mode = 'ARCHIVELOG' THEN 'PASS' ELSE 'FAIL' END as status, 'Required for point-in-time recovery' as verificationFROM v$databaseUNION ALLSELECT 'Recent full database backup exists', CASE WHEN EXISTS ( SELECT 1 FROM v$rman_backup_job_details WHERE input_type = 'DB FULL' -- Changed from backup_type to input_type AND status = 'COMPLETED' AND end_time > SYSDATE - 7 -- Changed from completion_time to end_time ) THEN 'PASS' ELSE 'FAIL' END, 'Backup within last 7 days'FROM dualUNION ALLSELECT 'Archive log backups current', CASE WHEN EXISTS ( SELECT 1 FROM v$rman_backup_job_details WHERE input_type = 'ARCHIVELOG' -- Changed from backup_type to input_type AND status = 'COMPLETED' AND end_time > SYSDATE - 1 -- Changed from completion_time to end_time ) THEN 'PASS' ELSE 'FAIL' END, 'Archive logs backed up today'FROM dualUNION ALLSELECT 'Flash Recovery Area has free space', CASE WHEN (SELECT (1 - (SUM(SPACE_USED) / NULLIF(MAX(TO_NUMBER(value)), 0))) * 100 FROM v$recovery_file_dest r, v$parameter p WHERE r.name = 'db_recovery_file_dest_size') > 20 THEN 'PASS' ELSE 'FAIL' END, 'At least 20% free space in FRA'FROM dualUNION ALLSELECT 'Critical datafiles backed up', CASE WHEN NOT EXISTS ( SELECT 1 FROM v$datafile df WHERE df.name LIKE '%system%' AND NOT EXISTS ( SELECT 1 FROM v$backup_datafile bd WHERE bd.file# = df.file# AND bd.completion_time > SYSDATE - 7 ) ) THEN 'PASS' ELSE 'FAIL' END, 'SYSTEM tablespace has backup within 7 days'FROM dualORDER BY 2;
-- ------------------------------------------------------------------------------------- RECOVERY TIME ESTIMATION: Estimate recovery duration-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. RECOVERY TIME ESTIMATIONPROMPT ============================
COLUMN recovery_component FORMAT A30COLUMN size_gb FORMAT 999,999.99COLUMN transfer_rate_mbps FORMAT 999,999COLUMN estimated_time FORMAT A15
SELECT 'Datafile Restore' as recovery_component, ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) as size_gb, 100 as transfer_rate_mbps, -- Assumed transfer rate TO_CHAR(ROUND((SUM(bytes) / 1024 / 1024) / (100 * 60), 2)) || ' min' as estimated_timeFROM v$datafileUNION ALLSELECT 'Archive Log Application', ROUND(SUM(blocks * block_size) / 1024 / 1024 / 1024, 2), 200, -- Faster for sequential reads TO_CHAR(ROUND((SUM(blocks * block_size) / 1024 / 1024) / (200 * 60), 2)) || ' min'FROM v$archived_logWHERE first_time > SYSDATE - 1 -- Last 24 hoursUNION ALLSELECT 'Redo Log Recovery', 1.0, -- Estimated average 500, -- Very fast for redo '5-15 min'FROM dualORDER BY 1;
-- ------------------------------------------------------------------------------------- RECOVERY PROCEDURE GENERATION: Generate recovery commands-- -----------------------------------------------------------------------------------
PROMPT PROMPT 8. RECOVERY PROCEDURE GENERATIONPROMPT =================================
BEGIN IF UPPER('&1') IN ('RESTORE', 'RECOVER', 'TEST') THEN DECLARE v_recovery_type VARCHAR2(20) := UPPER('&2'); v_target_time VARCHAR2(20) := '&3'; BEGIN DBMS_OUTPUT.PUT_LINE('Generating recovery procedure for: ' || v_recovery_type); DBMS_OUTPUT.PUT_LINE('================================'); CASE v_recovery_type WHEN 'COMPLETE' THEN DBMS_OUTPUT.PUT_LINE('-- Complete Database Recovery Procedure'); DBMS_OUTPUT.PUT_LINE('-- 1. Shutdown database immediate'); DBMS_OUTPUT.PUT_LINE('-- 2. Startup mount'); DBMS_OUTPUT.PUT_LINE('-- 3. RMAN> RESTORE DATABASE;'); DBMS_OUTPUT.PUT_LINE('-- 4. RMAN> RECOVER DATABASE;'); DBMS_OUTPUT.PUT_LINE('-- 5. ALTER DATABASE OPEN;'); WHEN 'TABLESPACE' THEN DBMS_OUTPUT.PUT_LINE('-- Tablespace Recovery Procedure'); DBMS_OUTPUT.PUT_LINE('-- 1. Take tablespace offline:'); DBMS_OUTPUT.PUT_LINE('-- ALTER TABLESPACE tablespace_name OFFLINE IMMEDIATE;'); DBMS_OUTPUT.PUT_LINE('-- 2. RMAN> RESTORE TABLESPACE tablespace_name;'); DBMS_OUTPUT.PUT_LINE('-- 3. RMAN> RECOVER TABLESPACE tablespace_name;'); DBMS_OUTPUT.PUT_LINE('-- 4. Bring tablespace online:'); DBMS_OUTPUT.PUT_LINE('-- ALTER TABLESPACE tablespace_name ONLINE;'); WHEN 'DATAFILE' THEN DBMS_OUTPUT.PUT_LINE('-- Datafile Recovery Procedure'); DBMS_OUTPUT.PUT_LINE('-- 1. Take datafile offline:'); DBMS_OUTPUT.PUT_LINE('-- ALTER DATABASE DATAFILE ''file_name'' OFFLINE;'); DBMS_OUTPUT.PUT_LINE('-- 2. RMAN> RESTORE DATAFILE file_number;'); DBMS_OUTPUT.PUT_LINE('-- 3. RMAN> RECOVER DATAFILE file_number;'); DBMS_OUTPUT.PUT_LINE('-- 4. Bring datafile online:'); DBMS_OUTPUT.PUT_LINE('-- ALTER DATABASE DATAFILE ''file_name'' ONLINE;'); WHEN 'UNTIL_TIME' THEN DBMS_OUTPUT.PUT_LINE('-- Point-in-Time Recovery Procedure'); DBMS_OUTPUT.PUT_LINE('-- Target time: ' || v_target_time); DBMS_OUTPUT.PUT_LINE('-- 1. Shutdown database immediate'); DBMS_OUTPUT.PUT_LINE('-- 2. Startup mount'); DBMS_OUTPUT.PUT_LINE('-- 3. RMAN> RESTORE DATABASE;'); DBMS_OUTPUT.PUT_LINE('-- 4. RMAN> RECOVER DATABASE UNTIL TIME "' || v_target_time || '";'); DBMS_OUTPUT.PUT_LINE('-- 5. ALTER DATABASE OPEN RESETLOGS;'); END CASE; DBMS_OUTPUT.PUT_LINE('================================'); DBMS_OUTPUT.PUT_LINE('Note: These are sample procedures. Actual commands may vary.'); DBMS_OUTPUT.PUT_LINE('Always test recovery procedures in a non-production environment.'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error generating recovery procedure: ' || SQLERRM); END; ELSE DBMS_OUTPUT.PUT_LINE('Recovery procedure generation skipped (operation: ' || UPPER('&1') || ')'); END IF;END;/
-- ------------------------------------------------------------------------------------- RECOVERY TESTING RECOMMENDATIONS: Best practices for recovery testing-- -----------------------------------------------------------------------------------
PROMPT PROMPT 9. RECOVERY TESTING RECOMMENDATIONSPROMPT ====================================
COLUMN test_scenario FORMAT A30COLUMN frequency FORMAT A15COLUMN test_method FORMAT A60COLUMN success_criteria FORMAT A60
SELECT 'Full Database Recovery' as test_scenario, 'QUARTERLY' as frequency, 'Restore to test environment using production backups' as test_method, 'Database opens successfully, critical data accessible' as success_criteriaFROM dualUNION ALLSELECT 'Tablespace Recovery', 'MONTHLY', 'Restore individual tablespace in test environment', 'Tablespace recovers, objects accessible'FROM dualUNION ALLSELECT 'Point-in-Time Recovery', 'SEMI-ANNUAL', 'Recover to specific timestamp in test environment', 'Database recovers to specified time, data consistent'FROM dualUNION ALLSELECT 'Datafile Recovery', 'MONTHLY', 'Simulate datafile loss and recovery', 'Datafile recovers, no data loss'FROM dualORDER BY 2;
-- ------------------------------------------------------------------------------------- COMPLETION: Recovery management summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT ======================================PROMPT DATABASE RECOVERY MANAGEMENT COMPLETEDPROMPT ======================================PROMPT Operation: &1PROMPT Recovery Type: &2PROMPT Target Time: &3PROMPT Completion Time: &DATE1 &TIME1PROMPT PROMPT Key Recovery Insights:PROMPT - Validate backup integrity regularlyPROMPT - Test recovery procedures frequentlyPROMPT - Document recovery time objectivesPROMPT - Maintain recovery documentation updatedPROMPT
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment and stop spooling-- -----------------------------------------------------------------------------------
SPOOL OFF
CLEAR COLUMNSSET VERIFY ONSET TIMING OFF
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------