Skip to content

Oracle 监控数据管理

监控数据的类型

  • 性能监控数据:包括等待事件、SQL执行统计、资源使用率等
  • 空间监控数据:包括表空间使用、数据文件增长、空间分配等
  • 安全监控数据:包括审计日志、访问控制、权限变更等
  • 故障监控数据:包括告警日志、错误信息、故障诊断等
  • 配置监控数据:包括参数变更、配置修改、版本信息等

监控数据的来源

  • 动态性能视图:如v$session、v$sysstat、v$waitstat等
  • 数据字典视图:如dba_tablespaces、dba_data_files、dba_users等
  • 告警日志文件:记录数据库的关键事件和错误信息
  • 跟踪文件:记录SQL执行、进程活动等详细信息
  • AWR/ASH报告:自动工作负载仓库和活动会话历史报告

监控数据的作用

  • 性能优化:识别性能瓶颈,优化数据库性能
  • 故障预测:通过趋势分析预测潜在故障
  • 问题诊断:快速定位和解决数据库问题
  • 容量规划:基于历史数据预测资源需求
  • 安全审计:监控和审计数据库活动

监控数据收集

动态性能视图数据收集

常用动态性能视图

sql
-- 会话信息
SELECT * FROM v$session;

-- 系统统计信息
SELECT * FROM v$sysstat;

-- 等待事件
SELECT * FROM v$waitstat;

-- 数据库性能指标
SELECT * FROM v$sysmetric;

-- SQL执行统计
SELECT * FROM v$sql;

数据收集脚本

sql
-- 收集系统性能数据
CREATE OR REPLACE PROCEDURE collect_system_metrics AS
BEGIN
  INSERT INTO perf_metrics (collection_time, metric_name, metric_value)
  SELECT SYSDATE, 'CPU_USAGE', value
  FROM v$sysmetric
  WHERE metric_name = 'CPU Usage Per Sec';
  
  INSERT INTO perf_metrics (collection_time, metric_name, metric_value)
  SELECT SYSDATE, 'MEMORY_USAGE', value
  FROM v$sysmetric
  WHERE metric_name = 'Shared Pool Free %';
  
  COMMIT;
END;
/

AWR/ASH数据收集

AWR快照配置

sql
-- 查看当前AWR配置
SELECT * FROM dba_hist_wr_control;

-- 修改AWR快照间隔和保留时间
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
  retention => 7*24*60,  -- 7天
  interval => 30,         -- 30分钟
  topnsql => 100          -- 捕获前100个SQL
);

-- 手动创建AWR快照
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

ASH数据收集

sql
-- 查看ASH数据
SELECT * FROM v$active_session_history
ORDER BY sample_time DESC
FETCH FIRST 100 ROWS ONLY;

-- 创建ASH报告
SELECT output FROM TABLE(
  DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_TEXT(
    l_dbid => (SELECT dbid FROM v$database),
    l_inst_num => 1,
    l_begin_time => SYSDATE - 1/24,
    l_end_time => SYSDATE
  )
);

告警日志和跟踪文件收集

告警日志监控

sql
-- 查看告警日志位置
SELECT value FROM v$parameter WHERE name = 'background_dump_dest';

-- 使用外部表读取告警日志
CREATE DIRECTORY alert_log_dir AS 'D:\\ORADATA\\ORCL\\diag\\rdbms\\orcl\\ORCL\\trace';

CREATE TABLE alert_log (
  line_number NUMBER,
  log_text VARCHAR2(4000)
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY alert_log_dir
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY '|'
    MISSING FIELD VALUES ARE NULL
  )
  LOCATION ('alert_ORCL.log')
);

-- 查询告警日志中的错误
SELECT * FROM alert_log
WHERE log_text LIKE '%ERROR%' OR log_text LIKE '%WARNING%'
ORDER BY line_number DESC;

跟踪文件管理

sql
-- 查看跟踪文件位置
SELECT value FROM v$parameter WHERE name = 'user_dump_dest';

-- 为会话启用SQL跟踪
ALTER SESSION SET sql_trace = TRUE;

-- 使用10046事件进行详细跟踪
ALTER SESSION SET events '10046 trace name context forever, level 12';

-- 禁用跟踪
ALTER SESSION SET sql_trace = FALSE;
ALTER SESSION SET events '10046 trace name context off';

监控数据存储

本地存储

数据库表存储

sql
-- 创建性能监控表
CREATE TABLE perf_metrics (
  metric_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  collection_time TIMESTAMP(3),
  metric_name VARCHAR2(100),
  metric_value NUMBER,
  instance_number NUMBER,
  host_name VARCHAR2(100)
);

-- 创建索引
CREATE INDEX idx_perf_metrics_time ON perf_metrics(collection_time);
CREATE INDEX idx_perf_metrics_name ON perf_metrics(metric_name);

-- 分区表存储历史数据
CREATE TABLE perf_metrics_history (
  metric_id NUMBER,
  collection_time TIMESTAMP(3),
  metric_name VARCHAR2(100),
  metric_value NUMBER,
  instance_number NUMBER,
  host_name VARCHAR2(100)
)
PARTITION BY RANGE (collection_time) (
  PARTITION p_2023_Q1 VALUES LESS THAN (TO_DATE('2023-04-01', 'YYYY-MM-DD')),
  PARTITION p_2023_Q2 VALUES LESS THAN (TO_DATE('2023-07-01', 'YYYY-MM-DD')),
  PARTITION p_2023_Q3 VALUES LESS THAN (TO_DATE('2023-10-01', 'YYYY-MM-DD')),
  PARTITION p_2023_Q4 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
  PARTITION p_future VALUES LESS THAN (MAXVALUE)
);

文件系统存储

  • 配置日志归档目录:设置适当的目录结构存储日志文件
  • 日志轮换机制:定期轮换日志文件,避免单个文件过大
  • 压缩历史日志:对历史日志文件进行压缩,节省存储空间
  • 备份日志文件:定期备份重要的监控日志文件

远程存储

数据库链接存储

sql
-- 创建数据库链接
CREATE DATABASE LINK monitoring_db
CONNECT TO monitoring_user IDENTIFIED BY password
USING 'TARGET_DB';

-- 通过数据库链接存储监控数据
INSERT INTO perf_metrics@monitoring_db
SELECT * FROM perf_metrics;

第三方监控系统

  • Enterprise Manager:Oracle官方的监控管理平台
  • Zabbix:开源监控系统,支持Oracle数据库监控
  • Nagios:网络监控系统,可通过插件监控Oracle
  • Prometheus + Grafana:时序数据库和可视化平台

云存储

  • AWS CloudWatch:Amazon云平台的监控服务
  • Azure Monitor:Microsoft Azure的监控服务
  • Google Cloud Monitoring:Google云平台的监控服务

监控数据管理

数据保留策略

制定保留策略

  • 热数据:最近7-30天的数据,存储在高性能存储中
  • 温数据:30-90天的数据,存储在普通存储中
  • 冷数据:90天以上的数据,存储在归档存储中

数据清理脚本

sql
-- 清理过期的性能监控数据
CREATE OR REPLACE PROCEDURE purge_old_metrics AS
BEGIN
  -- 删除30天前的详细数据
  DELETE FROM perf_metrics
  WHERE collection_time < SYSDATE - 30;
  
  -- 将30-90天的数据移至历史表
  INSERT INTO perf_metrics_history
  SELECT * FROM perf_metrics
  WHERE collection_time BETWEEN SYSDATE - 90 AND SYSDATE - 30;
  
  DELETE FROM perf_metrics
  WHERE collection_time BETWEEN SYSDATE - 90 AND SYSDATE - 30;
  
  -- 删除90天前的历史数据
  DELETE FROM perf_metrics_history
  WHERE collection_time < SYSDATE - 90;
  
  COMMIT;
END;
/

-- 调度定期执行
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'PURGE_OLD_METRICS',
    job_type => 'STORED_PROCEDURE',
    job_action => 'purge_old_metrics',
    start_date => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=2',
    enabled => TRUE
  );
END;
/

数据压缩

表压缩

sql
-- 启用表压缩
ALTER TABLE perf_metrics_history COMPRESS FOR OLTP;

-- 对现有表进行压缩
ALTER TABLE perf_metrics_history MOVE COMPRESS FOR OLTP;

分区压缩

sql
-- 创建压缩分区
ALTER TABLE perf_metrics_history MODIFY PARTITION p_2023_Q1 COMPRESS FOR OLTP;

-- 为新分区启用压缩
ALTER TABLE perf_metrics_history SET PARTITIONING
  PARTITION BY RANGE (collection_time) (
    PARTITION p_2024_Q1 VALUES LESS THAN (TO_DATE('2024-04-01', 'YYYY-MM-DD')) COMPRESS FOR OLTP,
    PARTITION p_2024_Q2 VALUES LESS THAN (TO_DATE('2024-07-01', 'YYYY-MM-DD')) COMPRESS FOR OLTP
  );

数据备份

监控数据备份策略

  • 每日增量备份:备份当天的监控数据
  • 每周完整备份:备份所有监控数据
  • 备份到远程位置:确保备份数据的安全性
  • 测试备份恢复:定期测试备份的可恢复性

RMAN备份监控表

sql
-- 使用RMAN备份监控表空间
BACKUP TABLESPACE monitoring_ts;

-- 备份到磁带
BACKUP TABLESPACE monitoring_ts DEVICE TYPE sbt;

监控数据分析

性能分析

趋势分析

sql
-- 分析CPU使用率趋势
SELECT TO_CHAR(collection_time, 'YYYY-MM-DD') AS collection_date,
       AVG(CASE WHEN metric_name = 'CPU_USAGE' THEN metric_value END) AS avg_cpu,
       MAX(CASE WHEN metric_name = 'CPU_USAGE' THEN metric_value END) AS max_cpu
FROM perf_metrics
WHERE collection_time > SYSDATE - 30
GROUP BY TO_CHAR(collection_time, 'YYYY-MM-DD')
ORDER BY collection_date;

-- 分析SQL执行时间趋势
SELECT TO_CHAR(snap_time, 'YYYY-MM-DD') AS snap_date,
       AVG(elapsed_time/1000000) AS avg_exec_time_sec
FROM dba_hist_sqlstat hss,
     dba_hist_snapshot hs
WHERE hss.snap_id = hs.snap_id
AND hss.sql_id = 'abc123'
AND hs.begin_interval_time > SYSDATE - 30
GROUP BY TO_CHAR(snap_time, 'YYYY-MM-DD')
ORDER BY snap_date;

瓶颈识别

sql
-- 识别Top等待事件
SELECT event, total_waits, time_waited_micro/1000000 AS time_waited_sec
FROM v$system_event
WHERE event NOT LIKE 'SQL*Net%'
AND event NOT LIKE 'rdbms%'
ORDER BY time_waited_micro DESC
FETCH FIRST 10 ROWS ONLY;

-- 识别资源密集型SQL
SELECT sql_id, elapsed_time/executions/1000000 AS avg_exec_time_sec,
       buffer_gets/executions AS avg_buffer_gets,
       disk_reads/executions AS avg_disk_reads
FROM v$sql
WHERE executions > 5
ORDER BY elapsed_time/executions DESC
FETCH FIRST 10 ROWS ONLY;

空间分析

表空间增长分析

sql
-- 分析表空间增长趋势
SELECT TO_CHAR(collection_time, 'YYYY-MM-DD') AS collection_date,
       tablespace_name,
       AVG(used_percent) AS avg_used_percent
FROM tablespace_metrics
WHERE collection_time > SYSDATE - 60
GROUP BY TO_CHAR(collection_time, 'YYYY-MM-DD'), tablespace_name
ORDER BY collection_date, tablespace_name;

-- 预测表空间耗尽时间
SELECT tablespace_name,
       current_size_mb,
       used_mb,
       growth_rate_mb_per_day,
       ROUND((current_size_mb - used_mb) / growth_rate_mb_per_day, 2) AS days_until_full
FROM tablespace_growth
ORDER BY days_until_full;

数据文件分析

sql
-- 分析数据文件使用情况
SELECT file_name, tablespace_name,
       bytes/1024/1024 AS current_size_mb,
       maxbytes/1024/1024 AS max_size_mb,
       (bytes - free_bytes)/1024/1024 AS used_mb,
       ROUND((bytes - free_bytes)/bytes * 100, 2) AS used_percent
FROM (
  SELECT df.file_name, df.tablespace_name, df.bytes, df.maxbytes,
         NVL(fs.bytes, 0) AS free_bytes
  FROM dba_data_files df
  LEFT JOIN dba_free_space fs ON df.file_id = fs.file_id
);

安全分析

审计数据分析

sql
-- 分析失败的登录尝试
SELECT username, os_username, terminal, 
       to_char(timestamp, 'YYYY-MM-DD HH24:MI:SS') AS login_time,
       returncode
FROM dba_audit_session
WHERE returncode != 0
ORDER BY timestamp DESC;

-- 分析权限变更
SELECT grantee, privilege, admin_option,
       to_char(timestamp, 'YYYY-MM-DD HH24:MI:SS') AS change_time
FROM dba_audit_privilege
ORDER BY timestamp DESC;

异常访问检测

sql
-- 检测非工作时间的数据库访问
SELECT username, os_username, terminal, 
       to_char(timestamp, 'YYYY-MM-DD HH24:MI:SS') AS access_time
FROM dba_audit_session
WHERE TO_CHAR(timestamp, 'HH24') NOT BETWEEN '09' AND '18'
AND TO_CHAR(timestamp, 'D') NOT IN ('1', '7')  -- 排除周末
ORDER BY timestamp DESC;

-- 检测大量数据操作
SELECT username, os_username, 
       to_char(timestamp, 'YYYY-MM-DD HH24:MI:SS') AS operation_time,
       object_name, action_name,
       sql_text
FROM dba_audit_trail
WHERE action_name IN ('DELETE', 'UPDATE', 'INSERT')
AND row_count > 1000
ORDER BY timestamp DESC;

监控数据可视化

内置工具

Enterprise Manager

  • 性能页面:实时监控数据库性能指标
  • 图表视图:查看各种性能指标的趋势图表
  • 仪表盘:自定义监控仪表盘
  • 告警设置:配置基于阈值的告警

AWR报告

sql
-- 生成AWR报告
SELECT output FROM TABLE(
  DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
    l_dbid => (SELECT dbid FROM v$database),
    l_inst_num => 1,
    l_bid => 100,
    l_eid => 110
  )
);

第三方工具

Grafana

  • 数据源配置:配置Oracle作为数据源
  • 仪表板创建:创建自定义监控仪表板
  • 告警设置:配置基于阈值的告警
  • 报表导出:导出监控报表

Tableau

  • 数据连接:连接Oracle数据库
  • 交互式仪表板:创建交互式监控仪表板
  • 数据钻取:支持从汇总数据钻取到详细数据
  • 计划刷新:定期刷新监控数据

Power BI

  • Oracle连接器:连接Oracle数据库
  • 可视化报表:创建丰富的可视化报表
  • 实时连接:支持实时监控数据
  • 移动应用:通过移动设备查看监控数据

监控数据自动化

自动收集

调度作业

sql
-- 创建自动收集性能数据的作业
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'COLLECT_PERF_METRICS',
    job_type => 'STORED_PROCEDURE',
    job_action => 'collect_system_metrics',
    start_date => SYSTIMESTAMP,
    repeat_interval => 'FREQ=MINUTELY; INTERVAL=15',
    enabled => TRUE
  );
END;
/

-- 创建自动收集表空间数据的作业
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'COLLECT_TABLESPACE_METRICS',
    job_type => 'STORED_PROCEDURE',
    job_action => 'collect_tablespace_metrics',
    start_date => SYSTIMESTAMP,
    repeat_interval => 'FREQ=HOURLY',
    enabled => TRUE
  );
END;
/

事件触发

sql
-- 创建DDL触发器监控 schema 变更
CREATE OR REPLACE TRIGGER monitor_ddl
AFTER DDL ON DATABASE
DECLARE
  l_operation VARCHAR2(100);
BEGIN
  l_operation := ORA_SYSEVENT;
  
  INSERT INTO ddl_audit (
    audit_time,
    operation,
    object_type,
    object_name,
    username,
    os_user
  ) VALUES (
    SYSTIMESTAMP,
    l_operation,
    ORA_DICT_OBJ_TYPE,
    ORA_DICT_OBJ_NAME,
    ORA_LOGIN_USER,
    SYS_CONTEXT('USERENV', 'OS_USER')
  );
END;
/

自动分析

异常检测

sql
-- 创建异常检测存储过程
CREATE OR REPLACE PROCEDURE detect_anomalies AS
  l_avg_cpu NUMBER;
  l_stddev_cpu NUMBER;
  l_current_cpu NUMBER;
BEGIN
  -- 计算CPU使用率的平均值和标准差
  SELECT AVG(metric_value), STDDEV(metric_value)
  INTO l_avg_cpu, l_stddev_cpu
  FROM perf_metrics
  WHERE metric_name = 'CPU_USAGE'
  AND collection_time > SYSDATE - 7;
  
  -- 获取当前CPU使用率
  SELECT metric_value
  INTO l_current_cpu
  FROM perf_metrics
  WHERE metric_name = 'CPU_USAGE'
  ORDER BY collection_time DESC
  FETCH FIRST 1 ROW ONLY;
  
  -- 检测异常(超过2个标准差)
  IF ABS(l_current_cpu - l_avg_cpu) > 2 * l_stddev_cpu THEN
    INSERT INTO anomaly_alert (
      alert_time,
      metric_name,
      current_value,
      expected_value,
      deviation
    ) VALUES (
      SYSTIMESTAMP,
      'CPU_USAGE',
      l_current_cpu,
      l_avg_cpu,
      (l_current_cpu - l_avg_cpu) / l_stddev_cpu
    );
    
    COMMIT;
  END IF;
END;
/

-- 调度异常检测作业
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'DETECT_ANOMALIES',
    job_type => 'STORED_PROCEDURE',
    job_action => 'detect_anomalies',
    start_date => SYSTIMESTAMP,
    repeat_interval => 'FREQ=MINUTELY; INTERVAL=30',
    enabled => TRUE
  );
END;
/

自动报告

sql
-- 创建自动生成性能报告的存储过程
CREATE OR REPLACE PROCEDURE generate_performance_report AS
  l_report CLOB;
BEGIN
  -- 生成AWR报告
  SELECT output INTO l_report
  FROM TABLE(
    DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
      l_dbid => (SELECT dbid FROM v$database),
      l_inst_num => 1,
      l_bid => (SELECT MAX(snap_id) - 10 FROM dba_hist_snapshot),
      l_eid => (SELECT MAX(snap_id) FROM dba_hist_snapshot)
    )
  );
  
  -- 存储报告
  INSERT INTO performance_reports (
    report_time,
    report_type,
    report_content
  ) VALUES (
    SYSTIMESTAMP,
    'AWR',
    l_report
  );
  
  COMMIT;
END;
/

-- 调度每周生成报告
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'GENERATE_WEEKLY_REPORT',
    job_type => 'STORED_PROCEDURE',
    job_action => 'generate_performance_report',
    start_date => SYSTIMESTAMP,
    repeat_interval => 'FREQ=WEEKLY; BYDAY=MON; BYHOUR=8',
    enabled => TRUE
  );
END;
/

常见问题(FAQ)

Q1: 监控数据收集对数据库性能有影响吗?

A1: 监控数据收集会对数据库性能产生一定影响,但可以通过以下方法最小化:

  • 合理设置收集频率,避免过于频繁的收集
  • 使用采样技术,只收集代表性数据
  • 限制收集的数据量,只收集必要的指标
  • 在低峰期进行大量数据收集
  • 使用异步收集方式,减少对主业务的影响

Q2: 如何选择合适的监控数据存储方式?

A2: 选择监控数据存储方式应考虑以下因素:

  • 数据量:大量数据适合使用时序数据库
  • 查询需求:频繁查询适合使用关系型数据库
  • 存储成本:长期存储适合使用压缩和归档
  • 扩展性:考虑未来数据增长的扩展性
  • 集成需求:与现有监控系统的集成需求

Q3: 如何制定合理的监控数据保留策略?

A3: 制定监控数据保留策略应考虑:

  • 业务需求:根据业务对历史数据的需求
  • 法规要求:符合行业法规对数据保留的要求
  • 存储成本:平衡数据价值和存储成本
  • 性能影响:过多的历史数据会影响查询性能
  • 备份策略:与备份策略相协调

Q4: 如何处理监控数据量增长过快的问题?

A4: 处理监控数据量增长过快的方法:

  • 实施数据采样,减少原始数据量
  • 增加数据聚合,存储汇总数据而非详细数据
  • 优化数据模型,使用分区表和压缩
  • 制定更严格的数据保留策略
  • 考虑使用专门的时序数据库存储监控数据
  • 定期归档和清理过期数据

Q5: 如何确保监控数据的安全性?

A5: 确保监控数据安全性的方法:

  • 实施访问控制,限制对监控数据的访问
  • 对敏感监控数据进行加密存储
  • 审计对监控数据的访问和修改
  • 定期备份监控数据
  • 实施网络隔离,保护监控系统
  • 确保监控数据传输的加密

Q6: 如何使用监控数据预测数据库故障?

A6: 使用监控数据预测故障的方法:

  • 建立性能基线,识别偏离正常模式的行为
  • 分析趋势数据,预测资源耗尽的时间
  • 监控关键指标的变化率,识别异常增长
  • 建立预测模型,基于历史数据预测故障
  • 实施早期预警系统,在问题扩大前发出告警
  • 定期分析AWR/ASH报告,识别潜在问题

Q7: 如何集成不同来源的监控数据?

A7: 集成不同来源监控数据的方法:

  • 建立统一的数据仓库,集中存储所有监控数据
  • 使用ETL工具提取、转换和加载数据
  • 建立数据映射,确保不同来源数据的一致性
  • 使用数据湖存储原始数据,按需处理
  • 采用标准化的数据模型,统一不同来源的数据结构
  • 使用API集成第三方监控系统

Q8: 如何优化监控数据的查询性能?

A8: 优化监控数据查询性能的方法:

  • 使用适当的索引,加速查询
  • 实施分区表,减少查询范围
  • 使用物化视图,预计算汇总数据
  • 优化SQL语句,避免全表扫描
  • 考虑使用内存数据库存储热数据
  • 定期收集统计信息,帮助优化器生成更好的执行计划

Q9: 如何应对监控系统本身的故障?

A9: 应对监控系统故障的方法:

  • 实施监控系统的冗余,避免单点故障
  • 定期备份监控配置和数据
  • 建立监控系统的监控机制(元监控)
  • 制定监控系统故障的应急响应计划
  • 确保监控数据的离线存储,即使监控系统故障也能保留数据
  • 定期测试监控系统的恢复流程

Q10: 如何衡量监控数据管理的效果?

A10: 衡量监控数据管理效果的指标:

  • 数据完整性:监控数据的完整性和准确性
  • 数据可用性:监控数据的可访问性和响应时间
  • 存储效率:数据压缩率和存储利用率
  • 查询性能:监控数据查询的响应时间
  • 故障检测率:通过监控数据检测到的故障比例
  • 预测准确性:基于监控数据的预测准确性
  • 成本效益:监控投资与获得的收益比
  • 合规性:符合行业法规和内部政策的程度