Grafana监控OracleDB的完整过程

背景

两年前曾经写过一个进行Oracle 监控的简单blog
但是周天晚上尝试进行处理时发现很不完整了.
很多数据获取不到.
晚上又熬夜了好久进行处理. 感觉还是需要总结一下,不然就忘记了

获取镜像

还是使用 docker 的方式来暴露Oracle的服务
使用的镜像为:
docker pull ghcr.io/iamseth/oracledb_exporter:0.4.3

需要注意这个镜像比较大, 压缩完还 270MB 左右

注意事项-1

镜像内的监控事项比较少. 可能只是非常简单的. 
我这边想监控的是一些性能相关的内容, 仅看一些简单内容, 感觉没有任何意义.
然后继续从github上面找相关的资料: 
https://github.com/saitiger008/oracledb_exporter_metrics/blob/main/default-metrics.toml

这个哥们自己加了一些监控事项挺好的. 
我这边将他的监控与默认的default进行合并. 内容放到最下面. 
然后通过-v 的方式在容器内加载默认的metrics的监控事项. 
可以通过docker logs --tail=100 containername的方式进行查看具体内容.

注意事项-2

正常启用之后发现还是无法进行相关的监控信息.
主要是无法获取一些系统的监控信息. 

日志里面很多这样的提示信息:
 msg="Error scraping for" context=resource metricsDesc="unsupported value type" s
 msg="Error scraping for" context=sysmetric metricsDesc="unsupported value type" 
 msg="Error scraping for" context=wait_time metricsDesc="unsupported value type" 
 msg="Error scraping for" context=rman_info metricsDesc="unsupported value type" 

提示无法获取对应的监控信息.

注意事项-2

这个地方耗时很久进行处理. 发现总是比较难易解决问题.
主要问题是 只有 sys 账户会查询 select * from v$sysmetirc的视图.
并且必须要sysdba的账户进行登录, 如果不是sysdba 查询此视图都是空的.

所以问题的核心是用Docker的语法实现 sysdba 角色的登录. 
我这边尝试 / as sysdba 发现是没有效果的.
查看官方文档是发现有一个解决方法为:
在 DATA_SOURCE_NAME 的和面添加 ?as=sysdba 的方式来指定角色.

# 清理之前的容器
docker rm -f oracledb_exporter
# 创建对应的容器进行监听  
docker run -d --name oracledb_exporter  --restart=always --net=host \
-e DATA_SOURCE_NAME='sys/[email protected]/gscdb?as=sysdba' \
-v /oracledb/default-metrics.toml:/default-metrics.toml ghcr.io/iamseth/oracledb_exporter:0.4.3
# 查看日志信息.
docker logs -f  oracledb_exporter

注意事项-3

使用 桥接网络 也可以 但是需要注意写法为:

docker rm -f oracledb_exporter2 
docker run -d --name oracledb_exporter2  --restart=always -p 9162:9161 --privileged \
-e DATA_SOURCE_NAME='sys/[email protected]/ora19c?as=sysdba' \
-v /oracledb/default-metrics.toml:/default-metrics.toml ghcr.io/iamseth/oracledb_exporter:0.4.3 
docker logs -f  oracledb_exporter2

注意事项-4

Prometheus的设置为:
scrape_configs:
  - job_name: '116Oracle_ora19c' # job_name默认写入timeseries的labels中，可以用于查询使用
    static_configs: # 静态配置
      - targets: ['10.110.136.xxx:9162']

注意 需要重启Prometheus使之生效.
另外 yaml 格式一定注意空格和格式.

注意事项-5

Grafana的设置
注意 这次选用的是国人开发的一个配置:
编号为: 17136

具体地址为: 
https://grafana.com/grafana/dashboards/17136-oracledb-new-202210/

效果为

监控配置脚本

[[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_utilizatinotallow= "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)." }
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"

[[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="Generic counter metric from v$sysstat view in Oracle." }
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="Gauge metric with count of processes." }
request = "SELECT COUNT(*) as count FROM v$process"

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

[[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
ORDER by tablespace
'''

[[metric]]
context = "parameter"
labels = [ "name","param" ]
metricsdesc = { value ="Gauge metric with database parameter" }
request = "select name,value as param,1 as value from v$parameter"

[[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 = "parameter_sessions"
metricsdesc = { value ="Gauge metric with sessions parameter" }
request = "select value  from v$parameter t where t.name = 'sessions'"


[[metric]]
context = "volumns"
metricsdesc = { value= "database total volumes,size G" }
request = "SELECT sum(bytes/1024/1024/1024) as value FROM dba_segments"


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

[[metric]]
context = "env"
labels = [ "version" ]
metricsdesc = { running_time = "get version and running_time" }
request = '''
select version,trunc(sysdate - startup_time) as running_time from v$instance
'''

[[metric]]
context = "env"
labels = [ "platform_name","log_mode","force_logging","flashback_on","database_role","open_mode","nls_characterset" ]
metricsdesc = { value = "get other env" }
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
'''

[[metric]]
context = "env"
labels = [ "name" ]
metricsdesc = { redo_value = "get redo logs info" }
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
'''

[[metric]]
context = "env"
metricsdesc = { redo_switch_value = "Gauge metric with sum of segments data." }
request = '''
select max(sequence#) as redo_switch_value from v$log
'''


[[metric]]
context = "env"
metricsdesc = { vols = "Gauge metric with sum of segments data." }
request = '''
select to_char(sum(bytes)) as vols from dba_segments
'''


[[metric]]
context = "events"
labels = [ "event_name"]
metricsdesc = { value = "get events 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
'''

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

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

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


[[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." }
request = '''
SELECT
  df.tablespace_name       as tablespace,
  df.type                  as type,
  nvl(sum(df.bytes),0)     as bytes,
  nvl(sum(df.max_bytes),0) as max_bytes,
  nvl(sum(f.free),0)       as free
FROM
  (
    SELECT
      ddf.file_id,
      dt.contents as type,
      ddf.file_name,
      ddf.tablespace_name,
      TRUNC(ddf.bytes) as bytes,
      TRUNC(GREATEST(ddf.bytes,ddf.maxbytes)) as max_bytes
    FROM
      dba_data_files ddf,
      dba_tablespaces dt
    WHERE ddf.tablespace_name = dt.tablespace_name
  ) df,
  (
    SELECT
      TRUNC(SUM(bytes)) AS free,
      file_id
    FROM dba_free_space
    GROUP BY file_id
  ) f
WHERE df.file_id = f.file_id (+)
GROUP BY df.tablespace_name, df.type
UNION ALL
SELECT
  Y.name                   as tablespace_name,
  Y.type                   as type,
  SUM(Y.bytes)             as bytes,
  SUM(Y.max_bytes)         as max_bytes,
  MAX(nvl(Y.free_bytes,0)) as free
FROM
  (
    SELECT
      dtf.tablespace_name as name,
      dt.contents as type,
      dtf.status as status,
      dtf.bytes as bytes,
      (
        SELECT
          ((f.total_blocks - s.tot_used_blocks)*vp.value)
        FROM
          (SELECT tablespace_name, sum(used_blocks) tot_used_blocks FROM gv$sort_segment WHERE  tablespace_name!='DUMMY' GROUP BY tablespace_name) s,
          (SELECT tablespace_name, sum(blocks) total_blocks FROM dba_temp_files where tablespace_name !='DUMMY' GROUP BY tablespace_name) f,
          (SELECT value FROM v$parameter WHERE name = 'db_block_size') vp
        WHERE f.tablespace_name=s.tablespace_name AND f.tablespace_name = dtf.tablespace_name
      ) as free_bytes,
      CASE
        WHEN dtf.maxbytes = 0 THEN dtf.bytes
        ELSE dtf.maxbytes
      END as max_bytes
    FROM
      sys.dba_temp_files dtf,
      sys.dba_tablespaces dt
    WHERE dtf.tablespace_name = dt.tablespace_name
  ) Y
GROUP BY Y.name, Y.type
ORDER BY tablespace
'''

[[metric]]
context = "db_recovery_file_dest"
metricsdesc = { value="sum(PERCENT_SPACE_USED) of V$RECOVERY_AREA_USAG" }
request = '''
select sum(t.PERCENT_SPACE_USED) as value from V$RECOVERY_AREA_USAGE t
'''


[[metric]]
context = "db_recovery_file_dest"
labels = [ "file_type"]
metricsdesc = { percent_space_used = "percent_space_used of V$RECOVERY_AREA_USAGE ." }
request = '''
select t.file_type,t.percent_space_used from V$RECOVERY_AREA_USAGE t
'''

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

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


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


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