zabbix监控Oracle

可监控项

使用zabbix监控oracle数据库需要借助第三方的插件，目前使用较多的是orabbix。目前维护到了1.2.3版本。关于oracle自带的监控项目有以下几个：

DB Version (i.e. Validity of package)数据库版本
Archiving (Archive log production with trend analysis)归档文件
Event Waits (Files I/O, single block read, multi-block read, direct path read, SQLNet Messages, Control file I/O, Log Write)等待的事件
Hit Ratio (Hit Ratio on Triggers, Tables/Procedures, SQL Area, Body)
Logical I/O (Server performance on Logical I/O of: Current Read, Consistent Read, Block Change)逻辑I/O
Physical I/O (Redo Writes, Datafile Writes, Datafile Reads)物理I/O
PGA
SGA (In particular; Fixed Buffer, Java Pool, Large Pool, Log Buffer, Shared Poolm Buffer Cache)
Shared Pool (Pool Dictionary Cache, Pool Free Memory, Library Chache, SQL Area, MISC.)共享池
Pin Hit Ratio (Oracle library cache pin are caused by contention with the library cache, the area used to store SQL executables for re-use)
Sessions / Processes**会话数和进程数**
Sessions (Active Sessions, Inactive Sessions, System Sessions)
DBSize/DBFileSize (DBSize size of database really used space and of Filesize)数据库大小

实施步骤
前期准备
1.节点：zabbix服务端和oracle数据库两个节点。
2.安全设置：为了实验起见，关闭selinux和iptables
3.获取安装包：
orabbix-1.2.3.zip
4.下载安装jdk（x86_64），在oracle的官网可以下载，配置好JAVA_HOME等环境变量。

安装步骤
1.创建访问数据库的用户并进行相关授权

使用oracle的管理员用户登录oracle数据库：

[oracle@server ~]$ sqlplus sys/oracle@hybris as sysdba

创建zabbix用户并且授予其相关权限：

（1）创建用户

CREATE USER ZABBIX
IDENTIFIED BY ZABBIX DEFAULT TABLESPACE SYSTEM
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;

（2）授予权限

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;

上述是给zabbix较高权限，如果只是为了满足orabbix自带的监控项目，可以只授予zabbix如下权限：

CREATE USER ZABBIX
IDENTIFIED BY ZABBIX
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
GRANT ALTER SESSION TO ZABBIX;
GRANT CREATE SESSION TO ZABBIX;
GRANT CONNECT TO ZABBIX;
ALTER USER ZABBIX DEFAULT ROLE ALL;
GRANT SELECT ON V_$INSTANCE TO ZABBIX;
GRANT SELECT ON DBA_USERS TO ZABBIX;
GRANT SELECT ON V_$LOG_HISTORY TO ZABBIX;
GRANT SELECT ON V_$PARAMETER TO ZABBIX;
GRANT SELECT ON SYS.DBA_AUDIT_SESSION TO ZABBIX;
GRANT SELECT ON V_$LOCK TO ZABBIX;
GRANT SELECT ON DBA_REGISTRY TO ZABBIX;
GRANT SELECT ON V_$LIBRARYCACHE TO ZABBIX;
GRANT SELECT ON V_$SYSSTAT TO ZABBIX;
GRANT SELECT ON V_$PARAMETER TO ZABBIX;
GRANT SELECT ON V_$LATCH TO ZABBIX;
GRANT SELECT ON V_$PGASTAT TO ZABBIX;
GRANT SELECT ON V_$SGASTAT TO ZABBIX;
GRANT SELECT ON V_$LIBRARYCACHE TO ZABBIX;
GRANT SELECT ON V_$PROCESS TO ZABBIX;
GRANT SELECT ON DBA_DATA_FILES TO ZABBIX;
GRANT SELECT ON DBA_TEMP_FILES TO ZABBIX;
GRANT SELECT ON DBA_FREE_SPACE TO ZABBIX;
GRANT SELECT ON V_$SYSTEM_EVENT TO ZABBIX;

如果当前的oracle版本是11g的话，还需要添加如下的语句开放acl的访问控制，否则在监控的过程中有部份内容无法正常显示（例于数据库版本，数据库文件大小等）。

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;

授权完成后可以使用zabbix用户登录测试下：

[oracle@server ~]$ sqlplus zabbix/zabbix

SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 7 20:11:12 2016

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

2.解压安装包orabbix,设置配置文件,启动服务

获得orabbix-1.2.3.zip解压包，这里需要注意的事必须解压到/opt/orabbix目录下（由启动服务脚本决定），所以需要创建该目录：

[root@server opt]# mkdir /opt/orabbix/
1
解压到该目录：

[root@server orabbix]# unzip orabbix-1.2.3.zip

得到配置文件/opt/orabbix/conf/config.props:

[root@server conf]# cp /opt/orabbix/conf/config.props.sample /opt/orabbix/conf/config.props

修改配置文件：

[root@server conf]# grep "^[^#]" config.props
ZabbixServerList=ZabbixServer1,ZabbixServer2

ZabbixServer1.Address=IP
ZabbixServer1.Port=port

ZabbixServer2.Address=ip
ZabbixServer2.Port=port

OrabbixDaemon.PidFile=./logs/orabbix.pid
OrabbixDaemon.Sleep=300
OrabbixDaemon.MaxThreadNumber=100

DatabaseList=hybristest

DatabaseList.MaxActive=10
DatabaseList.MaxWait=100
DatabaseList.MaxIdle=1

hybristest.Url=jdbc:oracle:thin:@10.211.33.177:1521:HYBRIS
hybristest.User=zabbix
hybristest.Password=zabbix
hybristest.MaxActive=10
hybristest.MaxWait=100
hybristest.MaxIdle=1
hybristest.QueryListFile=./conf/query.props

这里非常需要注意的一点是DatabaseList这个参数“DatabaseList=hybristest”，他所代表的含义是：被监控服务器的名称。必须要和被监控服务器在zabbix服务端控制台中的主机名称一致，要不会导致即使数据可以从数据库中获取到，但是依然无法显示到zabbix的界面上。

ZabbixServerList=ZabbixServer1,ZabbixServer2

ZabbixServer1.Address=IP
ZabbixServer1.Port=port

ZabbixServer2.Address=ip
ZabbixServer2.Port=port

其中ZabbixServerList列举的是zabbix服务端的名称，然后指定其ip和端口即可。

将启动脚本拷贝到/etc/init.d/目录中，并且授予相关文件的可执行权限：

[root@server init.d]# cp /opt/orabbix/init.d/orabbix /etc/init.d/

[root@server init.d]# chmod +x /etc/init.d/orabbix /opt/orabbix/run.sh

启动orabbix服务并且观察日志信息：

[root@server init.d]# /etc/init.d/orabbix start
[root@server init.d]# vim /opt/orabbix/logs/orabbix.log

2016-11-07 17:01:44,508 [main] INFO Orabbix - Starting Orabbix Version 1.2.3
2016-11-07 17:01:44,518 [main] INFO Orabbix - Orabbix started with pid:26807
2016-11-07 17:01:44,518 [main] INFO Orabbix - PidFile -> ./logs/orabbix.pid
2016-11-07 17:01:44,642 [main] INFO Orabbix - DB Pool created: org.apache.commons.dbcp.datasources.SharedPoolDataSource@c2e1f26
2016-11-07 17:01:44,642 [main] INFO Orabbix - URL=jdbc:oracle:thin:@10.211.33.177:1521:HYBRIS
2016-11-07 17:01:44,642 [main] INFO Orabbix - maxPoolSize=10
2016-11-07 17:01:44,643 [main] INFO Orabbix - maxIdleSize=1
2016-11-07 17:01:44,643 [main] INFO Orabbix - maxIdleTime=1800000ms
2016-11-07 17:01:44,643 [main] INFO Orabbix - poolTimeout=100
2016-11-07 17:01:44,643 [main] INFO Orabbix - timeBetweenEvictionRunsMillis=-1
2016-11-07 17:01:44,643 [main] INFO Orabbix - numTestsPerEvictionRun=3
2016-11-07 17:01:45,065 [main] INFO Orabbix - Connected as ZABBIX
2016-11-07 17:01:45,066 [main] INFO Orabbix - --------- on Database -> HYBRIS
2016-11-07 17:01:45,394 [pool-1-thread-1] INFO Orabbix - Done with dbJob on database hybristest QueryList elapsed time 299 ms
2016-11-07 17:02:45,123 [pool-1-thread-2] INFO Orabbix - Done with dbJob on database hybristest QueryList elapsed time 16 ms
2016-11-07 17:03:45,356 [pool-1-thread-3] INFO Orabbix - Done with dbJob on database hybristest QueryList elapsed time 233 ms
2016-11-07 17:04:45,149 [pool-1-thread-4] INFO Orabbix - Done with dbJob on database hybristest QueryList elapsed time 13 ms
2016-11-07 17:05:45,387 [pool-1-thread-5] INFO Orabbix - Done with dbJob on database hybristest QueryList elapsed time 234 ms
2016-11-07 17:06:45,181 [pool-1-thread-6] INFO Orabbix - Done with dbJob on database hybristest QueryList elapsed time 9 ms
2016-11-07 17:07:45,404 [pool-1-thread-7] INFO Orabbix - Done with dbJob on database hybristest QueryList elapsed time 214 ms
2016-11-07 17:08:45,217 [pool-1-thread-8] INFO Orabbix - Done with dbJob on database hybristest QueryList elapsed time 14 ms
2016-11-07 17:09:45,435 [pool-1-thread-9] INFO Orabbix - Done with dbJob on database hybristest QueryList elapsed time 221 ms
2016-11-07 17:10:45,234 [pool-1-thread-10] INFO Orabbix - Done with dbJob on database hybristest QueryList elapsed time 9 ms
2016-11-07 17:11:45,491 [pool-1-thread-11] INFO Orabbix - Done with dbJob on database hybristest QueryList elapsed time 250 ms
2016-11-07 17:12:45,266 [pool-1-thread-12] INFO Orabbix - Done with dbJob on database hybristest QueryList elapsed time 12 ms
2016-11-07 17:13:45,510 [pool-1-thread-13] INFO Orabbix - Done with dbJob on database hybristest QueryList elapsed time 240 ms
2016-11-07 17:14:45,289 [pool-1-thread-14] INFO Orabbix - Done with dbJob on database hybristest QueryList elapsed time 6 ms
2016-11-07 17:15:45,502 [pool-1-thread-15] INFO Orabbix - Done with dbJob on database hybristest QueryList elapsed time 201 ms
2016-11-07 17:16:45,321 [pool-1-thread-16] INFO Orabbix - Done with dbJob on database hybristest QueryList elapsed time 7 ms
2016-11-07 17:17:45,542 [pool-1-thread-17] INFO Orabbix - Done with dbJob on database hybristest QueryList elapsed time 221 ms
2016-11-07 17:18:45,339 [pool-1-thread-18] INFO Orabbix - Done with dbJob on database hybristest QueryList elapsed time 6 ms
2016-11-07 17:19:45,546 [pool-1-thread-19] INFO Orabbix - Done with dbJob on database hybristest QueryList elapsed time 196 ms

没有任何的报错，说明服务是正常启动的。

查看服务端口：

[root@server init.d]# netstat -anutlp | grep ora
tcp 0 0 10.211.33.177:51461 10.211.33.177:1521 ESTABLISHED 3117/ora_pmon_HYBRI
tcp 0 0 :::59335 :::* LISTEN 3149/ora_d000_HYBRI
tcp 0 0 ::ffff:10.211.33.177:1521 ::ffff:172.20.0.26:39631 ESTABLISHED 11609/oracleHYBRIS
tcp 0 0 ::ffff:10.211.33.177:1521 ::ffff:10.211.33.177:22366 ESTABLISHED 26824/oracleHYBRIS
tcp 0 0 ::ffff:10.211.33.177:1521 ::ffff:172.20.0.26:44515 ESTABLISHED 24901/oracleHYBRIS
udp 0 0 :::11898 :::* 3145/ora_mmon_HYBRI
udp 0 0 ::1:12305 :::* 3151/ora_s000_HYBRI
udp 0 0 ::1:42515 :::* 3149/ora_d000_HYBRI
udp 0 0 ::1:15670 :::* 3117/ora_pmon_HYBRI

注意：需要说明的是，orabbix可以部署在oracle所在的节点上。

3.在zabbix前台操作界面添加模板和设置监控节点

（1）首先需要导入orabbix的模板文件：

[root@server template]# pwd
/opt/orabbix/template
[root@server template]# ls
Orabbix_export_full.xml Orabbix_export_graphs.xml Orabbix_export_items.xml Orabbix_export_triggers.xml

在zabbix控制界面导入该模板：