zabbix通过orabbix监控oracle数据库

十二月 04, 2019

本文记录通过zabbix以及orabbix插件对oracle数据库进行监控。

orabbix简介

orabbix是开源的zabbix监控oracle插件，通过orabbix你可以从任何你想监控的数据库实例中获取数据并在zabbix中展现报表和收集数据，对于每一个发现的麻烦和性能问题都可以设置触发器发送邮件报警。官方网站是http://www.smartmarmot.com/product/orabbix

orabbix架构图

orabbix监控项:

DBVersion 数据库版本(and relative validity of package)
Archive 归档文件( archive log production with relative trend)
EventWaits 等待事件(monitor Files I/O,single block read, multiblock read, direct path read,SQLNet messages, Controlfile I/O,LogWrite)
HitRatio (monitor Hit Ratio on Trigger, Tables/Procedures, SQLArea,Body)
Logical I/O 逻辑IO (monitor Logical I/O values of : Current Read, Consistent Read, Block Change)
PGA
SGA (in particolar: Fixed Buffer, Java Pool, Large Pool, Log Buffer,Shared Pool,Buffer Cache)
Physical I/O 物理IO(Redo Writes,Datafiles Writes,Datafiles Read)
SharedPool (Pool Dictionary Cache, Pool Free Memory, Library Cache,Sql Area ,Misc.)
Pin Hit Ratio (monitor Hit Ratio on Trigger, Tables/Procedures, SQLArea,Body)
Session/Processes (monitor Sessions and processes)
Session (Active Session, Inactive Sessions, System Session)

orabbix监控配置

orabbix部署

基础环境

ubuntu==18.04.1 LTS
zabbix==3.4.14
orabbix==1.2.3
jdk==1.8.0_231

这里zabbix server我是部署在ubuntu系统中，orabbix安装在zabbix server服务器，orabbix是通过JDBC去链接oracle数据库，所以还需要安装jdk环境。
jdk安装

#jdk下载地址https://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html
mkdir -p /usr/local/java
tar -zxvf jdk8.tar.gz
cp ./jdk8/* /usr/local/java
#修改/etc/profile添加环境变量
export JAVA_HOME=/usr/local/java
export JRE_HOME=$JAVA_HOME/jre
export CLASSPATH=.:$JAVA_HOME/lib:$JRE_HOME/lib:$CLASSPATH
export PATH=$JAVA_HOME/bin:$JRE_HOME/bin:$PATH
#测试jdk安装情况
java -version

orabbix安装

#orabbix下载地址http://www.smartmarmot.com/product/orabbix/download/
mkdir /opt/orabbix
cp orabbix-1.2.3.zip /opt/orabbix/
cd /opt/orabbix && unzip orabbix-1.2.3.zip
cp ./conf/config.props.sample conf/config.props
cp init.d/orabbix /etc/init.d
#修改orabbix启动脚本使其符合systemctl规范
systemctl reload
systemctl enable orabbix

orabbix配置

#修改orabbix配置文件/opt/orabbix/conf/config.props
#zabbix sever列表,分隔
ZabbixServerList=ZabbixServer1
#zabbix server1地址
ZabbixServer1.Address=127.0.0.1
#zabbix server1端口
ZabbixServer1.Port=10051
#pid位置
OrabbixDaemon.PidFile=./logs/orabbix.pid
OrabbixDaemon.Sleep=300
OrabbixDaemon.MaxThreadNumber=100
#要监控的数据库列表
DatabaseList=CW-TEST-DB
DatabaseList.MaxActive=10
DatabaseList.MaxWait=100
DatabaseList.MaxIdle=1
#CW-TEST-DB连接信息配置
CW-TEST-DB.Url=jdbc:oracle:thin:@172.18.x.x:1521:orcl
CW-TEST-DB.User=zabbix
CW-TEST-DB.Password=zabbix
CW-TEST-DB.MaxActive=10
CW-TEST-DB.MaxWait=100
CW-TEST-DB.MaxIdle=1
#监控项的查询语句文件，不够用可自定义
CW-TEST-DB.QueryListFile=./conf/query.props

被监控数据库端添加用户及授权

CREATE USER ZABBIX IDENTIFIED BY zabbix DEFAULT TABLESPACE SYSTEM TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK;
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;
GRANT SELECT ON V_$SESSION TO ZABBIX;
GRANT SELECT ON V_$SYSTEM_EVENT TO ZABBIX;
GRANT SELECT ON V_$EVENT_NAME TO ZABBIX;
GRANT SELECT ON V_$RECOVERY_FILE_DEST TO ZABBIX;
-- oracle11g需要额外执行语句
exec dbms_network_acl_admin.create_acl(acl => 'resolve.xml',description => 'resolve acl', principal =>'ZABBIX', is_grant => true, privilege => 'resolve');
exec dbms_network_acl_admin.assign_acl(acl => 'resolve.xml', host =>'*');
commit;

orabbix启动及日志查看

#启动命令
systemctl start orabbix
#日志查看
tail -f /opt/orabbix/logs/orabbix.log

至此orabbix部署完成。

zabbix配置

导入模板（模板在/opt/orabbix/template/Orabbix_export_full.xml）

添加数据库监控，新建主机，主机名称要和orabbix配置文件相对应

设置模板链接

查看效果图

orabbix自定义监控项

在/orabbix/conf/query.props中添加监控项
QueryList添加key如：QueryList=k1,k2,k3
添加key对应的值，实际上是sql语句无需加分号如：k1=sql1
在zabbix界面添加监控项常规步骤，键值要对应

查看评论