zabbix创建oracle监控模板

On 2024年4月7日2024年4月7日By 去哪儿

1、监控asm磁盘、表空间、实例名称，数据以json方式呈现

编写脚本rac.asm_disk[*]获取asm磁盘名称，提供自动发现规则使用，DISK_NAME必须是大写的。数据呈现格式：[{"{#DISK_NAME}":"MGMT"},{"{#DISK_NAME}":"DATA"}]

#!/bin/bash
result="\""{\#DISK_NAME}"\":"
for diskgroup in $(echo 'SELECT NAME  FROM v$asm_diskgroup;' | sudo su - oracle -c "sqlplus -s / as sysdba" | grep -Ev "NAME|-|^$"); do
   # 查询每个磁盘组的名称
   result_disk="{$result"\"$diskgroup\"}","$result_disk
done
echo "["$(echo $result_disk | sed 's/,$//')"]"

编写脚本rac.asm_disk[*]获取asm磁盘空间使用情况，数据呈现格式：{"DATA":{"TOTAL_MB":300,"FREE_MB":286},"MGMT":{"TOTAL_MB":80,"FREE_MB":30}}

#!/bin/bash
i=1
for diskgroup in $(echo 'SELECT NAME,TOTAL_MB/1024,round(FREE_MB/1024,0) FROM v$asm_diskgroup;' | sudo su - oracle -c "sqlplus -s / as sysdba" | grep -Ev "NAME|-|^$"); do
   # 查询每个磁盘组的空间信息
   if [ $i -eq 1 ];then
      result_disk=$result_disk","\"$diskgroup\"
      ((i++))
   elif [ $i -eq 2 ];then
      result_disk=$result_disk":{\"TOTAL_MB\":$diskgroup"
      ((i++))
   else
      result_disk=$result_disk",\"FREE_MB\":$diskgroup}"
      i=1
   fi
done
echo "{"$(echo $result_disk | sed 's/^,//')"}"

监控表空间、实例状态，脚本如asm磁盘监控，唯一区别将两个脚本写在同一个脚本中，采用$1调用

#!/bin/bash

if [ $1 == "tablespace_name" ];then
	#获取表空间名称
	result="\""{\#TABLESPACE_NAME}"\":"
	for tablespace_name in $(echo 'SELECT tablespace_name  FROM dba_data_files;' | sudo su - oracle -c 'sqlplus -s / as sysdba' | grep -Ev 'TABLESPACE_NAME|Free_Space|-|rows|^$');do
		result_tablename="{$result"\"$tablespace_name\"}","$result_tablename
	done
	echo "["$(echo $result_tablename | sed 's/,$//')"]"
elif [ $1 == "tablespace_free" ];then
	#获取表空间剩余可用空间
	i=1
	for tablespace in $(echo 'SELECT df.tablespace_name,ROUND(SUM(f.bytes) / 1024 / 1024, 2) AS "Free_Space" FROM dba_free_space f JOIN dba_data_files df ON f.tablespace_name = df.tablespace_name GROUP BY df.tablespace_name;' | sudo su - oracle -c 'sqlplus -s / as sysdba' | grep -Ev 'TABLESPACE_NAME|Free_Space|-|rows|^$');do
		if [ $i -eq 1 ];then
			result_space=$result_space","\"$tablespace\"
      			((i++))
   		elif [ $i -eq 2 ];then
      			result_space=$result_space":{\"FREE_MB\":$tablespace}"
      			i=1
   		fi
	done
	echo "{"$(echo $result_space | sed 's/^,//')"}"
elif [ $1 == "instance_state" ];then
	#数据库状台信息
	i=1
        for dbinfo in $(echo 'SELECT instance_name, status FROM gv$INSTANCE;' | sudo su - oracle -c 'sqlplus -s / as sysdba' | grep -Ev 'HOST_NAME|INSTANCE_NAME|STATUS|-');do
		if [ $i -eq 1 ];then
			result_dbinfo=$result_dbinfo","\"$dbinfo"\""
			((i++))
		elif [ $i -eq 2 ];then
			result_dbinfo=$result_dbinfo":{\"STATUS\":\"$dbinfo\"}"
			i=1
		fi            
	done
	echo "{"$(echo $result_dbinfo | sed 's/^,//')"}"
elif [ $1 == "instance_name" ];then
	#获取数据库名称
	result="\""{\#DB_NAME}"\":"
        for db_name in $(echo 'SELECT instance_name FROM gv$INSTANCE;' | sudo su - oracle -c 'sqlplus -s / as sysdba' | grep -Ev 'HOST_NAME|INSTANCE_NAME|STATUS|-');do
                result_dbname="{$result"\"$db_name\""},"$result_dbname
        done
        echo "["$(echo $result_dbname | sed 's/,$//')"]"
fi

2、客户端主机修改配置

将用户zabbix加入wheel组：usermod -aG wheel zabbix

visudo配置wheel组NOPASSWORD：%wheel ALL=(ALL) NOPASSWD: ALL

zabbix配置文件修改：UnsafeUserParameters=1，Timeout=15

配置自定义监控文件：

[root@rac01 script]# cat /etc/zabbix/zabbix_agent2.d/oracle.conf 
UserParameter=rac.asm_disk[*], /u01/script/oracle_disk.sh
UserParameter=rac.disk[*], /u01/script/oracle_asm.sh
UserParameter=ap_check_cmd[*], /usr/bin/python2.7 /u01/script/check_ap.py $1
UserParameter=rac.space[*], /u01/script/oracle_tablesapce.sh $1

3、zabbix创建模板