环球报道:Grafana监控OracleDB的完整过程

2023-04-18 14:55:13 来源：博客园

(资料图片仅供参考)

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/MYpassword@10.110.xx.xx/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/Mypassword@10.110.136.37/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_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)." }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 VALUEFROM  v$waitclassmetric  m, v$system_wait_class nWHERE  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_percentFROM  dba_tablespace_usage_metrics dtum, dba_tablespaces dtWHERE dtum.tablespace_name = dt.tablespace_nameORDER 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_racfrom 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 tempunion allselect  "member_num" as name ,member_num as redo_value from tempunion allselect  "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 nameunion allSELECT 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 freeFROM  (    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  ) fWHERE df.file_id = f.file_id (+)GROUP BY df.tablespace_name, df.typeUNION ALLSELECT  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 freeFROM  (    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  ) YGROUP BY Y.name, Y.typeORDER 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""""

关键词：