使用Zabbix监控Oracle数据库实例（orabbix插件）

效果如下

环境介绍

172.16.0.168 centos6.9 Oracle11g
172.16.0.21 zabbix3.4.15 MariaDB5.5.68 zabbix和数据库同一台机器

一、zabbix-mysql搭建

修改主机名，加入一个zabbix国内源
hostnamectl set-hostname zabbix
cd /etc/yum.repos.d/
vim zabbix.repo
##repo源内容如下
[zabbix]
name=Zabbix Official Repository - $basearch
baseurl=https://mirrors.aliyun.com/zabbix/zabbix/3.4/rhel/7/$basearch/
enabled=1
gpgcheck=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-ZABBIX-A14FE591
[zabbix-non-supported]
name=Zabbix Official Repository non-supported - $basearch
baseurl=https://mirrors.aliyun.com/zabbix/non-supported/rhel/7/$basearch/
enabled=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-ZABBIX
gpgcheck=0

mariadb安装配置
yum makecache
yum install -y mariadb mariadb-server zabbix-server-mysql
cd /usr/share/mysql/
cp my-huge.cnf /etc/my.cnf
vim /etc/my.cnf
#修改如下内容
61 server-id   = 21
62 log_slave_updates
63 binlog-ignore-db = mysql
64 datadir = /data/mysql

mkdir -p /data/mysql
chown -R mysql:mysql /data/mysql/
systemctl start mariadb
systemctl enable mariadb

创建zabbix数据库并授权
mysql -u root -p
MariaDB [(none)]> create database zabbix character set utf8 collate utf8_bin;
MariaDB [(none)]> grant all on zabbix.* to zabbix@'localhost' identified by '12345';
MariaDB [(none)]> grant all on zabbix.* to zabbix@'172.16.%' identified by '12345';
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> quit

cd /usr/share/doc/zabbix-server-mysql-3.4.15/
gunzip create.sql.gz
mysql zabbix < create.sql
##检查zabbix的表是否导入成功
mysql -u zabbix
MariaDB [zabbix]> show tables;

二、LAMP环境搭建

yum install -y httpd php php-mysql php-gd libjpeg* php-ldap php-odbc php-pear php-xml php-xmlrpc php-mbstring php-bcmath php-mhash

vim /etc/httpd/conf/httpd.conf
 DirectoryIndex index.php index.html <--修改这行，加入index.php
 ##修改如下内容
vim /etc/php.ini
384 max_execution_time = 300
        ##脚本最大执行时间，秒
394 max_input_time = 300
        ##接收数据的最大时间，秒
672 post_max_size = 16M
        ##接收数据的最大值
878 date.timezone = Asia/Shanghai
        ##时区

三、zabbix-server配置

yum install -y zabbix-server-mysql zabbix-web-mysql zabbix-get zabbix-java-gateway

添加简体中文支持字库
yum -y install wqy-microhei-fonts  <--微软雅黑字体
mkdir -p /usr/share/zabbix/assets/fonts/
mv /usr/share/fonts/wqy-microhei/wqy-microhei.ttc /usr/share/zabbix/assets/fonts/msyh.ttf
cd /usr/share/zabbix/include/
修改zabbixUI配置文件，53行是字体路径，54行是字体名字
vim defines.inc.php
  53 define('ZBX_FONTPATH',  realpath('assets/fonts')); // where to search for font (GD > 2     .0.18)
  54 define('ZBX_GRAPH_FONT_NAME',             'msyh'); // font file name

修改JAVA配置文件
vim /etc/zabbix/zabbix_java_gateway.conf
打开这3行注释，修改线程数为50
  9 LISTEN_IP="0.0.0.0"
17 LISTEN_PORT=10052
35 START_POLLERS=50


修改zabbix主配置文件
vim /etc/zabbix/zabbix_server.conf
91 DBHost=localhost ##mysql的地址，如果在同一台机器就填localhost，或者zabbix本机IP都可以
100 DBName=zabbix ##mysql中zabbix的库名
116 DBUser=zabbix    ##zabbix在mysql中的用户名
124 DBPassword=12345 ##zabbix在mysql中的密码
131 DBSocket=/var/lib/mysql/mysql.sock  #mysql套接字文件所在位置
139 DBPort=3306    ##mysql远程端口
261 JavaGateway=localhost    ##java的地址，写localhost就行
269 JavaGatewayPort=10052  ##java-gate-way的服务端口
277 StartJavaPollers=50        ##java的工作线程数

启动服务
systemctl start httpd
systemctl start zabbix-java-gateway
systemctl start zabbix-server
systemctl enable httpd
systemctl enable zabbix-java-gateway
systemctl enable zabbix-server

检查服务状态
三个端口都出现说明配置成功
不成功就看zabbix的错误日志less /var/log/zabbix/zabbix_server.log
netstat -antp | egrep ":80|:10051|:10052"
tcp        0      0 0.0.0.0:10051           0.0.0.0:*               LISTEN      3578/zabbix_server
tcp6       0      0 :::10051                :::*                    LISTEN      3578/zabbix_server
tcp6       0      0 :::10052                :::*                    LISTEN      3442/java  
tcp6       0      0 :::80                   :::*                    LISTEN      3304/httpd

登录zabbix
http://172.16.0.21/zabbix
默认账号:Admin
默认密码:zabbix

四、Oracle端配置

因为Orabbix插件是通过java客户端连接形式进行采集，所以需要oracle对这个连接用户进行授权，
主要需要一些登录访问权限，动态视图查看权限，存储过程调用权限等

SYS@prod>ed
  1  CREATE USER ZABBIX  --创建zabbix用户
  2  IDENTIFIED BY zabbix   --密码zabbix
  3  DEFAULT TABLESPACE SYSTEM    --保存到system表空间
  4  TEMPORARY TABLESPACE TEMP    --分配临时表空间
  5  PROFILE DEFAULT    --配置文件默认
  6* ACCOUNT UNLOCK    --解锁账户
SYS@prod>/

--分配两个角色给zabbix
SYS@prod>GRANT CONNECT TO ZABBIX; --赋予连接权限

Grant succeeded.

SYS@prod>GRANT RESOURCE TO ZABBIX; --赋予存储过程调用权限

Grant succeeded.

SYS@prod>ALTER USER ZABBIX DEFAULT ROLE ALL; --生效角色权限

User altered.

--分配4个系统权限给zabbix
SYS@prod>GRANT SELECT ANY TABLE TO ZABBIX; --查询权限

Grant succeeded.

SYS@prod>GRANT CREATE SESSION TO ZABBIX; --建立会话权限

Grant succeeded.

SYS@prod>GRANT SELECT ANY DICTIONARY TO ZABBIX; --查询数据字典权限

Grant succeeded.

SYS@prod>GRANT UNLIMITED TABLESPACE TO ZABBIX;  --不限制表空间配额

Grant succeeded.

SYS@prod>conn zabbix/zabbix
Connected.
--验证权限列表
ZABBIX@prod>select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
ZABBIX                         CONNECT                        NO  YES NO
ZABBIX                         RESOURCE                       NO  YES NO



--调用2个存储过程，解除abbix用户ACL限制
SYS@prod>exec dbms_network_acl_admin.create_acl(acl => 'resolve.xml',description => 'resolve acl', principal =>'ZABBIX', is_grant => true, privilege => 'resolve');

PL/SQL procedure successfully completed.

SYS@prod>exec dbms_network_acl_admin.assign_acl(acl => 'resolve.xml', host =>'*');

PL/SQL procedure successfully completed.

SYS@prod>commit;

Commit complete.

--验证结果
SYS@prod>select utl_inaddr.get_host_name('127.0.0.1') from dual;

UTL_INADDR.GET_HOST_NAME('127.0.0.1')
--------------------------------------
localhost

五、Orabbix采集器安装

orabbix安装
wget https://jaist.dl.sourceforge.net/project/orabbix/orabbix-1.2.3.zip
unzip -q orabbix-1.2.3.zip -d /opt/orabbix/
cp /opt/orabbix/init.d/orabbix /etc/init.d/orabbix
chmod +x /etc/init.d/orabbix
chmod +x /opt/orabbix/run.sh
vim /opt/orabbix/conf/config.props.sample
  2 ZabbixServerList=zabbix
  4 zabbix.Address=127.0.0.1
  5 zabbix.Port=10051
18 DatabaseList=oracle11g
31 oracle11g.Url=jdbc:oracle:thin:@172.16.0.167:1521:prod
32 oracle11g.User=zabbix
33 oracle11g.Password=zabbix
35 oracle11g.MaxActive=10
36 oracle11g.MaxWait=100
37 oracle11g.MaxIdle=1
38 oracle11g.QueryListFile=./conf/query.props
生成orabbix配置文件
cp config.props.sample config.props

在oracle和zabbix两台服务器中写入hosts
vim /etc/hosts
172.16.0.167    oracle11g.com   oracle11g
172.16.0.21     zabbix.com      zabbix

添加启动项：
chkconfig --add orabbix
启动orabbix
[root@zabbix orabbix]# /etc/init.d/orabbix start
Starting orabbix (via systemctl):                          [  OK  ]

查看日志
less /opt/orabbix/logs/orabbix.log
如果显示如下内容说明成功
2020-12-31 18:12:03,977 [main] INFO  Orabbix - Starting Orabbix Version 1.2.3
2020-12-31 18:12:03,991 [main] INFO  Orabbix - Orabbix started with pid:4072
2020-12-31 18:12:03,991 [main] INFO  Orabbix - PidFile -> ./logs/orabbix.pid
2020-12-31 18:12:04,207 [main] INFO  Orabbix - DB Pool created: org.apache.commons.dbcp.datasources.SharedPoolDataSource@38cccef
2020-12-31 18:12:04,207 [main] INFO  Orabbix - URL=jdbc:oracle:thin:@172.16.0.167:1521:prod
2020-12-31 18:12:04,207 [main] INFO  Orabbix - maxPoolSize=10
2020-12-31 18:12:04,207 [main] INFO  Orabbix - maxIdleSize=1
2020-12-31 18:12:04,207 [main] INFO  Orabbix - maxIdleTime=1800000ms
2020-12-31 18:12:04,207 [main] INFO  Orabbix - poolTimeout=100
2020-12-31 18:12:04,207 [main] INFO  Orabbix - timeBetweenEvictionRunsMillis=-1
2020-12-31 18:12:04,207 [main] INFO  Orabbix - numTestsPerEvictionRun=3
2020-12-31 18:12:04,560 [main] INFO  Orabbix - Connected as ZABBIX
2020-12-31 18:12:04,562 [main] INFO  Orabbix - --------- on Database -> prod
2020-12-31 18:12:04,769 [pool-1-thread-1] INFO  Orabbix - Done with dbJob on database oracle11g QueryList elapsed time 173 ms


导入orabbix监控模板,一共四个，导入full的就行
位置：/opt/orabbix/template/
Orabbix_export_full.xml    
Orabbix_export_items.xml
Orabbix_export_graphs.xml  
Orabbix_export_triggers.xml
采集器只提供oracle实例的相关信息：如SGA,PGA,SESSION,逻辑IO，物理IO等信息
需要监控oracle服务器等信息需要安装zabbix-agent

总结

没啥总结，挺简单的
遇到问题的话留言吧，看见就会解答