[记录]Zabbix3.4配置监控Oracle12c的存活状态和表空间使用率
Zabbix3.4配置监控Oracle的存活状态和表空间使用率 1.安装zabbix3.4 agent: # rpm -ivh http://repo.zabbix.com/zabbix/3.4/rhel/7/x86_64/zabbix-release-3.4-2.el7.noarch.rpm # yum install zabbix-agent # service zabbix-agent start 2.配置zabbix_agentd.conf [root@centos zabbix]# cat zabbix_agentd.conf PidFile=/var/run/zabbix/zabbix_agentd.pid LogFile=/var/log/zabbix/zabbix_agentd.log LogFileSize=0 Server=server_ip or proxy_ip Hostname=agent_IP Include=/etc/zabbix/zabbix_agentd.d/*.conf UnsafeUserParameters=1 AllowRoot=1 3.添加建立oracle的配置文件: [root@centos scripts]# cat ../zabbix_agentd.d/zabbix_oracle.conf UserParameter=oracle.active,/etc/zabbix/scripts/oracle_status active UserParameter=ora.tab.discovery,/etc/zabbix/scripts/oracle_discovery.sh UserParameter=tablespace[*],/etc/zabbix/scripts/oracle_checktablespace.sh $1 $2 [root@centos scripts]#service zabbix-agent restart 4.创建zabbix监控oracle的用户和密码并授权: CREATE USER ZABBIX IDENTIFIED BY xxx DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP; GRANT CONNECT TO ZABBIX; GRANT RESOURCE TO ZABBIX; ALTER USER ZABBIX DEFAULT ROLE ALL; GRANT SELECT ANY TABLE TO ZABBIX; GRANT CREATE SESSION TO ZABBIX; GRANT SELECT ANY DICTIONARY TO ZABBIX; GRANT UNLIMITED TABLESPACE TO ZABBIX; GRANT SELECT ANY DICTIONARY TO ZABBIX; 5.编写监控oracle存活状态的脚本: [root@centos scripts]# cat oracle_status #!/bin/bash function active() { ps -ef | grep pmon | grep -v grep | awk '{print $NF}' > /dev/null if [ $? -eq 0 ]; then echo 1 else echo 0 fi } if [ $# -ne 1 ]; then echo "the args is error." else $1 fi 6.编写监控oracle表空间的脚本(三个脚本): 1)获取表空间状态输出到/tmp/oracle_tablespace.log文件。这是一个定时脚本。 */1 * * * * oracle /bin/bash /etc/zabbix/scripts/oracle_tablespace.sh >> /tmp/oracle_tablespace_cron.log 2>&1 [root@centos scripts]# cat oracle_tablespace.sh #!/bin/bash #function: #获取到oracle数据库中的tablespace列表,并输出到/tmp/oracle_tablespace.log日志中去,在crontab中设置每一分钟执行一次. source /home/oracle/.bash_profile sqlplus -s zabbix/xxx> /tmp/oracle_tablespace.log<<EOF set linesize 140 pagesize 10000 col "Status" for a10 col "Name" for a25 col "Type" for a10 col "Extent" for a15 SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent", NVL(a.bytes, 0) "Size (M)", NVL(a.bytes - NVL(f.bytes, 0), 0) "Used (M)", round(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0),2) "Used %" 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 d.tablespace_name not in ('SYSAUX','SYSTEM') AND NOT (d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY') UNION ALL SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent", NVL(a.bytes, 0) "Size (M)", NVL(t.bytes,0) "Used (M)", round(NVL(t.bytes / a.bytes * 100, 0),2) "Used %" FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a, (select tablespace_name, sum(bytes_cached) bytes from v\$temp_extent_pool group by tablespace_name) t WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY' ORDER BY 7; EOF sed -i '$d' /tmp/oracle_tablespace.log sed -i '$d' /tmp/oracle_tablespace.log sed -i 's/,//g' /tmp/oracle_tablespace.log 2)从/tmp/oracle_tablespace.log文件中取出Name那一列,并进行JSON格式化输出。 [root@centos scripts]# cat oracle_discovery.sh #!/bin/bash #function: #这个脚本的功能是从/tmp/oracle_tablespace.log文件中取出Name那一列,并进行JSON格式化输出(因为zabbix的自动发现功能获取的数据类型是JSON格式的) TABLESPACE=`cat /tmp/oracle_tablespace.log |awk '{print$2}'|awk 'NR>3{print}'` 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 ']}' 3)这是zabbix调用获取值的脚本: [root@centos scripts]# cat oracle_checktablespace.sh #!/bin/bash #function: #获取/tmp/oracle_tablespace.log中的最后三列数据,其中maxmb和used取出的值是M,根据需要转换单位,方便zabbix取值. EQ_DATA="$2" ZBX_REQ_DATA_TAB="$1" SOURCE_DATA=/tmp/oracle_tablespace.log case $2 in maxmb) grep -wE "$ZBX_REQ_DATA_TAB" $SOURCE_DATA | awk '{print $5}';; used) grep -wE "$ZBX_REQ_DATA_TAB" $SOURCE_DATA | awk '{print $6}';; autopercent) grep -wE "$ZBX_REQ_DATA_TAB" $SOURCE_DATA | awk '{print $7}';; *) echo $ERROR_WRONG_PARAM exit 1;; esac exit 0
7.zabbix3.4-server面板添加agent主机并配置对应的监控项,图表和触发器。如下图:
1)添加主机:
链接模板:
2)添加oracle存活状态的监控:
添加图表:
图表数据展示:
添加触发器:
3)添加oracle表空间使用率的监控:
添加过滤关联item项:
添加图表:
数据展示:
添加触发器:
报警示例:
完。