Alert and Notification Scripts

Alert and Notification Scripts

Comprehensive scripts for setting up automated alerts and notifications in Oracle databases.

Database Alert Framework

Create Alert Infrastructure

-- Create alert configuration table
CREATE TABLE alert_config (
    alert_id          NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    alert_name        VARCHAR2(100) NOT NULL UNIQUE,
    alert_type        VARCHAR2(50) NOT NULL,
    check_sql         CLOB NOT NULL,
    threshold_value   NUMBER,
    threshold_type    VARCHAR2(20) CHECK (threshold_type IN ('ABOVE', 'BELOW', 'EQUALS', 'NOT_EQUALS')),
    severity          VARCHAR2(20) CHECK (severity IN ('CRITICAL', 'HIGH', 'MEDIUM', 'LOW')),
    check_interval    NUMBER DEFAULT 5, -- minutes
    enabled           CHAR(1) DEFAULT 'Y' CHECK (enabled IN ('Y', 'N')),
    email_list        VARCHAR2(4000),
    last_check        TIMESTAMP,
    last_alert        TIMESTAMP,
    alert_count       NUMBER DEFAULT 0,
    created_date      TIMESTAMP DEFAULT SYSTIMESTAMP,
    modified_date     TIMESTAMP DEFAULT SYSTIMESTAMP
);

-- Create alert history table
CREATE TABLE alert_history (
    history_id        NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    alert_id          NUMBER NOT NULL,
    alert_timestamp   TIMESTAMP DEFAULT SYSTIMESTAMP,
    alert_value       NUMBER,
    alert_message     VARCHAR2(4000),
    notification_sent CHAR(1) DEFAULT 'N',
    CONSTRAINT fk_alert_history FOREIGN KEY (alert_id) REFERENCES alert_config(alert_id)
);

-- Create index for performance
CREATE INDEX idx_alert_history_timestamp ON alert_history(alert_timestamp);
CREATE INDEX idx_alert_history_alert_id ON alert_history(alert_id, alert_timestamp);

Alert Configuration Package

CREATE OR REPLACE PACKAGE pkg_alert_manager AS

    -- Add new alert
    PROCEDURE add_alert(
        p_alert_name      IN VARCHAR2,
        p_alert_type      IN VARCHAR2,
        p_check_sql       IN CLOB,
        p_threshold_value IN NUMBER,
        p_threshold_type  IN VARCHAR2,
        p_severity        IN VARCHAR2,
        p_check_interval  IN NUMBER DEFAULT 5,
        p_email_list      IN VARCHAR2 DEFAULT NULL
    );

    -- Check and trigger alerts
    PROCEDURE check_alerts;

    -- Send alert notification
    PROCEDURE send_alert_notification(
        p_alert_id    IN NUMBER,
        p_alert_value IN NUMBER,
        p_message     IN VARCHAR2
    );

    -- Get alert summary
    FUNCTION get_alert_summary RETURN SYS_REFCURSOR;

END pkg_alert_manager;
/

CREATE OR REPLACE PACKAGE BODY pkg_alert_manager AS

    PROCEDURE add_alert(
        p_alert_name      IN VARCHAR2,
        p_alert_type      IN VARCHAR2,
        p_check_sql       IN CLOB,
        p_threshold_value IN NUMBER,
        p_threshold_type  IN VARCHAR2,
        p_severity        IN VARCHAR2,
        p_check_interval  IN NUMBER DEFAULT 5,
        p_email_list      IN VARCHAR2 DEFAULT NULL
    ) IS
    BEGIN
        INSERT INTO alert_config (
            alert_name, alert_type, check_sql, threshold_value,
            threshold_type, severity, check_interval, email_list
        ) VALUES (
            p_alert_name, p_alert_type, p_check_sql, p_threshold_value,
            p_threshold_type, p_severity, p_check_interval, p_email_list
        );

        COMMIT;
        DBMS_OUTPUT.PUT_LINE('Alert "' || p_alert_name || '" added successfully');
    EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
            RAISE_APPLICATION_ERROR(-20001, 'Alert with this name already exists');
    END add_alert;

    PROCEDURE check_alerts IS
        v_current_value NUMBER;
        v_alert_triggered BOOLEAN;
        v_message VARCHAR2(4000);
        v_sql_error VARCHAR2(4000);
    BEGIN
        FOR alert IN (
            SELECT * FROM alert_config
            WHERE enabled = 'Y'
                AND (last_check IS NULL OR
                     last_check < SYSTIMESTAMP - INTERVAL '1' MINUTE * check_interval)
        ) LOOP
            BEGIN
                -- Execute check SQL
                EXECUTE IMMEDIATE alert.check_sql INTO v_current_value;

                -- Check threshold
                v_alert_triggered := FALSE;
                CASE alert.threshold_type
                    WHEN 'ABOVE' THEN
                        v_alert_triggered := (v_current_value > alert.threshold_value);
                    WHEN 'BELOW' THEN
                        v_alert_triggered := (v_current_value < alert.threshold_value);
                    WHEN 'EQUALS' THEN
                        v_alert_triggered := (v_current_value = alert.threshold_value);
                    WHEN 'NOT_EQUALS' THEN
                        v_alert_triggered := (v_current_value != alert.threshold_value);
                END CASE;

                -- Update last check time
                UPDATE alert_config
                SET last_check = SYSTIMESTAMP
                WHERE alert_id = alert.alert_id;

                IF v_alert_triggered THEN
                    v_message := 'Alert: ' || alert.alert_name ||
                                ' - Current value: ' || v_current_value ||
                                ' ' || alert.threshold_type || ' threshold: ' || alert.threshold_value;

                    -- Log alert
                    INSERT INTO alert_history (
                        alert_id, alert_value, alert_message
                    ) VALUES (
                        alert.alert_id, v_current_value, v_message
                    );

                    -- Update alert count and last alert time
                    UPDATE alert_config
                    SET last_alert = SYSTIMESTAMP,
                        alert_count = alert_count + 1
                    WHERE alert_id = alert.alert_id;

                    -- Send notification
                    send_alert_notification(alert.alert_id, v_current_value, v_message);

                    COMMIT;
                END IF;

            EXCEPTION
                WHEN OTHERS THEN
                    v_sql_error := SQLERRM;
                    -- Log error
                    INSERT INTO alert_history (
                        alert_id, alert_message
                    ) VALUES (
                        alert.alert_id, 'Error checking alert: ' || v_sql_error
                    );
                    COMMIT;
            END;
        END LOOP;
    END check_alerts;

    PROCEDURE send_alert_notification(
        p_alert_id    IN NUMBER,
        p_alert_value IN NUMBER,
        p_message     IN VARCHAR2
    ) IS
        v_email_list VARCHAR2(4000);
        v_subject    VARCHAR2(200);
        v_body       VARCHAR2(32000);
        v_alert_name VARCHAR2(100);
        v_severity   VARCHAR2(20);
    BEGIN
        -- Get alert details
        SELECT alert_name, email_list, severity
        INTO v_alert_name, v_email_list, v_severity
        FROM alert_config
        WHERE alert_id = p_alert_id;

        IF v_email_list IS NOT NULL THEN
            v_subject := '[' || v_severity || '] Oracle Alert: ' || v_alert_name;

            v_body := 'Database Alert Notification' || CHR(10) || CHR(10) ||
                     'Alert Name: ' || v_alert_name || CHR(10) ||
                     'Severity: ' || v_severity || CHR(10) ||
                     'Timestamp: ' || TO_CHAR(SYSTIMESTAMP, 'DD-MON-YYYY HH24:MI:SS') || CHR(10) ||
                     'Database: ' || SYS_CONTEXT('USERENV', 'DB_NAME') || CHR(10) ||
                     'Instance: ' || SYS_CONTEXT('USERENV', 'INSTANCE_NAME') || CHR(10) ||
                     'Server: ' || SYS_CONTEXT('USERENV', 'SERVER_HOST') || CHR(10) || CHR(10) ||
                     'Alert Details:' || CHR(10) ||
                     p_message || CHR(10) || CHR(10) ||
                     'Please investigate immediately if this is a critical alert.';

            -- Send email using UTL_MAIL (requires configuration)
            BEGIN
                UTL_MAIL.SEND(
                    sender => '[email protected]',
                    recipients => v_email_list,
                    subject => v_subject,
                    message => v_body
                );

                -- Mark notification as sent
                UPDATE alert_history
                SET notification_sent = 'Y'
                WHERE alert_id = p_alert_id
                    AND alert_timestamp = (
                        SELECT MAX(alert_timestamp)
                        FROM alert_history
                        WHERE alert_id = p_alert_id
                    );
            EXCEPTION
                WHEN OTHERS THEN
                    DBMS_OUTPUT.PUT_LINE('Error sending email: ' || SQLERRM);
            END;
        END IF;
    END send_alert_notification;

    FUNCTION get_alert_summary RETURN SYS_REFCURSOR IS
        v_cursor SYS_REFCURSOR;
    BEGIN
        OPEN v_cursor FOR
            SELECT
                a.alert_name,
                a.alert_type,
                a.severity,
                a.enabled,
                a.last_check,
                a.last_alert,
                a.alert_count,
                COUNT(h.history_id) as alerts_last_24h
            FROM alert_config a
            LEFT JOIN alert_history h ON a.alert_id = h.alert_id
                AND h.alert_timestamp > SYSTIMESTAMP - INTERVAL '24' HOUR
            GROUP BY a.alert_name, a.alert_type, a.severity,
                     a.enabled, a.last_check, a.last_alert, a.alert_count
            ORDER BY a.severity, a.alert_name;

        RETURN v_cursor;
    END get_alert_summary;

END pkg_alert_manager;
/

Pre-configured Alerts

Performance Alerts

-- CPU usage alert
BEGIN
    pkg_alert_manager.add_alert(
        p_alert_name => 'High CPU Usage',
        p_alert_type => 'PERFORMANCE',
        p_check_sql => 'SELECT value FROM v$osstat WHERE stat_name = ''BUSY_TIME'' AND ROWNUM = 1',
        p_threshold_value => 80,
        p_threshold_type => 'ABOVE',
        p_severity => 'HIGH',
        p_check_interval => 5,
        p_email_list => '[email protected]'
    );
END;
/

-- Active sessions alert
BEGIN
    pkg_alert_manager.add_alert(
        p_alert_name => 'Too Many Active Sessions',
        p_alert_type => 'PERFORMANCE',
        p_check_sql => 'SELECT COUNT(*) FROM v$session WHERE status = ''ACTIVE'' AND username IS NOT NULL',
        p_threshold_value => 100,
        p_threshold_type => 'ABOVE',
        p_severity => 'MEDIUM',
        p_check_interval => 2,
        p_email_list => '[email protected]'
    );
END;
/

-- Long running queries alert
BEGIN
    pkg_alert_manager.add_alert(
        p_alert_name => 'Long Running Queries',
        p_alert_type => 'PERFORMANCE',
        p_check_sql => q'[
            SELECT COUNT(*)
            FROM v$session s
            WHERE status = 'ACTIVE'
                AND username IS NOT NULL
                AND last_call_et > 1800
        ]',
        p_threshold_value => 5,
        p_threshold_type => 'ABOVE',
        p_severity => 'HIGH',
        p_check_interval => 10,
        p_email_list => '[email protected]'
    );
END;
/

Space Alerts

-- Tablespace usage alert
BEGIN
    pkg_alert_manager.add_alert(
        p_alert_name => 'Tablespace Nearly Full',
        p_alert_type => 'SPACE',
        p_check_sql => q'[
            SELECT MAX(used_percent)
            FROM (
                SELECT tablespace_name,
                       ROUND((used_space / tablespace_size) * 100, 2) as used_percent
                FROM dba_tablespace_usage_metrics
            )
        ]',
        p_threshold_value => 90,
        p_threshold_type => 'ABOVE',
        p_severity => 'CRITICAL',
        p_check_interval => 15,
        p_email_list => '[email protected]'
    );
END;
/

-- Archive log space alert
BEGIN
    pkg_alert_manager.add_alert(
        p_alert_name => 'Archive Log Space Critical',
        p_alert_type => 'SPACE',
        p_check_sql => q'[
            SELECT ROUND((space_used / space_limit) * 100, 2)
            FROM v$recovery_file_dest
        ]',
        p_threshold_value => 80,
        p_threshold_type => 'ABOVE',
        p_severity => 'CRITICAL',
        p_check_interval => 5,
        p_email_list => '[email protected]'
    );
END;
/

Availability Alerts

-- Database uptime check
BEGIN
    pkg_alert_manager.add_alert(
        p_alert_name => 'Recent Database Restart',
        p_alert_type => 'AVAILABILITY',
        p_check_sql => q'[
            SELECT ROUND((SYSDATE - startup_time) * 24 * 60, 2)
            FROM v$instance
        ]',
        p_threshold_value => 30, -- minutes
        p_threshold_type => 'BELOW',
        p_severity => 'HIGH',
        p_check_interval => 5,
        p_email_list => '[email protected],[email protected]'
    );
END;
/

-- Failed jobs alert
BEGIN
    pkg_alert_manager.add_alert(
        p_alert_name => 'Failed Scheduled Jobs',
        p_alert_type => 'AVAILABILITY',
        p_check_sql => q'[
            SELECT COUNT(*)
            FROM dba_scheduler_job_run_details
            WHERE log_date > SYSTIMESTAMP - INTERVAL '1' HOUR
                AND status = 'FAILED'
        ]',
        p_threshold_value => 0,
        p_threshold_type => 'ABOVE',
        p_severity => 'HIGH',
        p_check_interval => 30,
        p_email_list => '[email protected]'
    );
END;
/

Custom Alert Implementation

Lock Wait Alert

CREATE OR REPLACE PROCEDURE check_lock_waits IS
    v_lock_count NUMBER;
    v_max_wait_time NUMBER;
    v_details VARCHAR2(4000);
BEGIN
    -- Check for sessions waiting on locks
    SELECT COUNT(*), MAX(seconds_in_wait)
    INTO v_lock_count, v_max_wait_time
    FROM v$session
    WHERE blocking_session IS NOT NULL
        AND seconds_in_wait > 300; -- 5 minutes

    IF v_lock_count > 0 THEN
        -- Build detailed message
        v_details := 'Found ' || v_lock_count || ' sessions waiting on locks.' || CHR(10);
        v_details := v_details || 'Maximum wait time: ' || v_max_wait_time || ' seconds' || CHR(10) || CHR(10);

        -- Get details of blocking sessions
        FOR rec IN (
            SELECT
                'Session ' || s1.sid || ' (' || s1.username || ') is blocking ' ||
                'Session ' || s2.sid || ' (' || s2.username || ') for ' ||
                s2.seconds_in_wait || ' seconds' as blocking_info
            FROM v$session s1
            JOIN v$session s2 ON s1.sid = s2.blocking_session
            WHERE s2.seconds_in_wait > 300
            ORDER BY s2.seconds_in_wait DESC
            FETCH FIRST 5 ROWS ONLY
        ) LOOP
            v_details := v_details || rec.blocking_info || CHR(10);
        END LOOP;

        -- Send alert
        send_custom_alert(
            p_alert_type => 'LOCK_WAIT',
            p_severity => 'HIGH',
            p_subject => 'Lock Wait Alert - ' || v_lock_count || ' sessions blocked',
            p_message => v_details
        );
    END IF;
END;
/

Invalid Objects Alert

CREATE OR REPLACE PROCEDURE check_invalid_objects IS
    v_invalid_count NUMBER;
    v_details VARCHAR2(32000);
    v_schema_list VARCHAR2(4000) := 'APP_SCHEMA,HR,SALES'; -- Schemas to monitor
BEGIN
    -- Count invalid objects
    SELECT COUNT(*)
    INTO v_invalid_count
    FROM dba_objects
    WHERE status = 'INVALID'
        AND owner IN (
            SELECT REGEXP_SUBSTR(v_schema_list, '[^,]+', 1, LEVEL)
            FROM dual
            CONNECT BY REGEXP_SUBSTR(v_schema_list, '[^,]+', 1, LEVEL) IS NOT NULL
        );

    IF v_invalid_count > 0 THEN
        v_details := 'Found ' || v_invalid_count || ' invalid objects:' || CHR(10) || CHR(10);

        -- List invalid objects
        FOR rec IN (
            SELECT owner, object_type, object_name
            FROM dba_objects
            WHERE status = 'INVALID'
                AND owner IN (
                    SELECT REGEXP_SUBSTR(v_schema_list, '[^,]+', 1, LEVEL)
                    FROM dual
                    CONNECT BY REGEXP_SUBSTR(v_schema_list, '[^,]+', 1, LEVEL) IS NOT NULL
                )
            ORDER BY owner, object_type, object_name
        ) LOOP
            v_details := v_details || rec.owner || '.' || rec.object_name ||
                        ' (' || rec.object_type || ')' || CHR(10);
        END LOOP;

        -- Send alert
        send_custom_alert(
            p_alert_type => 'INVALID_OBJECTS',
            p_severity => 'MEDIUM',
            p_subject => 'Invalid Objects Alert - ' || v_invalid_count || ' objects',
            p_message => v_details
        );
    END IF;
END;
/

Alert Scheduler Setup

-- Create scheduler job for alert checking
BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name => 'CHECK_ALERTS_JOB',
        job_type => 'PLSQL_BLOCK',
        job_action => 'BEGIN pkg_alert_manager.check_alerts; END;',
        start_date => SYSTIMESTAMP,
        repeat_interval => 'FREQ=MINUTELY; INTERVAL=1',
        enabled => TRUE,
        comments => 'Job to check database alerts every minute'
    );
END;
/

-- Create job for custom alert checks
BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name => 'CUSTOM_ALERTS_JOB',
        job_type => 'PLSQL_BLOCK',
        job_action => '
            BEGIN
                check_lock_waits;
                check_invalid_objects;
            END;',
        start_date => SYSTIMESTAMP,
        repeat_interval => 'FREQ=MINUTELY; INTERVAL=5',
        enabled => TRUE,
        comments => 'Job to check custom alerts every 5 minutes'
    );
END;
/

Alert Dashboard Views

-- Create view for alert dashboard
CREATE OR REPLACE VIEW v_alert_dashboard AS
SELECT
    ac.alert_name,
    ac.alert_type,
    ac.severity,
    ac.enabled,
    ac.threshold_value,
    ac.threshold_type,
    ac.last_check,
    ac.last_alert,
    ac.alert_count,
    ah.recent_alerts,
    ah.last_alert_value,
    ah.last_alert_time
FROM alert_config ac
LEFT JOIN (
    SELECT
        alert_id,
        COUNT(*) as recent_alerts,
        MAX(alert_value) as last_alert_value,
        MAX(alert_timestamp) as last_alert_time
    FROM alert_history
    WHERE alert_timestamp > SYSTIMESTAMP - INTERVAL '24' HOUR
    GROUP BY alert_id
) ah ON ac.alert_id = ah.alert_id
ORDER BY
    CASE ac.severity
        WHEN 'CRITICAL' THEN 1
        WHEN 'HIGH' THEN 2
        WHEN 'MEDIUM' THEN 3
        WHEN 'LOW' THEN 4
    END,
    ac.alert_name;

-- Alert history summary
CREATE OR REPLACE VIEW v_alert_history_summary AS
SELECT
    TRUNC(alert_timestamp, 'HH24') as hour,
    alert_type,
    severity,
    COUNT(*) as alert_count
FROM alert_history ah
JOIN alert_config ac ON ah.alert_id = ac.alert_id
WHERE alert_timestamp > SYSTIMESTAMP - INTERVAL '7' DAY
GROUP BY TRUNC(alert_timestamp, 'HH24'), alert_type, severity
ORDER BY hour DESC, severity;

Best Practices

1. Thresholds: Set appropriate thresholds based on baseline metrics
2. Frequency: Balance alert frequency with system overhead
3. Severity: Use severity levels to prioritize response
4. Deduplication: Implement logic to prevent alert storms
5. Documentation: Document each alert’s purpose and response
6. Testing: Test alerts in non-production environments first
7. Maintenance: Regularly review and update alert configurations