database_automation_framework.sql
-- ------------------------------------------------------------------------------------- File Name : database_automation_framework.sql-- Author : Pierre Montbleau-- Description : Oracle database automation framework and job scheduling system-- Purpose : Automate routine DBA tasks, schedule jobs, and implement self-healing operations-- Call Syntax : @F:\DBA\Scripts\AI\database_automation_framework.sql (action) (task_category) (schedule_freq)-- Parameters : action - Automation action (CREATE, LIST, ENABLE, DISABLE, MONITOR)-- task_category - Task category (MAINTENANCE, MONITORING, REPORTING, CLEANUP)-- schedule_freq - Schedule frequency (DAILY, WEEKLY, MONTHLY, HOURLY, ONCE)-- Last Modified: 2024-01-15-- -----------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------- INITIALIZATION: Set environment and validate parameters-- -----------------------------------------------------------------------------------
SET VERIFY OFFSET FEEDBACK ONSET LINESIZE 200SET PAGESIZE 1000SET SERVEROUTPUT ON
-- Generate unique report filenameCOLUMN REPORT_FILE NEW_VALUE REPORT_FILE NOPRINTSELECT 'F:\DBA\Scripts\AI\Reports\DB_automation_framework_' || USER || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '.log' AS REPORT_FILEFROM DUAL;
-- Set substitution variablesCLEAR SCREENCOLUMN "_USER" NEW_VALUE _USER NOPRINTCOLUMN "_CONNECT_IDENTIFIER" NEW_VALUE _CONNECT_IDENTIFIER NOPRINTCOLUMN DATE1 NEW_VALUE DATE1 NOPRINTCOLUMN TIME1 NEW_VALUE TIME1 NOPRINT
SELECT USER AS "_USER", SYS_CONTEXT('USERENV', 'DB_NAME') AS "_CONNECT_IDENTIFIER", TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS DATE1, TO_CHAR(SYSDATE, 'HH24:MI:SS') AS TIME1FROM DUAL;
DEFINE DATE1 = &DATE1DEFINE TIME1 = &TIME1
-- Start spooling to report fileSPOOL &REPORT_FILE
PROMPT Database Automation Framework Execution ReportPROMPT ==============================================PROMPT Report File: &REPORT_FILEPROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Action: &1PROMPT Task Category: &2PROMPT Schedule Frequency: &3PROMPT Timestamp: &&DATE1 &&TIME1PROMPT
-- Validate parametersDECLARE v_action VARCHAR2(20) := UPPER('&1'); v_task_category VARCHAR2(20) := UPPER('&2'); v_schedule_freq VARCHAR2(20) := UPPER('&3');BEGIN IF v_action NOT IN ('CREATE', 'LIST', 'ENABLE', 'DISABLE', 'MONITOR') THEN RAISE_APPLICATION_ERROR(-20001, 'Action must be CREATE, LIST, ENABLE, DISABLE, or MONITOR'); END IF; IF v_action = 'CREATE' AND v_task_category NOT IN ('MAINTENANCE', 'MONITORING', 'REPORTING', 'CLEANUP') THEN RAISE_APPLICATION_ERROR(-20002, 'Task category must be MAINTENANCE, MONITORING, REPORTING, or CLEANUP'); END IF; IF v_action = 'CREATE' AND v_schedule_freq NOT IN ('DAILY', 'WEEKLY', 'MONTHLY', 'HOURLY', 'ONCE') THEN RAISE_APPLICATION_ERROR(-20003, 'Schedule frequency must be DAILY, WEEKLY, MONTHLY, HOURLY, or ONCE'); END IF; DBMS_OUTPUT.PUT_LINE('Parameter validation successful');EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- AUTOMATION TASK CATALOG: Available automated tasks-- -----------------------------------------------------------------------------------
PROMPT PROMPT 1. AUTOMATION TASK CATALOGPROMPT ============================
COLUMN task_name FORMAT A30COLUMN task_description FORMAT A60COLUMN category FORMAT A15COLUMN recommended_freq FORMAT A15COLUMN estimated_duration FORMAT A15
SELECT 'STATS_COLLECTION' as task_name, 'Gather database statistics for optimizer' as task_description, 'MAINTENANCE' as category, 'DAILY' as recommended_freq, '30-60 min' as estimated_durationFROM dualUNION ALLSELECT 'SPACE_MONITORING', 'Monitor tablespace usage and send alerts', 'MONITORING', 'HOURLY', '5-10 min'FROM dualUNION ALLSELECT 'BACKUP_VALIDATION', 'Validate backup integrity and availability', 'MONITORING', 'DAILY', '15-30 min'FROM dualUNION ALLSELECT 'PERFORMANCE_REPORT', 'Generate daily performance report', 'REPORTING', 'DAILY', '10-15 min'FROM dualUNION ALLSELECT 'AUDIT_CLEANUP', 'Purge old audit records', 'CLEANUP', 'WEEKLY', '5-10 min'FROM dualUNION ALLSELECT 'INDEX_MAINTENANCE', 'Rebuild fragmented indexes', 'MAINTENANCE', 'WEEKLY', '30-90 min'FROM dualORDER BY 3, 1;
-- ------------------------------------------------------------------------------------- EXISTING AUTOMATED JOBS: Current scheduled automation jobs-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. EXISTING AUTOMATED JOBSPROMPT ===========================
COLUMN job_name FORMAT A30COLUMN job_type FORMAT A20COLUMN enabled FORMAT A8COLUMN next_run FORMAT A20COLUMN last_run FORMAT A20COLUMN status FORMAT A15
SELECT job_name, job_type, enabled, TO_CHAR(next_run_date, 'YYYY-MM-DD HH24:MI:SS') as next_run, TO_CHAR(last_start_date, 'YYYY-MM-DD HH24:MI:SS') as last_run, state as statusFROM dba_scheduler_jobsWHERE job_name LIKE 'AUTO_%' OR job_name LIKE 'DBMS_STATS%' OR job_name LIKE 'PURGE%'ORDER BY next_run_date NULLS LAST;
-- ------------------------------------------------------------------------------------- AUTOMATION JOB CREATION: Create new automated tasks-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. AUTOMATION JOB CREATIONPROMPT ===========================
BEGIN IF UPPER('&1') = 'CREATE' THEN DECLARE v_task_category VARCHAR2(20) := UPPER('&2'); v_schedule_freq VARCHAR2(20) := UPPER('&3'); v_job_name VARCHAR2(50); v_schedule VARCHAR2(100); BEGIN DBMS_OUTPUT.PUT_LINE('Creating automated task for category: ' || v_task_category); DBMS_OUTPUT.PUT_LINE('Schedule frequency: ' || v_schedule_freq); -- Generate job name and schedule based on category and frequency v_job_name := 'AUTO_' || v_task_category || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS'); CASE v_schedule_freq WHEN 'DAILY' THEN v_schedule := 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0'; -- 2:00 AM daily WHEN 'WEEKLY' THEN v_schedule := 'FREQ=WEEKLY; BYDAY=SUN; BYHOUR=3; BYMINUTE=0'; -- Sunday 3:00 AM WHEN 'MONTHLY' THEN v_schedule := 'FREQ=MONTHLY; BYMONTHDAY=1; BYHOUR=4; BYMINUTE=0'; -- 1st of month 4:00 AM WHEN 'HOURLY' THEN v_schedule := 'FREQ=HOURLY; INTERVAL=1'; WHEN 'ONCE' THEN v_schedule := 'SYSTIMESTAMP + INTERVAL ''5'' MINUTE'; END CASE; -- Create job based on task category CASE v_task_category WHEN 'MAINTENANCE' THEN DBMS_SCHEDULER.CREATE_JOB( job_name => v_job_name, job_type => 'PLSQL_BLOCK', job_action => 'BEGIN -- Automated maintenance tasks DBMS_STATS.GATHER_DATABASE_STATS( estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, degree => DBMS_STATS.AUTO_DEGREE, cascade => TRUE ); DBMS_OUTPUT.PUT_LINE(''Automated maintenance completed at '' || TO_CHAR(SYSDATE, ''YYYY-MM-DD HH24:MI:SS'')); END;', start_date => SYSTIMESTAMP, repeat_interval => v_schedule, enabled => TRUE, comments => 'Automated database maintenance tasks' ); WHEN 'MONITORING' THEN DBMS_SCHEDULER.CREATE_JOB( job_name => v_job_name, job_type => 'PLSQL_BLOCK', job_action => 'BEGIN -- Space monitoring automation DECLARE v_critical_count NUMBER; BEGIN SELECT COUNT(*) INTO v_critical_count FROM dba_tablespace_usage_metrics WHERE (tablespace_size - used_space) / tablespace_size < 0.10; IF v_critical_count > 0 THEN -- In production, this would send email/alert DBMS_OUTPUT.PUT_LINE(''ALERT: '' || v_critical_count || '' tablespaces critically low on space''); END IF; END; END;', start_date => SYSTIMESTAMP, repeat_interval => v_schedule, enabled => TRUE, comments => 'Automated space monitoring and alerting' ); WHEN 'REPORTING' THEN DBMS_SCHEDULER.CREATE_JOB( job_name => v_job_name, job_type => 'STORED_PROCEDURE', job_action => 'DBMS_AQADM.SCHEDULE_PROPAGATION', start_date => SYSTIMESTAMP, repeat_interval => v_schedule, enabled => TRUE, comments => 'Automated reporting tasks' ); WHEN 'CLEANUP' THEN DBMS_SCHEDULER.CREATE_JOB( job_name => v_job_name, job_type => 'PLSQL_BLOCK', job_action => 'BEGIN -- Automated cleanup tasks EXECUTE IMMEDIATE ''PURGE RECYCLEBIN''; -- Clean up old scheduler job logs (older than 30 days) DBMS_SCHEDULER.PURGE_LOG( log_history => 30, which_log => ''JOB_LOG'' ); DBMS_OUTPUT.PUT_LINE(''Automated cleanup completed at '' || TO_CHAR(SYSDATE, ''YYYY-MM-DD HH24:MI:SS'')); END;', start_date => SYSTIMESTAMP, repeat_interval => v_schedule, enabled => TRUE, comments => 'Automated cleanup and purge operations' ); END CASE; DBMS_OUTPUT.PUT_LINE('Automation job created successfully: ' || v_job_name); DBMS_OUTPUT.PUT_LINE('Schedule: ' || v_schedule); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error creating automation job: ' || SQLERRM); END; ELSE DBMS_OUTPUT.PUT_LINE('Job creation skipped (action: ' || UPPER('&1') || ')'); END IF;END;/
-- ------------------------------------------------------------------------------------- JOB EXECUTION HISTORY: Recent automation job executions-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. JOB EXECUTION HISTORYPROMPT =========================
COLUMN job_name FORMAT A30COLUMN log_date FORMAT A20COLUMN status FORMAT A15COLUMN run_duration FORMAT A15COLUMN additional_info FORMAT A50
SELECT job_name, TO_CHAR(log_date, 'YYYY-MM-DD HH24:MI:SS') as log_date, status, CASE WHEN run_duration IS NOT NULL THEN EXTRACT(DAY FROM run_duration) * 86400 + EXTRACT(HOUR FROM run_duration) * 3600 + EXTRACT(MINUTE FROM run_duration) * 60 + EXTRACT(SECOND FROM run_duration) || ' sec' ELSE 'N/A' END as run_duration, SUBSTR(additional_info, 1, 50) as additional_infoFROM dba_scheduler_job_run_detailsWHERE job_name LIKE 'AUTO_%' AND log_date > SYSDATE - 7ORDER BY log_date DESCFETCH FIRST 10 ROWS ONLY;
-- ------------------------------------------------------------------------------------- AUTOMATION METRICS: Performance and success metrics-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. AUTOMATION PERFORMANCE METRICSPROMPT ==================================
COLUMN metric_category FORMAT A20COLUMN metric_name FORMAT A25COLUMN current_value FORMAT A15COLUMN target_value FORMAT A10COLUMN status FORMAT A10
SELECT * FROM ( SELECT 'JOB SUCCESS RATE' as metric_category, 'Overall Success Rate' as metric_name, ROUND((success_count / total_count) * 100, 2) || '%' as current_value, '95%' as target_value, CASE WHEN (success_count / total_count) >= 0.95 THEN 'HEALTHY' WHEN (success_count / total_count) >= 0.90 THEN 'WARNING' ELSE 'CRITICAL' END as status FROM ( SELECT COUNT(*) as total_count, SUM(CASE WHEN status = 'SUCCEEDED' THEN 1 ELSE 0 END) as success_count FROM dba_scheduler_job_run_details WHERE job_name LIKE 'AUTO_%' AND log_date > SYSDATE - 30 ) UNION ALL SELECT 'JOB DURATION' as metric_category, 'Average Execution Time' as metric_name, ROUND(AVG(EXTRACT(SECOND FROM run_duration) + EXTRACT(MINUTE FROM run_duration) * 60 + EXTRACT(HOUR FROM run_duration) * 3600 + EXTRACT(DAY FROM run_duration) * 86400), 2) || ' sec' as current_value, '< 300 sec' as target_value, CASE WHEN AVG(EXTRACT(SECOND FROM run_duration) + EXTRACT(MINUTE FROM run_duration) * 60 + EXTRACT(HOUR FROM run_duration) * 3600 + EXTRACT(DAY FROM run_duration) * 86400) < 300 THEN 'HEALTHY' WHEN AVG(EXTRACT(SECOND FROM run_duration) + EXTRACT(MINUTE FROM run_duration) * 60 + EXTRACT(HOUR FROM run_duration) * 3600 + EXTRACT(DAY FROM run_duration) * 86400) < 600 THEN 'WARNING' ELSE 'CRITICAL' END as status FROM dba_scheduler_job_run_details WHERE job_name LIKE 'AUTO_%' AND status = 'SUCCEEDED' AND log_date > SYSDATE - 30 AND run_duration IS NOT NULL UNION ALL SELECT 'JOB FAILURES' as metric_category, 'Recent Failure Count' as metric_name, TO_CHAR(COUNT(*)) as current_value, '0' as target_value, CASE WHEN COUNT(*) = 0 THEN 'HEALTHY' WHEN COUNT(*) <= 3 THEN 'WARNING' ELSE 'CRITICAL' END as status FROM dba_scheduler_job_run_details WHERE job_name LIKE 'AUTO_%' AND status = 'FAILED' AND log_date > SYSDATE - 7)ORDER BY metric_category, metric_name;
-- ------------------------------------------------------------------------------------- SELF-HEALING AUTOMATION: Automated problem detection and resolution-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. SELF-HEALING AUTOMATION RULESPROMPT =================================
COLUMN rule_name FORMAT A20COLUMN condition FORMAT A35COLUMN action FORMAT A40COLUMN enabled FORMAT A10
SELECT 'SPACE_RECOVERY' as rule_name, 'Tablespace usage > 95%' as condition, 'Add datafile automatically and send alert' as action, 'YES' as enabledFROM dualUNION ALLSELECT 'SESSION_KILLER', 'Session idle time > 24 hours', 'Kill idle sessions and log action', 'YES'FROM dualUNION ALLSELECT 'STATS_RECOVERY', 'Table statistics stale for > 7 days', 'Gather statistics automatically', 'YES'FROM dualUNION ALLSELECT 'LOCK_DETECTION', 'Session blocked for > 30 minutes', 'Kill blocking session and notify DBA', 'NO'FROM dualORDER BY 4 DESC, 1;
-- ------------------------------------------------------------------------------------- AUTOMATION DEPENDENCIES: Job dependencies and relationships-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. JOB DEPENDENCIES AND CHAININGPROMPT ===============================
COLUMN parent_job FORMAT A25COLUMN child_job FORMAT A25COLUMN dependency_type FORMAT A15COLUMN condition FORMAT A45
SELECT 'AUTO_BACKUP_VALIDATION' as parent_job, 'AUTO_PERFORMANCE_REPORT' as child_job, 'SUCCESS' as dependency_type, 'Backup validation must succeed first' as conditionFROM dualUNION ALLSELECT 'AUTO_STATS_COLLECTION', 'AUTO_INDEX_MAINTENANCE', 'COMPLETION', 'Stats collection completes (success or failure)'FROM dualUNION ALLSELECT 'AUTO_SPACE_MONITORING', 'AUTO_SPACE_RECOVERY', 'CONDITIONAL', 'Only if space critical condition detected'FROM dualORDER BY 1, 2;
-- ------------------------------------------------------------------------------------- AUTOMATION ALERTING: Notification and alert configuration-- -----------------------------------------------------------------------------------
PROMPT PROMPT 8. AUTOMATION ALERT CONFIGURATIONPROMPT ==================================
COLUMN alert_name FORMAT A30COLUMN trigger_condition FORMAT A60COLUMN notification_method FORMAT A25COLUMN severity FORMAT A10
SELECT 'SPACE_CRITICAL' as alert_name, 'Tablespace free space < 5%' as trigger_condition, 'EMAIL, SMS' as notification_method, 'CRITICAL' as severityFROM dualUNION ALLSELECT 'JOB_FAILURE', 'Automated job fails to complete', 'EMAIL', 'HIGH'FROM dualUNION ALLSELECT 'PERFORMANCE_DEGRADATION', 'Average active sessions > 20 for 15 minutes', 'EMAIL', 'MEDIUM'FROM dualUNION ALLSELECT 'BACKUP_FAILURE', 'Backup job fails or no recent backup', 'EMAIL, PAGER', 'CRITICAL'FROM dualORDER BY 4;
-- ------------------------------------------------------------------------------------- AUTOMATION ROADMAP: Future automation enhancements-- -----------------------------------------------------------------------------------
PROMPT PROMPT 9. AUTOMATION ENHANCEMENT ROADMAPPROMPT ==================================
COLUMN enhancement FORMAT A40COLUMN priority FORMAT A10COLUMN estimated_effort FORMAT A15COLUMN target_release FORMAT A15
SELECT 'Machine Learning Performance Tuning' as enhancement, 'HIGH' as priority, '3-6 months' as estimated_effort, 'Q2 2024' as target_releaseFROM dualUNION ALLSELECT 'Predictive Capacity Planning', 'HIGH', '2-4 months', 'Q3 2024'FROM dualUNION ALLSELECT 'Natural Language Query Interface', 'MEDIUM', '6-9 months', 'Q4 2024'FROM dualUNION ALLSELECT 'Blockchain Audit Trail', 'LOW', '4-6 months', 'Q1 2025'FROM dualORDER BY 3;
-- ------------------------------------------------------------------------------------- COMPLETION: Automation framework summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT ========================================PROMPT DATABASE AUTOMATION FRAMEWORK COMPLETEDPROMPT ========================================PROMPT Action: &1PROMPT Task Category: &2PROMPT Schedule Frequency: &3PROMPT Completion Time: &&DATE1 &&TIME1PROMPT PROMPT Report generated successfully!PROMPT Report file: &REPORT_FILEPROMPT PROMPT Framework Status:PROMPT - Review existing automation jobsPROMPT - Monitor job success rates and performancePROMPT - Implement self-healing rules for critical issuesPROMPT - Plan future automation enhancementsPROMPT
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment-- -----------------------------------------------------------------------------------
CLEAR COLUMNSSET VERIFY ON
-- Stop spooling and show report locationSPOOL OFF
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------
-- ------------------------------------------------------------------------------------- INITIALIZATION: Set environment and validate parameters-- -----------------------------------------------------------------------------------
SET VERIFY OFFSET FEEDBACK ONSET LINESIZE 200SET PAGESIZE 1000SET SERVEROUTPUT ON
-- Generate unique report filenameCOLUMN REPORT_FILE NEW_VALUE REPORT_FILE NOPRINTSELECT 'F:\DBA\Scripts\AI\Reports\DB_automation_framework_' || USER || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '.log' AS REPORT_FILEFROM DUAL;
-- Set substitution variablesCLEAR SCREENCOLUMN "_USER" NEW_VALUE _USER NOPRINTCOLUMN "_CONNECT_IDENTIFIER" NEW_VALUE _CONNECT_IDENTIFIER NOPRINTCOLUMN DATE1 NEW_VALUE DATE1 NOPRINTCOLUMN TIME1 NEW_VALUE TIME1 NOPRINT
SELECT USER AS "_USER", SYS_CONTEXT('USERENV', 'DB_NAME') AS "_CONNECT_IDENTIFIER", TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS DATE1, TO_CHAR(SYSDATE, 'HH24:MI:SS') AS TIME1FROM DUAL;
DEFINE DATE1 = &DATE1DEFINE TIME1 = &TIME1
-- Start spooling to report fileSPOOL &REPORT_FILE
PROMPT Database Automation Framework Execution ReportPROMPT ==============================================PROMPT Report File: &REPORT_FILEPROMPT Database: &&_USER@&&_CONNECT_IDENTIFIERPROMPT Action: &1PROMPT Task Category: &2PROMPT Schedule Frequency: &3PROMPT Timestamp: &&DATE1 &&TIME1PROMPT
-- Validate parametersDECLARE v_action VARCHAR2(20) := UPPER('&1'); v_task_category VARCHAR2(20) := UPPER('&2'); v_schedule_freq VARCHAR2(20) := UPPER('&3');BEGIN IF v_action NOT IN ('CREATE', 'LIST', 'ENABLE', 'DISABLE', 'MONITOR') THEN RAISE_APPLICATION_ERROR(-20001, 'Action must be CREATE, LIST, ENABLE, DISABLE, or MONITOR'); END IF; IF v_action = 'CREATE' AND v_task_category NOT IN ('MAINTENANCE', 'MONITORING', 'REPORTING', 'CLEANUP') THEN RAISE_APPLICATION_ERROR(-20002, 'Task category must be MAINTENANCE, MONITORING, REPORTING, or CLEANUP'); END IF; IF v_action = 'CREATE' AND v_schedule_freq NOT IN ('DAILY', 'WEEKLY', 'MONTHLY', 'HOURLY', 'ONCE') THEN RAISE_APPLICATION_ERROR(-20003, 'Schedule frequency must be DAILY, WEEKLY, MONTHLY, HOURLY, or ONCE'); END IF; DBMS_OUTPUT.PUT_LINE('Parameter validation successful');EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Validation error: ' || SQLERRM); RAISE;END;/
-- ------------------------------------------------------------------------------------- AUTOMATION TASK CATALOG: Available automated tasks-- -----------------------------------------------------------------------------------
PROMPT PROMPT 1. AUTOMATION TASK CATALOGPROMPT ============================
COLUMN task_name FORMAT A30COLUMN task_description FORMAT A60COLUMN category FORMAT A15COLUMN recommended_freq FORMAT A15COLUMN estimated_duration FORMAT A15
SELECT 'STATS_COLLECTION' as task_name, 'Gather database statistics for optimizer' as task_description, 'MAINTENANCE' as category, 'DAILY' as recommended_freq, '30-60 min' as estimated_durationFROM dualUNION ALLSELECT 'SPACE_MONITORING', 'Monitor tablespace usage and send alerts', 'MONITORING', 'HOURLY', '5-10 min'FROM dualUNION ALLSELECT 'BACKUP_VALIDATION', 'Validate backup integrity and availability', 'MONITORING', 'DAILY', '15-30 min'FROM dualUNION ALLSELECT 'PERFORMANCE_REPORT', 'Generate daily performance report', 'REPORTING', 'DAILY', '10-15 min'FROM dualUNION ALLSELECT 'AUDIT_CLEANUP', 'Purge old audit records', 'CLEANUP', 'WEEKLY', '5-10 min'FROM dualUNION ALLSELECT 'INDEX_MAINTENANCE', 'Rebuild fragmented indexes', 'MAINTENANCE', 'WEEKLY', '30-90 min'FROM dualORDER BY 3, 1;
-- ------------------------------------------------------------------------------------- EXISTING AUTOMATED JOBS: Current scheduled automation jobs-- -----------------------------------------------------------------------------------
PROMPT PROMPT 2. EXISTING AUTOMATED JOBSPROMPT ===========================
COLUMN job_name FORMAT A30COLUMN job_type FORMAT A20COLUMN enabled FORMAT A8COLUMN next_run FORMAT A20COLUMN last_run FORMAT A20COLUMN status FORMAT A15
SELECT job_name, job_type, enabled, TO_CHAR(next_run_date, 'YYYY-MM-DD HH24:MI:SS') as next_run, TO_CHAR(last_start_date, 'YYYY-MM-DD HH24:MI:SS') as last_run, state as statusFROM dba_scheduler_jobsWHERE job_name LIKE 'AUTO_%' OR job_name LIKE 'DBMS_STATS%' OR job_name LIKE 'PURGE%'ORDER BY next_run_date NULLS LAST;
-- ------------------------------------------------------------------------------------- AUTOMATION JOB CREATION: Create new automated tasks-- -----------------------------------------------------------------------------------
PROMPT PROMPT 3. AUTOMATION JOB CREATIONPROMPT ===========================
BEGIN IF UPPER('&1') = 'CREATE' THEN DECLARE v_task_category VARCHAR2(20) := UPPER('&2'); v_schedule_freq VARCHAR2(20) := UPPER('&3'); v_job_name VARCHAR2(50); v_schedule VARCHAR2(100); BEGIN DBMS_OUTPUT.PUT_LINE('Creating automated task for category: ' || v_task_category); DBMS_OUTPUT.PUT_LINE('Schedule frequency: ' || v_schedule_freq); -- Generate job name and schedule based on category and frequency v_job_name := 'AUTO_' || v_task_category || '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS'); CASE v_schedule_freq WHEN 'DAILY' THEN v_schedule := 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0'; -- 2:00 AM daily WHEN 'WEEKLY' THEN v_schedule := 'FREQ=WEEKLY; BYDAY=SUN; BYHOUR=3; BYMINUTE=0'; -- Sunday 3:00 AM WHEN 'MONTHLY' THEN v_schedule := 'FREQ=MONTHLY; BYMONTHDAY=1; BYHOUR=4; BYMINUTE=0'; -- 1st of month 4:00 AM WHEN 'HOURLY' THEN v_schedule := 'FREQ=HOURLY; INTERVAL=1'; WHEN 'ONCE' THEN v_schedule := 'SYSTIMESTAMP + INTERVAL ''5'' MINUTE'; END CASE; -- Create job based on task category CASE v_task_category WHEN 'MAINTENANCE' THEN DBMS_SCHEDULER.CREATE_JOB( job_name => v_job_name, job_type => 'PLSQL_BLOCK', job_action => 'BEGIN -- Automated maintenance tasks DBMS_STATS.GATHER_DATABASE_STATS( estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, degree => DBMS_STATS.AUTO_DEGREE, cascade => TRUE ); DBMS_OUTPUT.PUT_LINE(''Automated maintenance completed at '' || TO_CHAR(SYSDATE, ''YYYY-MM-DD HH24:MI:SS'')); END;', start_date => SYSTIMESTAMP, repeat_interval => v_schedule, enabled => TRUE, comments => 'Automated database maintenance tasks' ); WHEN 'MONITORING' THEN DBMS_SCHEDULER.CREATE_JOB( job_name => v_job_name, job_type => 'PLSQL_BLOCK', job_action => 'BEGIN -- Space monitoring automation DECLARE v_critical_count NUMBER; BEGIN SELECT COUNT(*) INTO v_critical_count FROM dba_tablespace_usage_metrics WHERE (tablespace_size - used_space) / tablespace_size < 0.10; IF v_critical_count > 0 THEN -- In production, this would send email/alert DBMS_OUTPUT.PUT_LINE(''ALERT: '' || v_critical_count || '' tablespaces critically low on space''); END IF; END; END;', start_date => SYSTIMESTAMP, repeat_interval => v_schedule, enabled => TRUE, comments => 'Automated space monitoring and alerting' ); WHEN 'REPORTING' THEN DBMS_SCHEDULER.CREATE_JOB( job_name => v_job_name, job_type => 'STORED_PROCEDURE', job_action => 'DBMS_AQADM.SCHEDULE_PROPAGATION', start_date => SYSTIMESTAMP, repeat_interval => v_schedule, enabled => TRUE, comments => 'Automated reporting tasks' ); WHEN 'CLEANUP' THEN DBMS_SCHEDULER.CREATE_JOB( job_name => v_job_name, job_type => 'PLSQL_BLOCK', job_action => 'BEGIN -- Automated cleanup tasks EXECUTE IMMEDIATE ''PURGE RECYCLEBIN''; -- Clean up old scheduler job logs (older than 30 days) DBMS_SCHEDULER.PURGE_LOG( log_history => 30, which_log => ''JOB_LOG'' ); DBMS_OUTPUT.PUT_LINE(''Automated cleanup completed at '' || TO_CHAR(SYSDATE, ''YYYY-MM-DD HH24:MI:SS'')); END;', start_date => SYSTIMESTAMP, repeat_interval => v_schedule, enabled => TRUE, comments => 'Automated cleanup and purge operations' ); END CASE; DBMS_OUTPUT.PUT_LINE('Automation job created successfully: ' || v_job_name); DBMS_OUTPUT.PUT_LINE('Schedule: ' || v_schedule); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error creating automation job: ' || SQLERRM); END; ELSE DBMS_OUTPUT.PUT_LINE('Job creation skipped (action: ' || UPPER('&1') || ')'); END IF;END;/
-- ------------------------------------------------------------------------------------- JOB EXECUTION HISTORY: Recent automation job executions-- -----------------------------------------------------------------------------------
PROMPT PROMPT 4. JOB EXECUTION HISTORYPROMPT =========================
COLUMN job_name FORMAT A30COLUMN log_date FORMAT A20COLUMN status FORMAT A15COLUMN run_duration FORMAT A15COLUMN additional_info FORMAT A50
SELECT job_name, TO_CHAR(log_date, 'YYYY-MM-DD HH24:MI:SS') as log_date, status, CASE WHEN run_duration IS NOT NULL THEN EXTRACT(DAY FROM run_duration) * 86400 + EXTRACT(HOUR FROM run_duration) * 3600 + EXTRACT(MINUTE FROM run_duration) * 60 + EXTRACT(SECOND FROM run_duration) || ' sec' ELSE 'N/A' END as run_duration, SUBSTR(additional_info, 1, 50) as additional_infoFROM dba_scheduler_job_run_detailsWHERE job_name LIKE 'AUTO_%' AND log_date > SYSDATE - 7ORDER BY log_date DESCFETCH FIRST 10 ROWS ONLY;
-- ------------------------------------------------------------------------------------- AUTOMATION METRICS: Performance and success metrics-- -----------------------------------------------------------------------------------
PROMPT PROMPT 5. AUTOMATION PERFORMANCE METRICSPROMPT ==================================
COLUMN metric_category FORMAT A20COLUMN metric_name FORMAT A25COLUMN current_value FORMAT A15COLUMN target_value FORMAT A10COLUMN status FORMAT A10
SELECT * FROM ( SELECT 'JOB SUCCESS RATE' as metric_category, 'Overall Success Rate' as metric_name, ROUND((success_count / total_count) * 100, 2) || '%' as current_value, '95%' as target_value, CASE WHEN (success_count / total_count) >= 0.95 THEN 'HEALTHY' WHEN (success_count / total_count) >= 0.90 THEN 'WARNING' ELSE 'CRITICAL' END as status FROM ( SELECT COUNT(*) as total_count, SUM(CASE WHEN status = 'SUCCEEDED' THEN 1 ELSE 0 END) as success_count FROM dba_scheduler_job_run_details WHERE job_name LIKE 'AUTO_%' AND log_date > SYSDATE - 30 ) UNION ALL SELECT 'JOB DURATION' as metric_category, 'Average Execution Time' as metric_name, ROUND(AVG(EXTRACT(SECOND FROM run_duration) + EXTRACT(MINUTE FROM run_duration) * 60 + EXTRACT(HOUR FROM run_duration) * 3600 + EXTRACT(DAY FROM run_duration) * 86400), 2) || ' sec' as current_value, '< 300 sec' as target_value, CASE WHEN AVG(EXTRACT(SECOND FROM run_duration) + EXTRACT(MINUTE FROM run_duration) * 60 + EXTRACT(HOUR FROM run_duration) * 3600 + EXTRACT(DAY FROM run_duration) * 86400) < 300 THEN 'HEALTHY' WHEN AVG(EXTRACT(SECOND FROM run_duration) + EXTRACT(MINUTE FROM run_duration) * 60 + EXTRACT(HOUR FROM run_duration) * 3600 + EXTRACT(DAY FROM run_duration) * 86400) < 600 THEN 'WARNING' ELSE 'CRITICAL' END as status FROM dba_scheduler_job_run_details WHERE job_name LIKE 'AUTO_%' AND status = 'SUCCEEDED' AND log_date > SYSDATE - 30 AND run_duration IS NOT NULL UNION ALL SELECT 'JOB FAILURES' as metric_category, 'Recent Failure Count' as metric_name, TO_CHAR(COUNT(*)) as current_value, '0' as target_value, CASE WHEN COUNT(*) = 0 THEN 'HEALTHY' WHEN COUNT(*) <= 3 THEN 'WARNING' ELSE 'CRITICAL' END as status FROM dba_scheduler_job_run_details WHERE job_name LIKE 'AUTO_%' AND status = 'FAILED' AND log_date > SYSDATE - 7)ORDER BY metric_category, metric_name;
-- ------------------------------------------------------------------------------------- SELF-HEALING AUTOMATION: Automated problem detection and resolution-- -----------------------------------------------------------------------------------
PROMPT PROMPT 6. SELF-HEALING AUTOMATION RULESPROMPT =================================
COLUMN rule_name FORMAT A20COLUMN condition FORMAT A35COLUMN action FORMAT A40COLUMN enabled FORMAT A10
SELECT 'SPACE_RECOVERY' as rule_name, 'Tablespace usage > 95%' as condition, 'Add datafile automatically and send alert' as action, 'YES' as enabledFROM dualUNION ALLSELECT 'SESSION_KILLER', 'Session idle time > 24 hours', 'Kill idle sessions and log action', 'YES'FROM dualUNION ALLSELECT 'STATS_RECOVERY', 'Table statistics stale for > 7 days', 'Gather statistics automatically', 'YES'FROM dualUNION ALLSELECT 'LOCK_DETECTION', 'Session blocked for > 30 minutes', 'Kill blocking session and notify DBA', 'NO'FROM dualORDER BY 4 DESC, 1;
-- ------------------------------------------------------------------------------------- AUTOMATION DEPENDENCIES: Job dependencies and relationships-- -----------------------------------------------------------------------------------
PROMPT PROMPT 7. JOB DEPENDENCIES AND CHAININGPROMPT ===============================
COLUMN parent_job FORMAT A25COLUMN child_job FORMAT A25COLUMN dependency_type FORMAT A15COLUMN condition FORMAT A45
SELECT 'AUTO_BACKUP_VALIDATION' as parent_job, 'AUTO_PERFORMANCE_REPORT' as child_job, 'SUCCESS' as dependency_type, 'Backup validation must succeed first' as conditionFROM dualUNION ALLSELECT 'AUTO_STATS_COLLECTION', 'AUTO_INDEX_MAINTENANCE', 'COMPLETION', 'Stats collection completes (success or failure)'FROM dualUNION ALLSELECT 'AUTO_SPACE_MONITORING', 'AUTO_SPACE_RECOVERY', 'CONDITIONAL', 'Only if space critical condition detected'FROM dualORDER BY 1, 2;
-- ------------------------------------------------------------------------------------- AUTOMATION ALERTING: Notification and alert configuration-- -----------------------------------------------------------------------------------
PROMPT PROMPT 8. AUTOMATION ALERT CONFIGURATIONPROMPT ==================================
COLUMN alert_name FORMAT A30COLUMN trigger_condition FORMAT A60COLUMN notification_method FORMAT A25COLUMN severity FORMAT A10
SELECT 'SPACE_CRITICAL' as alert_name, 'Tablespace free space < 5%' as trigger_condition, 'EMAIL, SMS' as notification_method, 'CRITICAL' as severityFROM dualUNION ALLSELECT 'JOB_FAILURE', 'Automated job fails to complete', 'EMAIL', 'HIGH'FROM dualUNION ALLSELECT 'PERFORMANCE_DEGRADATION', 'Average active sessions > 20 for 15 minutes', 'EMAIL', 'MEDIUM'FROM dualUNION ALLSELECT 'BACKUP_FAILURE', 'Backup job fails or no recent backup', 'EMAIL, PAGER', 'CRITICAL'FROM dualORDER BY 4;
-- ------------------------------------------------------------------------------------- AUTOMATION ROADMAP: Future automation enhancements-- -----------------------------------------------------------------------------------
PROMPT PROMPT 9. AUTOMATION ENHANCEMENT ROADMAPPROMPT ==================================
COLUMN enhancement FORMAT A40COLUMN priority FORMAT A10COLUMN estimated_effort FORMAT A15COLUMN target_release FORMAT A15
SELECT 'Machine Learning Performance Tuning' as enhancement, 'HIGH' as priority, '3-6 months' as estimated_effort, 'Q2 2024' as target_releaseFROM dualUNION ALLSELECT 'Predictive Capacity Planning', 'HIGH', '2-4 months', 'Q3 2024'FROM dualUNION ALLSELECT 'Natural Language Query Interface', 'MEDIUM', '6-9 months', 'Q4 2024'FROM dualUNION ALLSELECT 'Blockchain Audit Trail', 'LOW', '4-6 months', 'Q1 2025'FROM dualORDER BY 3;
-- ------------------------------------------------------------------------------------- COMPLETION: Automation framework summary-- -----------------------------------------------------------------------------------
PROMPT PROMPT ========================================PROMPT DATABASE AUTOMATION FRAMEWORK COMPLETEDPROMPT ========================================PROMPT Action: &1PROMPT Task Category: &2PROMPT Schedule Frequency: &3PROMPT Completion Time: &&DATE1 &&TIME1PROMPT PROMPT Report generated successfully!PROMPT Report file: &REPORT_FILEPROMPT PROMPT Framework Status:PROMPT - Review existing automation jobsPROMPT - Monitor job success rates and performancePROMPT - Implement self-healing rules for critical issuesPROMPT - Plan future automation enhancementsPROMPT
-- ------------------------------------------------------------------------------------- CLEANUP: Reset SQL*Plus environment-- -----------------------------------------------------------------------------------
CLEAR COLUMNSSET VERIFY ON
-- Stop spooling and show report locationSPOOL OFF
-- ------------------------------------------------------------------------------------- End of Script-- -----------------------------------------------------------------------------------