database_alert_monitor.sql
-- ------------------------------------------------------------------------------------- File Name : database_alert_monitor.sql-- Author : Pierre Montbleau-- Description : Real-time Oracle database alert monitoring and notification system-- Purpose : Monitor critical database metrics, detect issues, and generate alerts-- Call Syntax : @F:\DBA\Scripts\database_alert_monitor.sql (monitor_type) (alert_level) (refresh_sec)-- Parameters : monitor_type - Type of monitoring (REALTIME, BATCH, SUMMARY)-- alert_level - Alert threshold level (LOW, MEDIUM, HIGH, CRITICAL)-- refresh_sec - Refresh interval in seconds for realtime mode (default: 30)-- Execution Ex : @F:\DBA\Scripts\AI\database_alert_monitor.sql REALTIME LOW 30-- Last Modified: 2024-01-15-- -----------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------- INITIALIZATION: Set environment and validate parameters-- -----------------------------------------------------------------------------------
CLEAR SCREENCOLUMN "_USER" NEW_VALUE _USER NOPRINTCOLUMN "_CONNECT_IDENTIFIER" NEW_VALUE _CONNECT_IDENTIFIER NOPRINTCOLUMN DATE1 NEW_VALUE DATE1 NOPRINTCOLUMN TIME1 NEW_VALUE TIME1 NOPRINTCOLUMN REPORT_FILE NEW_VALUE REPORT_FILE NOPRINT
-- Generate unique report filenameSELECT 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 TIME1, 'F:\DBA\Scripts\AI\Reports\DB_Alert_Monitor_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '.log' AS REPORT_FILEFROM DUAL;
DEFINE DATE1 = &DATE1DEFINE TIME1 = &TIME1
SET VERIFY OFFSET FEEDBACK ONSET LINESIZE 200SET PAGESIZE 1000SET SERVEROUTPUT ON
-- Start spooling to unique report fileSPOOL &REPORT_FILE
PROMPT Starting Database Alert Monitor...PROMPT ===================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Monitor Type: &1PROMPT Alert Level: &2PROMPT Refresh Seconds: &3PROMPT Timestamp: &&DATE1 &&TIME1PROMPT Report File: &REPORT_FILEPROMPT
-- Validate parametersDECLARE v_monitor_type VARCHAR2(20) := UPPER(NVL('&1', 'SUMMARY')); v_alert_level VARCHAR2(20) := UPPER(NVL('&2', 'MEDIUM')); v_refresh_sec NUMBER := NVL(TO_NUMBER('&3'), 30);BEGIN IF v_monitor_type NOT IN ('REALTIME', 'BATCH', 'SUMMARY') THEN RAISE_APPLICATION_ERROR(-20001, 'Monitor type must be REALTIME, BATCH, or SUMMARY'); 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_sec < 3 OR v_refresh_sec > 300 THEN RAISE_APPLICATION_ERROR(-20003, 'Refresh seconds must be between 3 and 300'); END IF; DBMS_OUTPUT.PUT_LINE('Parameter validation successful');EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE('Error: Invalid numeric parameter for refresh seconds'); RAISE; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- DATABASE AVAILABILITY CHECK: Basic database status and connectivity-- -----------------------------------------------------------------------------------
PROMPT PROMPT 1. DATABASE AVAILABILITY STATUSPROMPT ================================
COLUMN status_check FORMAT A30COLUMN current_status FORMAT A20COLUMN alert_level FORMAT A10
SELECT * FROM ( SELECT 'Database Status' as status_check, status as current_status, CASE status WHEN 'OPEN' THEN 'NORMAL' ELSE 'CRITICAL' END as alert_level FROM v$instance UNION ALL SELECT 'Archive Log Mode', log_mode, CASE log_mode WHEN 'ARCHIVELOG' THEN 'NORMAL' ELSE 'MEDIUM' END FROM v$database UNION ALL SELECT 'Active Sessions', TO_CHAR(COUNT(*)), CASE WHEN COUNT(*) > 500 THEN 'HIGH' WHEN COUNT(*) > 200 THEN 'MEDIUM' ELSE 'NORMAL' END FROM v$session WHERE type = 'USER' UNION ALL SELECT 'Blocking Sessions', TO_CHAR(COUNT(DISTINCT blocking_session)), CASE WHEN COUNT(DISTINCT blocking_session) > 0 THEN 'MEDIUM' ELSE 'NORMAL' END FROM v$session WHERE blocking_session IS NOT NULL)ORDER BY CASE alert_level WHEN 'CRITICAL' THEN 1 WHEN 'HIGH' THEN 2 WHEN 'MEDIUM' THEN 3 ELSE 4 END, status_check;
-- ------------------------------------------------------------------------------------- TABLESPACE ALERTS: Storage capacity warnings and critical alerts-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. TABLESPACE STORAGE ALERTSPROMPT =============================
COLUMN tablespace_name FORMAT A25COLUMN used_pct FORMAT 999.99COLUMN free_gb FORMAT 999,999.99COLUMN alert_level 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, CASE WHEN (tablespace_size - used_space) / tablespace_size < 0.05 THEN 'CRITICAL' WHEN (tablespace_size - used_space) / tablespace_size < 0.10 THEN 'HIGH' WHEN (tablespace_size - used_space) / tablespace_size < 0.20 THEN 'MEDIUM' ELSE 'LOW' END as alert_level, CASE WHEN (tablespace_size - used_space) / tablespace_size < 0.05 THEN 'IMMEDIATE ACTION REQUIRED: Add space' WHEN (tablespace_size - used_space) / tablespace_size < 0.10 THEN 'Urgent: Monitor and plan expansion' WHEN (tablespace_size - used_space) / tablespace_size < 0.20 THEN 'Schedule maintenance: Consider resizing' ELSE 'Adequate free space' END as recommendationFROM dba_tablespace_usage_metricsWHERE (tablespace_size - used_space) / tablespace_size < 0.20 AND UPPER('&2') IN ('MEDIUM', 'HIGH', 'CRITICAL')ORDER BY used_pct DESC;
-- ------------------------------------------------------------------------------------- PERFORMANCE ALERTS: Real-time performance issue detection-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. PERFORMANCE METRIC ALERTSPROMPT =============================
COLUMN metric_name FORMAT A40COLUMN current_value FORMAT 999,999,999.99COLUMN threshold FORMAT 999,999,999.99COLUMN alert_level FORMAT A10COLUMN status FORMAT A15
WITH metric_thresholds AS ( SELECT metric_name, value as current_value, CASE metric_name WHEN 'Average Active Sessions' THEN 10 WHEN 'Database CPU Time Ratio' THEN 20 WHEN 'Database Wait Time Ratio' THEN 30 WHEN 'Physical Reads Per Sec' THEN 1000 WHEN 'Physical Writes Per Sec' THEN 500 ELSE 100 END as threshold, CASE metric_name WHEN 'Average Active Sessions' THEN 20 WHEN 'Database CPU Time Ratio' THEN 30 WHEN 'Database Wait Time Ratio' THEN 40 WHEN 'Physical Reads Per Sec' THEN 2000 WHEN 'Physical Writes Per Sec' THEN 1000 ELSE 200 END as high_threshold FROM v$sysmetric WHERE group_id = 2 AND metric_name IN ( 'Average Active Sessions', 'Database CPU Time Ratio', 'Database Wait Time Ratio', 'Physical Reads Per Sec', 'Physical Writes Per Sec' ))SELECT metric_name, current_value, threshold, CASE WHEN current_value > high_threshold THEN 'HIGH' WHEN current_value > threshold THEN 'MEDIUM' ELSE 'LOW' END as alert_level, CASE WHEN current_value > high_threshold THEN 'WARNING' WHEN current_value > threshold THEN 'MONITOR' ELSE 'NORMAL' END as statusFROM metric_thresholdsWHERE (UPPER('&2') IN ('MEDIUM', 'HIGH', 'CRITICAL') OR (UPPER('&2') = 'LOW' AND current_value > threshold))ORDER BY CASE WHEN current_value > high_threshold THEN 1 WHEN current_value > threshold THEN 2 ELSE 3 END, current_value DESC;
-- ------------------------------------------------------------------------------------- WAIT EVENT ALERTS: Critical wait event monitoring-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. WAIT EVENT ALERTSPROMPT =====================
COLUMN event_name FORMAT A40COLUMN total_waits FORMAT 999,999,999COLUMN time_waited_sec FORMAT 999,999,999COLUMN avg_wait_ms FORMAT 999,999.99COLUMN alert_level FORMAT A10
SELECT event as event_name, total_waits, ROUND(time_waited_micro / 1000000, 2) as time_waited_sec, ROUND((time_waited_micro / total_waits) / 1000, 2) as avg_wait_ms, CASE WHEN ROUND(time_waited_micro / 1000000, 2) > 3600 THEN 'CRITICAL' WHEN ROUND(time_waited_micro / 1000000, 2) > 600 THEN 'HIGH' WHEN ROUND(time_waited_micro / 1000000, 2) > 60 THEN 'MEDIUM' ELSE 'LOW' END as alert_levelFROM 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) > 10))ORDER BY time_waited_micro DESCFETCH FIRST 10 ROWS ONLY;
-- ------------------------------------------------------------------------------------- MEMORY ALERTS: Memory usage and pressure detection-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. MEMORY USAGE ALERTSPROMPT =======================
COLUMN memory_component FORMAT A25COLUMN usage_pct FORMAT 999.99COLUMN current_gb FORMAT 999,999.99COLUMN max_gb FORMAT 999,999.99COLUMN alert_level FORMAT A10
SELECT * FROM ( SELECT 'SGA Usage' as memory_component, ROUND((SELECT SUM(value) FROM v$sga) / (SELECT value FROM v$parameter WHERE name = 'sga_max_size') * 100, 2) as usage_pct, ROUND((SELECT SUM(value) FROM v$sga) / 1024 / 1024 / 1024, 2) as current_gb, ROUND((SELECT value FROM v$parameter WHERE name = 'sga_max_size') / 1024 / 1024 / 1024, 2) as max_gb, CASE WHEN (SELECT SUM(value) FROM v$sga) / (SELECT value FROM v$parameter WHERE name = 'sga_max_size') > 0.95 THEN 'CRITICAL' WHEN (SELECT SUM(value) FROM v$sga) / (SELECT value FROM v$parameter WHERE name = 'sga_max_size') > 0.90 THEN 'HIGH' WHEN (SELECT SUM(value) FROM v$sga) / (SELECT value FROM v$parameter WHERE name = 'sga_max_size') > 0.85 THEN 'MEDIUM' ELSE 'LOW' END as alert_level FROM dual UNION ALL SELECT 'PGA Usage', ROUND((SELECT value FROM v$pgastat WHERE name = 'aggregate PGA target parameter') / (SELECT value FROM v$parameter WHERE name = 'pga_aggregate_limit') * 100, 2), ROUND((SELECT value FROM v$pgastat WHERE name = 'aggregate PGA target parameter') / 1024 / 1024 / 1024, 2), ROUND((SELECT value FROM v$parameter WHERE name = 'pga_aggregate_limit') / 1024 / 1024 / 1024, 2), CASE WHEN (SELECT value FROM v$pgastat WHERE name = 'aggregate PGA target parameter') / (SELECT value FROM v$parameter WHERE name = 'pga_aggregate_limit') > 0.95 THEN 'CRITICAL' WHEN (SELECT value FROM v$pgastat WHERE name = 'aggregate PGA target parameter') / (SELECT value FROM v$parameter WHERE name = 'pga_aggregate_limit') > 0.90 THEN 'HIGH' WHEN (SELECT value FROM v$pgastat WHERE name = 'aggregate PGA target parameter') / (SELECT value FROM v$parameter WHERE name = 'pga_aggregate_limit') > 0.85 THEN 'MEDIUM' ELSE 'LOW' END FROM dual WHERE UPPER('&2') IN ('MEDIUM', 'HIGH', 'CRITICAL'))ORDER BY CASE alert_level WHEN 'CRITICAL' THEN 1 WHEN 'HIGH' THEN 2 WHEN 'MEDIUM' THEN 3 ELSE 4 END;
-- ------------------------------------------------------------------------------------- BACKUP AND RECOVERY ALERTS: Backup status and recoverability-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. BACKUP AND RECOVERY ALERTSPROMPT ==============================
COLUMN backup_check FORMAT A40COLUMN status FORMAT A30COLUMN last_occurrence FORMAT A20COLUMN alert_level FORMAT A10
SELECT * FROM ( -- Last Successful Full Backup SELECT 'Last Successful Full Backup' as backup_check, TO_CHAR(MAX(end_time), 'YYYY-MM-DD HH24:MI:SS') as status, TO_CHAR(MAX(end_time), 'YYYY-MM-DD HH24:MI:SS') as last_occurrence, CASE WHEN MAX(end_time) > SYSDATE - 1 THEN 'LOW' WHEN MAX(end_time) > SYSDATE - 2 THEN 'MEDIUM' WHEN MAX(end_time) > SYSDATE - 7 THEN 'HIGH' ELSE 'CRITICAL' END as alert_level FROM v$rman_backup_job_details WHERE status = 'COMPLETED' AND input_type = 'DB FULL' UNION ALL -- Recovery Area Status (Simplified approach) SELECT 'Recovery Area Status', CASE WHEN name IS NOT NULL THEN 'CONFIGURED' ELSE 'NOT CONFIGURED' END as status, 'Current', CASE WHEN name IS NOT NULL THEN 'LOW' ELSE 'MEDIUM' END FROM v$recovery_file_dest WHERE ROWNUM = 1 UNION ALL -- Archive Log Mode SELECT 'Archive Log Mode', log_mode, 'Current', CASE WHEN log_mode = 'ARCHIVELOG' THEN 'LOW' ELSE 'HIGH' END FROM v$database UNION ALL -- Backup Config Check SELECT 'Backup Configuration', CASE WHEN COUNT(*) > 0 THEN 'BACKUPS CONFIGURED' ELSE 'NO BACKUP CONFIG' END, 'Current', CASE WHEN COUNT(*) > 0 THEN 'LOW' ELSE 'HIGH' END FROM v$rman_configuration WHERE ROWNUM = 1)ORDER BY CASE alert_level WHEN 'CRITICAL' THEN 1 WHEN 'HIGH' THEN 2 WHEN 'MEDIUM' THEN 3 ELSE 4 END;
-- ------------------------------------------------------------------------------------- SESSION AND PROCESS ALERTS: Session-related issues-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. SESSION AND PROCESS ALERTSPROMPT ==============================
COLUMN session_metric FORMAT A30COLUMN current_value FORMAT 999,999COLUMN threshold FORMAT 999,999COLUMN alert_level FORMAT A10
SELECT * FROM ( SELECT 'Long Running Sessions (>1 hour)' as session_metric, COUNT(*) as current_value, 10 as threshold, CASE WHEN COUNT(*) > 20 THEN 'CRITICAL' WHEN COUNT(*) > 10 THEN 'HIGH' WHEN COUNT(*) > 5 THEN 'MEDIUM' ELSE 'LOW' END as alert_level FROM v$session WHERE status = 'ACTIVE' AND last_call_et > 3600 AND username IS NOT NULL UNION ALL SELECT 'Sessions with High CPU', COUNT(*), 5, CASE WHEN COUNT(*) > 10 THEN 'HIGH' WHEN COUNT(*) > 5 THEN 'MEDIUM' ELSE 'LOW' END FROM v$session s WHERE s.status = 'ACTIVE' AND (SELECT ROUND(cpu_time/1000000, 2) FROM v$sqlstats WHERE sql_id = s.sql_id) > 300 UNION ALL SELECT 'Blocked Sessions', COUNT(*), 0, CASE WHEN COUNT(*) > 5 THEN 'HIGH' WHEN COUNT(*) > 0 THEN 'MEDIUM' ELSE 'LOW' END FROM v$session WHERE blocking_session IS NOT NULL)ORDER BY CASE alert_level WHEN 'CRITICAL' THEN 1 WHEN 'HIGH' THEN 2 WHEN 'MEDIUM' THEN 3 ELSE 4 END, current_value DESC;
-- ------------------------------------------------------------------------------------- REAL-TIME MONITORING LOOP: Continuous monitoring for realtime mode-- -----------------------------------------------------------------------------------
PROMPT PROMPT 8. REAL-TIME MONITORING ACTIVITYPROMPT =================================
BEGIN IF UPPER('&1') = 'REALTIME' THEN DECLARE v_refresh_sec NUMBER := &3; v_monitor_count NUMBER := 0; v_max_iterations NUMBER := 10; -- Safety limit v_active_sessions NUMBER; v_alert_message VARCHAR2(4000); BEGIN DBMS_OUTPUT.PUT_LINE('Starting real-time monitoring (refresh: ' || v_refresh_sec || 's)...'); DBMS_OUTPUT.PUT_LINE('Press Ctrl+C to stop monitoring'); DBMS_OUTPUT.PUT_LINE('================================'); WHILE v_monitor_count < v_max_iterations LOOP v_monitor_count := v_monitor_count + 1; -- Check for critical tablespace alerts FOR ts_rec IN ( SELECT 'CRITICAL: Tablespace >95% full: ' || tablespace_name as alert_msg FROM dba_tablespace_usage_metrics WHERE (tablespace_size - used_space) / tablespace_size < 0.05 ) LOOP DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE, 'HH24:MI:SS') || ' - ' || ts_rec.alert_msg); END LOOP; -- Check for backup alerts BEGIN SELECT COUNT(*) INTO v_active_sessions FROM v$rman_backup_job_details WHERE status = 'COMPLETED' AND input_type = 'DB FULL' AND end_time > SYSDATE - 2; IF v_active_sessions = 0 THEN DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE, 'HH24:MI:SS') || ' - CRITICAL: No recent backup found'); END IF; EXCEPTION WHEN OTHERS THEN NULL; -- Skip if there's an error END; -- Check archive log mode FOR db_rec IN ( SELECT 'HIGH: Database not in ARCHIVELOG mode' as alert_msg FROM v$database WHERE log_mode != 'ARCHIVELOG' ) LOOP DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE, 'HH24:MI:SS') || ' - ' || db_rec.alert_msg); END LOOP; -- Get active sessions count SELECT COUNT(*) INTO v_active_sessions FROM v$session WHERE status = 'ACTIVE' AND type = 'USER'; DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE, 'HH24:MI:SS') || ' - Monitoring active sessions: ' || v_active_sessions); -- Wait for refresh interval DBMS_LOCK.SLEEP(v_refresh_sec); END LOOP; DBMS_OUTPUT.PUT_LINE('Real-time monitoring completed after ' || v_max_iterations || ' iterations'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Real-time monitoring interrupted: ' || SQLERRM); END; ELSE DBMS_OUTPUT.PUT_LINE('Real-time monitoring not activated (monitor_type: ' || UPPER('&1') || ')'); END IF;END;/
-- ------------------------------------------------------------------------------------- ALERT SUMMARY AND RECOMMENDATIONS: Consolidated alert report-- -----------------------------------------------------------------------------------
PROMPT PROMPT 9. ALERT SUMMARY AND ACTION PLANPROMPT =================================
COLUMN alert_priority FORMAT A10COLUMN alert_category FORMAT A20COLUMN alert_description FORMAT A60COLUMN immediate_action FORMAT A80
SELECT * FROM ( SELECT 'CRITICAL' as alert_priority, 'Storage' as alert_category, 'Tablespace critically low on space: ' || tablespace_name as alert_description, 'IMMEDIATE: Add datafiles or resize immediately' as immediate_action FROM dba_tablespace_usage_metrics WHERE (tablespace_size - used_space) / tablespace_size < 0.05 UNION ALL SELECT 'CRITICAL', 'Backup', 'No successful full backup in over 7 days', 'IMMEDIATE: Schedule and run full database backup' FROM dual WHERE NOT EXISTS ( SELECT 1 FROM v$rman_backup_job_details WHERE status = 'COMPLETED' AND input_type = 'DB FULL' AND end_time > SYSDATE - 7 ) UNION ALL SELECT 'HIGH', 'Performance', 'High wait events detected (>1 hour total wait)', 'URGENT: Investigate top wait events and tune' FROM v$system_event WHERE wait_class NOT IN ('Idle', 'Other') AND ROUND(time_waited_micro / 1000000, 2) > 3600 AND ROWNUM = 1 UNION ALL SELECT 'MEDIUM', 'Memory', 'SGA usage above 90% capacity', 'SCHEDULE: Monitor and plan for memory increase' FROM dual WHERE (SELECT SUM(value) FROM v$sga) / (SELECT value FROM v$parameter WHERE name = 'sga_max_size') > 0.90 UNION ALL SELECT 'LOW', 'Session', 'Multiple long-running sessions detected', 'MONITOR: Review session activity and optimize queries' FROM dual WHERE (SELECT COUNT(*) FROM v$session WHERE status = 'ACTIVE' AND last_call_et > 3600) > 5)ORDER BY CASE alert_priority WHEN 'CRITICAL' THEN 1 WHEN 'HIGH' THEN 2 WHEN 'MEDIUM' THEN 3 WHEN 'LOW' THEN 4 END, alert_category;
-- ------------------------------------------------------------------------------------- COMPLETION: Alert monitoring summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT ===================================PROMPT DATABASE ALERT MONITORING COMPLETEDPROMPT ===================================PROMPT Monitor Type: &1PROMPT Alert Level: &2PROMPT Refresh Seconds: &3PROMPT Completion Time: &&DATE1 &&TIME1PROMPT Report File: &REPORT_FILEPROMPT PROMPT Monitoring Summary:PROMPT - Review CRITICAL and HIGH priority alerts immediatelyPROMPT - Address MEDIUM priority alerts in next maintenance windowPROMPT - Monitor LOW priority alerts for trendsPROMPT - Consider setting up automated alert notificationsPROMPT
PROMPT *******************************************************PROMPT * Report successfully saved to:PROMPT * &REPORT_FILEPROMPT *******************************************************
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment-- -----------------------------------------------------------------------------------
CLEAR COLUMNSSET VERIFY ON
-- Stop spooling and show final messageSPOOL OFF
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------- INITIALIZATION: Set environment and validate parameters-- -----------------------------------------------------------------------------------
CLEAR SCREENCOLUMN "_USER" NEW_VALUE _USER NOPRINTCOLUMN "_CONNECT_IDENTIFIER" NEW_VALUE _CONNECT_IDENTIFIER NOPRINTCOLUMN DATE1 NEW_VALUE DATE1 NOPRINTCOLUMN TIME1 NEW_VALUE TIME1 NOPRINTCOLUMN REPORT_FILE NEW_VALUE REPORT_FILE NOPRINT
-- Generate unique report filenameSELECT 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 TIME1, 'F:\DBA\Scripts\AI\Reports\DB_Alert_Monitor_' || SYS_CONTEXT('USERENV', 'DB_NAME') || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '.log' AS REPORT_FILEFROM DUAL;
DEFINE DATE1 = &DATE1DEFINE TIME1 = &TIME1
SET VERIFY OFFSET FEEDBACK ONSET LINESIZE 200SET PAGESIZE 1000SET SERVEROUTPUT ON
-- Start spooling to unique report fileSPOOL &REPORT_FILE
PROMPT Starting Database Alert Monitor...PROMPT ===================================PROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Monitor Type: &1PROMPT Alert Level: &2PROMPT Refresh Seconds: &3PROMPT Timestamp: &&DATE1 &&TIME1PROMPT Report File: &REPORT_FILEPROMPT
-- Validate parametersDECLARE v_monitor_type VARCHAR2(20) := UPPER(NVL('&1', 'SUMMARY')); v_alert_level VARCHAR2(20) := UPPER(NVL('&2', 'MEDIUM')); v_refresh_sec NUMBER := NVL(TO_NUMBER('&3'), 30);BEGIN IF v_monitor_type NOT IN ('REALTIME', 'BATCH', 'SUMMARY') THEN RAISE_APPLICATION_ERROR(-20001, 'Monitor type must be REALTIME, BATCH, or SUMMARY'); 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_sec < 3 OR v_refresh_sec > 300 THEN RAISE_APPLICATION_ERROR(-20003, 'Refresh seconds must be between 3 and 300'); END IF; DBMS_OUTPUT.PUT_LINE('Parameter validation successful');EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE('Error: Invalid numeric parameter for refresh seconds'); RAISE; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- DATABASE AVAILABILITY CHECK: Basic database status and connectivity-- -----------------------------------------------------------------------------------
PROMPT PROMPT 1. DATABASE AVAILABILITY STATUSPROMPT ================================
COLUMN status_check FORMAT A30COLUMN current_status FORMAT A20COLUMN alert_level FORMAT A10
SELECT * FROM ( SELECT 'Database Status' as status_check, status as current_status, CASE status WHEN 'OPEN' THEN 'NORMAL' ELSE 'CRITICAL' END as alert_level FROM v$instance UNION ALL SELECT 'Archive Log Mode', log_mode, CASE log_mode WHEN 'ARCHIVELOG' THEN 'NORMAL' ELSE 'MEDIUM' END FROM v$database UNION ALL SELECT 'Active Sessions', TO_CHAR(COUNT(*)), CASE WHEN COUNT(*) > 500 THEN 'HIGH' WHEN COUNT(*) > 200 THEN 'MEDIUM' ELSE 'NORMAL' END FROM v$session WHERE type = 'USER' UNION ALL SELECT 'Blocking Sessions', TO_CHAR(COUNT(DISTINCT blocking_session)), CASE WHEN COUNT(DISTINCT blocking_session) > 0 THEN 'MEDIUM' ELSE 'NORMAL' END FROM v$session WHERE blocking_session IS NOT NULL)ORDER BY CASE alert_level WHEN 'CRITICAL' THEN 1 WHEN 'HIGH' THEN 2 WHEN 'MEDIUM' THEN 3 ELSE 4 END, status_check;
-- ------------------------------------------------------------------------------------- TABLESPACE ALERTS: Storage capacity warnings and critical alerts-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. TABLESPACE STORAGE ALERTSPROMPT =============================
COLUMN tablespace_name FORMAT A25COLUMN used_pct FORMAT 999.99COLUMN free_gb FORMAT 999,999.99COLUMN alert_level 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, CASE WHEN (tablespace_size - used_space) / tablespace_size < 0.05 THEN 'CRITICAL' WHEN (tablespace_size - used_space) / tablespace_size < 0.10 THEN 'HIGH' WHEN (tablespace_size - used_space) / tablespace_size < 0.20 THEN 'MEDIUM' ELSE 'LOW' END as alert_level, CASE WHEN (tablespace_size - used_space) / tablespace_size < 0.05 THEN 'IMMEDIATE ACTION REQUIRED: Add space' WHEN (tablespace_size - used_space) / tablespace_size < 0.10 THEN 'Urgent: Monitor and plan expansion' WHEN (tablespace_size - used_space) / tablespace_size < 0.20 THEN 'Schedule maintenance: Consider resizing' ELSE 'Adequate free space' END as recommendationFROM dba_tablespace_usage_metricsWHERE (tablespace_size - used_space) / tablespace_size < 0.20 AND UPPER('&2') IN ('MEDIUM', 'HIGH', 'CRITICAL')ORDER BY used_pct DESC;
-- ------------------------------------------------------------------------------------- PERFORMANCE ALERTS: Real-time performance issue detection-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. PERFORMANCE METRIC ALERTSPROMPT =============================
COLUMN metric_name FORMAT A40COLUMN current_value FORMAT 999,999,999.99COLUMN threshold FORMAT 999,999,999.99COLUMN alert_level FORMAT A10COLUMN status FORMAT A15
WITH metric_thresholds AS ( SELECT metric_name, value as current_value, CASE metric_name WHEN 'Average Active Sessions' THEN 10 WHEN 'Database CPU Time Ratio' THEN 20 WHEN 'Database Wait Time Ratio' THEN 30 WHEN 'Physical Reads Per Sec' THEN 1000 WHEN 'Physical Writes Per Sec' THEN 500 ELSE 100 END as threshold, CASE metric_name WHEN 'Average Active Sessions' THEN 20 WHEN 'Database CPU Time Ratio' THEN 30 WHEN 'Database Wait Time Ratio' THEN 40 WHEN 'Physical Reads Per Sec' THEN 2000 WHEN 'Physical Writes Per Sec' THEN 1000 ELSE 200 END as high_threshold FROM v$sysmetric WHERE group_id = 2 AND metric_name IN ( 'Average Active Sessions', 'Database CPU Time Ratio', 'Database Wait Time Ratio', 'Physical Reads Per Sec', 'Physical Writes Per Sec' ))SELECT metric_name, current_value, threshold, CASE WHEN current_value > high_threshold THEN 'HIGH' WHEN current_value > threshold THEN 'MEDIUM' ELSE 'LOW' END as alert_level, CASE WHEN current_value > high_threshold THEN 'WARNING' WHEN current_value > threshold THEN 'MONITOR' ELSE 'NORMAL' END as statusFROM metric_thresholdsWHERE (UPPER('&2') IN ('MEDIUM', 'HIGH', 'CRITICAL') OR (UPPER('&2') = 'LOW' AND current_value > threshold))ORDER BY CASE WHEN current_value > high_threshold THEN 1 WHEN current_value > threshold THEN 2 ELSE 3 END, current_value DESC;
-- ------------------------------------------------------------------------------------- WAIT EVENT ALERTS: Critical wait event monitoring-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. WAIT EVENT ALERTSPROMPT =====================
COLUMN event_name FORMAT A40COLUMN total_waits FORMAT 999,999,999COLUMN time_waited_sec FORMAT 999,999,999COLUMN avg_wait_ms FORMAT 999,999.99COLUMN alert_level FORMAT A10
SELECT event as event_name, total_waits, ROUND(time_waited_micro / 1000000, 2) as time_waited_sec, ROUND((time_waited_micro / total_waits) / 1000, 2) as avg_wait_ms, CASE WHEN ROUND(time_waited_micro / 1000000, 2) > 3600 THEN 'CRITICAL' WHEN ROUND(time_waited_micro / 1000000, 2) > 600 THEN 'HIGH' WHEN ROUND(time_waited_micro / 1000000, 2) > 60 THEN 'MEDIUM' ELSE 'LOW' END as alert_levelFROM 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) > 10))ORDER BY time_waited_micro DESCFETCH FIRST 10 ROWS ONLY;
-- ------------------------------------------------------------------------------------- MEMORY ALERTS: Memory usage and pressure detection-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. MEMORY USAGE ALERTSPROMPT =======================
COLUMN memory_component FORMAT A25COLUMN usage_pct FORMAT 999.99COLUMN current_gb FORMAT 999,999.99COLUMN max_gb FORMAT 999,999.99COLUMN alert_level FORMAT A10
SELECT * FROM ( SELECT 'SGA Usage' as memory_component, ROUND((SELECT SUM(value) FROM v$sga) / (SELECT value FROM v$parameter WHERE name = 'sga_max_size') * 100, 2) as usage_pct, ROUND((SELECT SUM(value) FROM v$sga) / 1024 / 1024 / 1024, 2) as current_gb, ROUND((SELECT value FROM v$parameter WHERE name = 'sga_max_size') / 1024 / 1024 / 1024, 2) as max_gb, CASE WHEN (SELECT SUM(value) FROM v$sga) / (SELECT value FROM v$parameter WHERE name = 'sga_max_size') > 0.95 THEN 'CRITICAL' WHEN (SELECT SUM(value) FROM v$sga) / (SELECT value FROM v$parameter WHERE name = 'sga_max_size') > 0.90 THEN 'HIGH' WHEN (SELECT SUM(value) FROM v$sga) / (SELECT value FROM v$parameter WHERE name = 'sga_max_size') > 0.85 THEN 'MEDIUM' ELSE 'LOW' END as alert_level FROM dual UNION ALL SELECT 'PGA Usage', ROUND((SELECT value FROM v$pgastat WHERE name = 'aggregate PGA target parameter') / (SELECT value FROM v$parameter WHERE name = 'pga_aggregate_limit') * 100, 2), ROUND((SELECT value FROM v$pgastat WHERE name = 'aggregate PGA target parameter') / 1024 / 1024 / 1024, 2), ROUND((SELECT value FROM v$parameter WHERE name = 'pga_aggregate_limit') / 1024 / 1024 / 1024, 2), CASE WHEN (SELECT value FROM v$pgastat WHERE name = 'aggregate PGA target parameter') / (SELECT value FROM v$parameter WHERE name = 'pga_aggregate_limit') > 0.95 THEN 'CRITICAL' WHEN (SELECT value FROM v$pgastat WHERE name = 'aggregate PGA target parameter') / (SELECT value FROM v$parameter WHERE name = 'pga_aggregate_limit') > 0.90 THEN 'HIGH' WHEN (SELECT value FROM v$pgastat WHERE name = 'aggregate PGA target parameter') / (SELECT value FROM v$parameter WHERE name = 'pga_aggregate_limit') > 0.85 THEN 'MEDIUM' ELSE 'LOW' END FROM dual WHERE UPPER('&2') IN ('MEDIUM', 'HIGH', 'CRITICAL'))ORDER BY CASE alert_level WHEN 'CRITICAL' THEN 1 WHEN 'HIGH' THEN 2 WHEN 'MEDIUM' THEN 3 ELSE 4 END;
-- ------------------------------------------------------------------------------------- BACKUP AND RECOVERY ALERTS: Backup status and recoverability-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. BACKUP AND RECOVERY ALERTSPROMPT ==============================
COLUMN backup_check FORMAT A40COLUMN status FORMAT A30COLUMN last_occurrence FORMAT A20COLUMN alert_level FORMAT A10
SELECT * FROM ( -- Last Successful Full Backup SELECT 'Last Successful Full Backup' as backup_check, TO_CHAR(MAX(end_time), 'YYYY-MM-DD HH24:MI:SS') as status, TO_CHAR(MAX(end_time), 'YYYY-MM-DD HH24:MI:SS') as last_occurrence, CASE WHEN MAX(end_time) > SYSDATE - 1 THEN 'LOW' WHEN MAX(end_time) > SYSDATE - 2 THEN 'MEDIUM' WHEN MAX(end_time) > SYSDATE - 7 THEN 'HIGH' ELSE 'CRITICAL' END as alert_level FROM v$rman_backup_job_details WHERE status = 'COMPLETED' AND input_type = 'DB FULL' UNION ALL -- Recovery Area Status (Simplified approach) SELECT 'Recovery Area Status', CASE WHEN name IS NOT NULL THEN 'CONFIGURED' ELSE 'NOT CONFIGURED' END as status, 'Current', CASE WHEN name IS NOT NULL THEN 'LOW' ELSE 'MEDIUM' END FROM v$recovery_file_dest WHERE ROWNUM = 1 UNION ALL -- Archive Log Mode SELECT 'Archive Log Mode', log_mode, 'Current', CASE WHEN log_mode = 'ARCHIVELOG' THEN 'LOW' ELSE 'HIGH' END FROM v$database UNION ALL -- Backup Config Check SELECT 'Backup Configuration', CASE WHEN COUNT(*) > 0 THEN 'BACKUPS CONFIGURED' ELSE 'NO BACKUP CONFIG' END, 'Current', CASE WHEN COUNT(*) > 0 THEN 'LOW' ELSE 'HIGH' END FROM v$rman_configuration WHERE ROWNUM = 1)ORDER BY CASE alert_level WHEN 'CRITICAL' THEN 1 WHEN 'HIGH' THEN 2 WHEN 'MEDIUM' THEN 3 ELSE 4 END;
-- ------------------------------------------------------------------------------------- SESSION AND PROCESS ALERTS: Session-related issues-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. SESSION AND PROCESS ALERTSPROMPT ==============================
COLUMN session_metric FORMAT A30COLUMN current_value FORMAT 999,999COLUMN threshold FORMAT 999,999COLUMN alert_level FORMAT A10
SELECT * FROM ( SELECT 'Long Running Sessions (>1 hour)' as session_metric, COUNT(*) as current_value, 10 as threshold, CASE WHEN COUNT(*) > 20 THEN 'CRITICAL' WHEN COUNT(*) > 10 THEN 'HIGH' WHEN COUNT(*) > 5 THEN 'MEDIUM' ELSE 'LOW' END as alert_level FROM v$session WHERE status = 'ACTIVE' AND last_call_et > 3600 AND username IS NOT NULL UNION ALL SELECT 'Sessions with High CPU', COUNT(*), 5, CASE WHEN COUNT(*) > 10 THEN 'HIGH' WHEN COUNT(*) > 5 THEN 'MEDIUM' ELSE 'LOW' END FROM v$session s WHERE s.status = 'ACTIVE' AND (SELECT ROUND(cpu_time/1000000, 2) FROM v$sqlstats WHERE sql_id = s.sql_id) > 300 UNION ALL SELECT 'Blocked Sessions', COUNT(*), 0, CASE WHEN COUNT(*) > 5 THEN 'HIGH' WHEN COUNT(*) > 0 THEN 'MEDIUM' ELSE 'LOW' END FROM v$session WHERE blocking_session IS NOT NULL)ORDER BY CASE alert_level WHEN 'CRITICAL' THEN 1 WHEN 'HIGH' THEN 2 WHEN 'MEDIUM' THEN 3 ELSE 4 END, current_value DESC;
-- ------------------------------------------------------------------------------------- REAL-TIME MONITORING LOOP: Continuous monitoring for realtime mode-- -----------------------------------------------------------------------------------
PROMPT PROMPT 8. REAL-TIME MONITORING ACTIVITYPROMPT =================================
BEGIN IF UPPER('&1') = 'REALTIME' THEN DECLARE v_refresh_sec NUMBER := &3; v_monitor_count NUMBER := 0; v_max_iterations NUMBER := 10; -- Safety limit v_active_sessions NUMBER; v_alert_message VARCHAR2(4000); BEGIN DBMS_OUTPUT.PUT_LINE('Starting real-time monitoring (refresh: ' || v_refresh_sec || 's)...'); DBMS_OUTPUT.PUT_LINE('Press Ctrl+C to stop monitoring'); DBMS_OUTPUT.PUT_LINE('================================'); WHILE v_monitor_count < v_max_iterations LOOP v_monitor_count := v_monitor_count + 1; -- Check for critical tablespace alerts FOR ts_rec IN ( SELECT 'CRITICAL: Tablespace >95% full: ' || tablespace_name as alert_msg FROM dba_tablespace_usage_metrics WHERE (tablespace_size - used_space) / tablespace_size < 0.05 ) LOOP DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE, 'HH24:MI:SS') || ' - ' || ts_rec.alert_msg); END LOOP; -- Check for backup alerts BEGIN SELECT COUNT(*) INTO v_active_sessions FROM v$rman_backup_job_details WHERE status = 'COMPLETED' AND input_type = 'DB FULL' AND end_time > SYSDATE - 2; IF v_active_sessions = 0 THEN DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE, 'HH24:MI:SS') || ' - CRITICAL: No recent backup found'); END IF; EXCEPTION WHEN OTHERS THEN NULL; -- Skip if there's an error END; -- Check archive log mode FOR db_rec IN ( SELECT 'HIGH: Database not in ARCHIVELOG mode' as alert_msg FROM v$database WHERE log_mode != 'ARCHIVELOG' ) LOOP DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE, 'HH24:MI:SS') || ' - ' || db_rec.alert_msg); END LOOP; -- Get active sessions count SELECT COUNT(*) INTO v_active_sessions FROM v$session WHERE status = 'ACTIVE' AND type = 'USER'; DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE, 'HH24:MI:SS') || ' - Monitoring active sessions: ' || v_active_sessions); -- Wait for refresh interval DBMS_LOCK.SLEEP(v_refresh_sec); END LOOP; DBMS_OUTPUT.PUT_LINE('Real-time monitoring completed after ' || v_max_iterations || ' iterations'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Real-time monitoring interrupted: ' || SQLERRM); END; ELSE DBMS_OUTPUT.PUT_LINE('Real-time monitoring not activated (monitor_type: ' || UPPER('&1') || ')'); END IF;END;/
-- ------------------------------------------------------------------------------------- ALERT SUMMARY AND RECOMMENDATIONS: Consolidated alert report-- -----------------------------------------------------------------------------------
PROMPT PROMPT 9. ALERT SUMMARY AND ACTION PLANPROMPT =================================
COLUMN alert_priority FORMAT A10COLUMN alert_category FORMAT A20COLUMN alert_description FORMAT A60COLUMN immediate_action FORMAT A80
SELECT * FROM ( SELECT 'CRITICAL' as alert_priority, 'Storage' as alert_category, 'Tablespace critically low on space: ' || tablespace_name as alert_description, 'IMMEDIATE: Add datafiles or resize immediately' as immediate_action FROM dba_tablespace_usage_metrics WHERE (tablespace_size - used_space) / tablespace_size < 0.05 UNION ALL SELECT 'CRITICAL', 'Backup', 'No successful full backup in over 7 days', 'IMMEDIATE: Schedule and run full database backup' FROM dual WHERE NOT EXISTS ( SELECT 1 FROM v$rman_backup_job_details WHERE status = 'COMPLETED' AND input_type = 'DB FULL' AND end_time > SYSDATE - 7 ) UNION ALL SELECT 'HIGH', 'Performance', 'High wait events detected (>1 hour total wait)', 'URGENT: Investigate top wait events and tune' FROM v$system_event WHERE wait_class NOT IN ('Idle', 'Other') AND ROUND(time_waited_micro / 1000000, 2) > 3600 AND ROWNUM = 1 UNION ALL SELECT 'MEDIUM', 'Memory', 'SGA usage above 90% capacity', 'SCHEDULE: Monitor and plan for memory increase' FROM dual WHERE (SELECT SUM(value) FROM v$sga) / (SELECT value FROM v$parameter WHERE name = 'sga_max_size') > 0.90 UNION ALL SELECT 'LOW', 'Session', 'Multiple long-running sessions detected', 'MONITOR: Review session activity and optimize queries' FROM dual WHERE (SELECT COUNT(*) FROM v$session WHERE status = 'ACTIVE' AND last_call_et > 3600) > 5)ORDER BY CASE alert_priority WHEN 'CRITICAL' THEN 1 WHEN 'HIGH' THEN 2 WHEN 'MEDIUM' THEN 3 WHEN 'LOW' THEN 4 END, alert_category;
-- ------------------------------------------------------------------------------------- COMPLETION: Alert monitoring summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT ===================================PROMPT DATABASE ALERT MONITORING COMPLETEDPROMPT ===================================PROMPT Monitor Type: &1PROMPT Alert Level: &2PROMPT Refresh Seconds: &3PROMPT Completion Time: &&DATE1 &&TIME1PROMPT Report File: &REPORT_FILEPROMPT PROMPT Monitoring Summary:PROMPT - Review CRITICAL and HIGH priority alerts immediatelyPROMPT - Address MEDIUM priority alerts in next maintenance windowPROMPT - Monitor LOW priority alerts for trendsPROMPT - Consider setting up automated alert notificationsPROMPT
PROMPT *******************************************************PROMPT * Report successfully saved to:PROMPT * &REPORT_FILEPROMPT *******************************************************
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment-- -----------------------------------------------------------------------------------
CLEAR COLUMNSSET VERIFY ON
-- Stop spooling and show final messageSPOOL OFF
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------