prometheus监测oracle

青年数据库学习互助会 2024-12-19

1098

点击上方蓝字，关注我们

想学会更多实用技巧，欢迎加入青学会MOP技术社区（实名社区）。

加入方法：公众号后台回复关键字“加入”获取小助手微信，添加后登记入会。

同时欢迎大家在评论区留言互动交流！社区会不定期举行相关的抽奖、公开分享活动。

如果你有想了解的知识点希望我们发文可以后台私信。

正文开始

oracledb_exporter下载地址https://github.com/iamseth/oracledb_exporter

oracledb_exporter我认为最大的价值就是除开发者定义的监控指标外，可以通过自己的SQL，自定义监控指标。因为是Prometheus是采用拉取方式获得数据，所以不建议写查询耗时较长的SQL，否则可能会在TOPSQL中看到这条SQL。因为时序数据库的特点，不建议使用太多标签，会影响时序数据库性能。

配置过程

1、以oracle 11g rac为测试环境，下载后上传到每个节点目录，解压，并授权oracle:oinstall用户权限

[root@rac2-2 oracle]# ls
buding  oracledb_exporter.tar.gz  oradiag_oracle  pfile.ora
[root@rac2-2 oracle]# tar -zxvf oracledb_exporter.tar.gz 
oracledb_exporter-0.5.1.linux-amd64/
oracledb_exporter-0.5.1.linux-amd64/oracledb_exporter
[root@rac2-2 oracle]# chown -R oracle:oinstall oracledb_exporter
oracledb_exporter-0.5.1.linux-amd64/ oracledb_exporter.tar.gz             
[root@rac2-2 oracle]# chown -R oracle:oinstall oracledb_exporter-0.5.1.linux-amd64/
[root@rac2-2 oracle]# ls -rtl
total 5244
drwxr-xr-x. 2 oracle oinstall   4096 Sep 19 03:32 oracledb_exporter-0.5.1.linux-amd64
-rwxr-xr-x. 1 root  root     5351623 Oct 25 14:05 oracledb_exporter.tar.gz

2、运行前先看下oracledb_exporter的环境变量示例

# export Oracle location:
export DATA_SOURCE_NAME=oracle://system:password@oracle-sid
# or using a complete url:
export DATA_SOURCE_NAME=oracle://user:password@myhost:1521/service
# 19c client for primary/standby configuration
export DATA_SOURCE_NAME=oracle://user:password@primaryhost:1521,standbyhost:1521/service
# 19c client for primary/standby configuration with options
export DATA_SOURCE_NAME=oracle://user:password@primaryhost:1521,standbyhost:1521/service?connect_timeout=5&transport_connect_timeout=3&retry_count=3
# 19c client for ASM instance connection (requires SYSDBA)
export DATA_SOURCE_NAME=oracle://user:password@primaryhost:1521,standbyhost:1521/+ASM?as=sysdba
# Then run the exporter
/path/to/binary/oracledb_exporter --log.level error --web.listen-address 0.0.0.0:9161

3、运行exporter每个节点分别执行。

#先定义一个环境变量 DATA_SOURCE_NAME=oracle://username:password@host:1521/service
[oracle@rac2-1:/home/oracle/oracledb_exporter-0.5.1.linux-amd64]$export DATA_SOURCE_NAME=oracle://system:oracle@192.168.1.71:1521/dgorcl
#直接运行
[oracle@rac2-1:/home/oracle/oracledb_exporter-0.5.1.linux-amd64]$./oracledb_exporter 
ts=2023-10-22T02:49:38.760Z caller=default_metrics.go:81 level=error therewasanissuewhileloadingspecifieddefaultmetricsfileat:default-metrics.toml,proceedingtorunwithdefaultmetrics.="open default-metrics.toml: no such file or directory"
ts=2023-10-22T02:49:38.760Z caller=collector.go:262 level=error Errorpingingoracle:="missing port in address"
ts=2023-10-22T02:49:38.760Z caller=main.go:70 level=info msg="Starting oracledb_exporter" version="(version=, branch=, revision=95e16f21ceeeaf5d0f3599c0ee324e84492d1318)"
ts=2023-10-22T02:49:38.760Z caller=main.go:71 level=info msg="Build context" build="(go=go1.19.4, platform=linux/amd64, user=, date=, tags=unknown)"
ts=2023-10-22T02:49:38.760Z caller=main.go:72 level=info msg="Collect from: " metricPath=/metrics
ts=2023-10-22T02:49:38.761Z caller=tls_config.go:274 level=info msg="Listening on" address=[::]:9161
ts=2023-10-22T02:49:38.761Z caller=tls_config.go:277 level=info msg="TLS is disabled." http2=false address=[::]:9161

4、浏览器访问http://IP:9161/metrics ，可以拉取到exporter推送的数据

5、将exporter添加到Prometheus,编辑修改Prometheus配置文件，新增

oracledb_exporter
- job_name: "oracle"
   # metrics_path defaults to '/metrics'
   # scheme defaults to 'http'.
    static_configs:
     - targets: ["192.168.1.71:9161"]
     - targets: ["192.168.1.72:9161"]

6、向Prometheus发送信号，重新加载配置文件

killall -HUP prometheus

7、查看Prometheus页面是否识别到新增的两个监测

8、访问grafana网站，下载grafana模板https://grafana.com/grafana/dashboards/?plcmt=footer

9、下载后，在grafana中导入模板

10、选择数据源为Prometheus

11、按照示例自定义一个查询

[oracle@rac2-2:/home/oracle/oracledb_exporter-0.5.1.linux-amd64]$vi my-custom-metrics.toml
#注意格式和缩进,否则会报错，
#单行查询测试
[[metric]]
context = "session"
request = "SELECT (select count(*) from v$session where status='ACTIVE') as act_sess,(select count(*) from v$session where status='INACTIVE') as inact_sess,(select count(*) from v$lock) as lck FROM DUAL"
metricsdesc = { act_sess = "active会话", inact_sess = "inactive会话",lck = "count v$lock" }
#多行查询，定义一个lable,有多少列字符型的字段，就定义多个lable,因为Prometheus只能存储数值类型的指标，标签不要定义太多，否则数据量会增加并且查询效果下降。
[[metric]]
context = "asm_diskgroup"
labels = [ "name" ]
metricsdesc = { total = "Total size of ASM disk group.", free = "Free space available on ASM disk group." }
request = "SELECT name,total_mb*1024*1024 as total,free_mb*1024*1024 as free FROM v$asm_diskgroup_stat where exists (select 1 from v$datafile where name like '+%')"
ignorezeroresult = true

12、执行exporter并使用自定义metric文件

[oracle@rac2-1:/home/oracle/oracledb_exporter-0.5.1.linux-amd64]$export CUSTOM_METRICS=my-custom-metrics.toml
[oracle@rac2-1:/home/oracle/oracledb_exporter-0.5.1.linux-amd64]$./oracledb_exporter

13、查看自定义采集的数据