Oracle 监控告警配置

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

1. 概述

Oracle 提供多种监控告警机制[1]：

Server-Generated Alerts
Threshold Alerts
Event Alerts
OEM 告警

2. Server-Generated Alerts

2.1 概述

MMON 进程监控
AWR 基线
阈值告警

2.2 查看告警

SELECT 
  reason,
  metric_value,
  alert_type,
  status,
  creation_time
FROM dba_alert_history
ORDER BY creation_time DESC;

2.3 当前告警

SELECT 
  reason, 
  metric_value, 
  message_level
FROM dba_outstanding_alerts;

3. 阈值配置

3.1 表空间使用

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 => 'USERS'
  );
END;
/

3.2 其他指标

指标	说明
TABLESPACE_PCT_FULL	表空间使用率
SESSIONS_PCT	会话数
CPU_USAGE_PCT	CPU 使用
BLOCks_PER_SECOND	块/秒
RESPONSE_TIME_PER_TXN	事务响应时间

3.3 查看阈值

SELECT 
  metrics_name,
  warning_value,
  critical_value,
  status
FROM dba_thresholds;

4. 告警队列

4.1 订阅

-- 告警队列
SELECT * FROM dba_queues WHERE queue_name LIKE '%ALERT%';

4.2 查看

SELECT * FROM alert_que;

5. 常用监控指标

5.1 资源

-- CPU
SELECT metric_name, value FROM v$sysmetric 
WHERE metric_name LIKE '%CPU%'
ORDER BY begin_time DESC;

-- 内存
SELECT metric_name, value FROM v$sysmetric 
WHERE metric_name LIKE '%Memory%'
ORDER BY begin_time DESC;

5.2 性能

-- 响应时间
SELECT metric_name, value FROM v$sysmetric 
WHERE metric_name IN ('Response Time Per Txn', 'SQL Service Response Time')
ORDER BY begin_time DESC;

-- I/O
SELECT metric_name, value FROM v$sysmetric 
WHERE metric_name LIKE '%I/O%'
ORDER BY begin_time DESC;

5.3 容量

-- 表空间
SELECT 
  tablespace_name,
  ROUND(used / total * 100, 2) AS pct_used
FROM (
  SELECT 
    df.tablespace_name,
    SUM(df.bytes) AS total,
    SUM(df.bytes - fs.bytes) AS used
  FROM dba_data_files df, dba_free_space fs
  WHERE df.file_id = fs.file_id(+)
  GROUP BY df.tablespace_name
);

6. 自定义监控

6.1 自定义脚本

-- 表空间监控
CREATE OR REPLACE PROCEDURE check_tablespace AS
  v_pct NUMBER;
BEGIN
  FOR t IN (
    SELECT 
      df.tablespace_name,
      ROUND(SUM(df.bytes - fs.bytes) / SUM(df.bytes) * 100, 2) AS pct
    FROM dba_data_files df, dba_free_space fs
    WHERE df.file_id = fs.file_id(+)
    GROUP BY df.tablespace_name
  ) LOOP
    IF t.pct > 80 THEN
      INSERT INTO alert_log (alert_type, message, alert_time)
      VALUES ('TABLESPACE', 'Tablespace ' || t.tablespace_name || ' at ' || t.pct || '%', SYSDATE);
    END IF;
  END LOOP;
  COMMIT;
END;
/

6.2 调度

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'check_ts',
    job_type => 'STORED_PROCEDURE',
    job_action => 'check_tablespace',
    start_date => SYSTIMESTAMP,
    repeat_interval => 'FREQ=HOURLY; INTERVAL=1',
    enabled => TRUE
  );
END;
/

7. 邮件告警

7.1 配置 UTL_MAIL

@?/rdbms/admin/utlmail.sql

ALTER SYSTEM SET smtp_out_server = 'smtp.example.com:25' SCOPE=BOTH;

7.2 发送告警

CREATE OR REPLACE PROCEDURE send_alert(p_msg VARCHAR2) AS
BEGIN
  UTL_MAIL.SEND(
    sender => 'oracle@example.com',
    recipients => 'dba@example.com',
    subject => 'Oracle Alert',
    message => p_msg
  );
END;
/

8. OEM Cloud Control

8.1 告警规则

表空间使用
备份失败
性能下降
慢 SQL
锁等待

8.2 通知方式

邮件
短信
SNMP
HTTP

9. 监控指标建议

9.1 关键指标

指标	警告	严重
表空间	80%	95%
FRA	80%	95%
CPU	70%	90%
会话	80%	95%
锁等待	60s	300s
备份失败	-	立即

9.2 性能基线

AAS
平均响应时间
Top SQL 响应

10. 常见坑与排错

10.1 告警不触发

-- 1. 检查阈值
SELECT * FROM dba_thresholds;

-- 2. 检查 MMON
SELECT * FROM v$system_event WHERE event LIKE '%mmon%';

10.2 告警过多

-- 1. 调整阈值
-- 2. 调整 observation_period
-- 3. consecutive_occurrences

10.3 邮件失败

-- 1. 检查 SMTP
-- 2. 测试
EXEC UTL_MAIL.SEND(...);

11. 最佳实践

关键指标阈值：自动化
表空间 80/95：常规
邮件告警：及时
调度检查：定期
OEM 集中：多库
历史告警：分析
避免告警风暴：调优
响应流程：明确
定期复审：调整
文档化：维护

12. 参考资料

[1] Oracle Database Administrator’s Guide 19c, “Monitoring” https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/monitoring-database-operations.html