之前通过Orabbix监控Oracle的数据,但是确无法监控表容量,以及添加的新表。
以下通过sql语句提取表数据做一个巡检,通过脚本提取巡检数据,达到监控表容量的作用
vim zabbix.sh
#!/bin/sh
source ~/.bash_profile
connection="system/Pmph\_k28n3 as sysdba" # 连接数据库
echo "连接字符串:${connection}"
echo "--------begin------------"
sqlplus $connection @/root/zabbix.sql ## 读取执行 指定sql文件
echo "--------end------------ "
exit;
############### 以下是巡检的sql文件 ##############
vim zabbix.sql
set head off
set linesize 20000
set echo off
set feedback off
set pagesize 0
set termout off
set trimout on
set trimspool on
COLUMN c_free_mb NEW_VALUE _c_free_mb NOPRINT
COLUMN c_total_mb NEW_VALUE _c_total_mb NOPRINT
COLUMN c_retvalue NEW_VALUE _c_retvalue NOPRINT
COLUMN c_dbfilesize NEW_VALUE _c_dbfilesize NOPRINT
select total_mb c_total_mb,free_mb c_free_mb from v$asm_diskgroup where GROUP_NUMBER = 1;
select to_char(sum(bytes/1024/1024/1024), 'FM99999999999999990') c_retvalue from dba_data_files;
SELECT to_char(sum( NVL(a.bytes/1024/1024/10 - NVL(f.bytes/1024/1024/10, 0), 0)), 'FM99999999999999990') c_dbfilesize FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a, (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NOT (d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY');
spool /root/zabbixxuanjian.log ### 将 巡检文件 导出至 指定的位置,待会儿 zabbix 到这个文件取值
prompt free_mb:&_c_free_mb
prompt total_mb:&_c_total_mb
prompt dbfilesize:&_c_retvalue
prompt dbsize:&_c_dbfilesize
prompt query:
SELECT * FROM (
select '- Tablespace ->',t.tablespace_name ktablespace,
'- Type->',substr(t.contents, 1, 1) tipo,
'- Used(MB)->',trunc((d.tbs_size-nvl(s.free_space, 0))/1024/1024) ktbs_em_uso,
'- ActualSize(MB)->',trunc(d.tbs_size/1024/1024) ktbs_size,
'- MaxSize(MB)->',trunc(d.tbs_maxsize/1024/1024) ktbs_maxsize,
'- FreeSpace(MB)->',trunc(nvl(s.free_space, 0)/1024/1024) kfree_space,
'- Space->',trunc((d.tbs_maxsize - d.tbs_size + nvl(s.free_space, 0))/1024/1024) kspace,
'- Perc->',decode(d.tbs_maxsize, 0, 0, trunc((d.tbs_size-nvl(s.free_space, 0))*100/d.tbs_maxsize)) kperc
from
( select SUM(bytes) tbs_size,
SUM(decode(sign(maxbytes - bytes), -1, bytes, maxbytes)) tbs_maxsize, tablespace_name tablespace
from ( select nvl(bytes, 0) bytes, nvl(maxbytes, 0) maxbytes, tablespace_name
from dba_data_files
)
group by tablespace_name
) d,
( select SUM(bytes) free_space,tablespace_name tablespace
from dba_free_space
group by tablespace_name
表容量取值
vim oracle_check.sh
#!/bin/bash
EQ_DATA="$2"
ZBX_REQ_DATA_TAB="$1"
SOURCE_DATA=/root/zabbixxunjian.log
case $2 in
maxmb)
grep -w "$ZBX_REQ_DATA_TAB" $SOURCE_DATA |awk '{print $16}';;
used)
grep -w "$ZBX_REQ_DATA_TAB" $SOURCE_DATA |awk '{print $10}';;
autopercent)
grep -w "$ZBX_REQ_DATA_TAB" $SOURCE_DATA | awk '{print $NF}' ;;
type)
grep -w "$ZBX_REQ_DATA_TAB" $SOURCE_DATA | awk '{print $7}';;
actualsize)
grep -w "$ZBX_REQ_DATA_TAB" $SOURCE_DATA | awk '{print $13}' ;;
freespace)
grep -w "$ZBX_REQ_DATA_TAB" $SOURCE_DATA | awk '{print $19}' ;;
space)
grep -w "$ZBX_REQ_DATA_TAB" $SOURCE_DATA | awk '{print $22}' ;;
*)
echo $ERROR_WRONG_PARAM; exit 1;;
esac
exit 0
表发现取值
vim oracle_discovery.sh
#!/bin/bash
TABLESPACE=$(cat /root/zabbixxunjian.log | awk '/Tablespace/ {print $4}')
COUNT=$(echo "$TABLESPACE" |wc -l)
INDEX=0
echo '{"data":['
echo "$TABLESPACE" | while read LINE; do
echo -n '{"{#TABLENAME}":"'$LINE'"}'
INDEX=`expr $INDEX + 1`
if [ $INDEX -lt $COUNT ]; then
echo ','
fi
done
echo ']}'
设置zabbix-agent 自定义键值
vim /etc/zabbix/zabbix_agentd.d/userparameter_oracle.conf
UserParameter=ora.tab.discovery,/home/oracle/task/oracle_discovery.sh
UserParameter=tablespace[*],/home/oracle/task/oracle_check.sh $1 $2
UserParameter=dbfilesize,cat /home/oracle/zabbixXJ.log |awk -F ":" '/dbfilesize/ {print $NF}'
UserParameter=dbsize,cat /home/oracle/zabbixXJ.log |awk -F ":" '/dbsize/ {print $NF}'
UserParameter=datadisk_free,cat /home/oracle/zabbixXJ.log | awk '/free_mb/ {print $NF}'
UserParameter=datadisk_total,cat /home/oracle/zabbixXJ.log | awk '/total_mb/ {print $NF}'
systemctl restart zabbix-agent.service # 重启zabbix
zabbix_get -s 192.168.0.130 -k ora.tab.discovery # 在服务端测试键值,返回以下数据
{"data":[
{"{#TABLENAME}":"USERS"},
{"{#TABLENAME}":"UNDOTBS2"},
{"{#TABLENAME}":"SYSTEM"},
{"{#TABLENAME}":"SYSAUX"},
{"{#TABLENAME}":"P_EXAM_ANSWERDETAIL"},
{"{#TABLENAME}":"UNDOTBS1"},
{"{#TABLENAME}":"EXAM2X_DATA"},
{"{#TABLENAME}":"PART_TK_STUDENT_TASK_ANSWER"},
{"{#TABLENAME}":"EXAM_PRODUCTION_SPACE"},
{"{#TABLENAME}":"EXAM_PRODUCTION_BIGSPACE"},
{"{#TABLENAME}":"TEMP"},
{"{#TABLENAME}":"EXAM_PRODUCTION_TEMP"},
{"{#TABLENAME}":"EXAM2X_TEMP"}]}
zabbix服务端导入模板
模板文件已上传:
https://download.youkuaiyun.com/download/qq_26129413/42453555