Zabbix实战-简易教程--DB类--Oracle(表空间)
一、获取LLD的JSON结果（多个表空间名称）
通过脚本获取多个表空间名称，基本上和前面其他LLD一模一样：
#!/bin/bash gameprocess=(`su - oracle -c /etc/zabbix/externalscripts/tbs_usage.sh|grep -v "-"|grep -v ".*NAME"|grep -v "^$"|grep -v "rows"|awk -F ["|"] '{print $1,"-",$2}'|sed 's/[ \t]*//g' 2>/dev/null`) #gameprocess=(`su - oracle -c /etc/zabbix/externalscripts/tbs_usage.sh|grep -v "-"|grep -v ".*NAME"|grep -v "^$"|grep -v "rows"|awk -F ["|"] '{print $1}' 2>/dev/null`) length1=${#gameprocess[@]} printf "{\n" printf '\t'"\"data\":[" for ((i=0;i<$length1;i++)) do printf '\n\t\t{' printf "\"{#GAMEPROCESS}\":\"${gameprocess[$i]}\"}" if [ $i -lt $[$length1-1] ];then printf ',' fi done printf "\n\t]\n" printf "}\n" EOF
脚本执行结果如下：
[root@centos6564-73-131 ~]# zabbix_get -s 192.168.72.160 -k custom.tablespace.discovery { "data":[ {"{#GAMEPROCESS}":"LIEMO-FS3ACCOUNTBDATA"}, {"{#GAMEPROCESS}":"LIEMO-FS3ACCOUNTEDATA"}, {"{#GAMEPROCESS}":"LIEMO-FS3ACCOUNTIDX"}, {"{#GAMEPROCESS}":"LIEMO-FS3BASICINFIDX"}, {"{#GAMEPROCESS}":"LIEMO-FS3BASICINFODATA"}, {"{#GAMEPROCESS}":"LIEMO-FS3DUBADATA"}, {"{#GAMEPROCESS}":"LIEMO-FS3LOGOPER201006"}, {"{#GAMEPROCESS}":"LIEMO-FS3LOGOPER201506"}, {"{#GAMEPROCESS}":"LIEMO-FS3LOGOPER201512"}, {"{#GAMEPROCESS}":"LIEMO-FS3LOGOPER201606"}, {"{#GAMEPROCESS}":"LIEMO-FS3LOGOPER201612"}, {"{#GAMEPROCESS}":"LIEMO-FS3LOGOPERMAXVALUE"}, {"{#GAMEPROCESS}":"LIEMO-FS3LOGUF"}, {"{#GAMEPROCESS}":"LIEMO-FS3LOGUFIDX"}, {"{#GAMEPROCESS}":"LIEMO-FS3STATCITY"}, {"{#GAMEPROCESS}":"LIEMO-FS3STATDATA"}, {"{#GAMEPROCESS}":"LIEMO-FS3STATIDX"}, {"{#GAMEPROCESS}":"LIEMO-LIEMENGACCOUNTBDATA"}, {"{#GAMEPROCESS}":"LIEMO-LIEMENGACCOUNTEDATA"}, {"{#GAMEPROCESS}":"LIEMO-LIEMENGACCOUNTIDX"}, {"{#GAMEPROCESS}":"LIEMO-LIEMENGBASICINFIDX"}, {"{#GAMEPROCESS}":"LIEMO-LIEMENGBASICINFODATA"}, {"{#GAMEPROCESS}":"LIEMO-LIEMENGDEFDATA"}, {"{#GAMEPROCESS}":"LIEMO-LIEMENGLOGOPER201406"}, {"{#GAMEPROCESS}":"LIEMO-LIEMENGLOGOPER201412"}, {"{#GAMEPROCESS}":"LIEMO-LIEMENGLOGOPERMAXVALUE"}, {"{#GAMEPROCESS}":"LIEMO-LIEMENGLOGUF"}, {"{#GAMEPROCESS}":"LIEMO-LIEMENGLOGUFIDX"}, {"{#GAMEPROCESS}":"LIEMO-LIEMENGSTATCITY"}, {"{#GAMEPROCESS}":"LIEMO-LIEMENGSTATDATA"}, {"{#GAMEPROCESS}":"LIEMO-LIEMENGSTATIDX"}, {"{#GAMEPROCESS}":"LIEMO-LIEMOACCOUNTBDATA"}, {"{#GAMEPROCESS}":"LIEMO-LIEMOACCOUNTEDATA"}, {"{#GAMEPROCESS}":"LIEMO-LIEMOACCOUNTIDX"}, {"{#GAMEPROCESS}":"LIEMO-LIEMOBASICINFIDX"}, {"{#GAMEPROCESS}":"LIEMO-LIEMOBASICINFODATA"}, {"{#GAMEPROCESS}":"LIEMO-LIEMODEFDATA"}, {"{#GAMEPROCESS}":"LIEMO-LIEMOLOGOPER201406"}, {"{#GAMEPROCESS}":"LIEMO-LIEMOLOGOPER201412"}, {"{#GAMEPROCESS}":"LIEMO-LIEMOLOGOPERMAXVALUE"}, {"{#GAMEPROCESS}":"LIEMO-LIEMOLOGUF"}, {"{#GAMEPROCESS}":"LIEMO-LIEMOLOGUFIDX"}, {"{#GAMEPROCESS}":"LIEMO-LIEMOSTATCITY"}, {"{#GAMEPROCESS}":"LIEMO-LIEMOSTATDATA"}, {"{#GAMEPROCESS}":"LIEMO-LIEMOSTATIDX"}, {"{#GAMEPROCESS}":"LIEMO-SYSAUX"}, {"{#GAMEPROCESS}":"LIEMO-SYSTEM"}, {"{#GAMEPROCESS}":"LIEMO-UNDOTBS1"}, {"{#GAMEPROCESS}":"LIEMO-UNDO_2"}, {"{#GAMEPROCESS}":"LIEMO-USERS"}, {"{#GAMEPROCESS}":"YUEING-JX1ACCOUNTADMIN"}, {"{#GAMEPROCESS}":"YUEING-JX1ACCOUNTBDATA"}, {"{#GAMEPROCESS}":"YUEING-JX1ACCOUNTEDATA"}, {"{#GAMEPROCESS}":"YUEING-JX1ACCOUNTIDX"}, {"{#GAMEPROCESS}":"YUEING-JX1BASICINFIDX"}, {"{#GAMEPROCESS}":"YUEING-JX1BASICINFODATA"}, {"{#GAMEPROCESS}":"YUEING-JX1DUBADATA"}, {"{#GAMEPROCESS}":"YUEING-JX1LOGOPER201512"}, {"{#GAMEPROCESS}":"YUEING-JX1LOGOPER201606"}, {"{#GAMEPROCESS}":"YUEING-JX1LOGOPER201612"}, {"{#GAMEPROCESS}":"YUEING-JX1LOGOPERMAXVALUE"}, {"{#GAMEPROCESS}":"YUEING-JX1LOGUF"}, {"{#GAMEPROCESS}":"YUEING-JX1LOGUFIDX"}, {"{#GAMEPROCESS}":"YUEING-JX1STATCITY"}, {"{#GAMEPROCESS}":"YUEING-JX1STATDATA"}, {"{#GAMEPROCESS}":"YUEING-JX1STATIDX"}, {"{#GAMEPROCESS}":"YUEING-SYSAUX"}, {"{#GAMEPROCESS}":"YUEING-SYSTEM"}, {"{#GAMEPROCESS}":"YUEING-UNDOTBS1"}, {"{#GAMEPROCESS}":"YUEING-UNDO_2"}, {"{#GAMEPROCESS}":"YUEING-USERS"}, {"{#GAMEPROCESS}":"YUEING-YYCSACCOUNTBDATA"}, {"{#GAMEPROCESS}":"YUEING-YYCSACCOUNTEDATA"}, {"{#GAMEPROCESS}":"YUEING-YYCSACCOUNTIDX"}, {"{#GAMEPROCESS}":"YUEING-YYCSBASICINFIDX"}, {"{#GAMEPROCESS}":"YUEING-YYCSBASICINFODATA"}, {"{#GAMEPROCESS}":"YUEING-YYCSDEFDATA"}, {"{#GAMEPROCESS}":"YUEING-YYCSDUBADATA"}, {"{#GAMEPROCESS}":"YUEING-YYCSLOGOPER201503"}, {"{#GAMEPROCESS}":"YUEING-YYCSLOGOPER201506"}, {"{#GAMEPROCESS}":"YUEING-YYCSLOGOPER201509"}, {"{#GAMEPROCESS}":"YUEING-YYCSLOGOPER201512"}, {"{#GAMEPROCESS}":"YUEING-YYCSLOGOPER201603"}, {"{#GAMEPROCESS}":"YUEING-YYCSLOGOPER201606"}, {"{#GAMEPROCESS}":"YUEING-YYCSLOGOPER201609"}, {"{#GAMEPROCESS}":"YUEING-YYCSLOGOPER201612"}, {"{#GAMEPROCESS}":"YUEING-YYCSLOGOPERMAXVALUE"}, {"{#GAMEPROCESS}":"YUEING-YYCSLOGUF"}, {"{#GAMEPROCESS}":"YUEING-YYCSLOGUFIDX"}, {"{#GAMEPROCESS}":"YUEING-YYCSSTATCITY"}, {"{#GAMEPROCESS}":"YUEING-YYCSSTATDATA"}, {"{#GAMEPROCESS}":"YUEING-YYCSSTATIDX"} ] }
脚本里面用到的tbs_usage.sh:
#!/bin/bash str=${ORACLE_HOME#*product/} str1=${str%%.*} if [ "$str1" == "12" ]; then pdbs=`sqlplus -silent '/ as sysdba' << EOSQL whenever sqlerror exit sql.sqlcode set pagesize 0 feedback off verify off heading off echo off timing off select name from v\\$pdbs where open_mode='READ WRITE'; exit; EOSQL` for db in $pdbs ; do sqlplus -s '/as sysdba' <<zzz set lines 200 pages 500 set echo off set feedback off alter session set container=$db; select db_name||' | '||tablespace_name||' | '||allocated||' | '||used||' | '||free||' | '||usage from (select database_name db_name,tablespace_name, round(allocated) allocated,round(used) used,round(allocated-used) free,round(used/allocated*100) usage from (select tablespace_name, sum(case autoextensible when 'YES' then maxbytes else bytes end)/1024/1024 as allocated, sum(user_bytes)/1024/1024 used from dba_data_files group by tablespace_name order by tablespace_name)); exit; zzz done else for SID in $(ps -fu oracle | grep pmon | grep -v grep | cut -d_ -f3- | sort); do export ORACLE_SID=$SID sqlplus -s '/as sysdba' <<zzz set lines 200 pages 500 set echo off set feedback off select db_name||' | '||tablespace_name||' | '||allocated||' | '||used||' | '||free||' | '||usage from (select nvl(substr(database_name,1,instr(database_name,'.',1)-1),'$SID') db_name,tablespace_name, round(allocated) allocated,round(used) used,round(allocated-used) free,round(used/allocated*100) usage from (select tablespace_name, sum(case autoextensible when 'YES' then maxbytes else bytes end)/1024/1024 as allocated, sum(user_bytes)/1024/1024 used from dba_data_files group by tablespace_name order by tablespace_name)); exit; zzz done fi
二、测试和自定义key
获取多表空间名称后，接下来就是获取各个表空间的百分比了，百分比通过自定义key以及sed、awk稍微处理下就可以获取。自定义key如下：
#discovery table space UserParameter=custom.tablespace.discovery,sudo /bin/sh /etc/zabbix/externalscripts/gameprocess.sh UserParameter=custom.tablespace.percent[*],sudo su - oracle -c /etc/zabbix/externalscripts/tbs_usage.sh|grep -v "-"|grep -v ".*NAME"|grep -v "^\$"|grep -v "rows"|sed s/[[:space:]]//g|sed 's/|/-/'|grep \$1|awk -F[\|] '{print \$\$NF}'
执行获取表空间百分比自定义，结果如下：
[root@centos6564-73-131 ~]# zabbix_get -s 192.168.72.160 -k custom.tablespace.percent[YUEING-YYCSSTATIDX] 7
从结果可以获取当前表空间YUEING-YYCSSTATIDX使用百分比为7%。
三、制作模板
模板Template App Oracle已经上传至github地址：https://github.com/loveqx/zabbix-doc/tree/master/zabbix-scripts/zabbix-template-oracle/Template App Oracle.xml
监控项如下：
第一部分是监控oracle进程、主从同步状态、主从归档备份状态以及DB备份状态：
第二部分为表空间监控：
四、主机上套
五、效果图
六、脚本地址和参考文献
https://github.com/loveqx/zabbix-doc/tree/master/zabbix-scripts/zabbix-template-oracle/tb_usage.sh
参考文献：无
