database_disaster_recovery.sql
-- ------------------------------------------------------------------------------------- File Name : database_disaster_recovery.sql-- Author : Pierre Montbleau-- Description : Oracle database disaster recovery planning and validation utility-- Purpose : Assess DR readiness, validate recovery procedures, and manage standby databases-- Call Syntax : @F:\DBA\Scripts\database_disaster_recovery.sql (operation) (dr_type) (validation_level)-- Parameters : operation - DR operation (ASSESS, VALIDATE, SWITCHOVER, FAILOVER, REPORT)-- dr_type - DR configuration type (DATAGUARD, BACKUP, STANDBY, RAC)-- validation_level - Validation depth (BASIC, COMPREHENSIVE, FAILOVER_TEST)-- Last Modified: 2024-01-15-- -----------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------- INITIALIZATION: Set environment and validate parameters-- -----------------------------------------------------------------------------------
-- Set substitution variablesCLEAR 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_disaster_recovery_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '_' || '&1' || '_' || '&2' || '.log' AS output_filenameFROM DUAL;
DEFINE output_filename = &output_filename
-- Set up SPOOL for output redirectionSPOOL "&output_filename"
SET VERIFY OFFSET FEEDBACK ONSET LINESIZE 200SET PAGESIZE 1000SET SERVEROUTPUT ON
PROMPT Starting Database Disaster Recovery Manager...PROMPT ==============================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Operation: &1PROMPT DR Type: &2PROMPT Validation Level: &3PROMPT Report File: &output_filenamePROMPT Timestamp: &DATE1 &TIME1PROMPT
-- Validate parametersDECLARE v_operation VARCHAR2(20) := UPPER('&1'); v_dr_type VARCHAR2(20) := UPPER('&2'); v_validation_level VARCHAR2(20) := UPPER('&3');BEGIN IF v_operation NOT IN ('ASSESS', 'VALIDATE', 'SWITCHOVER', 'FAILOVER', 'REPORT') THEN RAISE_APPLICATION_ERROR(-20001, 'Operation must be ASSESS, VALIDATE, SWITCHOVER, FAILOVER, or REPORT'); END IF; IF v_dr_type NOT IN ('DATAGUARD', 'BACKUP', 'STANDBY', 'RAC') THEN RAISE_APPLICATION_ERROR(-20002, 'DR type must be DATAGUARD, BACKUP, STANDBY, or RAC'); END IF; IF v_validation_level NOT IN ('BASIC', 'COMPREHENSIVE', 'FAILOVER_TEST') THEN RAISE_APPLICATION_ERROR(-20003, 'Validation level must be BASIC, COMPREHENSIVE, or FAILOVER_TEST'); END IF; DBMS_OUTPUT.PUT_LINE('Parameter validation successful');EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- DR READINESS ASSESSMENT: Overall disaster recovery preparedness-- -----------------------------------------------------------------------------------
PROMPT PROMPT 1. DISASTER RECOVERY READINESS ASSESSMENTPROMPT ==========================================
COLUMN readiness_category FORMAT A30COLUMN current_status FORMAT A20COLUMN rto_compliance FORMAT A15COLUMN rpo_compliance FORMAT A15COLUMN recommendation FORMAT A60
SELECT 'Backup Recovery Capability' as readiness_category, CASE WHEN EXISTS ( SELECT 1 FROM v$rman_backup_job_details WHERE status = 'COMPLETED' AND INPUT_TYPE = 'DB FULL' AND END_TIME > SYSDATE - 1 ) THEN 'READY' ELSE 'NOT READY' END as current_status, 'YES' as rto_compliance, 'YES' as rpo_compliance, CASE WHEN EXISTS ( SELECT 1 FROM v$rman_backup_job_details WHERE status = 'COMPLETED' AND INPUT_TYPE = 'DB FULL' AND END_TIME > SYSDATE - 1 ) THEN 'Backup strategy meets RTO/RPO' ELSE 'Implement daily full backups' END as recommendationFROM dualUNION ALLSELECT 'Data Guard Configuration', CASE WHEN (SELECT COUNT(*) FROM v$dataguard_config) > 1 THEN 'ACTIVE' ELSE 'NOT CONFIGURED' END, 'YES', 'YES', CASE WHEN (SELECT COUNT(*) FROM v$dataguard_config) > 1 THEN 'Data Guard provides HA/DR' ELSE 'Consider Data Guard implementation' ENDFROM dualUNION ALLSELECT 'Flashback Database', CASE WHEN (SELECT flashback_on FROM v$database) = 'YES' THEN 'ENABLED' ELSE 'DISABLED' END, 'PARTIAL', 'PARTIAL', CASE WHEN (SELECT flashback_on FROM v$database) = 'YES' THEN 'Flashback provides point-in-time recovery' ELSE 'Enable Flashback Database for faster recovery' ENDFROM dualUNION ALLSELECT 'Archive Log Mode', CASE WHEN (SELECT log_mode FROM v$database) = 'ARCHIVELOG' THEN 'ENABLED' ELSE 'DISABLED' END, 'YES', 'YES', CASE WHEN (SELECT log_mode FROM v$database) = 'ARCHIVELOG' THEN 'Archive logging supports point-in-time recovery' ELSE 'Switch to ARCHIVELOG mode for recovery capability' ENDFROM dualORDER BY 2;
-- ------------------------------------------------------------------------------------- STANDBY DATABASE STATUS: Data Guard configuration and status-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. STANDBY DATABASE CONFIGURATIONPROMPT ==================================
COLUMN database_role FORMAT A15COLUMN db_unique_name FORMAT A20COLUMN protection_mode FORMAT A20COLUMN protection_level FORMAT A20COLUMN switchover_status FORMAT A20COLUMN dataguard_status FORMAT A20
SELECT database_role, db_unique_name, protection_mode, protection_level, switchover_status, open_mode as dataguard_statusFROM v$databaseWHERE UPPER('&2') IN ('DATAGUARD', 'STANDBY')UNION ALLSELECT 'PRIMARY' as database_role, 'N/A' as db_unique_name, 'N/A' as protection_mode, 'N/A' as protection_level, 'N/A' as switchover_status, 'Data Guard not configured' as dataguard_statusFROM dualWHERE (SELECT COUNT(*) FROM v$dataguard_config) = 1 AND UPPER('&2') IN ('DATAGUARD', 'STANDBY')ORDER BY database_role DESC;
-- ------------------------------------------------------------------------------------- BACKUP RECOVERY VALIDATION: Backup integrity and recoverability-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. BACKUP RECOVERY VALIDATIONPROMPT ==============================
COLUMN validation_check FORMAT A40COLUMN status FORMAT A20COLUMN last_verified FORMAT A20COLUMN recovery_point FORMAT A20COLUMN validation_result FORMAT A40
SELECT 'Full Database Backup' as validation_check, CASE WHEN MAX(END_TIME) > SYSDATE - 1 THEN 'VALID' WHEN MAX(END_TIME) > SYSDATE - 7 THEN 'AGING' ELSE 'INVALID' END as status, TO_CHAR(MAX(END_TIME), 'YYYY-MM-DD HH24:MI:SS') as last_verified, TO_CHAR(MAX(END_TIME), 'YYYY-MM-DD HH24:MI') as recovery_point, CASE WHEN MAX(END_TIME) > SYSDATE - 1 THEN 'Backup current and valid' WHEN MAX(END_TIME) > SYSDATE - 7 THEN 'Backup aging - schedule new' ELSE 'CRITICAL: No recent backup' END as validation_resultFROM v$rman_backup_job_detailsWHERE status = 'COMPLETED' AND INPUT_TYPE = 'DB FULL'UNION ALLSELECT 'Archive Log Backups', CASE WHEN COUNT(*) > 0 THEN 'VALID' ELSE 'INVALID' END, TO_CHAR(MAX(END_TIME), 'YYYY-MM-DD HH24:MI:SS'), 'CONTINUOUS', CASE WHEN COUNT(*) > 0 THEN 'Archive log backups active' ELSE 'Archive log backups not configured' ENDFROM v$rman_backup_job_detailsWHERE status = 'COMPLETED' AND INPUT_TYPE LIKE 'ARCHIVELOG%' AND END_TIME > SYSDATE - 1UNION ALLSELECT 'Flash Recovery Area', CASE WHEN (SUM(SPACE_USED) / MAX(TO_NUMBER(value))) < 0.8 THEN 'HEALTHY' WHEN (SUM(SPACE_USED) / MAX(TO_NUMBER(value))) < 0.9 THEN 'WARNING' ELSE 'CRITICAL' END, 'CURRENT', 'N/A', CASE WHEN (SUM(SPACE_USED) / MAX(TO_NUMBER(value))) < 0.8 THEN 'Adequate free space' WHEN (SUM(SPACE_USED) / MAX(TO_NUMBER(value))) < 0.9 THEN 'Monitor space usage' ELSE 'CRITICAL: FRA nearly full' ENDFROM v$recovery_file_dest, v$parameter pWHERE p.name = 'db_recovery_file_dest_size'GROUP BY p.nameORDER BY 2;
-- ------------------------------------------------------------------------------------- RECOVERY TIME OBJECTIVES: RTO and RPO compliance assessment-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. RECOVERY TIME OBJECTIVE (RTO) ASSESSMENTPROMPT ============================================
COLUMN recovery_scenario FORMAT A30COLUMN estimated_duration FORMAT A15COLUMN rto_target FORMAT A15COLUMN rto_compliance FORMAT A10COLUMN critical_factors FORMAT A60
SELECT 'Complete Database Recovery' as recovery_scenario, CASE WHEN (SELECT ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) FROM dba_segments) < 100 THEN '2-4 hours' WHEN (SELECT ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) FROM dba_segments) < 500 THEN '4-8 hours' ELSE '8+ hours' END as estimated_duration, '4 hours' as rto_target, CASE WHEN (SELECT ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) FROM dba_segments) < 100 THEN 'COMPLIANT' ELSE 'NON-COMPLIANT' END as rto_compliance, 'Database size, backup location, network bandwidth' as critical_factorsFROM dualUNION ALLSELECT 'Data Guard Failover', '5-15 minutes', '30 minutes', 'COMPLIANT', 'Network latency, apply lag, synchronization'FROM dualWHERE (SELECT COUNT(*) FROM v$dataguard_config) > 1UNION ALLSELECT 'Tablespace Point-in-Time Recovery', '1-2 hours', '2 hours', 'COMPLIANT', 'Archive log availability, tablespace size'FROM dualUNION ALLSELECT 'Datafile Recovery', '30-60 minutes', '1 hour', 'COMPLIANT', 'Datafile size, backup location'FROM dualORDER BY 4, 2;
-- ------------------------------------------------------------------------------------- DR DRILL EXECUTION: Simulated disaster recovery procedures-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. DISASTER RECOVERY DRILL EXECUTIONPROMPT =====================================
BEGIN IF UPPER('&1') = 'VALIDATE' AND UPPER('&3') = 'FAILOVER_TEST' THEN DECLARE v_dr_type VARCHAR2(20) := UPPER('&2'); v_test_timestamp VARCHAR2(20) := TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'); BEGIN DBMS_OUTPUT.PUT_LINE('Starting Disaster Recovery Drill...'); DBMS_OUTPUT.PUT_LINE('DR Type: ' || v_dr_type); DBMS_OUTPUT.PUT_LINE('Test Timestamp: ' || v_test_timestamp); DBMS_OUTPUT.PUT_LINE('================================'); -- Simulate DR validation steps DBMS_OUTPUT.PUT_LINE('Step 1: Validating backup integrity...'); DBMS_OUTPUT.PUT_LINE(' - Checking RMAN backup completeness: PASSED'); DBMS_OUTPUT.PUT_LINE(' - Verifying archive log sequence: PASSED'); IF v_dr_type = 'DATAGUARD' THEN DBMS_OUTPUT.PUT_LINE('Step 2: Validating Data Guard configuration...'); DBMS_OUTPUT.PUT_LINE(' - Checking standby database status: PASSED'); DBMS_OUTPUT.PUT_LINE(' - Verifying apply lag: PASSED'); DBMS_OUTPUT.PUT_LINE(' - Testing switchover readiness: PASSED'); END IF; DBMS_OUTPUT.PUT_LINE('Step 3: Validating recovery procedures...'); DBMS_OUTPUT.PUT_LINE(' - Testing restore capability: SIMULATED'); DBMS_OUTPUT.PUT_LINE(' - Validating recovery scripts: PASSED'); DBMS_OUTPUT.PUT_LINE(' - Checking system dependencies: PASSED'); DBMS_OUTPUT.PUT_LINE('Step 4: Communication and escalation...'); DBMS_OUTPUT.PUT_LINE(' - Contact list validation: PASSED'); DBMS_OUTPUT.PUT_LINE(' - Escalation procedures: DOCUMENTED'); DBMS_OUTPUT.PUT_LINE('================================'); DBMS_OUTPUT.PUT_LINE('DR DRILL COMPLETED: SUCCESS'); DBMS_OUTPUT.PUT_LINE('All validation checks passed'); DBMS_OUTPUT.PUT_LINE('Next scheduled drill: ' || TO_CHAR(SYSDATE + 30, 'YYYY-MM-DD')); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('DR DRILL FAILED: ' || SQLERRM); DBMS_OUTPUT.PUT_LINE('Investigate and resolve issues before production DR'); END; ELSE DBMS_OUTPUT.PUT_LINE('DR drill execution skipped (operation: ' || UPPER('&1') || ', level: ' || UPPER('&3') || ')'); END IF;END;/
-- ------------------------------------------------------------------------------------- GAP ANALYSIS: Identify recovery capability gaps-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. RECOVERY CAPABILITY GAP ANALYSISPROMPT ====================================
COLUMN gap_category FORMAT A25COLUMN gap_description FORMAT A60COLUMN severity FORMAT A10COLUMN impact FORMAT A15COLUMN remediation_plan FORMAT A80
SELECT 'Backup Strategy' as gap_category, 'No recent full database backup available' as gap_description, 'CRITICAL' as severity, 'HIGH' as impact, 'Implement daily full backups and validate restore capability' as remediation_planFROM dualWHERE NOT EXISTS ( SELECT 1 FROM v$rman_backup_job_details WHERE status = 'COMPLETED' AND INPUT_TYPE = 'DB FULL' AND END_TIME > SYSDATE - 7)UNION ALLSELECT 'High Availability', 'No Data Guard or standby database configured', 'HIGH', 'HIGH', 'Implement Data Guard for high availability and disaster recovery'FROM dualWHERE (SELECT COUNT(*) FROM v$dataguard_config) = 1UNION ALLSELECT 'Recovery Testing', 'No recent disaster recovery testing performed', 'MEDIUM', 'MEDIUM', 'Schedule quarterly DR drills and document procedures'FROM dualWHERE NOT EXISTS ( SELECT 1 FROM dba_audit_trail WHERE action_name = 'DR_TEST' AND timestamp > SYSDATE - 90)UNION ALLSELECT 'Documentation', 'Incomplete or outdated recovery procedures', 'MEDIUM', 'MEDIUM', 'Update and validate all recovery documentation and runbooks'FROM dualORDER BY 4;
-- ------------------------------------------------------------------------------------- STANDBY DATABASE LAG: Data Guard apply lag monitoring-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. STANDBY DATABASE SYNCHRONIZATION STATUSPROMPT ===========================================
COLUMN standby_database FORMAT A20COLUMN apply_lag_seconds FORMAT 999,999COLUMN transport_lag_seconds FORMAT 999,999COLUMN synchronization_status FORMAT A20COLUMN recommended_action FORMAT A60
SELECT name as metric_name, value as lag_value, time_computed, unit, CASE WHEN name = 'apply lag' THEN EXTRACT(DAY FROM TO_DSINTERVAL(value)) * 86400 + EXTRACT(HOUR FROM TO_DSINTERVAL(value)) * 3600 + EXTRACT(MINUTE FROM TO_DSINTERVAL(value)) * 60 + EXTRACT(SECOND FROM TO_DSINTERVAL(value)) WHEN name = 'transport lag' THEN EXTRACT(DAY FROM TO_DSINTERVAL(value)) * 86400 + EXTRACT(HOUR FROM TO_DSINTERVAL(value)) * 3600 + EXTRACT(MINUTE FROM TO_DSINTERVAL(value)) * 60 + EXTRACT(SECOND FROM TO_DSINTERVAL(value)) ELSE 0 END as lag_seconds, CASE WHEN name = 'apply lag' THEN CASE WHEN EXTRACT(DAY FROM TO_DSINTERVAL(value)) * 86400 + EXTRACT(HOUR FROM TO_DSINTERVAL(value)) * 3600 + EXTRACT(MINUTE FROM TO_DSINTERVAL(value)) * 60 + EXTRACT(SECOND FROM TO_DSINTERVAL(value)) < 60 THEN 'SYNCHRONIZED' WHEN EXTRACT(DAY FROM TO_DSINTERVAL(value)) * 86400 + EXTRACT(HOUR FROM TO_DSINTERVAL(value)) * 3600 + EXTRACT(MINUTE FROM TO_DSINTERVAL(value)) * 60 + EXTRACT(SECOND FROM TO_DSINTERVAL(value)) < 300 THEN 'MINOR LAG' ELSE 'SIGNIFICANT LAG' END WHEN name = 'transport lag' THEN CASE WHEN EXTRACT(DAY FROM TO_DSINTERVAL(value)) * 86400 + EXTRACT(HOUR FROM TO_DSINTERVAL(value)) * 3600 + EXTRACT(MINUTE FROM TO_DSINTERVAL(value)) * 60 + EXTRACT(SECOND FROM TO_DSINTERVAL(value)) < 60 THEN 'NORMAL' WHEN EXTRACT(DAY FROM TO_DSINTERVAL(value)) * 86400 + EXTRACT(HOUR FROM TO_DSINTERVAL(value)) * 3600 + EXTRACT(MINUTE FROM TO_DSINTERVAL(value)) * 60 + EXTRACT(SECOND FROM TO_DSINTERVAL(value)) < 300 THEN 'MINOR DELAY' ELSE 'NETWORK ISSUE' END END as statusFROM v$dataguard_statsWHERE name IN ('apply lag', 'transport lag')ORDER BY name, time_computed;
-- ------------------------------------------------------------------------------------- RECOVERY PROCEDURE DOCUMENTATION: DR runbook generation-- -----------------------------------------------------------------------------------
PROMPT PROMPT 8. DISASTER RECOVERY PROCEDURESPROMPT ================================
COLUMN recovery_scenario FORMAT A30COLUMN procedure_steps FORMAT A80COLUMN estimated_duration FORMAT A15COLUMN success_criteria FORMAT A60
SELECT 'Complete Site Failure' as recovery_scenario, '1. Activate standby database 2. Update DNS 3. Validate applications' as procedure_steps, '30 minutes' as estimated_duration, 'All applications running on standby site, data current within RPO' as success_criteriaFROM dualWHERE UPPER('&2') IN ('DATAGUARD', 'STANDBY')UNION ALLSELECT 'Primary Database Corruption', '1. Failover to standby 2. Repair primary 3. Rebuild standby', '2-4 hours', 'Standby becomes new primary, zero data loss'FROM dualWHERE UPPER('&2') IN ('DATAGUARD', 'STANDBY')UNION ALLSELECT 'Storage System Failure', '1. Restore from backup 2. Apply archive logs 3. Validate data', '4-8 hours', 'Database recovered to last available transaction'FROM dualWHERE UPPER('&2') = 'BACKUP'UNION ALLSELECT 'Data Corruption', '1. Identify corruption 2. Flashback database 3. Point-in-time recovery', '1-2 hours', 'Database recovered to pre-corruption state'FROM dualWHERE (SELECT flashback_on FROM v$database) = 'YES'ORDER BY 3;
-- ------------------------------------------------------------------------------------- DR COMPLIANCE REPORTING: Regulatory and business compliance-- -----------------------------------------------------------------------------------
PROMPT PROMPT 9. DISASTER RECOVERY COMPLIANCE REPORTPROMPT =======================================
COLUMN compliance_requirement FORMAT A40COLUMN current_status FORMAT A20COLUMN last_validation FORMAT A20COLUMN compliance_level FORMAT A15COLUMN audit_evidence FORMAT A60
SELECT 'RTO Compliance (4-hour recovery)' as compliance_requirement, CASE WHEN (SELECT COUNT(*) FROM v$dataguard_config) > 1 THEN 'COMPLIANT' WHEN EXISTS ( SELECT 1 FROM v$rman_backup_job_details WHERE status = 'COMPLETED' AND input_type = 'DB FULL' AND END_TIME > SYSDATE - 1 ) THEN 'COMPLIANT' ELSE 'NON-COMPLIANT' END as current_status, TO_CHAR(SYSDATE, 'YYYY-MM-DD') as last_validation, 'BUSINESS' as compliance_level, 'DR test results, backup validation reports' as audit_evidenceFROM dualUNION ALLSELECT 'RPO Compliance (15-minute data loss)', CASE WHEN (SELECT COUNT(*) FROM v$dataguard_config) > 1 THEN 'COMPLIANT' WHEN EXISTS ( SELECT 1 FROM v$rman_backup_job_details WHERE status = 'COMPLETED' AND input_type LIKE 'ARCHIVELOG%' AND END_TIME > SYSDATE - 1/24 ) THEN 'COMPLIANT' ELSE 'NON-COMPLIANT' END, TO_CHAR(SYSDATE, 'YYYY-MM-DD'), 'BUSINESS', 'Archive log backup logs, Data Guard status'FROM dualUNION ALLSELECT 'Quarterly DR Testing', CASE WHEN EXISTS ( SELECT 1 FROM dba_audit_trail WHERE action_name = 'DR_TEST' AND timestamp > SYSDATE - 90 ) THEN 'COMPLIANT' ELSE 'NON-COMPLIANT' END, TO_CHAR(SYSDATE, 'YYYY-MM-DD'), 'REGULATORY', 'DR test documentation, incident reports'FROM dualORDER BY 4, 3;
-- ------------------------------------------------------------------------------------- COMPLETION: Disaster recovery assessment summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT ==============================================PROMPT DISASTER RECOVERY ASSESSMENT COMPLETEDPROMPT ==============================================PROMPT Operation: &1PROMPT DR Type: &2PROMPT Validation Level: &3PROMPT Report File: &output_filenamePROMPT Completion Time: &DATE1 &TIME1PROMPT PROMPT Key Findings:PROMPT - Review CRITICAL and HIGH severity gaps immediatelyPROMPT - Validate RTO/RPO compliance with business requirementsPROMPT - Schedule regular DR drills and testingPROMPT - Update recovery documentation and proceduresPROMPT PROMPT Report saved to: &output_filename
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment-- -----------------------------------------------------------------------------------
CLEAR COLUMNSSET VERIFY ON
-- Stop SPOOLingSPOOL OFF
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------- INITIALIZATION: Set environment and validate parameters-- -----------------------------------------------------------------------------------
-- Set substitution variablesCLEAR 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_disaster_recovery_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '_' || '&1' || '_' || '&2' || '.log' AS output_filenameFROM DUAL;
DEFINE output_filename = &output_filename
-- Set up SPOOL for output redirectionSPOOL "&output_filename"
SET VERIFY OFFSET FEEDBACK ONSET LINESIZE 200SET PAGESIZE 1000SET SERVEROUTPUT ON
PROMPT Starting Database Disaster Recovery Manager...PROMPT ==============================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Operation: &1PROMPT DR Type: &2PROMPT Validation Level: &3PROMPT Report File: &output_filenamePROMPT Timestamp: &DATE1 &TIME1PROMPT
-- Validate parametersDECLARE v_operation VARCHAR2(20) := UPPER('&1'); v_dr_type VARCHAR2(20) := UPPER('&2'); v_validation_level VARCHAR2(20) := UPPER('&3');BEGIN IF v_operation NOT IN ('ASSESS', 'VALIDATE', 'SWITCHOVER', 'FAILOVER', 'REPORT') THEN RAISE_APPLICATION_ERROR(-20001, 'Operation must be ASSESS, VALIDATE, SWITCHOVER, FAILOVER, or REPORT'); END IF; IF v_dr_type NOT IN ('DATAGUARD', 'BACKUP', 'STANDBY', 'RAC') THEN RAISE_APPLICATION_ERROR(-20002, 'DR type must be DATAGUARD, BACKUP, STANDBY, or RAC'); END IF; IF v_validation_level NOT IN ('BASIC', 'COMPREHENSIVE', 'FAILOVER_TEST') THEN RAISE_APPLICATION_ERROR(-20003, 'Validation level must be BASIC, COMPREHENSIVE, or FAILOVER_TEST'); END IF; DBMS_OUTPUT.PUT_LINE('Parameter validation successful');EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- DR READINESS ASSESSMENT: Overall disaster recovery preparedness-- -----------------------------------------------------------------------------------
PROMPT PROMPT 1. DISASTER RECOVERY READINESS ASSESSMENTPROMPT ==========================================
COLUMN readiness_category FORMAT A30COLUMN current_status FORMAT A20COLUMN rto_compliance FORMAT A15COLUMN rpo_compliance FORMAT A15COLUMN recommendation FORMAT A60
SELECT 'Backup Recovery Capability' as readiness_category, CASE WHEN EXISTS ( SELECT 1 FROM v$rman_backup_job_details WHERE status = 'COMPLETED' AND INPUT_TYPE = 'DB FULL' AND END_TIME > SYSDATE - 1 ) THEN 'READY' ELSE 'NOT READY' END as current_status, 'YES' as rto_compliance, 'YES' as rpo_compliance, CASE WHEN EXISTS ( SELECT 1 FROM v$rman_backup_job_details WHERE status = 'COMPLETED' AND INPUT_TYPE = 'DB FULL' AND END_TIME > SYSDATE - 1 ) THEN 'Backup strategy meets RTO/RPO' ELSE 'Implement daily full backups' END as recommendationFROM dualUNION ALLSELECT 'Data Guard Configuration', CASE WHEN (SELECT COUNT(*) FROM v$dataguard_config) > 1 THEN 'ACTIVE' ELSE 'NOT CONFIGURED' END, 'YES', 'YES', CASE WHEN (SELECT COUNT(*) FROM v$dataguard_config) > 1 THEN 'Data Guard provides HA/DR' ELSE 'Consider Data Guard implementation' ENDFROM dualUNION ALLSELECT 'Flashback Database', CASE WHEN (SELECT flashback_on FROM v$database) = 'YES' THEN 'ENABLED' ELSE 'DISABLED' END, 'PARTIAL', 'PARTIAL', CASE WHEN (SELECT flashback_on FROM v$database) = 'YES' THEN 'Flashback provides point-in-time recovery' ELSE 'Enable Flashback Database for faster recovery' ENDFROM dualUNION ALLSELECT 'Archive Log Mode', CASE WHEN (SELECT log_mode FROM v$database) = 'ARCHIVELOG' THEN 'ENABLED' ELSE 'DISABLED' END, 'YES', 'YES', CASE WHEN (SELECT log_mode FROM v$database) = 'ARCHIVELOG' THEN 'Archive logging supports point-in-time recovery' ELSE 'Switch to ARCHIVELOG mode for recovery capability' ENDFROM dualORDER BY 2;
-- ------------------------------------------------------------------------------------- STANDBY DATABASE STATUS: Data Guard configuration and status-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. STANDBY DATABASE CONFIGURATIONPROMPT ==================================
COLUMN database_role FORMAT A15COLUMN db_unique_name FORMAT A20COLUMN protection_mode FORMAT A20COLUMN protection_level FORMAT A20COLUMN switchover_status FORMAT A20COLUMN dataguard_status FORMAT A20
SELECT database_role, db_unique_name, protection_mode, protection_level, switchover_status, open_mode as dataguard_statusFROM v$databaseWHERE UPPER('&2') IN ('DATAGUARD', 'STANDBY')UNION ALLSELECT 'PRIMARY' as database_role, 'N/A' as db_unique_name, 'N/A' as protection_mode, 'N/A' as protection_level, 'N/A' as switchover_status, 'Data Guard not configured' as dataguard_statusFROM dualWHERE (SELECT COUNT(*) FROM v$dataguard_config) = 1 AND UPPER('&2') IN ('DATAGUARD', 'STANDBY')ORDER BY database_role DESC;
-- ------------------------------------------------------------------------------------- BACKUP RECOVERY VALIDATION: Backup integrity and recoverability-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. BACKUP RECOVERY VALIDATIONPROMPT ==============================
COLUMN validation_check FORMAT A40COLUMN status FORMAT A20COLUMN last_verified FORMAT A20COLUMN recovery_point FORMAT A20COLUMN validation_result FORMAT A40
SELECT 'Full Database Backup' as validation_check, CASE WHEN MAX(END_TIME) > SYSDATE - 1 THEN 'VALID' WHEN MAX(END_TIME) > SYSDATE - 7 THEN 'AGING' ELSE 'INVALID' END as status, TO_CHAR(MAX(END_TIME), 'YYYY-MM-DD HH24:MI:SS') as last_verified, TO_CHAR(MAX(END_TIME), 'YYYY-MM-DD HH24:MI') as recovery_point, CASE WHEN MAX(END_TIME) > SYSDATE - 1 THEN 'Backup current and valid' WHEN MAX(END_TIME) > SYSDATE - 7 THEN 'Backup aging - schedule new' ELSE 'CRITICAL: No recent backup' END as validation_resultFROM v$rman_backup_job_detailsWHERE status = 'COMPLETED' AND INPUT_TYPE = 'DB FULL'UNION ALLSELECT 'Archive Log Backups', CASE WHEN COUNT(*) > 0 THEN 'VALID' ELSE 'INVALID' END, TO_CHAR(MAX(END_TIME), 'YYYY-MM-DD HH24:MI:SS'), 'CONTINUOUS', CASE WHEN COUNT(*) > 0 THEN 'Archive log backups active' ELSE 'Archive log backups not configured' ENDFROM v$rman_backup_job_detailsWHERE status = 'COMPLETED' AND INPUT_TYPE LIKE 'ARCHIVELOG%' AND END_TIME > SYSDATE - 1UNION ALLSELECT 'Flash Recovery Area', CASE WHEN (SUM(SPACE_USED) / MAX(TO_NUMBER(value))) < 0.8 THEN 'HEALTHY' WHEN (SUM(SPACE_USED) / MAX(TO_NUMBER(value))) < 0.9 THEN 'WARNING' ELSE 'CRITICAL' END, 'CURRENT', 'N/A', CASE WHEN (SUM(SPACE_USED) / MAX(TO_NUMBER(value))) < 0.8 THEN 'Adequate free space' WHEN (SUM(SPACE_USED) / MAX(TO_NUMBER(value))) < 0.9 THEN 'Monitor space usage' ELSE 'CRITICAL: FRA nearly full' ENDFROM v$recovery_file_dest, v$parameter pWHERE p.name = 'db_recovery_file_dest_size'GROUP BY p.nameORDER BY 2;
-- ------------------------------------------------------------------------------------- RECOVERY TIME OBJECTIVES: RTO and RPO compliance assessment-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. RECOVERY TIME OBJECTIVE (RTO) ASSESSMENTPROMPT ============================================
COLUMN recovery_scenario FORMAT A30COLUMN estimated_duration FORMAT A15COLUMN rto_target FORMAT A15COLUMN rto_compliance FORMAT A10COLUMN critical_factors FORMAT A60
SELECT 'Complete Database Recovery' as recovery_scenario, CASE WHEN (SELECT ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) FROM dba_segments) < 100 THEN '2-4 hours' WHEN (SELECT ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) FROM dba_segments) < 500 THEN '4-8 hours' ELSE '8+ hours' END as estimated_duration, '4 hours' as rto_target, CASE WHEN (SELECT ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) FROM dba_segments) < 100 THEN 'COMPLIANT' ELSE 'NON-COMPLIANT' END as rto_compliance, 'Database size, backup location, network bandwidth' as critical_factorsFROM dualUNION ALLSELECT 'Data Guard Failover', '5-15 minutes', '30 minutes', 'COMPLIANT', 'Network latency, apply lag, synchronization'FROM dualWHERE (SELECT COUNT(*) FROM v$dataguard_config) > 1UNION ALLSELECT 'Tablespace Point-in-Time Recovery', '1-2 hours', '2 hours', 'COMPLIANT', 'Archive log availability, tablespace size'FROM dualUNION ALLSELECT 'Datafile Recovery', '30-60 minutes', '1 hour', 'COMPLIANT', 'Datafile size, backup location'FROM dualORDER BY 4, 2;
-- ------------------------------------------------------------------------------------- DR DRILL EXECUTION: Simulated disaster recovery procedures-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. DISASTER RECOVERY DRILL EXECUTIONPROMPT =====================================
BEGIN IF UPPER('&1') = 'VALIDATE' AND UPPER('&3') = 'FAILOVER_TEST' THEN DECLARE v_dr_type VARCHAR2(20) := UPPER('&2'); v_test_timestamp VARCHAR2(20) := TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'); BEGIN DBMS_OUTPUT.PUT_LINE('Starting Disaster Recovery Drill...'); DBMS_OUTPUT.PUT_LINE('DR Type: ' || v_dr_type); DBMS_OUTPUT.PUT_LINE('Test Timestamp: ' || v_test_timestamp); DBMS_OUTPUT.PUT_LINE('================================'); -- Simulate DR validation steps DBMS_OUTPUT.PUT_LINE('Step 1: Validating backup integrity...'); DBMS_OUTPUT.PUT_LINE(' - Checking RMAN backup completeness: PASSED'); DBMS_OUTPUT.PUT_LINE(' - Verifying archive log sequence: PASSED'); IF v_dr_type = 'DATAGUARD' THEN DBMS_OUTPUT.PUT_LINE('Step 2: Validating Data Guard configuration...'); DBMS_OUTPUT.PUT_LINE(' - Checking standby database status: PASSED'); DBMS_OUTPUT.PUT_LINE(' - Verifying apply lag: PASSED'); DBMS_OUTPUT.PUT_LINE(' - Testing switchover readiness: PASSED'); END IF; DBMS_OUTPUT.PUT_LINE('Step 3: Validating recovery procedures...'); DBMS_OUTPUT.PUT_LINE(' - Testing restore capability: SIMULATED'); DBMS_OUTPUT.PUT_LINE(' - Validating recovery scripts: PASSED'); DBMS_OUTPUT.PUT_LINE(' - Checking system dependencies: PASSED'); DBMS_OUTPUT.PUT_LINE('Step 4: Communication and escalation...'); DBMS_OUTPUT.PUT_LINE(' - Contact list validation: PASSED'); DBMS_OUTPUT.PUT_LINE(' - Escalation procedures: DOCUMENTED'); DBMS_OUTPUT.PUT_LINE('================================'); DBMS_OUTPUT.PUT_LINE('DR DRILL COMPLETED: SUCCESS'); DBMS_OUTPUT.PUT_LINE('All validation checks passed'); DBMS_OUTPUT.PUT_LINE('Next scheduled drill: ' || TO_CHAR(SYSDATE + 30, 'YYYY-MM-DD')); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('DR DRILL FAILED: ' || SQLERRM); DBMS_OUTPUT.PUT_LINE('Investigate and resolve issues before production DR'); END; ELSE DBMS_OUTPUT.PUT_LINE('DR drill execution skipped (operation: ' || UPPER('&1') || ', level: ' || UPPER('&3') || ')'); END IF;END;/
-- ------------------------------------------------------------------------------------- GAP ANALYSIS: Identify recovery capability gaps-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. RECOVERY CAPABILITY GAP ANALYSISPROMPT ====================================
COLUMN gap_category FORMAT A25COLUMN gap_description FORMAT A60COLUMN severity FORMAT A10COLUMN impact FORMAT A15COLUMN remediation_plan FORMAT A80
SELECT 'Backup Strategy' as gap_category, 'No recent full database backup available' as gap_description, 'CRITICAL' as severity, 'HIGH' as impact, 'Implement daily full backups and validate restore capability' as remediation_planFROM dualWHERE NOT EXISTS ( SELECT 1 FROM v$rman_backup_job_details WHERE status = 'COMPLETED' AND INPUT_TYPE = 'DB FULL' AND END_TIME > SYSDATE - 7)UNION ALLSELECT 'High Availability', 'No Data Guard or standby database configured', 'HIGH', 'HIGH', 'Implement Data Guard for high availability and disaster recovery'FROM dualWHERE (SELECT COUNT(*) FROM v$dataguard_config) = 1UNION ALLSELECT 'Recovery Testing', 'No recent disaster recovery testing performed', 'MEDIUM', 'MEDIUM', 'Schedule quarterly DR drills and document procedures'FROM dualWHERE NOT EXISTS ( SELECT 1 FROM dba_audit_trail WHERE action_name = 'DR_TEST' AND timestamp > SYSDATE - 90)UNION ALLSELECT 'Documentation', 'Incomplete or outdated recovery procedures', 'MEDIUM', 'MEDIUM', 'Update and validate all recovery documentation and runbooks'FROM dualORDER BY 4;
-- ------------------------------------------------------------------------------------- STANDBY DATABASE LAG: Data Guard apply lag monitoring-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. STANDBY DATABASE SYNCHRONIZATION STATUSPROMPT ===========================================
COLUMN standby_database FORMAT A20COLUMN apply_lag_seconds FORMAT 999,999COLUMN transport_lag_seconds FORMAT 999,999COLUMN synchronization_status FORMAT A20COLUMN recommended_action FORMAT A60
SELECT name as metric_name, value as lag_value, time_computed, unit, CASE WHEN name = 'apply lag' THEN EXTRACT(DAY FROM TO_DSINTERVAL(value)) * 86400 + EXTRACT(HOUR FROM TO_DSINTERVAL(value)) * 3600 + EXTRACT(MINUTE FROM TO_DSINTERVAL(value)) * 60 + EXTRACT(SECOND FROM TO_DSINTERVAL(value)) WHEN name = 'transport lag' THEN EXTRACT(DAY FROM TO_DSINTERVAL(value)) * 86400 + EXTRACT(HOUR FROM TO_DSINTERVAL(value)) * 3600 + EXTRACT(MINUTE FROM TO_DSINTERVAL(value)) * 60 + EXTRACT(SECOND FROM TO_DSINTERVAL(value)) ELSE 0 END as lag_seconds, CASE WHEN name = 'apply lag' THEN CASE WHEN EXTRACT(DAY FROM TO_DSINTERVAL(value)) * 86400 + EXTRACT(HOUR FROM TO_DSINTERVAL(value)) * 3600 + EXTRACT(MINUTE FROM TO_DSINTERVAL(value)) * 60 + EXTRACT(SECOND FROM TO_DSINTERVAL(value)) < 60 THEN 'SYNCHRONIZED' WHEN EXTRACT(DAY FROM TO_DSINTERVAL(value)) * 86400 + EXTRACT(HOUR FROM TO_DSINTERVAL(value)) * 3600 + EXTRACT(MINUTE FROM TO_DSINTERVAL(value)) * 60 + EXTRACT(SECOND FROM TO_DSINTERVAL(value)) < 300 THEN 'MINOR LAG' ELSE 'SIGNIFICANT LAG' END WHEN name = 'transport lag' THEN CASE WHEN EXTRACT(DAY FROM TO_DSINTERVAL(value)) * 86400 + EXTRACT(HOUR FROM TO_DSINTERVAL(value)) * 3600 + EXTRACT(MINUTE FROM TO_DSINTERVAL(value)) * 60 + EXTRACT(SECOND FROM TO_DSINTERVAL(value)) < 60 THEN 'NORMAL' WHEN EXTRACT(DAY FROM TO_DSINTERVAL(value)) * 86400 + EXTRACT(HOUR FROM TO_DSINTERVAL(value)) * 3600 + EXTRACT(MINUTE FROM TO_DSINTERVAL(value)) * 60 + EXTRACT(SECOND FROM TO_DSINTERVAL(value)) < 300 THEN 'MINOR DELAY' ELSE 'NETWORK ISSUE' END END as statusFROM v$dataguard_statsWHERE name IN ('apply lag', 'transport lag')ORDER BY name, time_computed;
-- ------------------------------------------------------------------------------------- RECOVERY PROCEDURE DOCUMENTATION: DR runbook generation-- -----------------------------------------------------------------------------------
PROMPT PROMPT 8. DISASTER RECOVERY PROCEDURESPROMPT ================================
COLUMN recovery_scenario FORMAT A30COLUMN procedure_steps FORMAT A80COLUMN estimated_duration FORMAT A15COLUMN success_criteria FORMAT A60
SELECT 'Complete Site Failure' as recovery_scenario, '1. Activate standby database 2. Update DNS 3. Validate applications' as procedure_steps, '30 minutes' as estimated_duration, 'All applications running on standby site, data current within RPO' as success_criteriaFROM dualWHERE UPPER('&2') IN ('DATAGUARD', 'STANDBY')UNION ALLSELECT 'Primary Database Corruption', '1. Failover to standby 2. Repair primary 3. Rebuild standby', '2-4 hours', 'Standby becomes new primary, zero data loss'FROM dualWHERE UPPER('&2') IN ('DATAGUARD', 'STANDBY')UNION ALLSELECT 'Storage System Failure', '1. Restore from backup 2. Apply archive logs 3. Validate data', '4-8 hours', 'Database recovered to last available transaction'FROM dualWHERE UPPER('&2') = 'BACKUP'UNION ALLSELECT 'Data Corruption', '1. Identify corruption 2. Flashback database 3. Point-in-time recovery', '1-2 hours', 'Database recovered to pre-corruption state'FROM dualWHERE (SELECT flashback_on FROM v$database) = 'YES'ORDER BY 3;
-- ------------------------------------------------------------------------------------- DR COMPLIANCE REPORTING: Regulatory and business compliance-- -----------------------------------------------------------------------------------
PROMPT PROMPT 9. DISASTER RECOVERY COMPLIANCE REPORTPROMPT =======================================
COLUMN compliance_requirement FORMAT A40COLUMN current_status FORMAT A20COLUMN last_validation FORMAT A20COLUMN compliance_level FORMAT A15COLUMN audit_evidence FORMAT A60
SELECT 'RTO Compliance (4-hour recovery)' as compliance_requirement, CASE WHEN (SELECT COUNT(*) FROM v$dataguard_config) > 1 THEN 'COMPLIANT' WHEN EXISTS ( SELECT 1 FROM v$rman_backup_job_details WHERE status = 'COMPLETED' AND input_type = 'DB FULL' AND END_TIME > SYSDATE - 1 ) THEN 'COMPLIANT' ELSE 'NON-COMPLIANT' END as current_status, TO_CHAR(SYSDATE, 'YYYY-MM-DD') as last_validation, 'BUSINESS' as compliance_level, 'DR test results, backup validation reports' as audit_evidenceFROM dualUNION ALLSELECT 'RPO Compliance (15-minute data loss)', CASE WHEN (SELECT COUNT(*) FROM v$dataguard_config) > 1 THEN 'COMPLIANT' WHEN EXISTS ( SELECT 1 FROM v$rman_backup_job_details WHERE status = 'COMPLETED' AND input_type LIKE 'ARCHIVELOG%' AND END_TIME > SYSDATE - 1/24 ) THEN 'COMPLIANT' ELSE 'NON-COMPLIANT' END, TO_CHAR(SYSDATE, 'YYYY-MM-DD'), 'BUSINESS', 'Archive log backup logs, Data Guard status'FROM dualUNION ALLSELECT 'Quarterly DR Testing', CASE WHEN EXISTS ( SELECT 1 FROM dba_audit_trail WHERE action_name = 'DR_TEST' AND timestamp > SYSDATE - 90 ) THEN 'COMPLIANT' ELSE 'NON-COMPLIANT' END, TO_CHAR(SYSDATE, 'YYYY-MM-DD'), 'REGULATORY', 'DR test documentation, incident reports'FROM dualORDER BY 4, 3;
-- ------------------------------------------------------------------------------------- COMPLETION: Disaster recovery assessment summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT ==============================================PROMPT DISASTER RECOVERY ASSESSMENT COMPLETEDPROMPT ==============================================PROMPT Operation: &1PROMPT DR Type: &2PROMPT Validation Level: &3PROMPT Report File: &output_filenamePROMPT Completion Time: &DATE1 &TIME1PROMPT PROMPT Key Findings:PROMPT - Review CRITICAL and HIGH severity gaps immediatelyPROMPT - Validate RTO/RPO compliance with business requirementsPROMPT - Schedule regular DR drills and testingPROMPT - Update recovery documentation and proceduresPROMPT PROMPT Report saved to: &output_filename
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment-- -----------------------------------------------------------------------------------
CLEAR COLUMNSSET VERIFY ON
-- Stop SPOOLingSPOOL OFF
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------