数据库监控：生产 Oracle 数据库异常，如何快速实现业务洞察

大家好，我是小斐呀。

由于维护的存储坏了，金蝶依赖的 Oracle 也出了小问题，同时最近有个星球好友总是在问我，如何监控 Oracle 数据库，我前段时间很忙，都是在拒绝他，最近他又来问了我好几次，就特意抽了个晚上把 Oracle 这块监控给捋了一下，现在可以分享出来给各位道友食用，那天用到了可以拿出来参考参考一下。

环境

还是老样子哦，不管网络、数据库、中间件还是其他上层应用，指标监控统一还是使用 Prometheus 体系，针对 Oracle 数据库监控告警架构具体如下所示：

这里我们需要先部署好相关架构插件，比如时序库，告警引擎等等，这里就不细说时序库和告警引擎的二进制安装或者 docker 部署，又或者 K8s 部署（可直接去专栏中查看详细部署过程），我主要展开说明采集器的部署和使用，以及指标如何采集。

Categraf 采集器

如果你想使用 Categraf 来监控 Oracle 的话，那么需要部署采集器，我将通过脚本的方式去部署：

# 通过脚本下载官网标准版本
wget https://gitee.com/robotneo/script/raw/master/install-categraf.sh && chmod +x install-categraf.sh && ./install-categraf.sh --debug

# 通过脚本下载官网 cgo 版本
wget https://gitee.com/robotneo/script/raw/master/install-categraf-cgo.sh && chmod +x install-categraf-cgo.sh && ./install-categraf-cgo.sh --debug

# 状态 重启 停止
sudo systemctl daemon-reload
sudo systemctl restart categraf.service
sudo systemctl status categraf.service
sudo systemctl stop categraf.service

由于下载资源是通过官网的链接下载的，而官网版本一般都会比开源站点上的版本慢 2 个小版本，故如果你要更新最新版本，可执行下面命令进行更新：

# 切换到安装了 categraf 目录下
cd /opt/categraf
# 获取最新版本号
latest_version=$(curl -s https://api.github.com/repos/flashcatcloud/categraf/releases/latest | grep "tag_name" | cut -d '"' -f 4)
# 执行更新命令 这里更新链接使用了开源站点的最新版本链接
./categraf --update --update_url https://github.com/flashcatcloud/categraf/releases/download/$latest_version/categraf-$latest_version-linux-amd64.tar.gz

以上就是安装 Categraf 最新版本的具体步骤，可以根据你部署的路径修改脚本进行安装。

OracleDB Exporter 采集器

如果你想用 OracleDB Exporter 采集器，那么就部署这个采集器，这个采集器现在由 Oracle 官方维护，故我们可以大胆使用了，下面我将继续使用脚本进行部署：

部署之前需要安装 Oracle 基础版本的即时客户端 依赖，必须规范选择 Client 和 Server 的版本支持兼容关系（ Instant Client ODBC 19c 可连接至 Oracle Database 11.2 或更高版本）：

我测试的实际环境 Oracle 的最低版本是 11g 故需要客户端兼容的话并且支持更多高版本，我必须下载 Instant Client 19c 这个版本：

下载好后，上传对应的 Instant Client 19c 基础包到 OracleDB Exporter 采集器部署的服务器上：

# 上传 instantclient 基础包并解压
unzip instantclient-basic-linux.x64-19.26.0.0.0dbru.zip

# 解压后有个 instantclient_19_26 目录移动到 /opt/oracle/ 目录下
mkdir -pv /opt/oracle
mv instantclient_19_26 /opt/oracle/

截止这篇文章发布时， oracledb_exporter 最新版本为 1.5.5 ，故我基于这个版本来做演示：

数据库中创建个单独的监控用户：

# 创建一个普通权限的监控用户
CREATE USER monitor IDENTIFIED BY <YOUR-PASSWORD>;
CREATE USER monitor IDENTIFIED BY qweNykl001133;
# 直接授予 SELECT_CATALOG_ROLE 角色
GRANT SELECT_CATALOG_ROLE TO monitor;

或者单独授予授予每个表/视图的 SELECT 权限：

GRANT CONNECT TO monitor;
GRANT SELECT ON SYS.GV_$RESOURCE_LIMIT to monitor;
GRANT SELECT ON SYS.V_$SESSION to monitor;
GRANT SELECT ON SYS.V_$WAITCLASSMETRIC to monitor;
GRANT SELECT ON SYS.GV_$PROCESS to monitor;
GRANT SELECT ON SYS.GV_$SYSSTAT to monitor;
GRANT SELECT ON SYS.V_$DATAFILE to monitor;
GRANT SELECT ON SYS.V_$ASM_DISKGROUP_STAT to monitor;
GRANT SELECT ON SYS.V_$SYSTEM_WAIT_CLASS to monitor;
GRANT SELECT ON SYS.DBA_TABLESPACE_USAGE_METRICS to monitor;
GRANT SELECT ON SYS.DBA_TABLESPACES to monitor;
GRANT SELECT ON SYS.GLOBAL_NAME to monitor;

安装 oracledb_exporter 服务：

# 下载二进制包并解压 网络不行自行解决 电脑魔法后下载上传
cd /opt/oracle
wget https://github.com/oracle/oracle-db-appdev-monitoring/releases/download/1.5.5/oracledb_exporter-1.5.5.linux-amd64.tar.gz
tar -zxvf oracledb_exporter-1.5.5.linux-amd64.tar.gz 
# 设置二进制文件目录为 oracledb_exporter
mv oracledb_exporter-1.5.5.linux-amd64 oracledb_exporter
# 创建用户
sudo groupadd oracledb_exporter
sudo useradd -g oracledb_exporter -s /bin/bash -M oracledb_exporter
# 设置 systemd 服务
cat <<EOF > /etc/systemd/system/oracledb_exporter.service
[Unit]
Description=Service for oracle telemetry client
After=network-online.target

[Service]
Type=simple
User=oracledb_exporter
Group=oracledb_exporter
Environment="DB_USERNAME=monitor"
Environment="DB_PASSWORD=qweNykl001133"
Environment="DB_CONNECT_STRING=172.17.40.108:1521/easdb"
# Environment="DB_ROLE=SYSOPER"
Environment="ORACLE_HOME=/opt/oracle/instantclient_19_26"
Environment="LD_LIBRARY_PATH=/opt/oracle/instantclient_19_26"
# Environment="TNS_ADMIN=/opt/oracle/instantclient_19_26/network/admin"
WorkingDirectory=/opt/oracle/oracledb_exporter
ExecStart=/opt/oracle/oracledb_exporter/oracledb_exporter --web.telemetry-path="/metrics" --default.metrics="./default-metrics.toml" --query.timeout=5 --log.destination="./alert.log" --web.listen-address=:9161 --log.level=info --log.format=logfmt 
KillMode=process
RemainAfterExit=no
Restart=on-failure
RestartSec=5s

[Install]
WantedBy=multi-user.target
EOF

# 权限设置
sudo chown -R oracledb_exporter:oracledb_exporter /opt/oracle

oracledb_exporter 的参数说明：

--log.disable=0                     # 0 开启告警日志 1 关闭告警日志
--log.interval=15s                  # 日志更新间隔
--database.maxIdleConns=0           # 连接池中最大空闲连接数
--database.maxOpenConns=10          # 连接池中最大打开连接数
--database.poolIncrement=-1         # 当连接池达到最大容量，连接的增量数
--database.poolMaxConnections=-1    # 连接池中的最大连接数
--database.poolMinConnections=-1    # 连接池中的最小连接数
--custom.metrics=""# 自定义指标采集文件

目前版本只支持 一对一 的采集，后续版本官方说会支持 多对一 的采集。

认证参数说明：

DB_USERNAME：数据库用户名
DB_PASSWORD：数据库用户密码
DB_CONNECT_STRING：数据库连接信息
DB_ROLE：数据库用户角色 推荐创建普通只读用户
ORACLE_HOME：Oracle Instant Client 目录
TNS_ADMIN：用于指定 Oracle 客户端网络配置文件的位置

默认的指标采集配置文件 default-metrics.toml ：

[[metric]]
context="sessions"
labels=["status","type"]
metricsdesc={value="Gauge metric with count of sessions by status and type."}
request="SELECT status, type, COUNT(*) as value FROM v$session GROUP BY status, type"

[[metric]]
context="resource"
labels=["resource_name"]
metricsdesc={current_utilization="Generic counter metric from v$resource_limit view in Oracle (current value).",limit_value="Genericcountermetricfromv$resource_limitviewinOracle(UNLIMITED:-1)."}
request='''
SELECT resource_name, current_utilization, CASE WHEN TRIM(limit_value) LIKE 'UNLIMITED'THEN'-1'ELSETRIM(limit_value)ENDaslimit_value
FROMv$resource_limit
'''
ignorezeroresult = true

[[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

[[metric]]
context="activity"
metricsdesc={value="Genericcountermetricfromv$sysstatviewinOracle."}
fieldtoappend="name"
request="SELECT name, value FROM v$sysstat WHERE name IN ('parse count (total)', 'execute count', 'user commits', 'user rollbacks')"

[[metric]]
context="process"
metricsdesc={count="Gaugemetricwithcountofprocesses."}
request="SELECT COUNT(*) as count FROM v$process"

[[metric]]
context="wait_time"
labels=["wait_class","con_id"]
metricsdesc={time_waited_sec_total="countermetricfromsystem_wait_classviewinOracle."}
metricstype={time_waited_sec_total="counter"}
fieldtoappend="wait_class"
request='''
select
  wait_class,
  round(time_waited/100,3) time_waited_sec_total,
  con_id
from v$system_wait_class
where wait_class <> 'Idle'
'''
ignorezeroresult = true

[[metric]]
context = "tablespace"
labels = [ "tablespace", "type" ]
metricsdesc = { bytes = "Generic counter metric of tablespaces bytes in Oracle.", max_bytes = "Generic counter metric of tablespaces max bytes in Oracle.", free = "Generic counter metric of tablespaces free bytes in Oracle.", used_percent = "Gauge metric showing as a percentage of how much of the tablespace has been used." }
request = '''
SELECT
dt.tablespace_nameastablespace,
dt.contentsastype,
dt.block_size*dtum.used_spaceasbytes,
dt.block_size*dtum.tablespace_sizeasmax_bytes,
dt.block_size*(dtum.tablespace_size-dtum.used_space)asfree,
dtum.used_percent
FROMdba_tablespace_usage_metricsdtum,dba_tablespacesdt
WHEREdtum.tablespace_name=dt.tablespace_name
anddt.contents!='TEMPORARY'
union
SELECT
dt.tablespace_nameastablespace,
'TEMPORARY'astype,
dt.tablespace_size-dt.free_spaceasbytes,
dt.tablespace_sizeasmax_bytes,
dt.free_spaceasfree,
((dt.tablespace_size-dt.free_space)/dt.tablespace_size)
FROMdba_temp_free_spacedt
orderbytablespace
'''

[[metric]]
context = "db_system"
labels = [ "name" ]
metricsdesc = { value = "Database system resources metric" }
request = '''
selectname,value
fromv$parameter
wherenamein('cpu_count','sga_max_size','pga_aggregate_limit')
'''

[[metric]]
context = "db_platform"
labels = [ "platform_name" ]
metricsdesc = { value = "Database platform" }
request = '''
SELECTplatform_name,1asvalueFROMv$database
'''

[[metric]]
context = "top_sql"
labels = [ "sql_id", "sql_text" ]
metricsdesc = { elapsed = "SQL statement elapsed time running" }
request = '''
select*from(
selectsql_id,elapsed_time/1000000aselapsed,SUBSTRB(REPLACE(sql_text,'',''),1,55) as sql_text
from   V$SQLSTATS
order by elapsed_time desc
) where ROWNUM <= 15
'''
ignorezeroresult=true
# scrapeinterval = "5m"
# The previous line is an example of changing the interval at which this one metric
# will be scraped. You may wish to do this to scrape a metric less often, if the SQL
# statement to collect that metric places more load on your database instance than 
# desired when it is run at every scrape.

[[metric]]
context="cache_hit_ratio"
labels=["cache_hit_type"]
metricsdesc={value="Cache Hit Ratio"}
request='''
select metric_name cache_hit_type, value
from v$sysmetric
where group_id=2 and metric_id in (2000,2050,2112,2110)
'''
ignorezeroresult=true

其实这里可以看出来，原理就是构建指标查询 SQL 语句，构建语法如下：

在 toml 文件下写好指标 [[metric]] 然后每个 [[metric]] 下有几个字段配置构建一个完整的指标采集：

[[metric]]
context = "example_context"# 用于构建指标的上下文（FQN的一部分）
labels = ["label1", "label2"]  # 指标标签，必须与查询中的列名匹配
metricsdesc = { field1 = "description1", field2 = "description2" }  # 字段和注释的映射
metricstype = { field1 = "gauge", field2 = "counter" }  # 字段与 Prometheus 指标类型的映射
metricsbuckets = { field1 = { "bucket1" = "value1", "bucket2" = "value2" } }  # 为直方图指标根据值分桶
fieldtoappend = "extra_field"# 要附加到指标FQN的字段（可选）
request = "SELECT field1, field2 FROM my_table WHERE condition"# 用于抓取数据的 SQL 查询
ignorezeroresult = true# 如果查询没有结果是否输出错误，默认为false
querytimeout = "10s"# 查询超时时间（如 300ms, 0.5h 等）
scrapeinterval = "30s"# 自定义指标的抓取间隔，若未提供 scrape.interval 时使用此值

自定义单个指标查询配置字段解释：

运行启动：

# 重新加载 systemd 配置并启动服务
systemctl daemon-reload
systemctl start oracledb_exporter
systemctl enable oracledb_exporter

错误修复：

# 错误问题修复1
ORA-00904: "CON_ID": invalid identifier

[[metric]]
context = "wait_time"
labels = ["wait_class","con_id"]
metricsdesc = { time_waited_sec_total="counter metric from system_wait_class view in Oracle." }
metricstype = { time_waited_sec_total = "counter" }
fieldtoappend= "wait_class"
request = '''
select
  wait_class,
  round(time_waited/100,3) time_waited_sec_total,
  con_id
from v$system_wait_class
where wait_class <> 'Idle'
'''
ignorezeroresult = true

## CON_ID 是 Oracle 从 12c 开始引入的字段，用于支持多租户架构（CDB/PDB）。而 Oracle 11g 并不支持这个字段。
## oracledb_exporter 使用的 default-metrics.toml 中包含了对 CON_ID 的查询语句，这是造成报错的直接原因。

# 错误问题修复2
# level=ERROR source=alertlog.go:113 msg="Error querying the alert logs"

# 因为 11g 不支持 v$diag_alert_ext，查询三次如果失败，默认不在继续启用 alertlog 模块。

该采集器正常启动：

使用浏览器或 curl 请求一下 oracledb_exporter 看看是否能拿到默认指标：

curl 172.17.40.25:9161/metrics

抓取配置

当 oracledb_exporter 安装并正常采集后，下一步就需要使用 vmagent 配置抓取任务：

scrape_configs:
# oracle 
-job_name:'oracle-exporter'
metrics_path:'/metrics'
scrape_interval:15s
scrape_timeout:10s
static_configs:
-targets:
-172.17.40.25:9161

采集正常：

在 default-metrics.toml 中定义的查询 SQL 最终都转化为 Prometheus 的指标，后续动作就是通过这些指标去 Grafana 实现指标数据可视化，构建监控面板和数据库告警。

Categraf 采集 Oracle

由于采集 Oracle 都依赖 Instant Client 故在 Categraf 采集 Oracle 数据库的过程中也需要指定 Instant Client 这是 Oracle 官方提供的 lib 库。

依赖下载看 oracledb_exporter 中的说明，这里把环境变量加到 categraf.service 服务下：

# 编辑 categraf.service 文件
vim /etc/systemd/system/categraf.service
# 添加环境变量
Environment="LD_LIBRARY_PATH=/opt/oracle/instantclient_19_26"

[Unit]
Description=Opensource telemetry collector
ConditionFileIsExecutable=/opt/categraf/categraf
After=network-online.target  
Wants=network-online.target 

[Service]
Environment="LD_LIBRARY_PATH=/opt/oracle/instantclient_19_26"
StandardOutput=journal
StandardError=journal
StartLimitInterval=3600
StartLimitBurst=10
ExecStart=/opt/categraf/categraf "-configs""/opt/categraf/conf"
WorkingDirectory=/opt/categraf
ExecReload=/bin/kill -HUP "$MAINPID"
Restart=on-failure
RestartSec=120
EnvironmentFile=-/etc/sysconfig/categraf
KillMode=process
[Install]
WantedBy=multi-user.target

切换到 Oracle 采集插件目录下，编辑对应的采集配置文件：

# 切换到 Categraf 下的 Oracle 采集插件目录
cd /opt/categraf/conf/input.oracle
# 目录下有两个采集配置文件
metric.toml  oracle.toml

oracle.toml 是配置连接地址， metric.toml 是配置查询监控数据的 SQL 语句配置，下面展开说明下配置字段的含义：

• mesurement: 自定义的一个指标前缀
• request: SQL 语句
• label_fields: SQL 查到的内容，会有多列，哪些列作为时序数据的 label
• metric_fields: SQL 查到的内容，会有多列，哪些列作为时序数据的值
• field_to_append: 是否要把某列的内容附到指标名称里
• timeout: SQL 执行的超时时间

有些字段可以为空，如果 mesurement 、 metric_fields 、 field_to_append 三个字段都配置了，会把这三部分拼成 metric 的最终指标名称。

单个 Categraf 可以采集多个目标实例，是一对多的关系，故我们可以在 oracle.toml 配置文件中配置多个 instances 实现单个 Categraf 采集多个 Oracle 数据库实例。

# collect interval
interval = 15

[[instances]]
# 数据库实例
address = "172.17.40.108:1521/easdb"
# 数据库普通监控用户
username = "monitor"
# 数据库监控用户密码
password = "qweNykl001133"
# 用户是否是 DBA 角色
is_sys_dba = false
# 用户是否是 OPER 角色
is_sys_oper = false
# 关闭连接池
disable_connection_pool = false
# 连接池中最大打开连接数
max_open_connections = 5
# interval = global.interval * interval_times
interval_times = 1
# 实例级别的全局标签
labels = { region="local" }

测试采集命令：

# 导入临时变量加载 Instant Client Lib 库 
export LD_LIBRARY_PATH=/opt/oracle/instantclient_19_26
# 执行采集测试
/opt/categraf/categraf --test --debug --inputs oracle

在 [[instances]] 配置下可以单独针对这个实例做指标的查询：

[[instances.metrics]]
mesurement = "sessions"
label_fields = [ "status", "type" ]
metric_fields = [ "value" ]
timeout = "3s"
request = '''
SELECT status, type, COUNT(*) as value FROM v$session GROUP BY status, type
'''

如何理解：有 scope 限制

就是每个 instances 实例下面可以配置 SQL ，这些 SQL 只生效到对应的实例中。 metrics.toml 配置文件中的 SQL 是针对 oracle.toml 所有的 instances 目标，某个实例特殊的配置则配置在 instances 下面，这个特殊配置段 [[instances.metrics]] 只对 [[instances]] 配置段生效。

这里面默认的 SQL 查询的指标还是比较少，不满足实际生产监控需求，故针对生产需求，我下面将补充更多指标查询。

新增指标

基于 oracledb_exporter 采集器我新增的查询 SQL 指标如下 default-metrics.toml 文件：

# 基于 oracledb_exporter 采集器采集 Oracle 数据库实例指标配置文件
# 指标名称 oracledb_sessions_value{status="labelvalue01", type="labelvalue02"} 说明：表示当前会话状态和类型的会话数
# oracledb_<context>_<metricsdesc>
[[metric]]
context = "sessions"
labels = [ "status", "type" ]
metricsdesc = { value = "Gauge metric with count of sessions by status and type." }
# metricstype = { value = "gauge" }
request = "SELECT status, type, COUNT(*) as value FROM v$session GROUP BY status, type"

# 指标名称  oracledb_lock_cnt 说明：当前被锁定对象的总数
[[metric]]
context = "lock"
metricsdesc = { cnt = "Number of currently locked database objects." }
request = '''
SELECT COUNT(*) AS cnt
  FROM ALL_OBJECTS A, V$LOCKED_OBJECT B, SYS.GV_$SESSION C
 WHERE A.OBJECT_ID = B.OBJECT_ID
   AND B.PROCESS = C.PROCESS
'''

# 用于统计数据库中最近 5 分钟 SQL 的慢查询 P95 和 P99 分位数
# oracle_slow_queries_p95_time_usecs
# oracle_slow_queries_p99_time_usecs
[[metric]]
context = "slow_queries"
metricsdesc = { p95_time_usecs = "95th percentile of SQL statement elapsed time (in microseconds) during the last 5 minutes.", p99_time_usecs = "99th percentile of SQL statement elapsed time (in microseconds) during the last 5 minutes." }
request = '''
select  percentile_disc(0.95)  within group (order by elapsed_time) as p95_time_usecs, 
  percentile_disc(0.99)  within group (order by elapsed_time) as p99_time_usecs 
from v$sql where last_active_time >= sysdate - 5/(24*60)
'''

# oracledb_resource_current_utilization{resource_name="labelvalue01"} 说明：表示当前资源使用率
# oracledb_resource_limit_value{resource_name="labelvalue01"} 说明：系统允许的最大值限制 -1 表示无限制
[[metric]]
context = "resource"
labels = [ "resource_name" ]
metricsdesc = { current_utilization = "Generic counter metric from v$resource_limit view in Oracle (current value).", limit_value="Generic counter metric from v$resource_limit view in Oracle (UNLIMITED: -1)." }
# metricstype = { current_utilization = "counter", limit_value = "counter" }
request = '''
  SELECT 
    resource_name,
    current_utilization,
    CASE 
      WHEN TRIM(limit_value) LIKE 'UNLIMITED' THEN '-1' 
      ELSE TRIM(limit_value) 
    END as limit_value 
  FROM v$resource_limit
'''
# 如果查询没有返回结果 即忽略错误 默认不忽略错误
ignorezeroresult = true

# oracledb_asm_diskgroup_total{diskgroup_name="labelvalue01"} 说明：表示ASM磁盘组的总大小
# oracledb_asm_diskgroup_free{diskgroup_name="labelvalue01"} 说明：表示ASM磁盘组的可用大小
[[metric]]
context = "asm_diskgroup"
labels = [ "name" ]
metricsdesc = { total = "Total size of ASM disk group.", free = "Free space available on ASM disk group." }
# metricstype = { total = "gauge", free = "gauge" }
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

# oracledb_activity_parse_count_total 说明：表示当前解析的SQL语句数
# oracledb_activity_execute_count 说明：表示当前执行的SQL语句数
# oracledb_activity_user_commits 说明：表示当前提交的事务数
# oracledb_activity_user_rollbacks 说明：表示当前回滚的事务数
[[metric]]
context = "activity"
metricsdesc = { value = "Generic counter metric from v$sysstat view in Oracle." }
# fieldtoappend 把查询结果中的某一列的值作为指标名拼接
fieldtoappend = "name"
request = '''
  SELECT name, value 
  FROM v$sysstat 
  WHERE name IN (
    'parse count (total)', 
    'execute count', 
    'user commits', 
    'user rollbacks'
  )
'''

# oracledb_process_count 说明：Oracle 数据库中正在运行的进程的总数
[[metric]]
context = "process"
metricsdesc = { count = "Gauge metric with count of processes." }
request = "SELECT COUNT(*) as count FROM v$process"

# oracledb_wait_time_value{wait_class="System I/O"} 说明：表示当前 System I/O 等待的时间
# 11g 及以下版本
[[metric]]
context = "wait_time"
metricsdesc = { value = "Generic counter metric from v$waitclassmetric view in Oracle." }
fieldtoappend= "wait_class"
request = '''
SELECT
  n.wait_class as WAIT_CLASS,
  round(m.time_waited/m.INTSIZE_CSEC,3) as VALUE
FROM
  v$waitclassmetric  m, v$system_wait_class n
WHERE
  m.wait_class_id=n.wait_class_id AND n.wait_class != 'Idle'
'''
ignorezeroresult = true

# 12c 及以上版本
[[metric]]
context = "wait_time"
labels = [ "wait_class", "con_id" ]
metricsdesc = { time_waited_sec_total = "counter metric from system_wait_class view in Oracle." }
metricstype = { time_waited_sec_total = "counter" }
fieldtoappend= "wait_class"
request = '''
select
  wait_class,
  round(time_waited/100,3) time_waited_sec_total,
  con_id
from v$system_wait_class
where wait_class <> 'Idle'
'''
ignorezeroresult = true

# oracledb_tablespace_bytes{tablespace="labelvalue01",type="labelvalue02"} 说明：表示表空间的字节数
# oracledb_tablespace_max_bytes{tablespace="labelvalue01",type="labelvalue02"} 说明：表示表空间的最大字节数
# oracledb_tablespace_free{tablespace="labelvalue01",type="labelvalue02"} 说明：表示表空间的可用字节数
# oracledb_tablespace_used_percent{tablespace="labelvalue01",type="labelvalue02"} 说明：表示表空间的使用百分比
[[metric]]
context = "tablespace"
labels = [ "tablespace", "type" ]
metricsdesc = { bytes = "Generic counter metric of tablespaces bytes in Oracle.", max_bytes = "Generic counter metric of tablespaces max bytes in Oracle.", free = "Generic counter metric of tablespaces free bytes in Oracle.", used_percent = "Gauge metric showing as a percentage of how much of the tablespace has been used." }
request = '''
SELECT
    dt.tablespace_name as tablespace,
    dt.contents as type,
    dt.block_size * dtum.used_space as bytes,
    dt.block_size * dtum.tablespace_size as max_bytes,
    dt.block_size * (dtum.tablespace_size - dtum.used_space) as free,
    dtum.used_percent
FROM  dba_tablespace_usage_metrics dtum, dba_tablespaces dt
WHERE dtum.tablespace_name = dt.tablespace_name
and dt.contents != 'TEMPORARY'
union
SELECT
    dt.tablespace_name as tablespace,
    'TEMPORARY' as type,
    dt.tablespace_size - dt.free_space as bytes,
    dt.tablespace_size as max_bytes,
    dt.free_space as free,
    ((dt.tablespace_size - dt.free_space) / dt.tablespace_size)
FROM  dba_temp_free_space dt
order by tablespace
'''

# oracledb_db_system_value{name="cpu_count"}    说明：采集数据库 CPU 核数   整数
# oracledb_db_system_value{name="sga_max_size"} 说明：SGA 配置的大小    单位：字节
# oracledb_db_system_value{name="pga_aggregate_limit"}  说明：PGA 配置的大小    单位：字节
[[metric]]
context = "db_system"
labels = [ "name" ]
metricsdesc = { value = "Database system resources metric" }
request = '''
select name, value 
from v$parameter 
where name in ('cpu_count', 'sga_max_size', 'pga_aggregate_limit')
'''

# # oracledb_db_platform_value{platform_name="labelvalue01"} 说明：表示数据库平台名称
# [[metric]]
# context = "db_platform"
# labels = [ "platform_name" ]
# metricsdesc = { value = "Database platform" }
# request = '''
# SELECT platform_name, 1 as value FROM v$database
# '''

# oracledb_top_sql_elapsed{sql_id="labelvalue01",sql_text="labelvalue02"} 说明：前 15 条最耗时的 SQL
[[metric]]
context = "top_sql"
labels = [ "sql_id", "sql_text" ]
metricsdesc = { elapsed = "SQL statement elapsed time running" }
request = '''
select * from (
select sql_id, elapsed_time / 1000000 as elapsed, SUBSTRB(REPLACE(sql_text,'',''),1,55) as sql_text
from   V$SQLSTATS
order by elapsed_time desc
) where ROWNUM <= 15
'''
ignorezeroresult = true
# 设置为每 5 分钟查一次，避免影响数据库性能
scrapeinterval = "5m"

# oracledb_cache_hit_ratio_value{cache_hit_type="Buffer Cache Hit Ratio"} 说明：表示当前 Buffer Cache 命中率
# oracledb_cache_hit_ratio_value{cache_hit_type="Library Cache Hit Ratio"} 说明：表示当前 Library Cache 命中率
# oracledb_cache_hit_ratio_value{cache_hit_type="Result Cache Hit Ratio"} 说明：表示当前 Result Cache 命中率
[[metric]]
context = "cache_hit_ratio"
labels = [ "cache_hit_type" ]
metricsdesc = { value = "Cache Hit Ratio" }
request = '''
select metric_name cache_hit_type, value
from v$sysmetric
where group_id=2 and metric_id in (2000,2050,2112,2110)
'''
ignorezeroresult = true

# oracledb_parameter_sessions_value 说明：当前 Oracle 设置的最大并发 session 数
[[metric]]
context = "parameter_sessions"
metricsdesc = { value = "Gauge metric with sessions parameter" }
request = "select value  from v$parameter t where t.name = 'sessions'"

# oracledb_env_is_rac{host_name="hostname"} 说明：表示当前 Oracle 是否为 RAC 环境 1 启用 RAC 0 未启用 RAC
[[metric]]
context = "env"
labels = [ "host_name" ]
metricsdesc = { is_rac = "if 1 then rac." }
request = '''
select host_name,
decode((select value from v$option where parameter = 'Real Application Clusters'), 'TRUE',1,0) as is_rac
from v$instance
'''

# oracledb_env_running_time{version="labelvalue01"} 说明：表示当前 Oracle 版本和运行时间
[[metric]]
context = "env"
labels = [ "version" ]
metricsdesc = { running_time = "Gauge metric representing the time elapsed since the database instance started, along with its version." }
request = '''
SELECT version, trunc(sysdate - startup_time) AS running_time FROM v$instance
'''

# oracledb_env_value{platform_name="labelvalue01",log_mode="labelvalue02",force_logging="labelvalue03",flashback_on="labelvalue04",database_role="labelvalue05",open_mode="labelvalue06",nls_characterset="labelvalue07"} 说明：表示当前 Oracle 环境设置
[[metric]]
context = "env"
labels = [ "platform_name", "log_mode", "force_logging", "flashback_on", "database_role", "open_mode", "nls_characterset" ]
metricsdesc = { value = "Gauge metric indicating current Oracle database environment settings." }
request = '''
select platform_name, log_mode, force_logging, flashback_on, database_role, open_mode,
(select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET') as nls_characterset, 1 as value 
from v$database
'''

# oracledb_env_redo_value{name="group_num"} 说明：表示当前 redo log group 数量
# oracledb_env_redo_value{name="member_num"}    说明：表示当前 redo log member 数量
# oracledb_env_redo_value{name="log_size"}  说明：表示当前 redo log group 的大小
[[metric]]
context = "env"
labels = [ "name" ]
metricsdesc = { redo_value = "Gauge metric exposing redo log group count, member count, and log file size (MB)." }
request = '''
with temp as(
  select 
    count(group#) as group_num,
    min(members) as member_num,
    min(bytes/1024/1024) as log_size 
  from v$log
)
select  'group_num' as name ,group_num as redo_value from temp
union all
select  'member_num' as name ,member_num as redo_value from temp
union all
select  'log_size' as name ,log_size as redo_value from temp
'''

# oracledb_env_redo_switch_value 说明：表示当前 redo log 切换的最大序列号
[[metric]]
context = "env"
metricsdesc = { redo_switch_value = "Gauge metric showing the current maximum redo log switch sequence number." }
request = '''
select max(sequence#) as redo_switch_value from v$log
'''

# oracledb_env_vols 所有数据库段的总大小 单位：字节
[[metric]]
context = "env"
metricsdesc = { vols = "Gauge metric showing total size (in bytes) of all database segments." }
request = '''
select to_char(sum(bytes)) as vols from dba_segments
'''

# oracledb_events_value{event_name="labelvalue01"} 说明：获取各个事件的计数值
[[metric]]
context = "events"
labels = [ "event_name"]
metricsdesc = { value = "Gauge metric showing the count of each wait event from v$session_wait." }
request = '''
select event as event_name, count(*) as value
   from v$session_wait
      where wait_time >= -1 group by event order by 2 desc
'''

# oracledb_db_time_value 说明：表示当前数据库的 DB time DB time 主要反映了数据库处理请求的总时间
[[metric]]
context = "db_time"
metricsdesc = { value = "Generic counter metric from v$sys_time_model view in Oracle." }
request = '''
select value from v$sys_time_model t where t.STAT_NAME = 'DB time'
'''

# oracledb_sysmetric_.* 说明：Oracle 数据库的系统性能指标
[[metric]]
context = "sysmetric"
metricsdesc = { value = "Get sysmetrics from v$sysmetric view in Oracle." }
fieldtoappend = "metric_name"
request = '''
select replace(metric_name,'%','') as metric_name ,value from v$sysmetric  where group_id = 2 and metric_name != 'Average Synchronous Single-Block Read Latency'
'''

# 说明：Oracle IOPS 和 MBPS 计算
# oracledb_sysstat_oracle_iops{metric_name="labelvalue01"} 说明：表示当前 Oracle IOPS
# oracledb_sysstat_oracle_mbps{metric_name="labelvalue01"} 说明：表示当前 Oracle MBPS
# oracledb_sysstat_consistent_gets{metric_name="labelvalue01"} 说明：表示当前 Oracle 一致性读取数
# oracledb_sysstat_execute_count{metric_name="labelvalue01"} 说明：表示当前 Oracle 执行的 SQL 语句数
[[metric]]
context = "sysstat"
metricsdesc = { value = "Compute Oracle IOPS and MBPS from v$sysstat view in Oracle." }
fieldtoappend = "name"
request = '''
select name , trunc(sum(value), 2) as value
  from (select decode(name,
                      'physical read total IO requests',
                      'Oracle IOPS',
                      'physical write total IO requests',
                      'Oracle IOPS',
                      'redo writes',
                      'Oracle IOPS',
                      'physical read total bytes',
                      'Oracle MBPS',
                      'physical write total bytes',
                      'Oracle MBPS',
                      'redo size',
                      'Oracle MBPS') as name,
               decode(name,
                      'physical read total IO requests',
                      value,
                      'physical write total IO requests',
                      value,
                      'redo writes',
                      value,
                      'physical read total bytes',
                      value / 1024 / 1024,
                      'physical write total bytes',
                      value / 1024 / 1024,
                      'redo size',
                      value / 1024 / 1024) as value
          from v$sysstat
         where name in (
                        'physical read total bytes',
                        'physical write total bytes',
                        'physical read total IO requests',
                        'physical write total IO requests',
                        'redo writes',
                        'redo size'))
 where name is not null
 group by name
union all
SELECT a.name, a.value
  FROM v$sysstat a, v$statname b
 where a.statistic# = b.statistic#
   and b.name in (
                  'consistent gets',
                  'execute count',
                  'parse count (hard)',
                  'parse count (total)',
                  'redo size',
                  'redo writes',
                  'sorts (disk)',
                  'sorts (memory)',
                  'sorts (rows)',
                  'user commits',
            'user rollbacks',
            'logons cumulative',
                  'bytes sent via SQL*Net to client',
                  'bytes received via SQL*Net from client')
'''

# oracledb_recovery_file_dest_value{file_type="labelvalue01"} 说明：Oracle 数据库恢复区的总体空间使用百分比
[[metric]]
context = "recovery_file_dest"
metricsdesc = { value = "Total percentage of space used in the database recovery area from V$RECOVERY_AREA_USAGE." }
request = '''
select sum(t.PERCENT_SPACE_USED) as value from V$RECOVERY_AREA_USAGE t
'''

# oracledb_recovery_file_dest_percent_space_used{file_type="labelvalue01"} 说明：Oracle数据库恢复区（Recovery Area）中每种文件类型的空间使用百分比
[[metric]]
context = "recovery_file_dest"
labels = [ "file_type"]
metricsdesc = { percent_space_used = "Percentage of space used in the recovery area for each file type from V$RECOVERY_AREA_USAGE." }
request = '''
select t.file_type,t.percent_space_used from V$RECOVERY_AREA_USAGE t
'''

# oracledb_rman_status_value 说明：表示当前 RMAN 的状态
[[metric]]
context = "rman_status"
metricsdesc = { value = "0 indicates rman error or no config rman job, 1 indicates rman is ok" }
request = '''
select decode(max(t.COMPLETION_TIME),'', 0,case when sysdate-max(t.COMPLETION_TIME) > 1 then 0 else 1 end) as value
  from v$backup_piece t
 where t.STATUS = 'A'
'''

# oracledb_rman_info_value{recid="labelvalue01",lv_type="labelvalue02",controlfile_included="labelvalue03",status="labelvalue04",device_type="labelvalue05",start_time="labelvalue06",completion_time="labelvalue07",elapsed_seconds="labelvalue08",bak_size="labelvalue09",compressed="labelvalue10",tag="labelvalue11"} 说明：表示当前 RMAN 的信息
[[metric]]
context = "rman_info"
labels = [ "recid", "lv_type", "controlfile_included", "status", "device_type", "start_time", "completion_time", "elapsed_seconds", "bak_size", "compressed", "tag" ]
metricsdesc = { value = "list rman information" }
request = '''
select a.recid as recid,
               decode(b.incremental_level,
                      '',
                      decode(backup_type, 'L', 'Archivelog', 'ControlFile'),
                      1,
                      'Incr-1',
                      0,
                      'Incr-0',
                      b.incremental_level) as lv_type,
               b.controlfile_included as controlfile_included,
               decode(a.status,
                      'A',
                      'AVAILABLE',
                      'D',
                      'DELETED',
                      'X',
                      'EXPIRED',
                      'ERROR') as status,
               a.device_type as device_type,
               a.start_time as start_time,
               a.completion_time as completion_time,
               a.elapsed_seconds as elapsed_seconds,
               round(a.bytes / 1024 / 1024 / 1024,3) as bak_size,
               a.compressed as compressed,
               a.tag as tag,
               1 as value
          from v$backup_piece a, v$backup_set b
         where a.set_stamp = b.set_stamp
           and a.deleted = 'NO'
           and a.set_count = b.set_count
           and a.start_time > sysdate-8
         order by a.completion_time desc
'''

# oracledb_dataguard_exist_value 说明：表示当前 Oracle 数据库是否启用 Data Guard   
[[metric]]
context = "dataguard_exist"
metricsdesc = { value ="1 indicates the presence of dg, 0 indicates no dg." }
request = '''
select decode(db_unique_name, 'NONE', 0, 1) as value
  from v$archive_dest_status
 where dest_name = 'LOG_ARCHIVE_DEST_2'
'''

# oracledb_dataguard_status_value 说明：用于反映 Oracle Data Guard 的运行状态
[[metric]]
context = "dataguard_status"
metricsdesc = { value = "0 indicates no dg, 1 indicates dg has errors, 1 indicates dg is ok;" }
request = '''
select case
         when db_unique_name = 'NONE' then
          0
         when status != 'VALID' then
          1
         else
          decode(recovery_mode,
                 'IDLE',
                 1,
                 'MANAGED',
                 2,
                 'MANAGED REAL TIME APPLY',
                 2,
                 'LOGICAL REAL TIME APPLY',
                 2,
                 'LOGICAL APPLY',
                 2,
                 1)
       end as value
  from v$archive_dest_status
 where dest_name = 'LOG_ARCHIVE_DEST_2'
'''

# oracledb_archivelog_count{log_day="2025-04-16"} 说明：该指标展示了每天归档日志（Archived Redo Logs）的数量
[[metric]]
context = "archivelog"
labels = [ "log_day" ]
metricsdesc = { count = "Number of archived logs per day based on completion time." }
request = '''
select to_char(trunc(completion_time), 'YYYY-MM-DD') as log_day, count(*) as count 
from v$archived_log 
group by trunc(completion_time)
'''
ignorezeroresult = true

# oracledb_applylag_value 说明：超过 1 分钟甚至 1 天的延迟，计算总秒数
[[metric]]
context = "applylag"
metricsdesc = { value = "Data Guard apply lag in seconds from v$dataguard_stats." }
request = '''
SELECT ROUND(EXTRACT(DAY FROM TO_DSINTERVAL(value)) * 86400 +
             EXTRACT(HOUR FROM TO_DSINTERVAL(value)) * 3600 +
             EXTRACT(MINUTE FROM TO_DSINTERVAL(value)) * 60 +
             EXTRACT(SECOND FROM TO_DSINTERVAL(value))) AS value
FROM v$dataguard_stats
WHERE name = 'apply lag'
'''
ignorezeroresult = true

基于 Categraf 采集器我新增的查询 SQL 指标如下 metric.toml 文件：

# 基于 categraf 下的 oracle 插件采集 Oracle 数据库实例指标配置文件
# 指标名称  oracle_sessions_value{status="labelvalue01", type="labelvalue02"} 说明：表示当前会话状态和类型的会话数
# oracle_<mesurement>_<metric_fields>
[[metrics]]
mesurement = "sessions"
label_fields = [ "status", "type" ]
metric_fields = [ "value" ]
timeout = "3s"
request = '''
SELECT status, type, COUNT(*) as value FROM v$session GROUP BY status, type
'''

# 指标名称  oracle_lock_cnt 说明：当前被锁定对象的总数
[[metrics]]
mesurement = "lock"
metric_fields = [ "cnt" ]
timeout = "3s"
request = '''
SELECT COUNT(*) AS cnt
  FROM ALL_OBJECTS A, V$LOCKED_OBJECT B, SYS.GV_$SESSION C
 WHERE A.OBJECT_ID = B.OBJECT_ID
   AND B.PROCESS = C.PROCESS
'''

# 用于统计数据库中最近 5 分钟 SQL 的慢查询 P95 和 P99 分位数
# oracle_slow_queries_p95_time_usecs
# oracle_slow_queries_p99_time_usecs
[[metrics]]
mesurement = "slow_queries"
metric_fields = [ "p95_time_usecs" , "p99_time_usecs"]
timeout = "3s"
request = '''
select  percentile_disc(0.95)  within group (order by elapsed_time) as p95_time_usecs, 
  percentile_disc(0.99)  within group (order by elapsed_time) as p99_time_usecs 
from v$sql where last_active_time >= sysdate - 5/(24*60)
'''

# oracle_resource_current_utilization{resource_name="labelvalue01"} 说明：表示当前资源使用率
# oracle_resource_limit_value{resource_name="labelvalue01"} 说明：系统允许的最大值限制 -1 表示无限制
[[metrics]]
mesurement = "resource"
label_fields = [ "resource_name" ]
metric_fields = [ "current_utilization", "limit_value" ]
timeout = "3s"
request = '''
  SELECT 
    resource_name,
    current_utilization,
    CASE 
      WHEN TRIM(limit_value) LIKE 'UNLIMITED' THEN '-1' 
      ELSE TRIM(limit_value) 
    END as limit_value 
  FROM v$resource_limit
'''
# 如果查询没有返回结果 即忽略错误 默认不忽略错误
IgnoreZeroResult = true

# oracle_asm_diskgroup_total{diskgroup_name="labelvalue01"} 说明：表示ASM磁盘组的总大小
# oracle_asm_diskgroup_free{diskgroup_name="labelvalue01"} 说明：表示ASM磁盘组的可用大小
[[metrics]]
mesurement = "asm_diskgroup"
label_fields = [ "name" ]
metric_fields = [ "total", "free" ]
timeout = "3s"
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

# oracle_activity_parse_count_total 说明：表示当前解析的SQL语句数
# oracle_activity_execute_count 说明：表示当前执行的SQL语句数
# oracle_activity_user_commits 说明：表示当前提交的事务数
# oracle_activity_user_rollbacks 说明：表示当前回滚的事务数
[[metrics]]
mesurement = "activity"
metric_fields = [ "value" ]
field_to_append = "name"
timeout = "3s"
request = '''
  SELECT name, value 
  FROM v$sysstat 
  WHERE name IN (
    'parse count (total)', 
    'execute count', 
    'user commits', 
    'user rollbacks'
  )
'''

# oracle_process_count 说明：Oracle 数据库中正在运行的进程的总数
[[metrics]]
mesurement = "process"
metric_fields = [ "count" ]
timeout = "3s"
request = "SELECT COUNT(*) as count FROM v$process"

# oracle_wait_time_value{wait_class="System I/O"} 说明：表示当前 System I/O 等待的时间
# 11g 及以下版本
[[metrics]]
mesurement = "wait_time"
metric_fields = [ "value" ]
label_fields = ["wait_class"]
timeout = "3s"
request = '''
SELECT
  n.wait_class as WAIT_CLASS,
  round(m.time_waited/m.INTSIZE_CSEC,3) as VALUE
FROM
  v$waitclassmetric  m, v$system_wait_class n
WHERE
  m.wait_class_id=n.wait_class_id AND n.wait_class != 'Idle'
'''
IgnoreZeroResult = true

# 12c 及以上版本或CDB 模式
[[metrics]]
mesurement = "wait_time"
label_fields = [ "wait_class", "con_id" ]
metric_fields = [ "time_waited_sec_total" ]
field_to_append= "wait_class"
timeout = "3s"
request = '''
select
  wait_class,
  round(time_waited/100,3) time_waited_sec_total,
  con_id
from v$system_wait_class
where wait_class <> 'Idle'
'''
IgnoreZeroResult = true

# oracle_tablespace_bytes{tablespace="labelvalue01",type="labelvalue02"} 说明：表示表空间的字节数
# oracle_tablespace_max_bytes{tablespace="labelvalue01",type="labelvalue02"} 说明：表示表空间的最大字节数
# oracle_tablespace_free{tablespace="labelvalue01",type="labelvalue02"} 说明：表示表空间的可用字节数
# oracle_tablespace_used_percent{tablespace="labelvalue01",type="labelvalue02"} 说明：表示表空间的使用百分比
[[metrics]]
mesurement = "tablespace"
label_fields = [ "tablespace", "type" ]
metric_fields = [ "bytes", "max_bytes", "free" ]
timeout = "3s"
request = '''
SELECT
    dt.tablespace_name as tablespace,
    dt.contents as type,
    dt.block_size * dtum.used_space as bytes,
    dt.block_size * dtum.tablespace_size as max_bytes,
    dt.block_size * (dtum.tablespace_size - dtum.used_space) as free,
    dtum.used_percent
FROM  dba_tablespace_usage_metrics dtum, dba_tablespaces dt
WHERE dtum.tablespace_name = dt.tablespace_name
and dt.contents != 'TEMPORARY'
union
SELECT
    dt.tablespace_name as tablespace,
    'TEMPORARY' as type,
    dt.tablespace_size - dt.free_space as bytes,
    dt.tablespace_size as max_bytes,
    dt.free_space as free,
    ((dt.tablespace_size - dt.free_space) / dt.tablespace_size)
FROM  dba_temp_free_space dt
order by tablespace
'''

# oracle_db_system_value{name="cpu_count"}    说明：采集数据库 CPU 核数   整数
# oracle_db_system_value{name="sga_max_size"} 说明：SGA 配置的大小    单位：字节
# oracle_db_system_value{name="pga_aggregate_limit"}  说明：PGA 配置的大小    单位：字节
[[metrics]]
mesurement = "db_system"
label_fields = [ "name" ]
metric_fields = [ "value" ]
timeout = "3s"
request = '''
select name, value 
from v$parameter 
where name in ('cpu_count', 'sga_max_size', 'pga_aggregate_limit')
'''

# oracle_top_sql_elapsed{sql_id="labelvalue01",sql_text="labelvalue02"} 说明：前 15 条最耗时的 SQL
[[metrics]]
mesurement = "top_sql"
label_fields = [ "sql_id", "sql_text" ]
metric_fields = [ "elapsed" ]
timeout = "3s"
request = '''
select * from (
select sql_id, elapsed_time / 1000000 as elapsed, SUBSTRB(REPLACE(sql_text,'',''),1,55) as sql_text
from   V$SQLSTATS
order by elapsed_time desc
) where ROWNUM <= 15
'''
IgnoreZeroResult = true

# oracle_cache_hit_ratio_value{cache_hit_type="Buffer Cache Hit Ratio"} 说明：表示当前 Buffer Cache 命中率
# oracle_cache_hit_ratio_value{cache_hit_type="Library Cache Hit Ratio"} 说明：表示当前 Library Cache 命中率
# oracle_cache_hit_ratio_value{cache_hit_type="Result Cache Hit Ratio"} 说明：表示当前 Result Cache 命中率
[[metrics]]
mesurement = "cache_hit_ratio"
label_fields = [ "cache_hit_type" ]
metric_fields = [ "value" ]
timeout = "3s"
request = '''
select metric_name cache_hit_type, value
from v$sysmetric
where group_id=2 and metric_id in (2000,2050,2112,2110)
'''
IgnoreZeroResult = true

# oracle_parameter_sessions_value 说明：当前 Oracle 设置的最大并发 session 数
[[metrics]]
mesurement = "parameter_sessions"
metric_fields = [ "value" ]
timeout = "3s"
request = "select value  from v$parameter t where t.name = 'sessions'"

# oracle_env_is_rac{host_name="hostname"} 说明：表示当前 Oracle 是否为 RAC 环境 1 启用 RAC 0 未启用 RAC
[[metrics]]
mesurement = "env"
label_fields = [ "host_name" ]
metric_fields = [ "is_rac" ]
timeout = "3s"
request = '''
select host_name,
decode((select value from v$option where parameter = 'Real Application Clusters'), 'TRUE',1,0) as is_rac
from v$instance
'''

# oracle_env_running_time{version="labelvalue01"} 说明：表示当前 Oracle 版本和运行时间
[[metrics]]
mesurement = "env"
label_fields = [ "version" ]
metric_fields = [ "running_time" ]
timeout = "3s"
request = '''
SELECT version, trunc(sysdate - startup_time) AS running_time FROM v$instance
'''

# oracle_env_value{platform_name="labelvalue01",log_mode="labelvalue02",force_logging="labelvalue03",flashback_on="labelvalue04",database_role="labelvalue05",open_mode="labelvalue06",nls_characterset="labelvalue07"} 说明：表示当前 Oracle 环境设置
[[metrics]]
mesurement = "env"
label_fields = [ "platform_name", "log_mode", "force_logging", "flashback_on", "database_role", "open_mode", "nls_characterset" ]
metric_fields = [ "value" ]
timeout = "3s"
request = '''
select platform_name, log_mode, force_logging, flashback_on, database_role, open_mode,
(select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET') as nls_characterset, 1 as value 
from v$database
'''

# oracle_env_redo_value{name="group_num"} 说明：表示当前 redo log group 数量
# oracle_env_redo_value{name="member_num"}    说明：表示当前 redo log member 数量
# oracle_env_redo_value{name="log_size"}  说明：表示当前 redo log group 的大小
[[metrics]]
mesurement = "env"
label_fields = [ "name" ]
metric_fields = [ "redo_value" ]
timeout = "3s"
request = '''
with temp as(
  select 
    count(group#) as group_num,
    min(members) as member_num,
    min(bytes/1024/1024) as log_size 
  from v$log
)
select  'group_num' as name ,group_num as redo_value from temp
union all
select  'member_num' as name ,member_num as redo_value from temp
union all
select  'log_size' as name ,log_size as redo_value from temp
'''

# oracle_env_redo_switch_value 说明：表示当前 redo log 切换的最大序列号
[[metrics]]
mesurement = "env"
metric_fields = [ "redo_switch_value" ]
timeout = "3s"
request = '''
select max(sequence#) as redo_switch_value from v$log
'''

# oracle_env_vols 所有数据库段的总大小 单位：字节
[[metrics]]
mesurement = "env"
metric_fields = [ "vols" ]
timeout = "3s"
request = '''
select to_char(sum(bytes)) as vols from dba_segments
'''

# oracle_events_value{event_name="labelvalue01"} 说明：获取各个事件的计数值
[[metrics]]
mesurement = "events"
label_fields = [ "event_name"]
metric_fields = [ "value" ]
timeout = "3s"
request = '''
select event as event_name, count(*) as value
   from v$session_wait
      where wait_time >= -1 group by event order by 2 desc
'''

# oracle_db_time_value 说明：表示当前数据库的 DB time DB time 主要反映了数据库处理请求的总时间
[[metrics]]
mesurement = "db_time"
metric_fields = [ "value" ]
timeout = "3s"
request = '''
select value from v$sys_time_model t where t.STAT_NAME = 'DB time'
'''

# oracle_sysmetric_.* 说明：Oracle 数据库的系统性能指标
[[metrics]]
mesurement = "sysmetric"
metric_fields = [ "value" ]
field_to_append = "metric_name"
timeout = "3s"
request = '''
select replace(metric_name,'%','') as metric_name ,value from v$sysmetric  where group_id = 2 and metric_name != 'Average Synchronous Single-Block Read Latency'
'''

# 说明：Oracle IOPS 和 MBPS 计算
# oracle_sysstat_oracle_iops{metric_name="labelvalue01"} 说明：表示当前 Oracle IOPS
# oracle_sysstat_oracle_mbps{metric_name="labelvalue01"} 说明：表示当前 Oracle MBPS
# oracle_sysstat_consistent_gets{metric_name="labelvalue01"} 说明：表示当前 Oracle 一致性读取数
# oracle_sysstat_execute_count{metric_name="labelvalue01"} 说明：表示当前 Oracle 执行的 SQL 语句数
[[metrics]]
mesurement = "sysstat"
metric_fields = [ "value" ]
field_to_append = "name"
timeout = "3s"
request = '''
select name , trunc(sum(value), 2) as value
  from (select decode(name,
                      'physical read total IO requests',
                      'Oracle IOPS',
                      'physical write total IO requests',
                      'Oracle IOPS',
                      'redo writes',
                      'Oracle IOPS',
                      'physical read total bytes',
                      'Oracle MBPS',
                      'physical write total bytes',
                      'Oracle MBPS',
                      'redo size',
                      'Oracle MBPS') as name,
               decode(name,
                      'physical read total IO requests',
                      value,
                      'physical write total IO requests',
                      value,
                      'redo writes',
                      value,
                      'physical read total bytes',
                      value / 1024 / 1024,
                      'physical write total bytes',
                      value / 1024 / 1024,
                      'redo size',
                      value / 1024 / 1024) as value
          from v$sysstat
         where name in (
                        'physical read total bytes',
                        'physical write total bytes',
                        'physical read total IO requests',
                        'physical write total IO requests',
                        'redo writes',
                        'redo size'))
 where name is not null
 group by name
union all
SELECT a.name, a.value
  FROM v$sysstat a, v$statname b
 where a.statistic# = b.statistic#
   and b.name in (
                  'consistent gets',
                  'execute count',
                  'parse count (hard)',
                  'parse count (total)',
                  'redo size',
                  'redo writes',
                  'sorts (disk)',
                  'sorts (memory)',
                  'sorts (rows)',
                  'user commits',
            'user rollbacks',
            'logons cumulative',
                  'bytes sent via SQL*Net to client',
                  'bytes received via SQL*Net from client')
'''

# oracle_recovery_file_dest_value{file_type="labelvalue01"} 说明：Oracle 数据库恢复区的总体空间使用百分比
[[metrics]]
mesurement = "recovery_file_dest"
metric_fields = [ "value" ]
timeout = "3s"
request = '''
select sum(t.PERCENT_SPACE_USED) as value from V$RECOVERY_AREA_USAGE t
'''

# oracle_recovery_file_dest_percent_space_used{file_type="labelvalue01"} 说明：Oracle数据库恢复区（Recovery Area）中每种文件类型的空间使用百分比
[[metrics]]
mesurement = "recovery_file_dest"
label_fields = [ "file_type"]
metric_fields = [ "percent_space_used" ]
request = '''
select t.file_type,t.percent_space_used from V$RECOVERY_AREA_USAGE t
'''

# oracle_rman_status_value 说明：表示当前 RMAN 的状态
[[metrics]]
mesurement = "rman_status"
metric_fields = [ "value"]
timeout = "3s"
request = '''
select decode(max(t.COMPLETION_TIME),'', 0,case when sysdate-max(t.COMPLETION_TIME) > 1 then 0 else 1 end) as value
  from v$backup_piece t
 where t.STATUS = 'A'
'''

# oracledb_rman_info_value{recid="labelvalue01",lv_type="labelvalue02",controlfile_included="labelvalue03",status="labelvalue04",device_type="labelvalue05",start_time="labelvalue06",completion_time="labelvalue07",elapsed_seconds="labelvalue08",bak_size="labelvalue09",compressed="labelvalue10",tag="labelvalue11"} 说明：表示当前 RMAN 的信息
[[metrics]]
mesurement = "rman_info"
label_fields = [ "recid", "lv_type", "controlfile_included", "status", "device_type", "start_time", "completion_time", "elapsed_seconds", "bak_size", "compressed", "tag" ]
metric_fields = [ "value"]
timeout = "3s"
request = '''
select a.recid as recid,
               decode(b.incremental_level,
                      '',
                      decode(backup_type, 'L', 'Archivelog', 'ControlFile'),
                      1,
                      'Incr-1',
                      0,
                      'Incr-0',
                      b.incremental_level) as lv_type,
               b.controlfile_included as controlfile_included,
               decode(a.status,
                      'A',
                      'AVAILABLE',
                      'D',
                      'DELETED',
                      'X',
                      'EXPIRED',
                      'ERROR') as status,
               a.device_type as device_type,
               a.start_time as start_time,
               a.completion_time as completion_time,
               a.elapsed_seconds as elapsed_seconds,
               round(a.bytes / 1024 / 1024 / 1024,3) as bak_size,
               a.compressed as compressed,
               a.tag as tag,
               1 as value
          from v$backup_piece a, v$backup_set b
         where a.set_stamp = b.set_stamp
           and a.deleted = 'NO'
           and a.set_count = b.set_count
           and a.start_time > sysdate-8
         order by a.completion_time desc
'''

# oracle_dataguard_exist_value 说明：表示当前 Oracle 数据库是否启用 Data Guard   
[[metrics]]
mesurement = "dataguard_exist"
metric_fields = [ "value" ]
timeout = "3s"
request = '''
select decode(db_unique_name, 'NONE', 0, 1) as value
  from v$archive_dest_status
 where dest_name = 'LOG_ARCHIVE_DEST_2'
'''

# oracledb_dataguard_status_value 说明：用于反映 Oracle Data Guard 的运行状态
[[metrics]]
mesurement = "dataguard_status"
metric_fields = [ "value" ]
timeout = "3s"
request = '''
select case
         when db_unique_name = 'NONE' then
          0
         when status != 'VALID' then
          1
         else
          decode(recovery_mode,
                 'IDLE',
                 1,
                 'MANAGED',
                 2,
                 'MANAGED REAL TIME APPLY',
                 2,
                 'LOGICAL REAL TIME APPLY',
                 2,
                 'LOGICAL APPLY',
                 2,
                 1)
       end as value
  from v$archive_dest_status
 where dest_name = 'LOG_ARCHIVE_DEST_2'
'''

# oracle_archivelog_count{log_day="2025-04-16"} 说明：该指标展示了每天归档日志（Archived Redo Logs）的数量
[[metrics]]
mesurement = "archivelog"
label_fields = [ "log_day" ]
metric_fields = [ "count" ]
timeout = "3s"
request = '''
select to_char(trunc(completion_time), 'YYYY-MM-DD') as log_day, count(*) as count 
from v$archived_log 
group by trunc(completion_time)
'''
IgnoreZeroResult = true

# oracledb_applylag_value 说明：超过 1 分钟甚至 1 天的延迟，计算总秒数
[[metrics]]
mesurement = "applylag"
metric_fields = [ "value" ]
timeout = "3s"
request = '''
SELECT ROUND(EXTRACT(DAY FROM TO_DSINTERVAL(value)) * 86400 +
             EXTRACT(HOUR FROM TO_DSINTERVAL(value)) * 3600 +
             EXTRACT(MINUTE FROM TO_DSINTERVAL(value)) * 60 +
             EXTRACT(SECOND FROM TO_DSINTERVAL(value))) AS value
FROM v$dataguard_stats
WHERE name = 'apply lag'
'''
IgnoreZeroResult = true

在写查询配置的时候建议先在 Oracle 执行查询一下：

selectname , trunc(sum(value), 2) asvalue
from (selectdecode(name,
'physical read total IO requests',
'Oracle IOPS',
'physical write total IO requests',
'Oracle IOPS',
'redo writes',
'Oracle IOPS',
'physical read total bytes',
'Oracle MBPS',
'physical write total bytes',
'Oracle MBPS',
'redo size',
'Oracle MBPS') asname,
decode(name,
'physical read total IO requests',
value,
'physical write total IO requests',
value,
'redo writes',
value,
'physical read total bytes',
value / 1024 / 1024,
'physical write total bytes',
value / 1024 / 1024,
'redo size',
value / 1024 / 1024) asvalue
from v$sysstat
wherenamein (
'physical read total bytes',
'physical write total bytes',
'physical read total IO requests',
'physical write total IO requests',
'redo writes',
'redo size'))
wherenameisnotnull
groupbyname
unionall
SELECT a.name, a.value
FROM v$sysstat a, v$statname b
where a.statistic# = b.statistic#
and b.name in (
'consistent gets',
'execute count',
'parse count (hard)',
'parse count (total)',
'redo size',
'redo writes',
'sorts (disk)',
'sorts (memory)',
'sorts (rows)',
'user commits',
'user rollbacks',
'logons cumulative',
'bytes sent via SQL*Net to client',
'bytes received via SQL*Net from client')

Oracle 监控可视化

基于 Prometheus 体系监控 Oracle 数据库算是比较完善的落地，如果还有不满足需求的指标，可自行写 SQL 语句构建查询指标，欢迎 Oracle 数据库专家能够提提意见，看看还有没有比较核心的指标需要监控。

📣欢迎朋友们关注公众号📢📢：【网络小斐】！

🙋‍♂️有想法的朋友也可以加我沟通，朋友🔘做个点赞之交！😂😂

欢迎点赞 👍、收藏 💗、关注 💡 三连支持一下，我们下期见~✨