★ FEATURED ARTICLE 1. 项目背景与核心价值最近在梳理企业级数据库监控方案时发现很多团队还在用传统的脚本邮件报警方式管理Oracle数据库。这种模式不仅实时性差而且当DBA需要同时管理几十个实例时很难快速定位性能瓶颈。于是花了两个周末时间用GrafanaOracleDB Exporter搭建了一套开箱即用的监控解决方案。这套方案最大的特点是用docker-compose实现了一键部署所有组件包括OracleDB Exporter采集Oracle性能指标Prometheus时间序列数据库Grafana可视化大屏配套的Dashboard模板实测下来单个节点从部署到出图不超过15分钟监控指标延迟控制在10秒内。下面分享具体实现过程和踩坑记录。2. 环境准备与架构设计2.1 基础环境要求操作系统建议Ubuntu 20.04/CentOS 7Docker版本20.10.0docker-compose版本1.29.0硬件配置以监控5个Oracle实例为例CPU4核内存8GB磁盘50GB建议SSD注意OracleDB Exporter需要通过Oracle Instant Client连接数据库要求被监控的Oracle版本在11gR2以上。2.2 组件通信架构graph LR A[OracleDB] --|SQL查询| B(OracleDB Exporter) B --|HTTP暴露metrics| C(Prometheus) C --|数据查询| D(Grafana)实际部署时每个Oracle实例需要配置一个Exporter。Prometheus通过scrape_configs动态发现这些Exporter。3. 关键组件配置详解3.1 OracleDB Exporter配置先准备配置文件oracledb-exporter/config.ymldefault: user: monitor_user password: secure_password connection_string: host:port/service_name max_idle_conns: 5 max_open_conns: 10 query_timeout: 30对应的Docker启动参数version: 3 services: oracledb-exporter: image: iamseth/oracledb_exporter volumes: - ./config.yml:/config.yml environment: - DATA_SOURCE_NAMEmonitor_user/secure_passwordhost:port/service_name ports: - 9161:9161避坑提示Oracle账户需要额外授予SELECT_CATALOG_ROLE和CREATE SESSION权限否则会报ORA-01031错误。3.2 Prometheus配置要点关键配置在prometheus/prometheus.yml中scrape_configs: - job_name: oracledb static_configs: - targets: [oracledb-exporter:9161] metrics_path: /metrics scrape_interval: 15s对于多实例监控建议使用文件服务发现scrape_configs: - job_name: oracledb file_sd_configs: - files: - /etc/prometheus/oracle_targets.json relabel_configs: - source_labels: [__address__] target_label: instance3.3 Grafana Dashboard导入推荐使用ID 3333的官方Oracle仪表板包含这些关键面板实例状态UP/DOWN表空间使用率会话数趋势SQL执行时间Top 10等待事件统计导入命令docker exec -it grafana grafana-cli plugins install grafana-piechart-panel4. docker-compose全量配置完整版的docker-compose.yml示例version: 3.7 services: prometheus: image: prom/prometheus ports: - 9090:9090 volumes: - ./prometheus:/etc/prometheus command: - --config.file/etc/prometheus/prometheus.yml grafana: image: grafana/grafana ports: - 3000:3000 volumes: - grafana-storage:/var/lib/grafana depends_on: - prometheus oracledb-exporter: image: iamseth/oracledb_exporter ports: - 9161:9161 environment: - DATA_SOURCE_NAMEsystem/passwordoracle:1521/ORCLCDB volumes: - ./oracledb-exporter:/config volumes: grafana-storage:启动命令docker-compose up -d5. 性能优化实战技巧5.1 指标采集优化通过修改Exporter的SQL查询频率降低负载custom_metrics: - name: tablespace_usage help: Tablespace usage metrics query: | SELECT tablespace_name, used_percent FROM dba_tablespace_usage_metrics interval: 3005.2 Prometheus存储优化调整prometheus.yml中的保留策略storage: tsdb: retention: 15d chunk_encoding: xor5.3 Grafana告警配置示例表空间使用率超过90%触发告警{ alert: { name: Oracle Tablespace Alert, conditions: [ { evaluator: { params: [90], type: gt } } ] } }6. 生产环境部署建议6.1 高可用方案建议的部署架构----------------- | Load Balancer | ---------------- | -------------------------------- | | -------------- ------------------ | Prometheus A | | Prometheus B | -------------- ------------------ | | -------------------------------- | ---------------- | Shared Storage | -----------------6.2 安全加固措施Exporter启用TLSoracledb-exporter: environment: - TLS_KEY_FILE/certs/key.pem - TLS_CRT_FILE/certs/cert.pemGrafana配置域账号集成[auth.generic_oauth] enabled true client_id your_client_id client_secret your_client_secret auth_url https://auth.example.com/oauth2/authorize token_url https://auth.example.com/oauth2/token api_url https://auth.example.com/oauth2/resource7. 常见问题排查指南7.1 Exporter连接失败错误现象ORA-12541: TNS:no listener排查步骤检查Oracle监听状态lsnrctl status验证tnsping连通性tnsping ORCLCDB检查防火墙规则iptables -L -n | grep 15217.2 指标缺失问题典型场景表空间指标未采集解决方法确认监控账户权限GRANT SELECT ON dba_tablespaces TO monitor_user;检查Exporter日志docker logs oracledb-exporter7.3 Prometheus存储异常错误日志msgError replaying WAL errcorruption after 12345678 bytes修复方案docker exec -it prometheus prometheus --storage.tsdb.retention.time12h8. 监控指标深度解析8.1 关键性能指标指标名称阈值参考采集频率oracledb_sessions_active300警告15soracledb_wait_time_ratio0.3警告30soracledb_tablespace_used_pct85%警告5m8.2 自定义指标开发示例监控长时间运行事务SELECT COUNT(*) as long_tx_count FROM v$transaction WHERE (SYSDATE - START_DATE) * 24 * 60 30在Exporter配置中添加custom_metrics: - name: long_transactions help: Count of transactions running longer than 30 minutes query: | SELECT COUNT(*) as value FROM v$transaction WHERE (SYSDATE - START_DATE) * 24 * 60 30 interval: 609. 扩展功能实现9.1 自动发现Oracle实例使用Consul实现服务发现scrape_configs: - job_name: oracledb consul_sd_configs: - server: consul:8500 services: [oracle-exporter]9.2 与Zabbix集成通过Grafana Webhook将告警转发到Zabbiximport requests import json zabbix_payload { request: sender data, data: [ { host: oracle_host, key: grafana.alert, value: alert_message } ] } requests.post(zabbix_url, datajson.dumps(zabbix_payload))9.3 历史数据分析配置Prometheus远程存储到VictoriaMetricsremote_write: - url: http://victoriametrics:8428/api/v1/write queue_config: capacity: 10000 max_shards: 3010. 维护与升级策略10.1 版本升级流程备份Grafana数据库docker exec grafana pg_dump -U grafana grafana_backup.sql更新docker-compose.yml中的镜像版本执行滚动更新docker-compose pull docker-compose up -d10.2 日常维护任务建议的crontab任务0 3 * * * docker system prune -f 0 4 * * * docker exec prometheus promtool check rules /etc/prometheus/rules.yml10.3 性能调优记录实测数据监控10个Oracle实例配置项默认值优化值效果Prometheus内存2GB4GBOOM减少60%Exporter连接池515超时减少75%Grafana刷新间隔30s60sCPU负载降低40%
阅读完成 · 觉得有帮助？
