CentOS下zabbix通过Orabbix监控oracle数据库

一、在zabbix-server上操作
1、安装JDK
1.1用yum安装JDK
查看yum库中都有哪些jdk版本(暂时只发现了openjdk)
[[email protected] ~]# yum search java|grep jdk
1.2选择版本,进行安装
//选择 1.8 版本进行安装
[[email protected] ~]# yum install java-1.8.0-openjdk
//安装完之后，java默认的安装目录是在:/usr/lib/ jvm/java-1.8.0-openjdk-1.8.0.201.b09-2.el7_6.x86_64/jre/bin/java
1.3设置环境变量
[[email protected] ~]# vi /etc/profile
在profile文件中添加如下内容

#set java environment
JAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.201.b09-2.el7_6.x86_64
JRE_HOME=$JAVA_HOME/jre
CLASS_PATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar:$JRE_HOME/lib
PATH=$PATH:$JAVA_HOME/bin:$JRE_HOME/bin
export JAVA_HOME JRE_HOME CLASS_PATH PATH

1.4让修改生效
[[email protected] ~]# source /etc/profile
1.5验证
[[email protected] ~]# java -version

openjdk version "1.8.0_201"
OpenJDK Runtime Environment (build 1.8.0_201-b09)
OpenJDK 64-Bit Server VM (build 25.201-b09, mixed mode)

2、安装orbbix软件
2.1下载 orabbix-1.2.3.zip
http://sourceforge.net/projects/orabbix/files/orabbix-1.2.3.zip/download
新建文件夹 mkdir /opt/orabbix
cd /opt/orabbix
我用的是xshell，可以用指令rz打开windows中的文件

2.2安装unzip orabbix-1.2.3.zip

2.3添加服务启动脚本，和相关脚本赋权

cp /opt/orabbix/init.d/orabbix /etc/init.d/
chmod u+x /etc/init.d/orabbix
chmod u+x /opt/orabbix/run.sh
2.4 修改 run.sh
vi run.sh
java路径需要修改

/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.201.b09-2.el7_6.x86_64/jre/bin/java -Duser.language=en -Duser.country=US -Dlog4j.configuration=./conf/log4j.properties -cp $(for i in lib/*.jar ; do echo -n $i: ; done).:./orabbix-1.2.3.jar com.smartmarmot.orabbix.bootstrap start ./conf/config.props &

二、在oracle主机上操作
1、创建数据库实例，账号、密码（知道这步可以省略）
所以我省略了
2、连接数据库并授权

conn 账户名/密码@实例名

GRANT CONNECT TO ~~ZABBIX~~ ;#账户名
GRANT RESOURCE TO ~~ZABBIX~~ ;
ALTER USER ~~ZABBIX~~  DEFAULT ROLE ALL;
– 5 System Privileges for ~~ZABBIX~~ 
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~~ ;

2、创建配置文件config.props
cd /opt/orabbix/conf/
cp /opt/orabbix/conf/config.props.sample config.props
2.1配置参数

[[email protected] conf]# cat config.props|grep -v '^#|^$'
#comma separed list of Zabbix servers
ZabbixServerList=ZabbixServer1
ZabbixServer1.Address=~~192.168.0.95~~ #zabbix-server的IP（如果在本机上运行，直接填127.0.0.1）
ZabbixServer1.Port=~~10051~~  #zabbix-server的端口号
 
#pidFile
OrabbixDaemon.PidFile=./logs/orabbix.pid
#frequency of item's refresh
OrabbixDaemon.Sleep=300
#MaxThreadNumber should be >= than the number of your databases
OrabbixDaemon.MaxThreadNumber=100
 
#put here your databases in a comma separated list
DatabaseList=~~oracledb~~ #客户端主机名，与zabbix页面上配置的主机名相同
 
#Configuration of Connection pool
#if not specified Orabbis is going to use default values (hardcoded)
#Maximum number of active connection inside pool
DatabaseList.MaxActive=10
#The maximum number of milliseconds that the pool will wait
#(when there are no available connections) for a connection to be returned
#before throwing an exception, or <= 0 to wait indefinitely.
DatabaseList.MaxWait=100
DatabaseList.MaxIdle=1
 
#define here your connection string for each database
~~oracledb~~ .Url=jdbc:oracle:thin:@~~192.168.0.92~~ :~~1521~~ :~~dbsrv1~~ 
#                                oracle数据库IP地址    端口号       数据库名称（不是实例名）
~~oracledb~~ .User=~~zabbix~~ #账号
~~oracledb~~ .Password=~~zabbix~~ #密码
#Those values are optionals if not specified Orabbix is going to use the general values
~~oracledb~~ .MaxActive=10
~~oracledb~~ .MaxWait=100
~~oracledb~~ .MaxIdle=1
~~oracledb~~ .QueryListFile=./conf/query.props

2.2 启动orabbix
service orabbix start
查看启动的进程
ps aux |grep orabbix
2.3查看日志，是否配置成功
cat /opt/orabbix/logs/orabbix.log

这样显示就算配置成功

三、zabbix-server web页面上添加监控杆模板
1、导入orabbix-1.2.3.zip中template文件夹中的xml文件
Orabbix_export_full.xml 全部导入(图表 监控项 触发器)
Orabbix_export_graphs.xml 图表
Orabbix_export_items.xml 监控项
Orabbix_export_triggers.xml 触发器

如果导入失败，可能是模板名重复，需要将orabbix的4个xml里面的TEMPLATES改成TEMPLATES_ORACLE，然后重新导入import即可。

2、 将被监控的主机关联模板
3、你会发现有一些监控项dbfilesize,dbsize没有值，这是因为/opt/orabbix/conf/query.props文件没有把这两个加入进查询的List下面，可以从query.props.sample把这两项复制进去

vi /opt/orabbix/conf/query.props
在QueryList最后面加上dbfilesize,dbsize

然后在文件末尾加入：

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')

四、结果演示