Zabbix(4)通过orabbix插件监控Oracle数据库

DBforBIX可以支持市面上主流的数据库监控,包括Oracle,Mysql,PostgreSQL,MSSQL,DB2,这篇文章简单的记录一下我监控oracle的过程;
软件环境:
Jdk 1.8.0_112
zabbix 3.4.2
orabbix 1.2.3

安装jdk

下载jdk:http://www.oracle.com/technetwork/java/archive-139210.html

1
2
3
4
5
tar xf jdk-8u112-linux-x64.tar.gz -C /usr/local/
vim /etc/profile
export JAVA_HOME=/usr/local/jdk1.8.0_112/
export CLASSPATH=.:$JAVA_HOME/lib/:$JAVA_HOME/jre/lib/
export PATH=$JAVA_HOME/bin:$PATH

1
source /etc/profile

oracle中增加监控用户权限

创建用户

1
2
3
4
5
su - oracle
$ sqlplus /nolog
SQL> conn /as sysdba
SQL> CREATE USER ZABBIX IDENTIFIED BY '<PASSWORD>' DEFAULT TABLESPACE SYSTEM TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK;
SQL> CREATE USER ZABBIX IDENTIFIED BY zabbix DEFAULT TABLESPACE SYSTEM TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK;

赋予权限

1
2
3
4
5
6
GRANT CONNECT TO ZABBIX;
GRANT RESOURCE TO ZABBIX;
GRANT CREATE SESSION TO ZABBIX;
GRANT SELECT ANY DICTIONARY TO ZABBIX;
GRANT UNLIMITED TABLESPACE TO ZABBIX;
GRANT SELECT ANY DICTIONARY TO ZABBIX;

如果是oracle11g的话,需执行以下语句
执行以下语句的时候,注意一下全角半角标点符号

1
2
SQL> exec dbms_network_acl_admin.create_acl(acl =>'resolve.xml',description => 'resolve acl', principal =>'ZABBIX',is_grant => true, privilege => 'resolve');
SQL> exec dbms_network_acl_admin.assign_acl(acl =>'resolve.xml', host =>'*');

验证语句

1
SQL> select utl_inaddr.get_host_name('127.0.0.1') from dual;

安装orabbix

1
2
3
4
wget https://nchc.dl.sourceforge.net/project/orabbix/orabbix-1.2.3.zip
mkdir /usr/local/orabbix
mv orabbix-1.2.3.zip /usr/local/orabbix/
unzip orabbix-1.2.3.zip
1
2
cp config.props.sample config.props
vim config.props
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
ZabbixServerList=ZabbixServer
ZabbixServer.Address=192.168.6.103
ZabbixServer.Port=10051
OrabbixDaemon.Sleep=300
OrabbixDaemon.MaxThreadNumber=100
#DatabaseList这里配置的主机名一定要和Zabbix里添加的主机名称一致,否则获取不到数据
DatabaseList=vm00
DatabaseList.MaxActive=10
DatabaseList.MaxWait=100
DatabaseList.MaxIdle=1
vm00.Url=jdbc:oracle:thin:@192.168.6.100:1521:ORCL
vm00.User=ZABBIX
vm00.Password=zabbix
vm00.MaxActive=10
vm00.MaxWait=100
vm00.MaxIdle=1
vm00.QueryListFile=./conf/query.props
1
2
3
4
5
cd /usr/local/orabbix
vim run.sh
修改java为/usr/local/jdk1.8.0_112/bin/java
chmod +x run.sh
cp init.d/orabbix /etc/init.d/
1
2
3
4
5
vim /etc/init.d/orabbix
修改orabbix=/opt/orabbix为orabbix=/usr/local/orabbix
chmod +x /etc/init.d/orabbix
chkconfig --level 35 --add orabbix
/etc/init.d/orabbix start

如果出现异常,可以查看日志排查 /usr/local/orabbix/logs/orabbix.log

导入模板

将/usr/local/orabbix/template/Orabbix_export_full.xml模板导入
zabbix_install
zabbix_install
添加主机,关联oracle数据库监控模板
zabbix_install
默认的配置文件无法正常获取到dbfilesize和dbsize,需要手动修改一下配置文件
/usr/local/orabbix/conf/query.props
在配置文件最后添加

1
2
3
4
5
6
7
8
dbfilesize.Query=select to_char(sum(bytes/1024/1024/10), 'FM99999999999999990') retvalue from dba_data_files
dbsize.Query=SELECT to_char(sum( NVL(a.bytes/1024/1024/10 - NVL(f.bytes/1024/1024/10, 0), 0)), 'FM99999999999999990') retvalue \
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')

在QueryList最后添加,dbfilesize,dbsize

1
2
3
4
5
6
QueryList=archive,audit,dbblockgets,dbconsistentgets,dbhitratio,dbphysicalread,dbversion,hitratio_body,hitratio_sqlarea,hitratio_table_proc, \
lio_current_read,locks,maxprocs,maxsession,miss_latch,pga_aggregate_target, pga,phio_datafile_reads,phio_datafile_writes,phio_redo_writes,pinhitratio_body,pinhitratio_sqlarea,pinhitratio_table-proc,pinhitratio_
trigger, \
pool_dict_cache,pool_free_mem,pool_lib_cache,pool_misc,pool_sql_area,procnum,session_active,session_inactive,session,session_system,sga_buffer_cache, \
sga_fixed,sga_java_pool,sga_large_pool,sga_log_buffer,sga_shared_pool,tbl_space,userconn,waits_controfileio,waits_directpath_read, \
waits_file_io,waits_latch,waits_logwrite,waits_multiblock_read,waits_singleblock_read,hitratio_trigger,lio_block_changes,lio_consistent_read,waits_other,waits_sqlnet,users_locked,uptime,dbfilesize,dbsize

zabbix_install
可以在最新数据里看到已经监控到的数据
zabbix_install

orabbix for zabbix4.x

如果使用的zabbix4.x版本,orabbix1.2.3默认是不能获取到监控项的,可以参考https://github.com/snickerjp/orabbix,重新编译orabbix-1.2.3.jar,替换目前版本即可;如果不想编译,这里有现成的: https://pan.baidu.com/s/1-QmNXEphOtJL6c-OZg9Q-A 提取码: 76zt 失效联系我。

参考链接:
https://www.kancloud.cn/devops-centos/centos-linux-devops/361200
http://fengwan.blog.51cto.com/508652/1722118
http://www.smartmarmot.com/wiki/index.php?title=DBforBIX#Install_steps_for_Oracle

独学无友,孤陋寡闻;学习记录,欢迎拍砖!