11.zabbix学习笔记：zabbix监控oracle

悠悠 2022-07-15 04:22 308阅读 0赞

# 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
    
    Copyright (c) 1982, 2009, Oracle.  All rights reserved.
    
    
    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/

解压到该目录：

[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的界面上。

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控制界面导入该模板：

![这里写图片描述][20161107211408709]

选择模板文件Orabbix\_export\_full.xml:  
![这里写图片描述][20161107211547007]

得到Template\_Oracle模板：

![这里写图片描述][20161107211701519]

（2）将模板导入到被监控的节点：  
![这里写图片描述][20161107211809723]

（3）在graphs中查看相关视图内容：

shared pool

![这里写图片描述][20161107212015445]

PGA

![这里写图片描述][20161107212056103]

Session/Process

![这里写图片描述][20161107212114322]

Logical IO

![这里写图片描述][20161107212220650]

还有很多视图就不一一列举了.

--------------------

### 出现的问题 ###

1.orabbix配置文件出现异常，首先需要清楚每个参数的含义，然后再进行配置;

2.数据无法获取（通过查看orabbix的日志文件可以看到报错），orabbix查询oracle数据库的用户权限不够，需要授予足够的权限；

3.zabbix界面无法得到数据，如果可以确保上面一项是可以从数据库中获得数据的话，则需要查看conf.props文件中指定的**DatabaseList**是否正确（必须是zabbix控制台上的主机名）。

--------------------

## 小结 ##

orabbix出了自身支持的监控项，根据用户的需求，还可以进行个性化的设置，这个交给大家自己发挥。

[20161107211408709]: /images/20220715/719c73e916bc4e97a4d59c9f02832202.png
[20161107211547007]: /images/20220715/fc4431c1f17f4b75b812ecbc11eeba9a.png
[20161107211701519]: /images/20220715/833b9ef4c8834546aa752e7990de1a9d.png
[20161107211809723]: /images/20220715/d29f068d32c343ab882f243818ed538c.png
[20161107212015445]: /images/20220715/5ed7a84a17a54a80a3ae44f53b53de0e.png
[20161107212056103]: /images/20220715/036ec3ffa2474e929607418ef45ff610.png
[20161107212114322]: /images/20220715/db051b373f6f4b69977645991b081d53.png
[20161107212220650]: /images/20220715/e88ad5f0f40445409c8096d7e125f369.png