backup_monitoring.sql
-- ------------------------------------------------------------------------------------- File Name : backup_monitoring.sql-- Author : Pierre Montbleau-- Description : Comprehensive Oracle database backup monitoring and validation script-- Purpose : Monitor backup status, validate recoverability, and report backup issues-- Call Syntax : @F:\DBA\Scripts\backup_monitoring.sql (days_back) (detail_level)-- Parameters : days_back - Number of days to look back for backup information (default: 7)-- detail_level - Level of detail in report (BASIC, DETAILED, VERBOSE)-- Execution Ex : @F:\DBA\Scripts\AI\backup_monitoring.sql 7 DETAILED-- Last Modified: 2024-01-15-- -----------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------- INITIALIZATION: Set environment and validate parameters-- -----------------------------------------------------------------------------------
CLEAR SCREEN
SET VERIFY OFFSET FEEDBACK OFFSET LINESIZE 200SET PAGESIZE 1000SET SERVEROUTPUT ON
-- Generate unique filename with timestampCOLUMN report_filename NEW_VALUE report_filenameSELECT 'F:\DBA\Scripts\AI\Reports\DB_backup_monitoring_report_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '.log' as report_filenameFROM dual;
-- Redirect output to fileSPOOL &report_filename
-- Default parameter handlingCOLUMN days_back_final NEW_VALUE days_back_finalCOLUMN detail_level_final NEW_VALUE detail_level_final
SELECT CASE WHEN '&&1' IS NULL OR '&&1' = '' OR LENGTH(TRIM(TRANSLATE('&&1', ' +-.0123456789', ' '))) IS NOT NULL OR TO_NUMBER('&&1') < 1 OR TO_NUMBER('&&1') > 365 THEN '7' ELSE '&&1' END as days_back_final, CASE WHEN UPPER('&&2') IN ('BASIC', 'DETAILED', 'VERBOSE') THEN UPPER('&&2') ELSE 'BASIC' END as detail_level_finalFROM dual;
PROMPT Starting Backup Monitoring Script...PROMPT =====================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Days Back: &&days_back_finalPROMPT Detail Level: &&detail_level_finalPROMPT Report File: &report_filenamePROMPT
-- ------------------------------------------------------------------------------------- BACKUP SUMMARY: Overview of backup operations in specified period-- -----------------------------------------------------------------------------------
PROMPT PROMPT 1. BACKUP OPERATIONS SUMMARY (Last &&days_back_final Days)PROMPT ============================================
COLUMN input_type FORMAT A15COLUMN status FORMAT A10COLUMN backup_count FORMAT 999COLUMN total_gb FORMAT 999,999.99COLUMN avg_duration_min FORMAT 999,999
SELECT input_type as backup_type, status, COUNT(*) as backup_count, ROUND(SUM(output_bytes) / 1024 / 1024 / 1024, 2) as total_gb, ROUND(AVG((end_time - start_time) * 24 * 60)) as avg_duration_minFROM v$rman_backup_job_detailsWHERE start_time >= SYSDATE - &&days_back_finalGROUP BY input_type, statusORDER BY input_type, status;
-- ------------------------------------------------------------------------------------- RECENT BACKUP STATUS: Detailed status of recent backup operations-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. RECENT BACKUP JOB DETAILSPROMPT =============================
COLUMN start_time FORMAT A20COLUMN end_time FORMAT A20COLUMN input_type FORMAT A15COLUMN status FORMAT A10COLUMN input_bytes_gb FORMAT 999,999.99COLUMN output_bytes_gb FORMAT 999,999.99COLUMN duration_min FORMAT 9999
SELECT TO_CHAR(start_time, 'YYYY-MM-DD HH24:MI:SS') as start_time, TO_CHAR(end_time, 'YYYY-MM-DD HH24:MI:SS') as end_time, input_type, status, ROUND(input_bytes / 1024 / 1024 / 1024, 2) as input_bytes_gb, ROUND(output_bytes / 1024 / 1024 / 1024, 2) as output_bytes_gb, ROUND((end_time - start_time) * 24 * 60) as duration_minFROM v$rman_backup_job_detailsWHERE start_time >= SYSDATE - &&days_back_finalORDER BY start_time DESCFETCH FIRST 15 ROWS ONLY;
-- ------------------------------------------------------------------------------------- DATAFILE BACKUP STATUS: Datafile backup completeness and status-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. DATAFILE BACKUP STATUSPROMPT ========================
COLUMN file_id FORMAT 9999COLUMN tablespace_name FORMAT A40COLUMN last_backup FORMAT A20COLUMN backup_status FORMAT A15COLUMN days_since_backup FORMAT 999
SELECT df.file# as file_id, ts.name as tablespace_name, TO_CHAR(MAX(bs.completion_time), 'YYYY-MM-DD HH24:MI:SS') as last_backup, CASE WHEN MAX(bs.completion_time) >= SYSDATE - 1 THEN 'CURRENT' WHEN MAX(bs.completion_time) >= SYSDATE - 2 THEN 'RECENT' WHEN MAX(bs.completion_time) >= SYSDATE - 7 THEN 'AGING' ELSE 'STALE' END as backup_status, ROUND(SYSDATE - MAX(bs.completion_time)) as days_since_backupFROM v$datafile dfJOIN v$tablespace ts ON df.ts# = ts.ts#LEFT JOIN v$backup_datafile bd ON df.file# = bd.file#LEFT JOIN v$backup_set bs ON bd.set_stamp = bs.set_stamp AND bd.set_count = bs.set_countWHERE ts.name NOT LIKE '%UNDO%'GROUP BY df.file#, ts.nameORDER BY days_since_backup DESC NULLS FIRST, ts.name;
-- ------------------------------------------------------------------------------------- ARCHIVELOG BACKUP STATUS: Archive log backup and retention information-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. ARCHIVELOG BACKUP AND RETENTIONPROMPT ===================================
COLUMN thread# FORMAT 999COLUMN sequence# FORMAT 999,999COLUMN first_time FORMAT A20COLUMN backup_status FORMAT A15COLUMN deleted FORMAT A7
SELECT al.thread#, al.sequence#, TO_CHAR(al.first_time, 'YYYY-MM-DD HH24:MI:SS') as first_time, CASE WHEN b.sequence# IS NOT NULL THEN 'BACKED UP' WHEN al.deleted = 'YES' THEN 'DELETED' ELSE 'NOT BACKED UP' END as backup_status, al.deletedFROM v$archived_log alLEFT JOIN (SELECT DISTINCT sequence#, thread# FROM v$backup_redolog) b ON al.sequence# = b.sequence# AND al.thread# = b.thread#WHERE al.first_time >= SYSDATE - &&days_back_final AND UPPER('&&detail_level_final') IN ('DETAILED', 'VERBOSE')ORDER BY al.first_time DESCFETCH FIRST 20 ROWS ONLY;
-- ------------------------------------------------------------------------------------- BACKUP PIECE INFORMATION: Physical backup files and locations-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. BACKUP PIECE DETAILSPROMPT ========================
COLUMN handle FORMAT A50COLUMN start_time FORMAT A20COLUMN completion_time FORMAT A20COLUMN bytes_gb FORMAT 999,999.99COLUMN status FORMAT A10
SELECT handle, TO_CHAR(start_time, 'YYYY-MM-DD HH24:MI:SS') as start_time, TO_CHAR(completion_time, 'YYYY-MM-DD HH24:MI:SS') as completion_time, ROUND(bytes / 1024 / 1024 / 1024, 2) as bytes_gb, statusFROM v$backup_pieceWHERE start_time >= SYSDATE - &&days_back_final AND UPPER('&&detail_level_final') IN ('DETAILED', 'VERBOSE')ORDER BY start_time DESCFETCH FIRST 15 ROWS ONLY;
-- ------------------------------------------------------------------------------------- RECOVERY WINDOW ANALYSIS: Flash recovery area and retention policy-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. RECOVERY WINDOW AND RETENTION POLICYPROMPT ========================================
COLUMN name FORMAT A30COLUMN value FORMAT A50COLUMN description FORMAT A50
-- Check if FRA is enabled and get usageVAR fra_enabled VARCHAR2(3)BEGIN SELECT CASE WHEN value IS NOT NULL THEN 'YES' ELSE 'NO' END INTO :fra_enabled FROM v$parameter WHERE name = 'db_recovery_file_dest_size';EXCEPTION WHEN NO_DATA_FOUND THEN :fra_enabled := 'NO';END;/
SELECT name, value, 'Current retention policy' as descriptionFROM v$rman_configurationWHERE name = 'RETENTION POLICY'UNION ALLSELECT 'FLASH_RECOVERY_AREA' as name, p.value || ' (' || (SELECT ROUND(SUM(space_used) / 1024 / 1024 / 1024, 2) FROM v$recovery_file_dest) || ' GB used)' as value, 'Flash recovery area size and usage' as descriptionFROM v$parameter pWHERE p.name = 'db_recovery_file_dest_size' AND :fra_enabled = 'YES'UNION ALLSELECT 'OLDEST_RECOVERABLE_BACKUP' as name, TO_CHAR(MIN(completion_time), 'YYYY-MM-DD HH24:MI:SS') as value, 'Earliest point for database recovery' as descriptionFROM v$backup_pieceWHERE status = 'A'UNION ALLSELECT 'FRA_STATUS' as name, :fra_enabled || ' - ' || (SELECT COUNT(*) || ' files in use' FROM v$recovery_file_dest) as value, 'Flash Recovery Area status' as descriptionFROM dualWHERE :fra_enabled = 'YES';
-- ------------------------------------------------------------------------------------- BACKUP ALERTS AND ISSUES: Critical backup problems requiring attention-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. BACKUP ALERTS AND ISSUESPROMPT =============================
COLUMN alert_level FORMAT A10COLUMN alert_description FORMAT A75COLUMN recommendation FORMAT A80
WITH datafile_backups AS ( SELECT df.file#, ts.name as tablespace_name, MAX(bs.completion_time) as last_backup_time FROM v$datafile df JOIN v$tablespace ts ON df.ts# = ts.ts# LEFT JOIN v$backup_datafile bd ON df.file# = bd.file# LEFT JOIN v$backup_set bs ON bd.set_stamp = bs.set_stamp AND bd.set_count = bs.set_count GROUP BY df.file#, ts.name),fra_usage AS ( SELECT ROUND((SUM(space_used) / SUM(space_limit)) * 100, 2) as total_usage FROM v$recovery_file_dest)SELECT alert_level, alert_description, recommendationFROM ( SELECT 'CRITICAL' as alert_level, 'No recent backups found for ANY datafiles' as alert_description, 'Schedule immediate FULL database backup' as recommendation, 1 as sort_order FROM dual WHERE NOT EXISTS ( SELECT 1 FROM datafile_backups WHERE last_backup_time >= SYSDATE - 7 ) UNION ALL SELECT 'HIGH' as alert_level, 'Datafile ' || file# || ' (' || tablespace_name || ') has no backup in ' || ROUND(SYSDATE - last_backup_time) || ' days' as alert_description, 'Schedule immediate backup of tablespace: ' || tablespace_name as recommendation, 2 as sort_order FROM datafile_backups WHERE last_backup_time IS NULL OR last_backup_time < SYSDATE - 2 UNION ALL SELECT 'MEDIUM' as alert_level, 'Flash recovery area usage: ' || total_usage || '%' as alert_description, CASE WHEN total_usage > 90 THEN 'CRITICAL: FRA nearly full - increase size or clean immediately' WHEN total_usage > 80 THEN 'WARNING: FRA usage high - monitor closely' ELSE 'FRA usage is normal' END as recommendation, 3 as sort_order FROM fra_usage WHERE total_usage > 80 AND :fra_enabled = 'YES' UNION ALL SELECT 'LOW' as alert_level, 'No successful full backup in last 7 days' as alert_description, 'Schedule full database backup immediately' as recommendation, 4 as sort_order FROM dual WHERE NOT EXISTS ( SELECT 1 FROM v$rman_backup_job_details WHERE input_type LIKE '%FULL%' AND status = 'COMPLETED' AND start_time >= SYSDATE - 7 ))ORDER BY sort_order;
-- ------------------------------------------------------------------------------------- CRITICAL FINDINGS SUMMARY-- -----------------------------------------------------------------------------------
PROMPT PROMPT 8. CRITICAL FINDINGS SUMMARYPROMPT =============================
COLUMN finding FORMAT A80
SELECT 'ALL DATAFILES SHOW AS STALE - NO RECENT BACKUPS DETECTED' as findingFROM dualWHERE NOT EXISTS ( SELECT 1 FROM v$backup_datafile bd JOIN v$backup_set bs ON bd.set_stamp = bs.set_stamp AND bd.set_count = bs.set_count WHERE bs.completion_time >= SYSDATE - 7);
-- ------------------------------------------------------------------------------------- COMPLETION: Script completion summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT =====================================PROMPT BACKUP MONITORING COMPLETEDPROMPT =====================================PROMPT Analysis Period: Last &&days_back_final DaysPROMPT Detail Level: &&detail_level_finalPROMPT Report Saved To: &report_filenamePROMPT PROMPT *** CRITICAL ISSUE DETECTED ***PROMPT No recent backups found for any datafilesPROMPT PROMPT Immediate Actions Required:PROMPT 1. Schedule and run a FULL database backup immediatelyPROMPT 2. Verify backup configuration and RMAN settingsPROMPT 3. Check backup job scheduling and logsPROMPT 4. Validate backup can be restored and recoveredPROMPT
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment-- -----------------------------------------------------------------------------------
CLEAR COLUMNSSET VERIFY ONSET FEEDBACK ON
-- Stop spoolingSPOOL OFF
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------- INITIALIZATION: Set environment and validate parameters-- -----------------------------------------------------------------------------------
CLEAR SCREEN
SET VERIFY OFFSET FEEDBACK OFFSET LINESIZE 200SET PAGESIZE 1000SET SERVEROUTPUT ON
-- Generate unique filename with timestampCOLUMN report_filename NEW_VALUE report_filenameSELECT 'F:\DBA\Scripts\AI\Reports\DB_backup_monitoring_report_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '.log' as report_filenameFROM dual;
-- Redirect output to fileSPOOL &report_filename
-- Default parameter handlingCOLUMN days_back_final NEW_VALUE days_back_finalCOLUMN detail_level_final NEW_VALUE detail_level_final
SELECT CASE WHEN '&&1' IS NULL OR '&&1' = '' OR LENGTH(TRIM(TRANSLATE('&&1', ' +-.0123456789', ' '))) IS NOT NULL OR TO_NUMBER('&&1') < 1 OR TO_NUMBER('&&1') > 365 THEN '7' ELSE '&&1' END as days_back_final, CASE WHEN UPPER('&&2') IN ('BASIC', 'DETAILED', 'VERBOSE') THEN UPPER('&&2') ELSE 'BASIC' END as detail_level_finalFROM dual;
PROMPT Starting Backup Monitoring Script...PROMPT =====================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Days Back: &&days_back_finalPROMPT Detail Level: &&detail_level_finalPROMPT Report File: &report_filenamePROMPT
-- ------------------------------------------------------------------------------------- BACKUP SUMMARY: Overview of backup operations in specified period-- -----------------------------------------------------------------------------------
PROMPT PROMPT 1. BACKUP OPERATIONS SUMMARY (Last &&days_back_final Days)PROMPT ============================================
COLUMN input_type FORMAT A15COLUMN status FORMAT A10COLUMN backup_count FORMAT 999COLUMN total_gb FORMAT 999,999.99COLUMN avg_duration_min FORMAT 999,999
SELECT input_type as backup_type, status, COUNT(*) as backup_count, ROUND(SUM(output_bytes) / 1024 / 1024 / 1024, 2) as total_gb, ROUND(AVG((end_time - start_time) * 24 * 60)) as avg_duration_minFROM v$rman_backup_job_detailsWHERE start_time >= SYSDATE - &&days_back_finalGROUP BY input_type, statusORDER BY input_type, status;
-- ------------------------------------------------------------------------------------- RECENT BACKUP STATUS: Detailed status of recent backup operations-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. RECENT BACKUP JOB DETAILSPROMPT =============================
COLUMN start_time FORMAT A20COLUMN end_time FORMAT A20COLUMN input_type FORMAT A15COLUMN status FORMAT A10COLUMN input_bytes_gb FORMAT 999,999.99COLUMN output_bytes_gb FORMAT 999,999.99COLUMN duration_min FORMAT 9999
SELECT TO_CHAR(start_time, 'YYYY-MM-DD HH24:MI:SS') as start_time, TO_CHAR(end_time, 'YYYY-MM-DD HH24:MI:SS') as end_time, input_type, status, ROUND(input_bytes / 1024 / 1024 / 1024, 2) as input_bytes_gb, ROUND(output_bytes / 1024 / 1024 / 1024, 2) as output_bytes_gb, ROUND((end_time - start_time) * 24 * 60) as duration_minFROM v$rman_backup_job_detailsWHERE start_time >= SYSDATE - &&days_back_finalORDER BY start_time DESCFETCH FIRST 15 ROWS ONLY;
-- ------------------------------------------------------------------------------------- DATAFILE BACKUP STATUS: Datafile backup completeness and status-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. DATAFILE BACKUP STATUSPROMPT ========================
COLUMN file_id FORMAT 9999COLUMN tablespace_name FORMAT A40COLUMN last_backup FORMAT A20COLUMN backup_status FORMAT A15COLUMN days_since_backup FORMAT 999
SELECT df.file# as file_id, ts.name as tablespace_name, TO_CHAR(MAX(bs.completion_time), 'YYYY-MM-DD HH24:MI:SS') as last_backup, CASE WHEN MAX(bs.completion_time) >= SYSDATE - 1 THEN 'CURRENT' WHEN MAX(bs.completion_time) >= SYSDATE - 2 THEN 'RECENT' WHEN MAX(bs.completion_time) >= SYSDATE - 7 THEN 'AGING' ELSE 'STALE' END as backup_status, ROUND(SYSDATE - MAX(bs.completion_time)) as days_since_backupFROM v$datafile dfJOIN v$tablespace ts ON df.ts# = ts.ts#LEFT JOIN v$backup_datafile bd ON df.file# = bd.file#LEFT JOIN v$backup_set bs ON bd.set_stamp = bs.set_stamp AND bd.set_count = bs.set_countWHERE ts.name NOT LIKE '%UNDO%'GROUP BY df.file#, ts.nameORDER BY days_since_backup DESC NULLS FIRST, ts.name;
-- ------------------------------------------------------------------------------------- ARCHIVELOG BACKUP STATUS: Archive log backup and retention information-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. ARCHIVELOG BACKUP AND RETENTIONPROMPT ===================================
COLUMN thread# FORMAT 999COLUMN sequence# FORMAT 999,999COLUMN first_time FORMAT A20COLUMN backup_status FORMAT A15COLUMN deleted FORMAT A7
SELECT al.thread#, al.sequence#, TO_CHAR(al.first_time, 'YYYY-MM-DD HH24:MI:SS') as first_time, CASE WHEN b.sequence# IS NOT NULL THEN 'BACKED UP' WHEN al.deleted = 'YES' THEN 'DELETED' ELSE 'NOT BACKED UP' END as backup_status, al.deletedFROM v$archived_log alLEFT JOIN (SELECT DISTINCT sequence#, thread# FROM v$backup_redolog) b ON al.sequence# = b.sequence# AND al.thread# = b.thread#WHERE al.first_time >= SYSDATE - &&days_back_final AND UPPER('&&detail_level_final') IN ('DETAILED', 'VERBOSE')ORDER BY al.first_time DESCFETCH FIRST 20 ROWS ONLY;
-- ------------------------------------------------------------------------------------- BACKUP PIECE INFORMATION: Physical backup files and locations-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. BACKUP PIECE DETAILSPROMPT ========================
COLUMN handle FORMAT A50COLUMN start_time FORMAT A20COLUMN completion_time FORMAT A20COLUMN bytes_gb FORMAT 999,999.99COLUMN status FORMAT A10
SELECT handle, TO_CHAR(start_time, 'YYYY-MM-DD HH24:MI:SS') as start_time, TO_CHAR(completion_time, 'YYYY-MM-DD HH24:MI:SS') as completion_time, ROUND(bytes / 1024 / 1024 / 1024, 2) as bytes_gb, statusFROM v$backup_pieceWHERE start_time >= SYSDATE - &&days_back_final AND UPPER('&&detail_level_final') IN ('DETAILED', 'VERBOSE')ORDER BY start_time DESCFETCH FIRST 15 ROWS ONLY;
-- ------------------------------------------------------------------------------------- RECOVERY WINDOW ANALYSIS: Flash recovery area and retention policy-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. RECOVERY WINDOW AND RETENTION POLICYPROMPT ========================================
COLUMN name FORMAT A30COLUMN value FORMAT A50COLUMN description FORMAT A50
-- Check if FRA is enabled and get usageVAR fra_enabled VARCHAR2(3)BEGIN SELECT CASE WHEN value IS NOT NULL THEN 'YES' ELSE 'NO' END INTO :fra_enabled FROM v$parameter WHERE name = 'db_recovery_file_dest_size';EXCEPTION WHEN NO_DATA_FOUND THEN :fra_enabled := 'NO';END;/
SELECT name, value, 'Current retention policy' as descriptionFROM v$rman_configurationWHERE name = 'RETENTION POLICY'UNION ALLSELECT 'FLASH_RECOVERY_AREA' as name, p.value || ' (' || (SELECT ROUND(SUM(space_used) / 1024 / 1024 / 1024, 2) FROM v$recovery_file_dest) || ' GB used)' as value, 'Flash recovery area size and usage' as descriptionFROM v$parameter pWHERE p.name = 'db_recovery_file_dest_size' AND :fra_enabled = 'YES'UNION ALLSELECT 'OLDEST_RECOVERABLE_BACKUP' as name, TO_CHAR(MIN(completion_time), 'YYYY-MM-DD HH24:MI:SS') as value, 'Earliest point for database recovery' as descriptionFROM v$backup_pieceWHERE status = 'A'UNION ALLSELECT 'FRA_STATUS' as name, :fra_enabled || ' - ' || (SELECT COUNT(*) || ' files in use' FROM v$recovery_file_dest) as value, 'Flash Recovery Area status' as descriptionFROM dualWHERE :fra_enabled = 'YES';
-- ------------------------------------------------------------------------------------- BACKUP ALERTS AND ISSUES: Critical backup problems requiring attention-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. BACKUP ALERTS AND ISSUESPROMPT =============================
COLUMN alert_level FORMAT A10COLUMN alert_description FORMAT A75COLUMN recommendation FORMAT A80
WITH datafile_backups AS ( SELECT df.file#, ts.name as tablespace_name, MAX(bs.completion_time) as last_backup_time FROM v$datafile df JOIN v$tablespace ts ON df.ts# = ts.ts# LEFT JOIN v$backup_datafile bd ON df.file# = bd.file# LEFT JOIN v$backup_set bs ON bd.set_stamp = bs.set_stamp AND bd.set_count = bs.set_count GROUP BY df.file#, ts.name),fra_usage AS ( SELECT ROUND((SUM(space_used) / SUM(space_limit)) * 100, 2) as total_usage FROM v$recovery_file_dest)SELECT alert_level, alert_description, recommendationFROM ( SELECT 'CRITICAL' as alert_level, 'No recent backups found for ANY datafiles' as alert_description, 'Schedule immediate FULL database backup' as recommendation, 1 as sort_order FROM dual WHERE NOT EXISTS ( SELECT 1 FROM datafile_backups WHERE last_backup_time >= SYSDATE - 7 ) UNION ALL SELECT 'HIGH' as alert_level, 'Datafile ' || file# || ' (' || tablespace_name || ') has no backup in ' || ROUND(SYSDATE - last_backup_time) || ' days' as alert_description, 'Schedule immediate backup of tablespace: ' || tablespace_name as recommendation, 2 as sort_order FROM datafile_backups WHERE last_backup_time IS NULL OR last_backup_time < SYSDATE - 2 UNION ALL SELECT 'MEDIUM' as alert_level, 'Flash recovery area usage: ' || total_usage || '%' as alert_description, CASE WHEN total_usage > 90 THEN 'CRITICAL: FRA nearly full - increase size or clean immediately' WHEN total_usage > 80 THEN 'WARNING: FRA usage high - monitor closely' ELSE 'FRA usage is normal' END as recommendation, 3 as sort_order FROM fra_usage WHERE total_usage > 80 AND :fra_enabled = 'YES' UNION ALL SELECT 'LOW' as alert_level, 'No successful full backup in last 7 days' as alert_description, 'Schedule full database backup immediately' as recommendation, 4 as sort_order FROM dual WHERE NOT EXISTS ( SELECT 1 FROM v$rman_backup_job_details WHERE input_type LIKE '%FULL%' AND status = 'COMPLETED' AND start_time >= SYSDATE - 7 ))ORDER BY sort_order;
-- ------------------------------------------------------------------------------------- CRITICAL FINDINGS SUMMARY-- -----------------------------------------------------------------------------------
PROMPT PROMPT 8. CRITICAL FINDINGS SUMMARYPROMPT =============================
COLUMN finding FORMAT A80
SELECT 'ALL DATAFILES SHOW AS STALE - NO RECENT BACKUPS DETECTED' as findingFROM dualWHERE NOT EXISTS ( SELECT 1 FROM v$backup_datafile bd JOIN v$backup_set bs ON bd.set_stamp = bs.set_stamp AND bd.set_count = bs.set_count WHERE bs.completion_time >= SYSDATE - 7);
-- ------------------------------------------------------------------------------------- COMPLETION: Script completion summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT =====================================PROMPT BACKUP MONITORING COMPLETEDPROMPT =====================================PROMPT Analysis Period: Last &&days_back_final DaysPROMPT Detail Level: &&detail_level_finalPROMPT Report Saved To: &report_filenamePROMPT PROMPT *** CRITICAL ISSUE DETECTED ***PROMPT No recent backups found for any datafilesPROMPT PROMPT Immediate Actions Required:PROMPT 1. Schedule and run a FULL database backup immediatelyPROMPT 2. Verify backup configuration and RMAN settingsPROMPT 3. Check backup job scheduling and logsPROMPT 4. Validate backup can be restored and recoveredPROMPT
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment-- -----------------------------------------------------------------------------------
CLEAR COLUMNSSET VERIFY ONSET FEEDBACK ON
-- Stop spoolingSPOOL OFF
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------