Zabbix监控Oracle表空间

1、创建监控脚本

# 切换为oracle用户下
[[email protected] ~]# su – oracle
[[email protected] ~]$ vim checklist_space.sh
#!/bin/bash
source ~/.bash_profile
function check {
sqlplus -S "/ as sysdba" <<  EOF
set linesize 200
set pagesize 200
spool /tmp/tablespace.txt
select a.tablespace_name, total, free,(total-free) as usage from 
(select tablespace_name, sum(bytes)/1024/1024 as total from dba_data_files group by tablespace_name) a, 
(select tablespace_name, sum(bytes)/1024/1024 as free from dba_free_space group by tablespace_name) b
where a.tablespace_name = b.tablespace_name;
spool off
set linesize 100
set pagesize 100
spool /tmp/autex.txt
select tablespace_name,autoextensible from dba_data_files;
spool off
quit
EOF
};check &>/dev/null

[[email protected] ~]$ chmod +x checklist_space.sh

# 执行这个脚本，并生成两个保存有oracle表空间名称信息的文件：/tmp/tablespace.txt和/tmp/autex.txt

2、测试监控脚本

#运行监控脚本，然后查看/tmp/tablespace.txt
[[email protected] ~]$ ./checklist_space.sh 
[[email protected] ~]$ cat /tmp/tablespace.txt 

TABLESPACE_NAME                     TOTAL       FREE      USAGE                                                                                                                                         
------------------------------ ---------- ---------- ---------                                                                                                                                       
SYSAUX                                640       37.5      602.5                                                                                                                                         
UNDOTBS1                              155    141.375     13.625                                                                                                                                         
USERS                            14638.75    739.625  13899.125                                                                                                                                         
SYSTEM                                870    89.3125   780.6875                                                                                                                                         
DQZ_TEST                            20480 10572.8125  9907.1875

[[email protected] ~]$ cat /tmp/autex.txt 

TABLESPACE_NAME                AUT                                                                  
------------------------------ ---                                                                  
USERS                          YES                                                                  
UNDOTBS1                       YES                                                                  
SYSAUX                         YES                                                                  
SYSTEM                         YES                                                                  
DQZ_TEST                       YES

加入到oracle用户的定时任务中，每隔5分钟执行一次，执行间隔根据情况来定；
注意：一定保证定时任务可以正常执行，定时任务日志查看

[[email protected] ~]$ crontab -e
*/5 * * * *  /home/oracle/checklist_space.sh

3、创建Zabbix获取数据脚本

[[email protected] ~]# cd /script
[[email protected] script]# vim zabbix_tablespace.sh
#!/bin/bash
table_spaces=(`cat /tmp/tablespace.txt | sed -e "1,3d" -e "/^$/d" -e "/selected/d" | awk '{print $1}'`)
length=${#table_spaces[@]}

printf "{\n"
printf '\t'"\"data\":["
for ((i=0;i<$length;i++))
do
    printf "\n\t\t{"
    printf "\"{#TABLESPACE_NAME}\":\"${table_spaces[$i]}\"}"
    if [ $i -lt $[$length-1] ];then
        printf ","
    fi
done
    printf "\n\t]\n"
printf "}\n"

[[email protected] script]# chmod +x zabbix_tablespace.sh

测试脚本，获取数值

[[email protected] script]# ./zabbix_tablespace.sh 
{
	"data":[
		{"{#TABLESPACE_NAME}":"SYSAUX"},
		{"{#TABLESPACE_NAME}":"UNDOTBS1"},
		{"{#TABLESPACE_NAME}":"USERS"},
		{"{#TABLESPACE_NAME}":"SYSTEM"},
		{"{#TABLESPACE_NAME}":"DQZ_TEST"}
	]
}

创建监控项脚本，获取oracle表空间使用率、剩余量和检查是否开启自动扩展

[[email protected] script]# vim zabbix_tablespace_check.sh
#!/bin/bash
CEHCK_TYPE=$1
TABLESPACE_NAME=$2
 
function usagepre {
    grep "\b$TABLESPACE_NAME\b" /tmp/tablespace.txt | awk '{printf "%.f\n",($2-$3)/$2*100}'
}
 
function available {
    grep "\b$TABLESPACE_NAME\b" /tmp/tablespace.txt | awk '{printf $3*1024*1024}'
}
 
function check {
    if grep "\b$TABLESPACE_NAME\b" /tmp/autex.txt | awk '{print $2}' | uniq | grep "YES" &>/dev/null;then
        echo 1
    else
        echo 0
    fi
}
 
case $CEHCK_TYPE in
    pre)
        usagepre ;;
    fre)
        available ;;
    check)
        check ;;
    *)
        echo -e "Usage: $0 [pre|fre|check] [TABLESPACE_NAME]"
esac

[[email protected] script]# chmod +x zabbix_tablespace_check.sh

测试脚本运行结果

[[email protected] script]# ./zabbix_tablespace_check.sh pre SYSTEM
90

[[email protected] script]# ./zabbix_tablespace_check.sh fre SYSTEM
93650944

[[email protected] script]# ./zabbix_tablespace_check.sh check SYSTEM
1

4、添加至Zabbix-Agent配置中

在Oracle所在服务器的Zabbix-agent配置文件中添加脚本路径和监控项key

注意：这里zabbix的item为discovery.oracle.tablespace和tablespace.check

[[email protected] script]# vim /etc/zabbix/zabbix_agentd.conf
UserParameter=discovery.oracle.tablespace[*],/script/zabbix_tablespace.sh
UserParameter=tablespace.check[*],/script/zabbix_tablespace_check.sh $1 $2

#重启zabbix-agent
[[email protected] ~]# systemctl restart zabbix-agent

5、Zabbix服务端测试

[[email protected] ~]# zabbix_get -s xx.xx.xx.xx -p 10050 -k 'tablespace.check[pre,SYSTEM]'
97

[[email protected] ~]# zabbix_get -s xx.xx.xx.xx -p 10050 -k 'tablespace.check[fre,SYSTEM]'
111214592

[[email protected] ~]# zabbix_get -s xx.xx.xx.xx -p 10050 -k 'tablespace.check[check,SYSTEM]'
1