Oracle 自动化运维与监控

适用版本：Oracle Database 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

---

1. 概述

Oracle 自动化运维降低人工成本[1]：

自动化内容：

• 备份
• 统计收集
• 健康检查
• 监控告警
• 调度任务

---

2. 自动备份

2.1 RMAN 调度

# 脚本
#!/bin/bash
rman target / <<EOF
RUN {
  BACKUP DATABASE PLUS ARCHIVELOG;
  DELETE OBSOLETE;
}
EOF

2.2 调度

# crontab
0 2 * * * /u01/scripts/rman_backup.sh

2.3 OEM 调度

• 备份策略
• 自动调度
• 失败重试

详细见：Oracle RMAN 备份恢复。

---

3. 自动统计收集

3.1 自动任务

SELECT * FROM dba_autotask_client
WHERE client_name = 'auto optimizer stats collection';

3.2 启用

EXEC DBMS_AUTO_TASK_ADMIN.ENABLE(
  client_name => 'auto optimizer stats collection',
  operation => NULL,
  window_name => NULL
);

3.3 窗口

SELECT * FROM dba_autotask_window_clients;

详细见：Oracle 直方图与统计信息。

---

4. 自动健康检查

4.1 健康检查

-- HM
SELECT * FROM v$hm_run;
SELECT * FROM dba_hm_findings;

-- 运行
BEGIN
  DBMS_HM.RUN_CHECK('Database Check Health', 'my_run');
END;
/

-- 报告
SELECT DBMS_HM.GET_RUN_REPORT('my_run') FROM dual;

4.2 自定义脚本

CREATE OR REPLACE PROCEDURE health_check AS
BEGIN
  -- 表空间检查
  FOR t IN (SELECT tablespace_name, pct_used FROM ...) LOOP
    IF t.pct_used > 80 THEN
      INSERT INTO alert_log VALUES ('TABLESPACE', ...);
    END IF;
  END LOOP;
END;
/

-- 调度
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'health_check_job',
    job_type => 'STORED_PROCEDURE',
    job_action => 'health_check',
    repeat_interval => 'FREQ=HOURLY; INTERVAL=1',
    enabled => TRUE
  );
END;
/

详细见：Oracle 数据库健康检查。

---

5. 自动告警

5.1 Server-Generated Alerts

SELECT * FROM dba_outstanding_alerts;
SELECT * FROM dba_alert_history ORDER BY creation_time DESC;

5.2 阈值配置

BEGIN
  DBMS_SERVER_ALERT.SET_THRESHOLD(
    metrics_id => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,
    warning_operator => DBMS_SERVER_ALERT.OPERATOR_GE,
    warning_value => 80,
    critical_operator => DBMS_SERVER_ALERT.OPERATOR_GE,
    critical_value => 95,
    observation_period => 1,
    consecutive_occurrences => 1,
    instance_name => NULL,
    object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
    object_name => NULL
  );
END;
/

详细见：Oracle 监控告警配置。

---

6. 调度任务

6.1 DBMS_SCHEDULER

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'daily_report',
    job_type => 'STORED_PROCEDURE',
    job_action => 'generate_daily_report',
    start_date => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=6',
    enabled => TRUE
  );
END;
/

6.2 查看

SELECT * FROM dba_scheduler_jobs;
SELECT * FROM dba_scheduler_running_jobs;
SELECT * FROM dba_scheduler_job_run_details;

6.3 启用/禁用

EXEC DBMS_SCHEDULER.ENABLE('daily_report');
EXEC DBMS_SCHEDULER.DISABLE('daily_report');

---

7. 自动诊断

7.1 ADDM

SELECT * FROM dba_advisor_tasks WHERE advisor_name = 'ADDM';

-- 手动
@?/rdbms/admin/addmrpt.sql

7.2 Segment Advisor

EXEC DBMS_SPACE.AUTO_SPACE_ADVISOR_JOB_PROC;

7.3 SQL Tuning Advisor

SELECT * FROM dba_autotask_task 
WHERE client_name = 'sql tuning advisor';

详细见：Oracle 自动化诊断 ADDM/ASH/AWR。

---

8. 自动数据生命周期

8.1 ILM

-- 19c+ ILM
ALTER TABLE sales ILM ADD POLICY 
  TIER TO archive_tbs READ ONLY
  AFTER 30 DAYS OF NO MODIFICATION;

8.2 分区自动

-- Interval 分区
CREATE TABLE sales (...)
PARTITION BY RANGE (sale_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
(...);

---

9. OEM Cloud Control

9.1 监控

• 多数据库集中
• 告警
• 性能分析

9.2 自动化

• 备份调度
• 补丁
• 配置管理

---

10. 监控脚本

10.1 表空间监控

SELECT 
  tablespace_name,
  ROUND(used / total * 100, 2) AS pct
FROM (
  SELECT 
    df.tablespace_name,
    SUM(df.bytes) AS total,
    SUM(df.bytes - NVL(fs.bytes, 0)) AS used
  FROM dba_data_files df, dba_free_space fs
  WHERE df.file_id = fs.file_id(+)
  GROUP BY df.tablespace_name
)
WHERE used / total > 0.8;

10.2 锁监控

SELECT 
  blocking_session, 
  sid, 
  event, 
  seconds_in_wait
FROM v$session
WHERE blocking_session IS NOT NULL;

10.3 慢 SQL 监控

SELECT 
  sql_id, 
  elapsed_time / 1000000 AS sec, 
  status
FROM v$sql_monitor
WHERE status = 'EXECUTING'
  AND elapsed_time > 60 * 1000000
ORDER BY elapsed_time DESC;

---

11. 常见坑与排错

11.1 自动任务失败

-- 查看错误
SELECT * FROM dba_scheduler_job_run_details WHERE status = 'FAILED';

11.2 告警丢失

-- 1. 检查阈值
-- 2. 检查 MMON
-- 3. 检查 ADR

11.3 调度错过

SELECT * FROM dba_scheduler_job_run_details 
WHERE actual_start_date > scheduled_start_date + 1/24;

---

12. 最佳实践

1. 自动备份：基础
2. 自动统计：性能
3. 自动诊断：发现问题
4. 自动告警：及时
5. 调度任务：自动化
6. OEM 集中：多库
7. ILM：数据生命周期
8. 定期复审：有效性
9. 文档化：维护
10. 监控自动化：降低人工

---

13. 参考资料

[1] Oracle Database Administrator’s Guide 19c, “Automating Database Tasks” https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/automating-database-tasks.html