可监控项
使用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)数据库大小
环境准备
Oracle11g、jdk1.7以上、orabbix-1.2.3.zip
实施流程：安装Oracle>>创建指定用户>>JDK环境变量>>配置Orabbix>>web界面导入模板>>对应主机添加模板>>检查：查看图形是否正常
- 安装Oracle11g
参考http://www.jianshu.com/p/64747ac09853
1、创建zabbix用户
- 1 Create User
CREATE USER ZABBIX IDENTIFIED BY "zabbix" DEFAULT TABLESPACE SYSTEM TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK;
2、授予权限
– 2 Roles for ZABBIX
GRANT CONNECT,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;
如果只是为了满足orabbix自带的监控项目，可以只授予zabbix如下权限，略过上面这一段，直接执行下面的语句：
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;
2、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;
-
安装jdk1.7，配置环境变量
参考http://www.jianshu.com/p/0067536a5247 -
安装orabbix
下载https://sourceforge.net/projects/orabbix/orabbix-1.2.3.zip
[root@localhost ~]# mkdir /opt/orabbix --创建安装目录
[root@localhost ~]# cp orabbix-1.2.3.zip /opt/orabbix/
[root@localhost orabbix]# cd /opt/orabbix/ &&unzip orabbix-1.2.3.zip --解压安装包
[root@localhost orabbix]# cp ./conf/config.props.sample conf/config.props --复制配置文件
[root@localhost orabbix]# cp init.d/orabbix /etc/init.d/ --复制启动项
[root@localhost orabbix]# chmod 755 /etc/init.d/orabbix /opt/orabbix/run.sh--授执行权限
[root@localhost orabbix]# chkconfig orabbix on --加入开机启动
脚本：
#!/bin/bash
mkdir /opt/orabbix
cp orabbix-1.2.3.zip /opt/orabbix/
cd /opt/orabbix/ &&unzip orabbix-1.2.3.zip
cp ./conf/config.props.sample conf/config.props
cp init.d/orabbix /etc/init.d/
chmod 755 /etc/init.d/orabbix /opt/orabbix/run.sh
chkconfig orabbix on
- 配置orabbix，编辑conf/config.props配置文件
[root@localhost orabbix]# vim conf/config.props
ZabbixServerList=ZabbixServer --ZabbixServer为修改后内容
ZabbixServer.Address=192.168.2.3 --IP对应Zabbix Server地址
ZabbixServer.Port=10051 --Port对应Zabbix Server监听端口
DatabaseList=orcl --DatabaseList填写的值和web页面上主机的名称一致，不然会获取不到值
这下面的orcl.***用的是DatabaseList的值orcl
orcl.Url=jdbc:oracle:thin:@数据库地址:端口:实例名
orcl.User=zabbix
orcl.Password=zabbix
orcl.MaxActive=10
orcl.MaxWait=100
orcl.MaxIdle=1
orcl.QueryListFile=./conf/query.props
- 启动orabbix，查看日志是否有报错信息
[root@server orabbix]# /etc/init.d/orabbix start
[root@server orabbix]# tail -f /opt/orabbix/logs/orabbix.log
2017-11-10 10:22:41,230 [main] INFO Orabbix - Starting Orabbix Version 1.2.3
2017-11-10 10:22:41,268 [main] INFO Orabbix - Orabbix started with pid:2891
2017-11-10 10:22:41,268 [main] INFO Orabbix - PidFile -> ./logs/orabbix.pid
2017-11-10 10:22:41,591 [main] INFO Orabbix - DB Pool created: org.apache.commons.dbcp.datasources.SharedPoolDataSource@6ed279e2
2017-11-10 10:22:41,591 [main] INFO Orabbix - URL=jdbc:oracle:thin:@这是Oracle服务器IP:1521:orcl
2017-11-10 10:22:41,591 [main] INFO Orabbix - maxPoolSize=10
2017-11-10 10:22:41,591 [main] INFO Orabbix - maxIdleSize=1
2017-11-10 10:22:41,591 [main] INFO Orabbix - maxIdleTime=1800000ms
2017-11-10 10:22:41,591 [main] INFO Orabbix - poolTimeout=100
2017-11-10 10:22:41,592 [main] INFO Orabbix - timeBetweenEvictionRunsMillis=-1
2017-11-10 10:22:41,592 [main] INFO Orabbix - numTestsPerEvictionRun=3
2017-11-10 10:22:42,547 [main] INFO Orabbix - Connected as ZABBIX
2017-11-10 10:22:42,550 [main] INFO Orabbix - --------- on Database -> orcl
2017-11-10 10:22:43,038 [pool-1-thread-1] INFO Orabbix - Done with dbJob on database 这是Oracle服务器IP QueryList elapsed time 409 ms
2017-11-10 10:23:42,668 [pool-1-thread-2] INFO Orabbix - Done with dbJob on database 这是Oracle服务器IP QueryList elapsed time 22 ms
2017-11-10 10:24:43,009 [pool-1-thread-3] INFO Orabbix - Done with dbJob on database 这是Oracle服务器IP QueryList elapsed time 344 ms
2017-11-10 10:25:42,722 [pool-1-thread-4] INFO Orabbix - Done with dbJob on database 这是Oracle服务器IP QueryList elapsed time 31 ms
2017-11-10 10:26:43,046 [pool-1-thread-5] INFO Orabbix - Done with dbJob on database 这是Oracle服务器IP QueryList elapsed time 318 ms
- Zabbix Web界面导入orabbix模板.选择模板文件Orabbix_export_full.xml
[root@localhost template]# pwd
/opt/orabbix/template
[root@localhost template]# ls
Orabbix_export_full.xml Orabbix_export_graphs.xml Orabbix_export_items.xml Orabbix_export_triggers.xml
——————1
编辑Orabbix_export_full.xml文件
将19行<group>TEMPLATES</group>编辑为<group>TEMPLATES_ORACLE</group>
不然会提示"TEMPLATES"已存在，无法更新主机；导入成功会创建一个名为"TEMPLATES_ORACLE"的群组，添加模板时需要选定此群组才能看到新导入的模板信息
——————2
主机添加Template_Oracle模板时会提示"键值vm.memory.size[total]已存在"，说明Template OS Linux模板中已经有了此项触发器，所有找到Template_Oracle模板中的这一项删除掉即可添加。
——————3
Orabbix无法获取Oracle DB Size和DB Files Size的解决方法
参考：http://www.jianshu.com/p/440d7efc11cd
web界面导入操作：配置>>模板>>右上角导入>>选择Orabbix_export_full.xml>>点击导入>>点击模板查看
web界面主机添加模板操作：配置>>主机>>选择要添加模板的主机>>点击模板选项>>选择新导入模板>>先添加后更新
查看新添加的图形信息：监测中>>图形>>选定对应的主机>>查看对应图形(shared pool、PGA、Session/Process、Logical IO等)
网友评论