龙空技术网

zabbix5监控SAP环境下Oracle数据库表空间

橙子粑粑哦 276

前言:

而今同学们对“oracle添加栏位”都比较重视,朋友们都需要分析一些“oracle添加栏位”的相关内容。那么小编也在网络上搜集了一些对于“oracle添加栏位””的相关资讯,希望兄弟们能喜欢,姐妹们一起来了解一下吧!

最早我安装的zabbix还是1.0版本,用了一段时间升级到了3.0,这个版本一直了用了好多年,也足够用了。没想到最近zabbix总是宕机,无奈只能重新安装了最新版本5。发现功能比以前强大了很多啊,网络上也出现很多新的监控方案,特别是自动发现IP和web监控的,很受用啊。其中对SAP环境下Oracle数据库监控,以前我是用orabbix这个插件,用起来挺好的,可以自己新增监控项目,只要你会写sql语句。而新版本直接增加了Template DB Oracle by ODBC这个模板,直接拿来用,简直爽歪歪啊。

zabbix5.0版本对oracle监控很好用,但对表空间tablespace,似乎少了一些东西,只有下面这些监控项。

而SAP事务代码db02的表空间显示如下图,对比下,大概zabbix只显示了其中4个栏位,如下图红色标记:

如果想监控其他的栏位,那得改写,无奈那个sql太复杂,看不懂啊,也不想重头写,于是就借用了SAP官方提供的脚本:Space_Tablespaces_11g+.txt,这个脚本可以去SAP网站下载哦。执行出来的结果如下图:

你会发现单位是GB的,zabbix建议使用单位B,至于为什么?因为在显示的时候它可以自动转义,B可以转为MB/GB/TB,如果你单位是GB,在转换TB时就变成了KGB,就看起来很别扭了。那我改改吧。

SELECT NULL TABLESPACE, NULL TABLESPACE_TYPE, NULL E, NULL C, NULL ALLOC_GB, NULL "DB_%",   NULL AUTOEXT_GB, NULL USED_GB, NULL "USED_PCT", NULL FREE_GB, NULL FILES,   NULL SEGMENTS, NULL EXTENTSFROM DUAL WHERE 1 = 0UNION ALL (SELECT NULL TABLESPACE, NULL TABLESPACE_TYPE, NULL E, NULL C, NULL ALLOC_GB, NULL "ALLOC_%",   NULL AUTOEXT_GB, NULL USED_GB, NULL "USED_PCT", NULL FREE_GB, NULL FILES,   NULL SEGMENTS, NULL EXTENTSFROM DUAL WHERE 1 = 0) UNION ALL (SELECT * FROM (WITH BASIS_INFO AS( SELECT /* 11g: Tablespace encryption */    ' ' INCLUDE_USAGE_METRICS,    'TABLESPACE' AGGREGATE_BY,    'SIZE' ORDER_BY,    '%' TABLESPACE_PATTERN_1,    '%' TABLESPACE_PATTERN_2,    TO_DATE('01.01.1000 01:00:00', 'dd.mm.yyyy hh24:mi:ss') CREATED_MIN_TIME,    TO_DATE('01.01.1000 01:00:00', 'dd.mm.yyyy hh24:mi:ss') LAST_DDL_MIN_TIME  FROM    DUAL),TABLESPACES AS( SELECT    TS.TABLESPACE_NAME,    TS.CONTENTS,    SUBSTR(TS.ENCRYPTED, 1, 1) ENCRYPTED,    DECODE(TS.COMPRESS_FOR, NULL, 'N', 'BASIC', 'B', 'OLTP', 'Y') COMPRESSED,    COUNT(*) DATAFILES,    SUM(DF.BYTES) BYTES,    MAX(TUM.TABLESPACE_SIZE) * MAX(TS.BLOCK_SIZE) ALLOC_BYTES_USAGE_METRIC,    MAX(TUM.USED_SPACE) * MAX(TS.BLOCK_SIZE) USED_BYTES_USAGE_METRIC,    DECODE(TS.EXTENT_MANAGEMENT, 'DICTIONARY', 'DMTS', 'LMTS') || '/' ||      SUBSTR(TS.CONTENTS, 1, 1) ||       DECODE(TS.ALLOCATION_TYPE, 'SYSTEM', ' (SYS)', 'UNIFORM',       ' (UNI ' || ROUND(TS.MIN_EXTLEN / 1024 / 1024) || 'M)') ||      DECODE(TS.SEGMENT_SPACE_MANAGEMENT, 'AUTO', ', ASSM') TABLESPACE_TYPE,    SUM(DECODE(DF.AUTOEXTENSIBLE, 'NO', DF.BYTES, DF.MAXBYTES)) MAX_BYTES  FROM    BASIS_INFO BI,    DBA_DATA_FILES DF,    DBA_TABLESPACES TS,    DBA_TABLESPACE_USAGE_METRICS TUM  WHERE    ( BI.TABLESPACE_PATTERN_1 = '%' AND BI.TABLESPACE_PATTERN_2 = '%' OR      BI.TABLESPACE_PATTERN_1 != '%' AND DF.TABLESPACE_NAME LIKE BI.TABLESPACE_PATTERN_1 OR      BI.TABLESPACE_PATTERN_2 != '%' AND DF.TABLESPACE_NAME LIKE BI.TABLESPACE_PATTERN_2 ) AND    DF.TABLESPACE_NAME = TS.TABLESPACE_NAME AND    DF.TABLESPACE_NAME = TUM.TABLESPACE_NAME (+)  GROUP BY    TS.TABLESPACE_NAME,    TS.CONTENTS,    TS.ENCRYPTED,    TS.COMPRESS_FOR,    TS.EXTENT_MANAGEMENT,    TS.CONTENTS,    TS.ALLOCATION_TYPE,    TS.MIN_EXTLEN,    TS.SEGMENT_SPACE_MANAGEMENT,    BI.TABLESPACE_PATTERN_1,    BI.TABLESPACE_PATTERN_2  UNION ALL  ( SELECT      TS.TABLESPACE_NAME,      TS.CONTENTS,      SUBSTR(TS.ENCRYPTED, 1, 1) ENCRYPTED,      DECODE(TS.COMPRESS_FOR, NULL, 'N', 'BASIC', 'B', 'OLTP', 'Y') COMPRESSED,      COUNT(*) DATAFILES,      SUM(TF.BYTES) BYTES,      MAX(TUM.TABLESPACE_SIZE) * MAX(TS.BLOCK_SIZE) ALLOC_BYTES_USAGE_METRIC,      MAX(TUM.USED_SPACE) * MAX(TS.BLOCK_SIZE) USED_BYTES_USAGE_METRIC,      DECODE(TS.EXTENT_MANAGEMENT, 'DICTIONARY', 'DMTS', 'LMTS') || '/' ||        SUBSTR(TS.CONTENTS, 1, 1) ||         DECODE(TS.ALLOCATION_TYPE, 'SYSTEM', ' (SYS)', 'UNIFORM',         ' (UNI ' || ROUND(TS.MIN_EXTLEN / 1024 / 1024) || 'M)') ||        DECODE(TS.SEGMENT_SPACE_MANAGEMENT, 'AUTO', ', ASSM') TABLESPACE_TYPE,      SUM(DECODE(AUTOEXTENSIBLE, 'NO', TF.BYTES, TF.MAXBYTES)) MAX_BYTES    FROM      BASIS_INFO BI,      DBA_TEMP_FILES TF,      DBA_TABLESPACES TS,      DBA_TABLESPACE_USAGE_METRICS TUM    WHERE    ( BI.TABLESPACE_PATTERN_1 = '%' AND BI.TABLESPACE_PATTERN_2 = '%' OR      BI.TABLESPACE_PATTERN_1 != '%' AND TF.TABLESPACE_NAME LIKE BI.TABLESPACE_PATTERN_1 OR      BI.TABLESPACE_PATTERN_2 != '%' AND TF.TABLESPACE_NAME LIKE BI.TABLESPACE_PATTERN_2 ) AND      TF.TABLESPACE_NAME = TS.TABLESPACE_NAME AND      TF.TABLESPACE_NAME = TUM.TABLESPACE_NAME (+)    GROUP BY      TS.TABLESPACE_NAME,      TS.CONTENTS,       TS.ENCRYPTED,      TS.COMPRESS_FOR,      TS.EXTENT_MANAGEMENT,      TS.CONTENTS,      TS.ALLOCATION_TYPE,      TS.MIN_EXTLEN,      TS.SEGMENT_SPACE_MANAGEMENT,      BI.TABLESPACE_PATTERN_1,      BI.TABLESPACE_PATTERN_2  )),SEGMENTS AS( SELECT    TABLESPACE_NAME,    COUNT(*) SEGMENTS,    SUM(BYTES) BYTES,    SUM(EXTENTS) EXTENTS  FROM  ( SELECT      S.TABLESPACE_NAME,      S.BYTES,      S.EXTENTS,      O.CREATED,      O.LAST_DDL_TIME,      BI.CREATED_MIN_TIME,      BI.LAST_DDL_MIN_TIME    FROM      BASIS_INFO BI,      DBA_OBJECTS O,      DBA_SEGMENTS S    WHERE      O.OWNER (+) = S.OWNER AND      O.OBJECT_NAME (+) = S.SEGMENT_NAME AND      O.OBJECT_TYPE (+) = S.SEGMENT_TYPE AND      NVL(O.SUBOBJECT_NAME (+), ' ') = NVL(S.PARTITION_NAME, ' ')   )  WHERE    CREATED IS NULL OR    /* undo segments are not recorded in DBA_OBJECTS, for example */    ( CREATED >= CREATED_MIN_TIME AND      LAST_DDL_TIME >= LAST_DDL_MIN_TIME )  GROUP BY    TABLESPACE_NAME  UNION ALL  ( SELECT      TABLESPACE_NAME,      0 SEGMENTS,      SUM(BYTES_USED) BYTES,      SUM(EXTENTS_USED) EXTENTS    FROM      GV$TEMP_EXTENT_POOL      GROUP BY      TABLESPACE_NAME  ))SELECT  DECODE(BI.AGGREGATE_BY, 'TABLESPACE', T.TABLESPACE_NAME, 'CONTENT', T.CONTENTS) AREA,  DECODE(BI.AGGREGATE_BY, 'TABLESPACE', T.TABLESPACE_TYPE, 'CONTENT', 'n/a') TABLESPACE_TYPE,  DECODE(BI.AGGREGATE_BY, 'TABLESPACE', DECODE(T.ENCRYPTED, 'N', ' ', T.ENCRYPTED), 'CONTENT', 'n/a') E,  DECODE(BI.AGGREGATE_BY, 'TABLESPACE', DECODE(T.COMPRESSED, 'N', ' ', T.COMPRESSED), 'CONTENT', 'n/a') C,  TO_CHAR(SUM(T.BYTES) , 999999999999999990.99) ALLOC_GB,  TO_CHAR(RATIO_TO_REPORT(SUM(T.BYTES)) OVER () * 100, 990.99) "DB_%",  TO_CHAR(SUM(T.MAX_BYTES) , 9999999999999990.99) || DECODE(BI.INCLUDE_USAGE_METRICS, 'X', ' (' ||     DECODE(SUM(T.ALLOC_BYTES_USAGE_METRIC), NULL, '     n/a',     TO_CHAR(SUM(T.ALLOC_BYTES_USAGE_METRIC) , 99999999999999999999.99)) || ')') AUTOEXT_GB,  TO_CHAR(SUM(NVL(S.BYTES, 0)) , 999999999999999990.99)  || DECODE(BI.INCLUDE_USAGE_METRICS, 'X', ' (' ||     DECODE(SUM(T.USED_BYTES_USAGE_METRIC), NULL, '     n/a',     TO_CHAR(SUM(T.USED_BYTES_USAGE_METRIC), 9999999999999999990.99)) || ')') USED_GB,  TO_CHAR(SUM(NVL(S.BYTES, 0)) / SUM(T.BYTES) * 100, 990.99) "USED_%",  TO_CHAR((SUM(T.BYTES) - SUM(NVL(S.BYTES, 0))) , 9999999999990.99) FREE_GB,  TO_CHAR(SUM(T.DATAFILES), 9990) FILES,  TO_CHAR(SUM(NVL(S.SEGMENTS, 0)), 9999990) SEGMENTS,  TO_CHAR(SUM(NVL(S.EXTENTS, 0)), 99999990) EXTENTSFROM  TABLESPACES T,  SEGMENTS S,  BASIS_INFO BIWHERE  T.TABLESPACE_NAME = S.TABLESPACE_NAME (+)GROUP BY  BI.AGGREGATE_BY,  T.CONTENTS,  T.TABLESPACE_NAME,  T.TABLESPACE_TYPE,  T.ENCRYPTED,  T.COMPRESSED,  BI.INCLUDE_USAGE_METRICS,  BI.ORDER_BYORDER BY  DECODE(BI.ORDER_BY,     'SIZE', SUM(T.BYTES),    'AUTOEXT_SIZE', SUM(T.MAX_BYTES),     'FREESPACE', SUM(T.BYTES) - SUM(NVL(S.BYTES, 0)), 1) DESC,  DECODE(BI.ORDER_BY,    'NAME', DECODE(BI.AGGREGATE_BY, 'TABLESPACE', T.TABLESPACE_NAME, 'CONTENT', T.CONTENTS))));

这么一大段的代码,是不是看起来很头疼呢?直接执行结果,单位就变成了B。

我们再去模版Template DB Oracle by ODBC新增监控项,至于怎么增加监控项?而且还是在discovery里边来添加,具体方法就不详细写了,自己去网络上搜索吧。

最终在last data可以看到自己想要的效果了。

标签: #oracle添加栏位