2026年CentOS Oracle定时任务配置完全指南：DBMS_SCHEDULER实战(2026)

发表评论

一、Oracle Scheduler概述

1.1 DBMS_SCHEDULER组件

Oracle DBMS_SCHEDULER是强大的任务调度组件，替代了旧的DBMS_JOB：

组件 说明
Program 任务执行的程序单元
Job 调度任务定义
Schedule 调度时间定义
Job Class 作业分类
Window 维护窗口 
-- 查看Scheduler状态
SELECT owner, job_name, job_type, status, enabled
FROM dba_scheduler_jobs;

1.2 Scheduler vs DBMS_JOB

特性 DBMS_JOB DBMS_SCHEDULER
依赖管理 无 支持
资源管理 无 支持
审计功能 有限 完整
事件触发 不支持 支持
远程执行 不支持 支持

二、创建Programs

2.1 存储过程型Program

-- 1. 创建存储过程
CREATE OR REPLACE PROCEDURE purge_old_logs AS
BEGIN
  DELETE FROM app_logs WHERE log_date < SYSDATE - 30;
  COMMIT;
  DBMS_OUTPUT.put_line('Purged old logs successfully');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line('Error: ' || SQLERRM);
    ROLLBACK;
END purge_old_logs;
/

-- 2. 创建Program
BEGIN
  DBMS_SCHEDULER.CREATE_PROGRAM(
    program_name => 'PURGE_LOGS_PROG',
    program_type => 'STORED_PROCEDURE',
    program_action => 'PURGE_OLD_LOGS',
    number_of_arguments => 0,
    enabled => FALSE,
    comments => 'Purges logs older than 30 days'
  );
END;
/

-- 3. 启用Program
BEGIN
  DBMS_SCHEDULER.ENABLE('PURGE_LOGS_PROG');
END;
/

-- 4. 查看Program
SELECT program_name, program_type, enabled, number_of_arguments
FROM dba_scheduler_programs;

2.2 Shell脚本型Program

# 1. 创建Shell脚本
cat > /home/oracle/scripts/backup_stats.sh << 'EOF'
#!/bin/bash
# Oracle Statistics Backup Script

ORACLE_SID=orcl
ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1
export ORACLE_SID ORACLE_HOME

$ORACLE_HOME/bin/sqlplus -s / as sysdba << SQL
SET HEADING OFF FEEDBACK OFF
SPOOL /home/oracle/logs/stats_backup.log
SELECT 'Backup completed at: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM dual;
SPOOL OFF
EXIT
SQL

echo "Statistics backup completed"
EOF

chmod +x /home/oracle/scripts/backup_stats.sh

-- 2. 创建外部Program
BEGIN
  DBMS_SCHEDULER.CREATE_PROGRAM(
    program_name => 'BACKUP_STATS_PROG',
    program_type => 'EXECUTABLE',
    program_action => '/home/oracle/scripts/backup_stats.sh',
    number_of_arguments => 0,
    enabled => FALSE,
    comments => 'Backup statistics to file'
  );
END;
/

-- 3. 授予执行权限
BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE(
    name => 'BACKUP_STATS_PROG',
    attribute => 'shell_executable',
    value => '/bin/bash'
  );
END;
/

2.3 PL/SQL匿名块型Program

-- 创建PL/SQL块Program
BEGIN
  DBMS_SCHEDULER.CREATE_PROGRAM(
    program_name => 'GENERATE_REPORT_PROG',
    program_type => 'PLSQL_BLOCK',
    program_action => 
      'BEGIN
        INSERT INTO daily_report (report_date, total_sales, total_orders)
        SELECT TRUNC(SYSDATE), SUM(amount), COUNT(*)
        FROM orders
        WHERE order_date >= TRUNC(SYSDATE);
        COMMIT;
      END;',
    number_of_arguments => 0,
    enabled => FALSE
  );
END;
/

三、创建Schedules

3.1 简单频率Schedule

-- 1. 每日执行（每天凌晨2点）
BEGIN
  DBMS_SCHEDULER.CREATE_SCHEDULE(
    schedule_name => 'DAILY_2AM_SCH',
    start_date => SYSDATE,
    repeat_interval => 'FREQ=DAILY;INTERVAL=1;BYHOUR=2;BYMINUTE=0',
    end_date => NULL,
    comments => 'Run daily at 2 AM'
  );
END;
/

-- 2. 每周执行（每周一凌晨3点）
BEGIN
  DBMS_SCHEDULER.CREATE_SCHEDULE(
    schedule_name => 'WEEKLY_MON_SCH',
    start_date => SYSDATE,
    repeat_interval => 'FREQ=WEEKLY;BYDAY=MON;BYHOUR=3;BYMINUTE=0',
    comments => 'Run every Monday at 3 AM'
  );
END;
/

-- 3. 每月执行（每月1日凌晨1点）
BEGIN
  DBMS_SCHEDULER.CREATE_SCHEDULE(
    schedule_name => 'MONTHLY_FIRST_SCH',
    start_date => SYSDATE,
    repeat_interval => 'FREQ=MONTHLY;BYMONTHDAY=1;BYHOUR=1;BYMINUTE=0',
    comments => 'Run on 1st of each month at 1 AM'
  );
END;
/

-- 4. 查看Schedule
SELECT schedule_name, repeat_interval, enabled
FROM dba_scheduler_schedules;

3.2 复杂频率Schedule

-- 1. 工作日每4小时执行
BEGIN
  DBMS_SCHEDULER.CREATE_SCHEDULE(
    schedule_name => 'BUSINESS_HOURS_SCH',
    start_date => SYSDATE,
    repeat_interval => 'FREQ=DAILY;BYDAY=MON,TUE,WED,THU,FRI;BYHOUR=8,12,16,20;BYMINUTE=0',
    comments => 'Every 4 hours during business hours on weekdays'
  );
END;
/

-- 2. 每15分钟执行（监控任务）
BEGIN
  DBMS_SCHEDULER.CREATE_SCHEDULE(
    schedule_name => 'EVERY_15_MIN_SCH',
    start_date => SYSDATE,
    repeat_interval => 'FREQ=MINUTELY;INTERVAL=15',
    comments => 'Run every 15 minutes'
  );
END;
/

-- 3. 每季度执行
BEGIN
  DBMS_SCHEDULER.CREATE_SCHEDULE(
    schedule_name => 'QUARTERLY_SCH',
    start_date => SYSDATE,
    repeat_interval => 'FREQ=YEARLY;BYMONTH=JAN,APR,JUL,OCT;BYMONTHDAY=1;BYHOUR=0;BYMINUTE=0',
    comments => 'Run quarterly on 1st of Jan, Apr, Jul, Oct'
  );
END;
/

-- 4. 使用Calendaring表达式
BEGIN
  DBMS_SCHEDULER.CREATE_SCHEDULE(
    schedule_name => 'CUSTOM_CAL_SCH',
    start_date => SYSDATE,
    repeat_interval => 
      'FREQ=DAILY;' ||
      'BYDAY=MON,TUE,WED,THU,FRI;' ||
      'BYHOUR=9,10,11,14,15,16;' ||
      'BYMINUTE=0;' ||
      'BYSECOND=0;' ||
      'INTERVAL=1',
    comments => 'Business hours every hour from 9-12 and 14-17 on weekdays'
  );
END;
/

3.3 使用日历语法

-- 使用PL/SQL表达式定义Schedule
BEGIN
  DBMS_SCHEDULER.CREATE_SCHEDULE(
    schedule_name => 'ORACLE_EXPR_SCH',
    start_date => SYSDATE,
    repeat_interval => 
      'freq=weekly; ' ||
      'byday=sun,sat; ' ||
      'byhour=10; ' ||
      'byminute=0; ' ||
      'bymonth=jan,mar,may,jul,sep,nov; ' ||
      'bymonthday=1,15',
    comments => 'First and 15th of odd months on weekends at 10 AM'
  );
END;
/

-- 使用Calendaring语法
BEGIN
  DBMS_SCHEDULER.CREATE_SCHEDULE(
    schedule_name => 'FISCAL_YEAR_SCH',
    start_date => TO_TIMESTAMP_TZ('2026-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),
    repeat_interval => 
      'FREQ=YEARLY;' ||
      'BYMONTH=APR;' ||
      'BYMONTHDAY=1;' ||
      'BYHOUR=0;' ||
      'BYMINUTE=0',
    end_date => TO_TIMESTAMP_TZ('2030-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),
    comments => 'Start of fiscal year'
  );
END;
/

四、创建Jobs

4.1 基础Job创建

-- 1. 创建简单Job（使用内联Schedule）
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'PURGE_LOGS_JOB',
    job_type => 'STORED_PROCEDURE',
    job_action => 'PURGE_OLD_LOGS',
    schedule_name => 'DAILY_2AM_SCH',
    enabled => FALSE,
    auto_drop => FALSE,
    comments => 'Daily purge of old logs'
  );
END;
/

-- 2. 创建Job（内联Schedule）
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'GENERATE_REPORT_JOB',
    job_type => 'PLSQL_BLOCK',
    job_action => 'BEGIN INSERT_REPORT; END;',
    start_date => SYSDATE,
    repeat_interval => 'FREQ=DAILY;INTERVAL=1',
    end_date => ADD_MONTHS(SYSDATE, 12),
    enabled => TRUE,
    comments => 'Generate daily report'
  );
END;
/

-- 3. 使用Program创建Job
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'BACKUP_STATS_JOB',
    program_name => 'BACKUP_STATS_PROG',
    schedule_name => 'DAILY_2AM_SCH',
    enabled => FALSE,
    auto_drop => FALSE,
    job_style => 'REGULAR'
  );
END;
/

4.2 带参数的Job

-- 1. 创建带参数的Program
CREATE OR REPLACE PROCEDURE purge_logs_by_days(p_days IN NUMBER) AS
BEGIN
  DELETE FROM app_logs WHERE log_date < SYSDATE - p_days;
  COMMIT;
  DBMS_OUTPUT.put_line('Purged logs older than ' || p_days || ' days');
END purge_logs_by_days;
/

-- 2. 创建带参数的Program
BEGIN
  DBMS_SCHEDULER.CREATE_PROGRAM(
    program_name => 'PURGE_LOGS_BY_DAYS_PROG',
    program_type => 'STORED_PROCEDURE',
    program_action => 'PURGE_LOGS_BY_DAYS',
    number_of_arguments => 1,
    enabled => FALSE
  );
END;
/

-- 3. 设置参数默认值
BEGIN
  DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT(
    program_name => 'PURGE_LOGS_BY_DAYS_PROG',
    argument_position => 1,
    argument_name => 'P_DAYS',
    argument_type => 'NUMBER',
    default_value => '30'
  );
  DBMS_SCHEDULER.ENABLE('PURGE_LOGS_BY_DAYS_PROG');
END;
/

-- 4. 创建Job
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'PURGE_30_DAYS_JOB',
    program_name => 'PURGE_LOGS_BY_DAYS_PROG',
    schedule_name => 'WEEKLY_MON_SCH',
    job_arguments => ARRAY(30),
    enabled => TRUE
  );
END;
/

-- 5. 运行带参数的Job
BEGIN
  DBMS_SCHEDULER.RUN_JOB(
    job_name => 'PURGE_30_DAYS_JOB',
    use_current_session => FALSE
  );
END;
/

4.3 轻量级Job

-- 创建轻量级Job（高频执行场景）
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'HEALTH_CHECK_JOB',
    job_type => 'PLSQL_BLOCK',
    job_action => 
      'BEGIN 
        UPDATE health_check SET last_check = SYSDATE WHERE check_name = ''DB_STATUS'';
      END;',
    schedule_name => 'EVERY_15_MIN_SCH',
    enabled => TRUE,
    auto_drop => FALSE,
    job_style => 'LIGHTWEIGHT'
  );
END;
/

-- 查看轻量级Job
SELECT job_name, job_style, enabled, running_count
FROM dba_scheduler_jobs
WHERE job_style = 'LIGHTWEIGHT';

五、Job管理

5.1 Job控制

-- 1. 启用Job
BEGIN
  DBMS_SCHEDULER.ENABLE('PURGE_LOGS_JOB');
END;
/

-- 2. 禁用Job
BEGIN
  DBMS_SCHEDULER.DISABLE('PURGE_LOGS_JOB');
END;
/

-- 3. 立即运行Job
BEGIN
  DBMS_SCHEDULER.RUN_JOB(
    job_name => 'PURGE_LOGS_JOB',
    use_current_session => FALSE
  );
END;
/

-- 4. 停止运行中的Job
BEGIN
  DBMS_SCHEDULER.STOP_JOB(
    job_name => 'PURGE_LOGS_JOB',
    force => TRUE
  );
END;
/

-- 5. 删除Job
BEGIN
  DBMS_SCHEDULER.DROP_JOB(
    job_name => 'PURGE_LOGS_JOB',
    force => TRUE
  );
END;
/

5.2 Job监控

-- 1. 查看Job运行状态
SELECT job_name, job_type, status, enabled, run_count, failure_count
FROM dba_scheduler_jobs;

-- 2. 查看Job运行历史
SELECT job_name, status, actual_start_date, actual_duration, run_duration
FROM dba_scheduler_job_run_details
WHERE job_name = 'PURGE_LOGS_JOB'
ORDER BY actual_start_date DESC;

-- 3. 查看当前运行的Job
SELECT job_name, session_id, elapsed_time, cpu_used
FROM dba_scheduler_running_jobs;

-- 4. 查看Job执行统计
SELECT job_name, 
  COUNT(*) AS total_runs,
  SUM(CASE WHEN status = 'SUCCEEDED' THEN 1 ELSE 0 END) AS success_count,
  SUM(CASE WHEN status = 'FAILED' THEN 1 ELSE 0 END) AS failure_count,
  AVG(ROUND((actual_start_date - LOG_DATE) * 24 * 60, 2)) AS avg_delay_minutes
FROM dba_scheduler_job_run_details
GROUP BY job_name;

5.3 Job错误处理

-- 1. 查看Job错误
SELECT job_name, error#, error_message, actual_start_date
FROM dba_scheduler_job_run_details
WHERE status = 'FAILED'
ORDER BY actual_start_date DESC;

-- 2. 设置Job重试次数
BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE(
    name => 'PURGE_LOGS_JOB',
    attribute => 'max_failures',
    value => 3
  );
END;
/

-- 3. 设置Job失败时自动禁用
BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE(
    name => 'PURGE_LOGS_JOB',
    attribute => 'auto_drop',
    value => FALSE
  );
  DBMS_SCHEDULER.SET_ATTRIBUTE(
    name => 'PURGE_LOGS_JOB',
    attribute => 'restart_on_failure',
    value => TRUE
  );
END;
/

-- 4. 创建错误处理Job
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'PURGE_LOGS_ERROR_JOB',
    job_type => 'PLSQL_BLOCK',
    job_action => 
      'BEGIN
        INSERT INTO job_error_log (job_name, error_message, error_date)
        SELECT ''PURGE_LOGS_JOB'', error_message, SYSDATE
        FROM dba_scheduler_job_run_details
        WHERE job_name = ''PURGE_LOGS_JOB'' AND status = ''FAILED'';
      END;',
    schedule_name => 'DAILY_2AM_SCH',
    enabled => TRUE
  );
END;
/

六、Job Classes

6.1 创建和使用Job Class

-- 1. 创建Resource Consumer Group
BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
    consumer_group => 'BATCH_JOB_CG',
    comment => 'Consumer group for batch jobs'
  );
END;
/

-- 2. 创建Job Class
BEGIN
  DBMS_SCHEDULER.CREATE_JOB_CLASS(
    job_class_name => 'BATCH_JOB_CLASS',
    resource_consumer_group => 'BATCH_JOB_CG',
    service => NULL,
    logging_level => DBMS_SCHEDULER.LOGGING_FULL,
    log_history => 90,
    comments => 'Job class for batch processing jobs'
  );
END;
/

-- 3. 将Job分配到Class
BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE(
    name => 'PURGE_LOGS_JOB',
    attribute => 'job_class',
    value => 'BATCH_JOB_CLASS'
  );
END;
/

-- 4. 查看Job Class
SELECT job_class_name, resource_consumer_group, logging_level
FROM dba_scheduler_job_classes;

6.2 Job Class权限管理

-- 1. 授予执行权限
GRANT EXECUTE ON CLASS SYS.BATCH_JOB_CLASS TO app_user;

-- 2. 创建Job时指定Class
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'APP_REPORT_JOB',
    job_type => 'PLSQL_BLOCK',
    job_action => 'BEGIN GENERATE_APP_REPORT; END;',
    schedule_name => 'DAILY_2AM_SCH',
    job_class => 'BATCH_JOB_CLASS',
    enabled => TRUE
  );
END;
/

-- 3. 设置默认Job Class
BEGIN
  DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE(
    attribute => 'DEFAULT_JOB_CLASS',
    value => 'DEFAULT_JOB_CLASS'
  );
END;
/

七、Windows

7.1 创建和维护Window

-- 1. 创建时间窗口
BEGIN
  DBMS_SCHEDULER.CREATE_WINDOW(
    window_name => 'MAINTENANCE_WINDOW',
    resource_plan => 'MAINTENANCE_PLAN',
    start_date => SYSDATE,
    repeat_interval => 'FREQ=WEEKLY;BYDAY=SAT;BYHOUR=22;BYMINUTE=0;BY SECOND=0',
    duration => INTERVAL '4' HOUR,
    window_priority => 'LOW',
    comments => 'Weekly maintenance window on Saturday nights'
  );
END;
/

-- 2. 创建窗口组
BEGIN
  DBMS_SCHEDULER.CREATE_WINDOW_GROUP(
    group_name => 'WEEKEND_WINDOWS',
    window_list => 'SATURDAY_WINDOW, SUNDAY_WINDOW'
  );
END;
/

-- 3. 将Job与Window关联
BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE(
    name => 'REBUILD_INDEXES_JOB',
    attribute => 'window_group',
    value => 'WEEKEND_WINDOWS'
  );
END;
/

-- 4. 查看Window
SELECT window_name, resource_plan, start_date, duration, enabled
FROM dba_scheduler_windows;

7.2 Window与Job调度

-- 1. 在Window内执行Job
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'INDEX_REBUILD_JOB',
    job_type => 'PLSQL_BLOCK',
    job_action => 
      'BEGIN 
        FOR rec IN (SELECT index_name FROM user_indexes WHERE status = ''UNUSABLE'') LOOP
          EXECUTE IMMEDIATE ''ALTER INDEX '' || rec.index_name || '' REBUILD'';
        END LOOP;
      END;',
    window_name => 'MAINTENANCE_WINDOW',
    enabled => TRUE
  );
END;
/

-- 2. 打开Window
BEGIN
  DBMS_SCHEDULER.OPEN_WINDOW(
    window_name => 'MAINTENANCE_WINDOW',
    duration => INTERVAL '2' HOUR,
    force => TRUE
  );
END;
/

-- 3. 关闭Window
BEGIN
  DBMS_SCHEDULER.CLOSE_WINDOW(window_name => 'MAINTENANCE_WINDOW');
END;
/

-- 4. 查看Window历史
SELECT window_name, actual_start_date, actual_duration, window_priority
FROM dba_scheduler_window_details
ORDER BY actual_start_date DESC;

八、Chains（任务链）

8.1 创建Chain

-- 1. 创建Chain
BEGIN
  DBMS_SCHEDULER.CREATE_CHAIN(
    chain_name => 'DATA_LOAD_CHAIN',
    rule_set_name => NULL,
    evaluation_interval => NULL,
    comments => 'Chain for data loading process'
  );
END;
/

-- 2. 定义Chain步骤
BEGIN
  -- 步骤1: 提取数据
  DBMS_SCHEDULER.DEFINE_CHAIN_STEP(
    chain_name => 'DATA_LOAD_CHAIN',
    step_name => 'EXTRACT_STEP',
    program_name => 'EXTRACT_DATA_PROG'
  );

  -- 步骤2: 转换数据
  DBMS_SCHEDULER.DEFINE_CHAIN_STEP(
    chain_name => 'DATA_LOAD_CHAIN',
    step_name => 'TRANSFORM_STEP',
    program_name => 'TRANSFORM_DATA_PROG'
  );

  -- 步骤3: 加载数据
  DBMS_SCHEDULER.DEFINE_CHAIN_STEP(
    chain_name => 'DATA_LOAD_CHAIN',
    step_name => 'LOAD_STEP',
    program_name => 'LOAD_DATA_PROG'
  );
END;
/

-- 3. 定义Chain规则
BEGIN
  -- 初始规则: 开始提取
  DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
    chain_name => 'DATA_LOAD_CHAIN',
    condition => 'TRUE',
    action => 'START(''EXTRACT_STEP'')',
    rule_name => 'START_RULE'
  );

  -- 提取完成后开始转换
  DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
    chain_name => 'DATA_LOAD_CHAIN',
    condition => 'EXTRACT_STEP COMPLETED',
    action => 'START(''TRANSFORM_STEP'')',
    rule_name => 'TRANSFORM_RULE'
  );

  -- 转换完成后开始加载
  DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
    chain_name => 'DATA_LOAD_CHAIN',
    condition => 'TRANSFORM_STEP COMPLETED',
    action => 'START(''LOAD_STEP'')',
    rule_name => 'LOAD_RULE'
  );

  -- 加载完成后结束
  DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
    chain_name => 'DATA_LOAD_CHAIN',
    condition => 'LOAD_STEP COMPLETED',
    action => 'END(''SUCCESS'')',
    rule_name => 'SUCCESS_RULE'
  );

  -- 任何步骤失败则结束
  DBMS_SCHEDULER.DEFINE_CHAIN_RULE(
    chain_name => 'DATA_LOAD_CHAIN',
    condition => 'EXTRACT_STEP FAILED OR TRANSFORM_STEP FAILED OR LOAD_STEP FAILED',
    action => 'END(''FAILURE'')',
    rule_name => 'FAILURE_RULE'
  );
END;
/

-- 4. 启用Chain
BEGIN
  DBMS_SCHEDULER.ENABLE('DATA_LOAD_CHAIN');
END;
/

8.2 运行Chain

-- 1. 创建Chain Job
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'DATA_LOAD_CHAIN_JOB',
    job_type => 'CHAIN',
    job_action => 'DATA_LOAD_CHAIN',
    schedule_name => 'DAILY_2AM_SCH',
    enabled => TRUE,
    auto_drop => FALSE
  );
END;
/

-- 2. 立即运行Chain
BEGIN
  DBMS_SCHEDULER.RUN_CHAIN(
    chain_name => 'DATA_LOAD_CHAIN',
    start_steps => 'EXTRACT_STEP'
  );
END;
/

-- 3. 查看Chain运行状态
SELECT chain_name, step_name, status
FROM dba_scheduler_running_chain_steps;

SELECT chain_name, state
FROM dba_scheduler_running_jobs
WHERE job_name LIKE '%CHAIN%';

九、CentOS系统配置

9.1 Oracle Scheduler后台进程

# 1. 查看Scheduler进程
ps -ef | grep ora_j

# 2. 启动Scheduler（通常自动启动）
sqlplus / as sysdba
EXEC DBMS_SCHEDULER.SET_AGENT_PARAMETERS('NONE');

# 3. 检查Job Coordinator状态
SELECT * FROM v$scheduler_global_attribute WHERE attribute_name LIKE 'SCHEDULER%';

# 4. 配置Job Coordinator数量
BEGIN
  DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE(
    attribute => 'MAX_JOB_SLAVE_PROCESSES',
    value => 5
  );
END;
/

9.2 Cron辅助调度

# 1. 创建系统Cron（备用）
sudo crontab -e

# 添加：
# Oracle Scheduler备份
0 3 * * * /home/oracle/scripts/scheduler_backup.sh >> /home/oracle/logs/cron.log 2>&1

# 2. 创建脚本
cat > /home/oracle/scripts/scheduler_backup.sh << 'EOF'
#!/bin/bash
ORACLE_SID=orcl
ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1
export ORACLE_SID ORACLE_HOME

$ORACLE_HOME/bin/sqlplus -s / as sysdba << SQL
SET HEADING OFF
SPOOL /home/oracle/logs/scheduler_backup.log
@$ORACLE_HOME/rdbms/admin/exspwd.sql
SPOOL OFF
EXIT
SQL
EOF

chmod +x /home/oracle/scripts/scheduler_backup.sh

# 3. 启用Cron服务
sudo systemctl enable crond
sudo systemctl start crond

9.3 监控脚本

# 创建监控脚本
cat > /home/oracle/scripts/monitor_scheduler.sh << 'EOF'
#!/bin/bash
ORACLE_SID=orcl
ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1
export ORACLE_SID ORACLE_HOME

echo "=== Oracle Scheduler Status ===" 
echo "Date: $(date)"
echo ""

$ORACLE_HOME/bin/sqlplus -s / as sysdba << SQL
SET LINES 200
COLUMN job_name FORMAT A30
COLUMN status FORMAT A15
COLUMN enabled FORMAT A10

-- Job总数
SELECT COUNT(*) AS total_jobs FROM dba_scheduler_jobs;

-- 启用Job
SELECT COUNT(*) AS enabled_jobs FROM dba_scheduler_jobs WHERE enabled = 'TRUE';

-- 运行中的Job
SELECT COUNT(*) AS running_jobs FROM dba_scheduler_running_jobs;

-- 失败的Job
SELECT COUNT(*) AS failed_jobs 
FROM dba_scheduler_job_run_details 
WHERE status = 'FAILED' 
  AND actual_start_date > SYSDATE - 7;

-- 最近失败的Job
SELECT job_name, status, error#, actual_start_date
FROM dba_scheduler_job_run_details
WHERE status = 'FAILED'
ORDER BY actual_start_date DESC
FETCH FIRST 5 ROWS ONLY;

EXIT
SQL

echo "=== End of Report ==="
EOF

chmod +x /home/oracle/scripts/monitor_scheduler.sh

十、最佳实践与总结

10.1 安全最佳实践

-- 1. 限制Job创建权限
REVOKE CREATE ANY JOB FROM public;
GRANT CREATE JOB TO app_scheduler;

-- 2. 审计Job操作
BEGIN
  DBMS_AUDIT_MGMT.INIT_AUDIT_MGMT(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED);
END;
/

-- 3. 创建审计策略
CREATE AUDIT POLICY scheduler_audit
  ACTIONS CREATE JOB, ALTER JOB, DROP JOB;
AUDIT POLICY scheduler_audit;

10.2 性能优化

优化项 建议
Job数量 单实例不超过500个活跃Job
轻量级Job 高频Job使用轻量级
资源管理 合理分配Job Class
日志保留 设置合理的日志保留天数
评估间隔 避免过短的评估间隔

10.3 总结

CentOS上Oracle Scheduler定时任务配置包括：

  1. Program创建：存储过程、Shell脚本、PL/SQL块
  2. Schedule定义：简单频率、复杂频率、日历语法
  3. Job管理：创建、启用、禁用、监控
  4. Job Class：资源管理、权限控制
  5. Window：维护窗口、批量处理
  6. Chain：复杂任务流程编排
  7. 系统集成：CentOS Cron、监控脚本

核心要点：
– 使用DBMS_SCHEDULER替代DBMS_JOB
– 合理设计Schedule避免任务冲突
– 设置Job失败处理和重试机制
– 使用Job Class管理资源
– 定期清理Job运行日志
– 监控Job执行状态和性能

通过本文的指南，你可以在CentOS上构建完整的Oracle定时任务调度系统。

注：本文基于CentOS 7/8和Oracle 19c编写，具体配置可能因版本差异而略有不同。

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注