database_health_monitor.sql
-- ------------------------------------------------------------------------------------- File Name : database_health_monitor.sql-- Author : Pierre Montbleau-- Description : Comprehensive Oracle database health monitoring and alerting system-- Purpose : Monitor database health, detect issues proactively, and generate health reports-- Call Syntax : @F:\DBA\Scripts\database_health_monitor.sql (monitor_mode) (alert_level) (refresh_interval)-- Parameters : monitor_mode - Monitoring mode (REALTIME, BATCH, SUMMARY, REPORT)-- alert_level - Alert threshold level (LOW, MEDIUM, HIGH, CRITICAL)-- refresh_interval - Refresh interval in seconds for realtime mode (default: 30)-- Last Modified: 2024-01-15-- -----------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------- INITIALIZATION: Set environment and validate parameters-- -----------------------------------------------------------------------------------
CLEAR SCREENset verify OFFCOLUMN "_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_health_monitor_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '_' || '&1' || '_' || '&2' || '.log' AS output_filenameFROM DUAL;
DEFINE output_filename = &output_filename
SET VERIFY OFFSET FEEDBACK ONSET LINESIZE 200SET PAGESIZE 1000SET SERVEROUTPUT ON
PROMPT Starting Database Health Monitor...PROMPT ====================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Monitor Mode: &1PROMPT Alert Level: &2PROMPT Refresh Interval: &3 secondsPROMPT Timestamp: &DATE1 &TIME1PROMPT
-- Validate parametersDECLARE v_monitor_mode VARCHAR2(20) := UPPER('&1'); v_alert_level VARCHAR2(20) := UPPER('&2'); v_refresh_interval NUMBER := NVL(TO_NUMBER('&3'), 30);BEGIN IF v_monitor_mode NOT IN ('REALTIME', 'BATCH', 'SUMMARY', 'REPORT') THEN RAISE_APPLICATION_ERROR(-20001, 'Monitor mode must be REALTIME, BATCH, SUMMARY, or REPORT'); END IF; IF v_alert_level NOT IN ('LOW', 'MEDIUM', 'HIGH', 'CRITICAL') THEN RAISE_APPLICATION_ERROR(-20002, 'Alert level must be LOW, MEDIUM, HIGH, or CRITICAL'); END IF; IF v_refresh_interval < 5 OR v_refresh_interval > 300 THEN RAISE_APPLICATION_ERROR(-20003, 'Refresh interval must be between 5 and 300 seconds'); END IF; DBMS_OUTPUT.PUT_LINE('Parameter validation successful');EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE('Error: Invalid numeric parameter for refresh interval'); RAISE; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- DATABASE AVAILABILITY: Basic database status and connectivity-- -----------------------------------------------------------------------------------
SPOOL &output_filename
PROMPT PROMPT 1. DATABASE AVAILABILITY STATUSPROMPT ================================
COLUMN status_check FORMAT A30COLUMN current_value FORMAT A20COLUMN expected_value FORMAT A20COLUMN health_status FORMAT A10
SELECT 'Database Status' as status_check, status as current_value, 'OPEN' as expected_value, CASE status WHEN 'OPEN' THEN 'HEALTHY' ELSE 'CRITICAL' END as health_statusFROM v$instanceUNION ALLSELECT 'Instance Name', instance_name, 'N/A', 'HEALTHY'FROM v$instanceUNION ALLSELECT 'Archive Log Mode', log_mode, 'ARCHIVELOG', CASE log_mode WHEN 'ARCHIVELOG' THEN 'HEALTHY' ELSE 'WARNING' ENDFROM v$databaseUNION ALLSELECT 'Active Sessions', TO_CHAR(COUNT(*)), '< 500', CASE WHEN COUNT(*) > 500 THEN 'WARNING' WHEN COUNT(*) > 1000 THEN 'CRITICAL' ELSE 'HEALTHY' ENDFROM v$sessionWHERE type = 'USER'ORDER BY 3, 1;
-- ------------------------------------------------------------------------------------- PERFORMANCE HEALTH: Key performance indicators-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. PERFORMANCE HEALTH INDICATORSPROMPT =================================
COLUMN metric_name FORMAT A40COLUMN current_value FORMAT 999,999,999.99COLUMN healthy_range FORMAT A20COLUMN health_status FORMAT A10COLUMN trend FORMAT A15
SELECT metric_name, value as current_value, CASE metric_name WHEN 'Database CPU Time Ratio' THEN '> 80%' WHEN 'Database Wait Time Ratio' THEN '< 20%' WHEN 'Average Active Sessions' THEN '< 10' WHEN 'Physical Reads Per Sec' THEN '< 1000' WHEN 'User Commits Per Sec' THEN 'N/A' ELSE 'N/A' END as healthy_range, CASE WHEN metric_name = 'Database CPU Time Ratio' AND value < 70 THEN 'HEALTHY' WHEN metric_name = 'Database CPU Time Ratio' AND value < 85 THEN 'WARNING' WHEN metric_name = 'Database CPU Time Ratio' AND value >= 85 THEN 'CRITICAL' WHEN metric_name = 'Average Active Sessions' AND value < 5 THEN 'HEALTHY' WHEN metric_name = 'Average Active Sessions' AND value < 15 THEN 'WARNING' WHEN metric_name = 'Average Active Sessions' AND value >= 15 THEN 'CRITICAL' WHEN metric_name = 'Physical Reads Per Sec' AND value < 500 THEN 'HEALTHY' WHEN metric_name = 'Physical Reads Per Sec' AND value < 1500 THEN 'WARNING' WHEN metric_name = 'Physical Reads Per Sec' AND value >= 1500 THEN 'CRITICAL' ELSE 'HEALTHY' END as health_status, 'STABLE' as trendFROM v$sysmetricWHERE group_id = 2 AND metric_name IN ( 'Database CPU Time Ratio', 'Database Wait Time Ratio', 'Average Active Sessions', 'Physical Reads Per Sec', 'User Commits Per Sec' )ORDER BY CASE health_status WHEN 'CRITICAL' THEN 1 WHEN 'WARNING' THEN 2 ELSE 3 END, metric_name;
-- ------------------------------------------------------------------------------------- STORAGE HEALTH: Tablespace and storage monitoring-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. STORAGE HEALTH ASSESSMENTPROMPT =============================
COLUMN tablespace_name FORMAT A25COLUMN used_pct FORMAT 999.99COLUMN free_gb FORMAT 999,999.99COLUMN autoextensible FORMAT A5COLUMN health_status FORMAT A10COLUMN recommendation FORMAT A60
SELECT tablespace_name, ROUND((tablespace_size - used_space) / tablespace_size * 100, 2) as used_pct, ROUND((tablespace_size - used_space) / 1024 / 1024 / 1024, 2) as free_gb, 'YES' as autoextensible, CASE WHEN (tablespace_size - used_space) / tablespace_size < 0.05 THEN 'CRITICAL' WHEN (tablespace_size - used_space) / tablespace_size < 0.10 THEN 'WARNING' WHEN (tablespace_size - used_space) / tablespace_size < 0.20 THEN 'MONITOR' ELSE 'HEALTHY' END as health_status, CASE WHEN (tablespace_size - used_space) / tablespace_size < 0.05 THEN 'IMMEDIATE ACTION: Add space' WHEN (tablespace_size - used_space) / tablespace_size < 0.10 THEN 'Urgent: Plan space expansion' WHEN (tablespace_size - used_space) / tablespace_size < 0.20 THEN 'Monitor growth trends' ELSE 'Adequate free space' END as recommendationFROM dba_tablespace_usage_metricsWHERE (tablespace_size - used_space) / tablespace_size < 0.20 OR tablespace_name IN ('SYSTEM', 'SYSAUX', 'UNDOTBS1')ORDER BY health_status, used_pct DESC;
-- ------------------------------------------------------------------------------------- MEMORY HEALTH: Memory usage and configuration-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. MEMORY HEALTH STATUSPROMPT ========================
COLUMN memory_component FORMAT A25COLUMN allocated_gb FORMAT 999,999.99COLUMN used_gb FORMAT 999,999.99COLUMN utilization_pct FORMAT 999.99COLUMN health_status FORMAT A10
SELECT 'SGA Total' as memory_component, ROUND(SUM(value) / 1024 / 1024 / 1024, 2) as allocated_gb, ROUND(SUM(value) / 1024 / 1024 / 1024, 2) as used_gb, 100.00 as utilization_pct, 'HEALTHY' as health_statusFROM v$sgaUNION ALLSELECT 'Buffer Cache', ROUND(bytes / 1024 / 1024 / 1024, 2), ROUND((bytes - free_memory) / 1024 / 1024 / 1024, 2), ROUND((bytes - free_memory) / bytes * 100, 2), CASE WHEN ROUND((bytes - free_memory) / bytes * 100, 2) > 95 THEN 'CRITICAL' WHEN ROUND((bytes - free_memory) / bytes * 100, 2) > 90 THEN 'WARNING' ELSE 'HEALTHY' ENDFROM ( SELECT SUM(bytes) as bytes, SUM(CASE WHEN name = 'free memory' THEN bytes ELSE 0 END) as free_memory FROM v$sgastat WHERE pool = 'buffer cache')UNION ALLSELECT 'Shared Pool', ROUND(bytes / 1024 / 1024 / 1024, 2), ROUND((bytes - free_memory) / 1024 / 1024 / 1024, 2), ROUND((bytes - free_memory) / bytes * 100, 2), CASE WHEN ROUND((bytes - free_memory) / bytes * 100, 2) > 90 THEN 'WARNING' ELSE 'HEALTHY' ENDFROM ( SELECT SUM(bytes) as bytes, SUM(CASE WHEN name = 'free memory' THEN bytes ELSE 0 END) as free_memory FROM v$sgastat WHERE pool = 'shared pool')ORDER BY 5,1;
-- ------------------------------------------------------------------------------------- WAIT EVENT HEALTH: Wait event analysis and health assessment-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. WAIT EVENT HEALTH ANALYSISPROMPT ==============================
COLUMN wait_event FORMAT A35COLUMN total_waits FORMAT 999,999,999,999COLUMN time_waited_sec FORMAT 999,999,999COLUMN wait_class FORMAT A20COLUMN health_status FORMAT A10
SELECT event as wait_event, total_waits, ROUND(time_waited_micro / 1000000, 2) as time_waited_sec, wait_class, CASE WHEN ROUND(time_waited_micro / 1000000, 2) > 3600 THEN 'CRITICAL' WHEN ROUND(time_waited_micro / 1000000, 2) > 600 THEN 'WARNING' WHEN ROUND(time_waited_micro / 1000000, 2) > 60 THEN 'MONITOR' ELSE 'HEALTHY' END as health_statusFROM v$system_eventWHERE wait_class NOT IN ('Idle', 'Other') AND total_waits > 0 AND (UPPER('&2') IN ('HIGH', 'CRITICAL') OR (UPPER('&2') = 'MEDIUM' AND ROUND(time_waited_micro / 1000000, 2) > 60) OR (UPPER('&2') = 'LOW' AND ROUND(time_waited_micro / 1000000, 2) > 300))ORDER BY 3 DESCFETCH FIRST 10 ROWS ONLY;
-- ------------------------------------------------------------------------------------- SESSION HEALTH: Session and connection health-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. SESSION HEALTH MONITORINGPROMPT =============================
COLUMN session_metric FORMAT A30COLUMN current_value FORMAT 999,999COLUMN healthy_threshold FORMAT A15COLUMN health_status FORMAT A10COLUMN action_required FORMAT A50
SELECT 'Total User Sessions' as session_metric, COUNT(*) as current_value, '< 500' as healthy_threshold, CASE WHEN COUNT(*) > 1000 THEN 'CRITICAL' WHEN COUNT(*) > 500 THEN 'WARNING' ELSE 'HEALTHY' END as health_status, CASE WHEN COUNT(*) > 1000 THEN 'Investigate session leaks' WHEN COUNT(*) > 500 THEN 'Monitor session growth' ELSE 'Normal session count' END as action_requiredFROM v$sessionWHERE type = 'USER'UNION ALLSELECT 'Active Sessions', SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END), '< 50', CASE WHEN SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END) > 100 THEN 'CRITICAL' WHEN SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END) > 50 THEN 'WARNING' ELSE 'HEALTHY' END, CASE WHEN SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END) > 100 THEN 'Check for runaway queries' WHEN SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END) > 50 THEN 'Monitor active workload' ELSE 'Normal activity level' ENDFROM v$sessionWHERE type = 'USER'UNION ALLSELECT 'Blocking Sessions', COUNT(DISTINCT blocking_session), '0', CASE WHEN COUNT(DISTINCT blocking_session) > 5 THEN 'CRITICAL' WHEN COUNT(DISTINCT blocking_session) > 0 THEN 'WARNING' ELSE 'HEALTHY' END, CASE WHEN COUNT(DISTINCT blocking_session) > 5 THEN 'Immediate investigation required' WHEN COUNT(DISTINCT blocking_session) > 0 THEN 'Identify and resolve blocks' ELSE 'No blocking sessions' ENDFROM v$sessionWHERE blocking_session IS NOT NULLORDER BY 4,1;
-- ------------------------------------------------------------------------------------- BACKUP AND RECOVERY HEALTH: Backup status and recoverability-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. BACKUP AND RECOVERY HEALTHPROMPT ============================
COLUMN backup_metric FORMAT A40COLUMN current_status FORMAT A30COLUMN last_occurrence FORMAT A20COLUMN health_status FORMAT A10COLUMN recommendation FORMAT A60
SELECT 'Last Successful RMAN Backup' as backup_metric, TO_CHAR(MAX(end_time), 'YYYY-MM-DD HH24:MI:SS') as current_status, TO_CHAR(MAX(end_time), 'YYYY-MM-DD HH24:MI') as last_occurrence, CASE WHEN MAX(end_time) > SYSDATE - 1 THEN 'HEALTHY' WHEN MAX(end_time) > SYSDATE - 2 THEN 'WARNING' ELSE 'CRITICAL' END as health_status, CASE WHEN MAX(end_time) > SYSDATE - 1 THEN 'Backup current' WHEN MAX(end_time) > SYSDATE - 2 THEN 'Schedule backup soon' ELSE 'IMMEDIATE BACKUP REQUIRED' END as recommendationFROM v$rman_statusWHERE status = 'COMPLETED' AND operation LIKE '%BACKUP%'
UNION ALL
SELECT 'Flash Recovery Area Usage', ROUND((space_used / space_limit) * 100, 2) || '%', 'Current', CASE WHEN (space_used / space_limit) > 0.95 THEN 'CRITICAL' WHEN (space_used / space_limit) > 0.90 THEN 'WARNING' WHEN (space_used / space_limit) > 0.85 THEN 'MONITOR' ELSE 'HEALTHY' END, CASE WHEN (space_used / space_limit) > 0.95 THEN 'IMMEDIATE CLEANUP REQUIRED' WHEN (space_used / space_limit) > 0.90 THEN 'Urgent: Clean up FRA' WHEN (space_used / space_limit) > 0.85 THEN 'Monitor FRA usage' ELSE 'Adequate free space' ENDFROM ( SELECT SUM(space_used) as space_used, SUM(space_limit) as space_limit FROM v$recovery_file_dest)
ORDER BY 4;
-- ------------------------------------------------------------------------------------- REAL-TIME MONITORING: Continuous health monitoring loop-- -----------------------------------------------------------------------------------
PROMPT PROMPT 8. REAL-TIME HEALTH MONITORINGPROMPT ===============================
DECLARE v_mode VARCHAR2(20) := UPPER('&1'); -- Get parameter v_refresh_interval NUMBER := 30; v_monitor_count NUMBER := 0; v_max_iterations NUMBER := 12; -- Variables for metrics v_active_sessions NUMBER; v_block_count NUMBER; v_tablespace_critical NUMBER := 0; v_wait_events_critical NUMBER := 0; v_high_cpu_sessions NUMBER;BEGIN IF v_mode = 'REALTIME' OR v_mode = 'MONITOR' THEN DBMS_OUTPUT.PUT_LINE('=== REAL-TIME DATABASE HEALTH MONITOR ==='); DBMS_OUTPUT.PUT_LINE('Started: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')); DBMS_OUTPUT.PUT_LINE('Refresh interval: ' || v_refresh_interval || ' seconds'); DBMS_OUTPUT.PUT_LINE('Maximum cycles: ' || v_max_iterations); DBMS_OUTPUT.PUT_LINE('Press Ctrl+C to stop monitoring'); DBMS_OUTPUT.PUT_LINE('=========================================' || CHR(10));
WHILE v_monitor_count < v_max_iterations LOOP v_monitor_count := v_monitor_count + 1; DBMS_OUTPUT.PUT_LINE('[' || TO_CHAR(SYSDATE, 'HH24:MI:SS') || '] Cycle #' || v_monitor_count);
-- 1. Check active sessions SELECT COUNT(*) INTO v_active_sessions FROM v$session WHERE status = 'ACTIVE' AND type = 'USER'; DBMS_OUTPUT.PUT_LINE(' Active Sessions: ' || v_active_sessions || CASE WHEN v_active_sessions > 200 THEN ' (CRITICAL)' WHEN v_active_sessions > 100 THEN ' (WARNING)' ELSE ' (Normal)' END);
-- 2. Check blocking sessions SELECT COUNT(DISTINCT blocking_session) INTO v_block_count FROM v$session WHERE blocking_session IS NOT NULL; IF v_block_count > 0 THEN DBMS_OUTPUT.PUT_LINE(' Blocking Sessions: ' || v_block_count || CASE WHEN v_block_count > 5 THEN ' (CRITICAL)' WHEN v_block_count > 2 THEN ' (WARNING)' ELSE '' END); END IF;
-- 3. Check tablespace usage v_tablespace_critical := 0; FOR space_rec IN ( SELECT tablespace_name, ROUND((used_space / tablespace_size) * 100, 1) as used_pct, CASE WHEN (used_space / tablespace_size) > 0.95 THEN 'CRITICAL' WHEN (used_space / tablespace_size) > 0.90 THEN 'WARNING' ELSE 'OK' END as status FROM dba_tablespace_usage_metrics WHERE (used_space / tablespace_size) > 0.85 ORDER BY used_space / tablespace_size DESC ) LOOP IF space_rec.status IN ('CRITICAL', 'WARNING') THEN DBMS_OUTPUT.PUT_LINE(' Tablespace ' || space_rec.tablespace_name || ': ' || space_rec.used_pct || '% (' || space_rec.status || ')'); IF space_rec.status = 'CRITICAL' THEN v_tablespace_critical := v_tablespace_critical + 1; END IF; END IF; END LOOP;
-- 4. Check for high CPU sessions SELECT COUNT(*) INTO v_high_cpu_sessions FROM ( SELECT s.sid, s.serial#, s.username, ROUND(ss.value/1000000, 2) as cpu_sec FROM v$session s, v$sesstat ss, v$statname sn WHERE s.sid = ss.sid AND ss.statistic# = sn.statistic# AND sn.name = 'CPU used by this session' AND ss.value > 300000000 -- 300 CPU seconds AND s.status = 'ACTIVE' AND ROWNUM <= 5 ); IF v_high_cpu_sessions > 0 THEN DBMS_OUTPUT.PUT_LINE(' High CPU Sessions: ' || v_high_cpu_sessions || ' sessions > 300 CPU seconds'); END IF;
-- Summary for first cycle IF v_monitor_count = 1 THEN DBMS_OUTPUT.PUT_LINE(CHR(10) || 'INITIAL STATUS SUMMARY:'); DBMS_OUTPUT.PUT_LINE(' - Active User Sessions: ' || v_active_sessions); DBMS_OUTPUT.PUT_LINE(' - Blocking Sessions: ' || v_block_count); DBMS_OUTPUT.PUT_LINE(' - Critical Tablespaces: ' || v_tablespace_critical); DBMS_OUTPUT.PUT_LINE(' - High CPU Sessions: ' || v_high_cpu_sessions); END IF;
DBMS_OUTPUT.PUT_LINE('----------------------------------------');
-- Wait for next cycle IF v_monitor_count < v_max_iterations THEN DBMS_LOCK.SLEEP(v_refresh_interval); END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(CHR(10) || '=== MONITORING COMPLETED ==='); DBMS_OUTPUT.PUT_LINE('Completed: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')); DBMS_OUTPUT.PUT_LINE('Total cycles: ' || v_monitor_count);
ELSE DBMS_OUTPUT.PUT_LINE('Real-time monitoring requires "REALTIME" or "MONITOR" parameter.'); DBMS_OUTPUT.PUT_LINE('Current mode: ' || v_mode); DBMS_OUTPUT.PUT_LINE('Usage: @script_name REALTIME'); END IF;EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Monitoring interrupted: ' || SQLERRM);END;/
-- ------------------------------------------------------------------------------------- HEALTH SUMMARY REPORT: Consolidated health assessment-- -----------------------------------------------------------------------------------
PROMPT PROMPT 9. DATABASE HEALTH SUMMARY REPORTPROMPT ==================================
COLUMN health_category FORMAT A25COLUMN health_score FORMAT 999COLUMN critical_issues FORMAT 999COLUMN warning_issues FORMAT 999COLUMN overall_status FORMAT A15
SELECT 'Availability' as health_category, 95 as health_score, 0 as critical_issues, 1 as warning_issues, 'HEALTHY' as overall_statusFROM dualWHERE (SELECT status FROM v$instance) = 'OPEN'UNION ALLSELECT 'Performance', CASE WHEN (SELECT COUNT(*) FROM v$sysmetric WHERE group_id = 2 AND metric_name = 'Database CPU Time Ratio' AND value > 85) > 0 THEN 70 ELSE 90 END, (SELECT COUNT(*) FROM v$sysmetric WHERE group_id = 2 AND ((metric_name = 'Database CPU Time Ratio' AND value > 90) OR (metric_name = 'Average Active Sessions' AND value > 20))), (SELECT COUNT(*) FROM v$sysmetric WHERE group_id = 2 AND ((metric_name = 'Database CPU Time Ratio' AND value BETWEEN 85 AND 90) OR (metric_name = 'Average Active Sessions' AND value BETWEEN 10 AND 20))), CASE WHEN (SELECT COUNT(*) FROM v$sysmetric WHERE group_id = 2 AND metric_name = 'Database CPU Time Ratio' AND value > 90) > 0 THEN 'WARNING' ELSE 'HEALTHY' ENDFROM dualUNION ALLSELECT 'Storage', CASE WHEN (SELECT COUNT(*) FROM dba_tablespace_usage_metrics WHERE (tablespace_size - used_space) / tablespace_size < 0.10) > 0 THEN 60 ELSE 95 END, (SELECT COUNT(*) FROM dba_tablespace_usage_metrics WHERE (tablespace_size - used_space) / tablespace_size < 0.05), (SELECT COUNT(*) FROM dba_tablespace_usage_metrics WHERE (tablespace_size - used_space) / tablespace_size BETWEEN 0.05 AND 0.10), CASE WHEN (SELECT COUNT(*) FROM dba_tablespace_usage_metrics WHERE (tablespace_size - used_space) / tablespace_size < 0.05) > 0 THEN 'CRITICAL' WHEN (SELECT COUNT(*) FROM dba_tablespace_usage_metrics WHERE (tablespace_size - used_space) / tablespace_size < 0.10) > 0 THEN 'WARNING' ELSE 'HEALTHY' ENDFROM dualUNION ALLSELECT 'Backup and Recovery', 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 95 ELSE 50 END, CASE WHEN EXISTS (SELECT 1 FROM v$rman_backup_job_details WHERE status = 'COMPLETED' AND input_type = 'DB FULL' AND end_time > SYSDATE - 7) THEN 0 ELSE 1 END, CASE WHEN EXISTS (SELECT 1 FROM v$rman_backup_job_details WHERE status = 'COMPLETED' AND input_type = 'DB FULL' AND end_time > SYSDATE - 2) THEN 0 ELSE 1 END, 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 'HEALTHY' WHEN EXISTS (SELECT 1 FROM v$rman_backup_job_details WHERE status = 'COMPLETED' AND input_type = 'DB FULL' AND end_time > SYSDATE - 7) THEN 'WARNING' ELSE 'CRITICAL' ENDFROM dualORDER BY 2;
-- ------------------------------------------------------------------------------------- COMPLETION: Health monitoring summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT ====================================PROMPT DATABASE HEALTH MONITORING COMPLETEDPROMPT ====================================PROMPT Monitor Mode: &1PROMPT Alert Level: &2PROMPT Refresh Interval: &3 secondsPROMPT Completion Time: &DATE1 &TIME1PROMPT PROMPT Health Summary:PROMPT - Review CRITICAL issues immediatelyPROMPT - Address WARNING level alerts promptlyPROMPT - Monitor HEALTHY systems regularlyPROMPT - Schedule periodic health assessmentsPROMPT
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment and stop spooling-- -----------------------------------------------------------------------------------
SPOOL OFF
CLEAR COLUMNSSET VERIFY ON
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------- INITIALIZATION: Set environment and validate parameters-- -----------------------------------------------------------------------------------
CLEAR SCREENset verify OFFCOLUMN "_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_health_monitor_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '_' || '&1' || '_' || '&2' || '.log' AS output_filenameFROM DUAL;
DEFINE output_filename = &output_filename
SET VERIFY OFFSET FEEDBACK ONSET LINESIZE 200SET PAGESIZE 1000SET SERVEROUTPUT ON
PROMPT Starting Database Health Monitor...PROMPT ====================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Monitor Mode: &1PROMPT Alert Level: &2PROMPT Refresh Interval: &3 secondsPROMPT Timestamp: &DATE1 &TIME1PROMPT
-- Validate parametersDECLARE v_monitor_mode VARCHAR2(20) := UPPER('&1'); v_alert_level VARCHAR2(20) := UPPER('&2'); v_refresh_interval NUMBER := NVL(TO_NUMBER('&3'), 30);BEGIN IF v_monitor_mode NOT IN ('REALTIME', 'BATCH', 'SUMMARY', 'REPORT') THEN RAISE_APPLICATION_ERROR(-20001, 'Monitor mode must be REALTIME, BATCH, SUMMARY, or REPORT'); END IF; IF v_alert_level NOT IN ('LOW', 'MEDIUM', 'HIGH', 'CRITICAL') THEN RAISE_APPLICATION_ERROR(-20002, 'Alert level must be LOW, MEDIUM, HIGH, or CRITICAL'); END IF; IF v_refresh_interval < 5 OR v_refresh_interval > 300 THEN RAISE_APPLICATION_ERROR(-20003, 'Refresh interval must be between 5 and 300 seconds'); END IF; DBMS_OUTPUT.PUT_LINE('Parameter validation successful');EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE('Error: Invalid numeric parameter for refresh interval'); RAISE; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- DATABASE AVAILABILITY: Basic database status and connectivity-- -----------------------------------------------------------------------------------
SPOOL &output_filename
PROMPT PROMPT 1. DATABASE AVAILABILITY STATUSPROMPT ================================
COLUMN status_check FORMAT A30COLUMN current_value FORMAT A20COLUMN expected_value FORMAT A20COLUMN health_status FORMAT A10
SELECT 'Database Status' as status_check, status as current_value, 'OPEN' as expected_value, CASE status WHEN 'OPEN' THEN 'HEALTHY' ELSE 'CRITICAL' END as health_statusFROM v$instanceUNION ALLSELECT 'Instance Name', instance_name, 'N/A', 'HEALTHY'FROM v$instanceUNION ALLSELECT 'Archive Log Mode', log_mode, 'ARCHIVELOG', CASE log_mode WHEN 'ARCHIVELOG' THEN 'HEALTHY' ELSE 'WARNING' ENDFROM v$databaseUNION ALLSELECT 'Active Sessions', TO_CHAR(COUNT(*)), '< 500', CASE WHEN COUNT(*) > 500 THEN 'WARNING' WHEN COUNT(*) > 1000 THEN 'CRITICAL' ELSE 'HEALTHY' ENDFROM v$sessionWHERE type = 'USER'ORDER BY 3, 1;
-- ------------------------------------------------------------------------------------- PERFORMANCE HEALTH: Key performance indicators-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. PERFORMANCE HEALTH INDICATORSPROMPT =================================
COLUMN metric_name FORMAT A40COLUMN current_value FORMAT 999,999,999.99COLUMN healthy_range FORMAT A20COLUMN health_status FORMAT A10COLUMN trend FORMAT A15
SELECT metric_name, value as current_value, CASE metric_name WHEN 'Database CPU Time Ratio' THEN '> 80%' WHEN 'Database Wait Time Ratio' THEN '< 20%' WHEN 'Average Active Sessions' THEN '< 10' WHEN 'Physical Reads Per Sec' THEN '< 1000' WHEN 'User Commits Per Sec' THEN 'N/A' ELSE 'N/A' END as healthy_range, CASE WHEN metric_name = 'Database CPU Time Ratio' AND value < 70 THEN 'HEALTHY' WHEN metric_name = 'Database CPU Time Ratio' AND value < 85 THEN 'WARNING' WHEN metric_name = 'Database CPU Time Ratio' AND value >= 85 THEN 'CRITICAL' WHEN metric_name = 'Average Active Sessions' AND value < 5 THEN 'HEALTHY' WHEN metric_name = 'Average Active Sessions' AND value < 15 THEN 'WARNING' WHEN metric_name = 'Average Active Sessions' AND value >= 15 THEN 'CRITICAL' WHEN metric_name = 'Physical Reads Per Sec' AND value < 500 THEN 'HEALTHY' WHEN metric_name = 'Physical Reads Per Sec' AND value < 1500 THEN 'WARNING' WHEN metric_name = 'Physical Reads Per Sec' AND value >= 1500 THEN 'CRITICAL' ELSE 'HEALTHY' END as health_status, 'STABLE' as trendFROM v$sysmetricWHERE group_id = 2 AND metric_name IN ( 'Database CPU Time Ratio', 'Database Wait Time Ratio', 'Average Active Sessions', 'Physical Reads Per Sec', 'User Commits Per Sec' )ORDER BY CASE health_status WHEN 'CRITICAL' THEN 1 WHEN 'WARNING' THEN 2 ELSE 3 END, metric_name;
-- ------------------------------------------------------------------------------------- STORAGE HEALTH: Tablespace and storage monitoring-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. STORAGE HEALTH ASSESSMENTPROMPT =============================
COLUMN tablespace_name FORMAT A25COLUMN used_pct FORMAT 999.99COLUMN free_gb FORMAT 999,999.99COLUMN autoextensible FORMAT A5COLUMN health_status FORMAT A10COLUMN recommendation FORMAT A60
SELECT tablespace_name, ROUND((tablespace_size - used_space) / tablespace_size * 100, 2) as used_pct, ROUND((tablespace_size - used_space) / 1024 / 1024 / 1024, 2) as free_gb, 'YES' as autoextensible, CASE WHEN (tablespace_size - used_space) / tablespace_size < 0.05 THEN 'CRITICAL' WHEN (tablespace_size - used_space) / tablespace_size < 0.10 THEN 'WARNING' WHEN (tablespace_size - used_space) / tablespace_size < 0.20 THEN 'MONITOR' ELSE 'HEALTHY' END as health_status, CASE WHEN (tablespace_size - used_space) / tablespace_size < 0.05 THEN 'IMMEDIATE ACTION: Add space' WHEN (tablespace_size - used_space) / tablespace_size < 0.10 THEN 'Urgent: Plan space expansion' WHEN (tablespace_size - used_space) / tablespace_size < 0.20 THEN 'Monitor growth trends' ELSE 'Adequate free space' END as recommendationFROM dba_tablespace_usage_metricsWHERE (tablespace_size - used_space) / tablespace_size < 0.20 OR tablespace_name IN ('SYSTEM', 'SYSAUX', 'UNDOTBS1')ORDER BY health_status, used_pct DESC;
-- ------------------------------------------------------------------------------------- MEMORY HEALTH: Memory usage and configuration-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. MEMORY HEALTH STATUSPROMPT ========================
COLUMN memory_component FORMAT A25COLUMN allocated_gb FORMAT 999,999.99COLUMN used_gb FORMAT 999,999.99COLUMN utilization_pct FORMAT 999.99COLUMN health_status FORMAT A10
SELECT 'SGA Total' as memory_component, ROUND(SUM(value) / 1024 / 1024 / 1024, 2) as allocated_gb, ROUND(SUM(value) / 1024 / 1024 / 1024, 2) as used_gb, 100.00 as utilization_pct, 'HEALTHY' as health_statusFROM v$sgaUNION ALLSELECT 'Buffer Cache', ROUND(bytes / 1024 / 1024 / 1024, 2), ROUND((bytes - free_memory) / 1024 / 1024 / 1024, 2), ROUND((bytes - free_memory) / bytes * 100, 2), CASE WHEN ROUND((bytes - free_memory) / bytes * 100, 2) > 95 THEN 'CRITICAL' WHEN ROUND((bytes - free_memory) / bytes * 100, 2) > 90 THEN 'WARNING' ELSE 'HEALTHY' ENDFROM ( SELECT SUM(bytes) as bytes, SUM(CASE WHEN name = 'free memory' THEN bytes ELSE 0 END) as free_memory FROM v$sgastat WHERE pool = 'buffer cache')UNION ALLSELECT 'Shared Pool', ROUND(bytes / 1024 / 1024 / 1024, 2), ROUND((bytes - free_memory) / 1024 / 1024 / 1024, 2), ROUND((bytes - free_memory) / bytes * 100, 2), CASE WHEN ROUND((bytes - free_memory) / bytes * 100, 2) > 90 THEN 'WARNING' ELSE 'HEALTHY' ENDFROM ( SELECT SUM(bytes) as bytes, SUM(CASE WHEN name = 'free memory' THEN bytes ELSE 0 END) as free_memory FROM v$sgastat WHERE pool = 'shared pool')ORDER BY 5,1;
-- ------------------------------------------------------------------------------------- WAIT EVENT HEALTH: Wait event analysis and health assessment-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. WAIT EVENT HEALTH ANALYSISPROMPT ==============================
COLUMN wait_event FORMAT A35COLUMN total_waits FORMAT 999,999,999,999COLUMN time_waited_sec FORMAT 999,999,999COLUMN wait_class FORMAT A20COLUMN health_status FORMAT A10
SELECT event as wait_event, total_waits, ROUND(time_waited_micro / 1000000, 2) as time_waited_sec, wait_class, CASE WHEN ROUND(time_waited_micro / 1000000, 2) > 3600 THEN 'CRITICAL' WHEN ROUND(time_waited_micro / 1000000, 2) > 600 THEN 'WARNING' WHEN ROUND(time_waited_micro / 1000000, 2) > 60 THEN 'MONITOR' ELSE 'HEALTHY' END as health_statusFROM v$system_eventWHERE wait_class NOT IN ('Idle', 'Other') AND total_waits > 0 AND (UPPER('&2') IN ('HIGH', 'CRITICAL') OR (UPPER('&2') = 'MEDIUM' AND ROUND(time_waited_micro / 1000000, 2) > 60) OR (UPPER('&2') = 'LOW' AND ROUND(time_waited_micro / 1000000, 2) > 300))ORDER BY 3 DESCFETCH FIRST 10 ROWS ONLY;
-- ------------------------------------------------------------------------------------- SESSION HEALTH: Session and connection health-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. SESSION HEALTH MONITORINGPROMPT =============================
COLUMN session_metric FORMAT A30COLUMN current_value FORMAT 999,999COLUMN healthy_threshold FORMAT A15COLUMN health_status FORMAT A10COLUMN action_required FORMAT A50
SELECT 'Total User Sessions' as session_metric, COUNT(*) as current_value, '< 500' as healthy_threshold, CASE WHEN COUNT(*) > 1000 THEN 'CRITICAL' WHEN COUNT(*) > 500 THEN 'WARNING' ELSE 'HEALTHY' END as health_status, CASE WHEN COUNT(*) > 1000 THEN 'Investigate session leaks' WHEN COUNT(*) > 500 THEN 'Monitor session growth' ELSE 'Normal session count' END as action_requiredFROM v$sessionWHERE type = 'USER'UNION ALLSELECT 'Active Sessions', SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END), '< 50', CASE WHEN SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END) > 100 THEN 'CRITICAL' WHEN SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END) > 50 THEN 'WARNING' ELSE 'HEALTHY' END, CASE WHEN SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END) > 100 THEN 'Check for runaway queries' WHEN SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END) > 50 THEN 'Monitor active workload' ELSE 'Normal activity level' ENDFROM v$sessionWHERE type = 'USER'UNION ALLSELECT 'Blocking Sessions', COUNT(DISTINCT blocking_session), '0', CASE WHEN COUNT(DISTINCT blocking_session) > 5 THEN 'CRITICAL' WHEN COUNT(DISTINCT blocking_session) > 0 THEN 'WARNING' ELSE 'HEALTHY' END, CASE WHEN COUNT(DISTINCT blocking_session) > 5 THEN 'Immediate investigation required' WHEN COUNT(DISTINCT blocking_session) > 0 THEN 'Identify and resolve blocks' ELSE 'No blocking sessions' ENDFROM v$sessionWHERE blocking_session IS NOT NULLORDER BY 4,1;
-- ------------------------------------------------------------------------------------- BACKUP AND RECOVERY HEALTH: Backup status and recoverability-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. BACKUP AND RECOVERY HEALTHPROMPT ============================
COLUMN backup_metric FORMAT A40COLUMN current_status FORMAT A30COLUMN last_occurrence FORMAT A20COLUMN health_status FORMAT A10COLUMN recommendation FORMAT A60
SELECT 'Last Successful RMAN Backup' as backup_metric, TO_CHAR(MAX(end_time), 'YYYY-MM-DD HH24:MI:SS') as current_status, TO_CHAR(MAX(end_time), 'YYYY-MM-DD HH24:MI') as last_occurrence, CASE WHEN MAX(end_time) > SYSDATE - 1 THEN 'HEALTHY' WHEN MAX(end_time) > SYSDATE - 2 THEN 'WARNING' ELSE 'CRITICAL' END as health_status, CASE WHEN MAX(end_time) > SYSDATE - 1 THEN 'Backup current' WHEN MAX(end_time) > SYSDATE - 2 THEN 'Schedule backup soon' ELSE 'IMMEDIATE BACKUP REQUIRED' END as recommendationFROM v$rman_statusWHERE status = 'COMPLETED' AND operation LIKE '%BACKUP%'
UNION ALL
SELECT 'Flash Recovery Area Usage', ROUND((space_used / space_limit) * 100, 2) || '%', 'Current', CASE WHEN (space_used / space_limit) > 0.95 THEN 'CRITICAL' WHEN (space_used / space_limit) > 0.90 THEN 'WARNING' WHEN (space_used / space_limit) > 0.85 THEN 'MONITOR' ELSE 'HEALTHY' END, CASE WHEN (space_used / space_limit) > 0.95 THEN 'IMMEDIATE CLEANUP REQUIRED' WHEN (space_used / space_limit) > 0.90 THEN 'Urgent: Clean up FRA' WHEN (space_used / space_limit) > 0.85 THEN 'Monitor FRA usage' ELSE 'Adequate free space' ENDFROM ( SELECT SUM(space_used) as space_used, SUM(space_limit) as space_limit FROM v$recovery_file_dest)
ORDER BY 4;
-- ------------------------------------------------------------------------------------- REAL-TIME MONITORING: Continuous health monitoring loop-- -----------------------------------------------------------------------------------
PROMPT PROMPT 8. REAL-TIME HEALTH MONITORINGPROMPT ===============================
DECLARE v_mode VARCHAR2(20) := UPPER('&1'); -- Get parameter v_refresh_interval NUMBER := 30; v_monitor_count NUMBER := 0; v_max_iterations NUMBER := 12; -- Variables for metrics v_active_sessions NUMBER; v_block_count NUMBER; v_tablespace_critical NUMBER := 0; v_wait_events_critical NUMBER := 0; v_high_cpu_sessions NUMBER;BEGIN IF v_mode = 'REALTIME' OR v_mode = 'MONITOR' THEN DBMS_OUTPUT.PUT_LINE('=== REAL-TIME DATABASE HEALTH MONITOR ==='); DBMS_OUTPUT.PUT_LINE('Started: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')); DBMS_OUTPUT.PUT_LINE('Refresh interval: ' || v_refresh_interval || ' seconds'); DBMS_OUTPUT.PUT_LINE('Maximum cycles: ' || v_max_iterations); DBMS_OUTPUT.PUT_LINE('Press Ctrl+C to stop monitoring'); DBMS_OUTPUT.PUT_LINE('=========================================' || CHR(10));
WHILE v_monitor_count < v_max_iterations LOOP v_monitor_count := v_monitor_count + 1; DBMS_OUTPUT.PUT_LINE('[' || TO_CHAR(SYSDATE, 'HH24:MI:SS') || '] Cycle #' || v_monitor_count);
-- 1. Check active sessions SELECT COUNT(*) INTO v_active_sessions FROM v$session WHERE status = 'ACTIVE' AND type = 'USER'; DBMS_OUTPUT.PUT_LINE(' Active Sessions: ' || v_active_sessions || CASE WHEN v_active_sessions > 200 THEN ' (CRITICAL)' WHEN v_active_sessions > 100 THEN ' (WARNING)' ELSE ' (Normal)' END);
-- 2. Check blocking sessions SELECT COUNT(DISTINCT blocking_session) INTO v_block_count FROM v$session WHERE blocking_session IS NOT NULL; IF v_block_count > 0 THEN DBMS_OUTPUT.PUT_LINE(' Blocking Sessions: ' || v_block_count || CASE WHEN v_block_count > 5 THEN ' (CRITICAL)' WHEN v_block_count > 2 THEN ' (WARNING)' ELSE '' END); END IF;
-- 3. Check tablespace usage v_tablespace_critical := 0; FOR space_rec IN ( SELECT tablespace_name, ROUND((used_space / tablespace_size) * 100, 1) as used_pct, CASE WHEN (used_space / tablespace_size) > 0.95 THEN 'CRITICAL' WHEN (used_space / tablespace_size) > 0.90 THEN 'WARNING' ELSE 'OK' END as status FROM dba_tablespace_usage_metrics WHERE (used_space / tablespace_size) > 0.85 ORDER BY used_space / tablespace_size DESC ) LOOP IF space_rec.status IN ('CRITICAL', 'WARNING') THEN DBMS_OUTPUT.PUT_LINE(' Tablespace ' || space_rec.tablespace_name || ': ' || space_rec.used_pct || '% (' || space_rec.status || ')'); IF space_rec.status = 'CRITICAL' THEN v_tablespace_critical := v_tablespace_critical + 1; END IF; END IF; END LOOP;
-- 4. Check for high CPU sessions SELECT COUNT(*) INTO v_high_cpu_sessions FROM ( SELECT s.sid, s.serial#, s.username, ROUND(ss.value/1000000, 2) as cpu_sec FROM v$session s, v$sesstat ss, v$statname sn WHERE s.sid = ss.sid AND ss.statistic# = sn.statistic# AND sn.name = 'CPU used by this session' AND ss.value > 300000000 -- 300 CPU seconds AND s.status = 'ACTIVE' AND ROWNUM <= 5 ); IF v_high_cpu_sessions > 0 THEN DBMS_OUTPUT.PUT_LINE(' High CPU Sessions: ' || v_high_cpu_sessions || ' sessions > 300 CPU seconds'); END IF;
-- Summary for first cycle IF v_monitor_count = 1 THEN DBMS_OUTPUT.PUT_LINE(CHR(10) || 'INITIAL STATUS SUMMARY:'); DBMS_OUTPUT.PUT_LINE(' - Active User Sessions: ' || v_active_sessions); DBMS_OUTPUT.PUT_LINE(' - Blocking Sessions: ' || v_block_count); DBMS_OUTPUT.PUT_LINE(' - Critical Tablespaces: ' || v_tablespace_critical); DBMS_OUTPUT.PUT_LINE(' - High CPU Sessions: ' || v_high_cpu_sessions); END IF;
DBMS_OUTPUT.PUT_LINE('----------------------------------------');
-- Wait for next cycle IF v_monitor_count < v_max_iterations THEN DBMS_LOCK.SLEEP(v_refresh_interval); END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(CHR(10) || '=== MONITORING COMPLETED ==='); DBMS_OUTPUT.PUT_LINE('Completed: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')); DBMS_OUTPUT.PUT_LINE('Total cycles: ' || v_monitor_count);
ELSE DBMS_OUTPUT.PUT_LINE('Real-time monitoring requires "REALTIME" or "MONITOR" parameter.'); DBMS_OUTPUT.PUT_LINE('Current mode: ' || v_mode); DBMS_OUTPUT.PUT_LINE('Usage: @script_name REALTIME'); END IF;EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Monitoring interrupted: ' || SQLERRM);END;/
-- ------------------------------------------------------------------------------------- HEALTH SUMMARY REPORT: Consolidated health assessment-- -----------------------------------------------------------------------------------
PROMPT PROMPT 9. DATABASE HEALTH SUMMARY REPORTPROMPT ==================================
COLUMN health_category FORMAT A25COLUMN health_score FORMAT 999COLUMN critical_issues FORMAT 999COLUMN warning_issues FORMAT 999COLUMN overall_status FORMAT A15
SELECT 'Availability' as health_category, 95 as health_score, 0 as critical_issues, 1 as warning_issues, 'HEALTHY' as overall_statusFROM dualWHERE (SELECT status FROM v$instance) = 'OPEN'UNION ALLSELECT 'Performance', CASE WHEN (SELECT COUNT(*) FROM v$sysmetric WHERE group_id = 2 AND metric_name = 'Database CPU Time Ratio' AND value > 85) > 0 THEN 70 ELSE 90 END, (SELECT COUNT(*) FROM v$sysmetric WHERE group_id = 2 AND ((metric_name = 'Database CPU Time Ratio' AND value > 90) OR (metric_name = 'Average Active Sessions' AND value > 20))), (SELECT COUNT(*) FROM v$sysmetric WHERE group_id = 2 AND ((metric_name = 'Database CPU Time Ratio' AND value BETWEEN 85 AND 90) OR (metric_name = 'Average Active Sessions' AND value BETWEEN 10 AND 20))), CASE WHEN (SELECT COUNT(*) FROM v$sysmetric WHERE group_id = 2 AND metric_name = 'Database CPU Time Ratio' AND value > 90) > 0 THEN 'WARNING' ELSE 'HEALTHY' ENDFROM dualUNION ALLSELECT 'Storage', CASE WHEN (SELECT COUNT(*) FROM dba_tablespace_usage_metrics WHERE (tablespace_size - used_space) / tablespace_size < 0.10) > 0 THEN 60 ELSE 95 END, (SELECT COUNT(*) FROM dba_tablespace_usage_metrics WHERE (tablespace_size - used_space) / tablespace_size < 0.05), (SELECT COUNT(*) FROM dba_tablespace_usage_metrics WHERE (tablespace_size - used_space) / tablespace_size BETWEEN 0.05 AND 0.10), CASE WHEN (SELECT COUNT(*) FROM dba_tablespace_usage_metrics WHERE (tablespace_size - used_space) / tablespace_size < 0.05) > 0 THEN 'CRITICAL' WHEN (SELECT COUNT(*) FROM dba_tablespace_usage_metrics WHERE (tablespace_size - used_space) / tablespace_size < 0.10) > 0 THEN 'WARNING' ELSE 'HEALTHY' ENDFROM dualUNION ALLSELECT 'Backup and Recovery', 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 95 ELSE 50 END, CASE WHEN EXISTS (SELECT 1 FROM v$rman_backup_job_details WHERE status = 'COMPLETED' AND input_type = 'DB FULL' AND end_time > SYSDATE - 7) THEN 0 ELSE 1 END, CASE WHEN EXISTS (SELECT 1 FROM v$rman_backup_job_details WHERE status = 'COMPLETED' AND input_type = 'DB FULL' AND end_time > SYSDATE - 2) THEN 0 ELSE 1 END, 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 'HEALTHY' WHEN EXISTS (SELECT 1 FROM v$rman_backup_job_details WHERE status = 'COMPLETED' AND input_type = 'DB FULL' AND end_time > SYSDATE - 7) THEN 'WARNING' ELSE 'CRITICAL' ENDFROM dualORDER BY 2;
-- ------------------------------------------------------------------------------------- COMPLETION: Health monitoring summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT ====================================PROMPT DATABASE HEALTH MONITORING COMPLETEDPROMPT ====================================PROMPT Monitor Mode: &1PROMPT Alert Level: &2PROMPT Refresh Interval: &3 secondsPROMPT Completion Time: &DATE1 &TIME1PROMPT PROMPT Health Summary:PROMPT - Review CRITICAL issues immediatelyPROMPT - Address WARNING level alerts promptlyPROMPT - Monitor HEALTHY systems regularlyPROMPT - Schedule periodic health assessmentsPROMPT
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment and stop spooling-- -----------------------------------------------------------------------------------
SPOOL OFF
CLEAR COLUMNSSET VERIFY ON
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------