Zabbix监控系统系列之十四：Oracle监控

&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61; 系列目录 &＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;
Zabbix监控系统系列之一 : Server部署
Zabbix监控系统系列之二 &＃xff1a; 初始化配置
Zabbix监控系统系列之三 &＃xff1a;版本升级
Zabbix监控系统系列之四&＃xff1a;Agent监控Windows客户端
Zabbix监控系统系列之五&＃xff1a;SNMP监控Windows客户端
Zabbix监控系统系列之六&＃xff1a;EMAIL警告配置
Zabbix监控系统系列之七&＃xff1a;VMware虚拟化监控
Zabbix监控系统系列之八&＃xff1a;日志监控
Zabbix监控系统系列之九&＃xff1a;监控网络设备指定接口流量
Zabbix监控系统系列之十&＃xff1a;自动发现配置
Zabbix监控系统系列之十一&＃xff1a;拓扑图绘制
Zabbix监控系统系列之十二&＃xff1a;SNMP Traps主动告警
Zabbix监控系统系列之十三&＃xff1a;SNMP Mibs库加载
Zabbix监控系统系列之十四&＃xff1a;Oracle监控
Zabbix监控系统系列之十五&＃xff1a;自动发现Oracle表空间并监控
&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;

Oracle数据库是我们经常运用的数据库之一&＃xff0c;特别是在一些比较关键的核心业务场景。Zabbix可以通过多种不同的途径实现对数据库的监控&＃xff0c;比如ODBC方式、Shell脚本方式、JDBC方式&＃xff1b;

我比较常用是Orabbix工具&＃xff0c;它是基于JDBC方式。它的模板已经实现如下监控&＃xff1a;

1. DBVersion (and relative validity of package) &＃61;> 数据库版本
2. Archive ( archive log production with relative trend) &＃61;> 归档文件
3. EventWaits (monitor Files I/O,single block read, multiblock read, direct path read,SQLNet messages, Controlfile I/O,LogWrite) &＃61;> 等待的事件
4. HitRatio (monitor Hit Ratio on Trigger, Tables/Procedures, SQLArea,Body) &＃61;> 命中率
5. Logical I/O (monitor Logical I/O values of : Current Read, Consistent Read, Block Change) &＃61;> 逻辑I/O
6. PGA > PGA运用情况
7. SGA (in particolar: Fixed Buffer, Java Pool, Large Pool, Log Buffer,Shared Pool,Buffer Cache) &＃61;> SGA运用情况
8. Physical I/O (Redo Writes,Datafiles Writes,Datafiles Read) &＃61;> 物理I/O
9. SharedPool (Pool Dictionary Cache, Pool Free Memory, Library Cache,Sql Area ,Misc.) &＃61;> 共享池
10. Pin Hit Ratio (monitor Hit Ratio on Trigger, Tables/Procedures, SQLArea,Body)
11. Session/Processes (monitor Sessions and processes) &＃61;> 进程与会话
12. Session (Active Session, Inactive Sessions, System Session) &＃61;> 会话状态

[部署步骤]
1.JAVA组件部署

yum install java


2.Orabbix部署&＃xff08;当前最新版本是1.2.3&＃xff09;

mkdir /opt/orabbix
cp orabbix-1.2.3.zip /opt/orabbix/
cd /opt/orabbix/
unzip orabbix-1.2.3.zip
chmod -R a&＃43;x *
cp /opt/orabbix/conf/config.props.sample /opt/orabbix/conf/config.props
vi conf/config.props
---- 配置开始 ----
#comma separed list of Zabbix servers
ZabbixServerList&＃61;ZabbixServer1 # Zabbix服务器信息 ZabbixServer1.Address&＃61;127.0.0.1
ZabbixServer1.Port&＃61;10051 #pidFile
OrabbixDaemon.PidFile&＃61;./logs/orabbix.pid
#frequency of item&＃39;s refresh
OrabbixDaemon.Sleep&＃61;300
#MaxThreadNumber should be >&＃61; than the number of your databases
OrabbixDaemon.MaxThreadNumber&＃61;100 #put here your databases in a comma separated list
DatabaseList&＃61;192.168.0.94 # 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&＃61;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 <&＃61; 0 to wait indefinitely.
DatabaseList.MaxWait&＃61;100
DatabaseList.MaxIdle&＃61;1 #define here your connection string for each database
192.168.0.94.Url&＃61;jdbc:oracle:thin:&＃64;192.168.0.94:1521:ORCL # Zabbix监控对象信息
192.168.0.94.User&＃61;ZABBIX
192.168.0.94.Password&＃61;ZABBIX
#Those values are optionals if not specified Orabbix is going to use the general values
192.168.0.94.MaxActive&＃61;10
192.168.0.94.MaxWait&＃61;100
192.168.0.94.MaxIdle&＃61;1
192.168.0.94.QueryListFile&＃61;./conf/query.props # 监控项的SQL语句记录在此文件 DB2.Url&＃61;jdbc:oracle:thin:&＃64;server2.domain.example.com::DB2
DB2.User&＃61;zabbix
DB2.Password&＃61;zabbix_password
DB2.QueryListFile&＃61;./conf/query.props DB3.Url&＃61;jdbc:oracle:thin:&＃64;server3.domain.example.com::DB3
DB3.User&＃61;zabbix
DB3.Password&＃61;zabbix_password
DB3.QueryListFile&＃61;./conf/query.props
---- 配置结束 ----


3.Orabbix启动配置

cp /opt/orabbix/init.d/orabbix /etc/init.d/orabbix
/etc/init.d/orabbix start
chkconfig --level 345 orabbix on
chkconfig --add orabbix


4.待监控数据库创建用户与权限配置

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_$LOG TO ZABBIX;
GRANT SELECT ON V_$ARCHIVED_LOG TO ZABBIX;
GRANT SELECT ON V_$PARAMETER TO ZABBIX;
GRANT SELECT ON SYS.DBA_AUDIT_SESSION TO ZABBIX;
GRANT SELECT ON DBA_OBJECTS TO ZABBIX;
GRANT SELECT ON V_$LOCKED_OBJECT 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 V_$SESSION 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 DBA_TABLESPACES TO ZABBIX;
GRANT SELECT ON V_$SYSTEM_EVENT TO ZABBIX;
GRANT SELECT ON V_$TEMP_EXTENT_POOL TO ZABBIX;


※ Oracle 11g或以上版本&＃xff0c;ACL访问控制列表调整
exec dbms_network_acl_admin.create_acl(acl &＃61;> ‘resolve.xml’,description&＃61;> ‘resolve acl’, principal &＃61;>‘ZABBIX’, is_grant &＃61;> true, privilege &＃61;> ‘resolve’);
exec dbms_network_acl_admin.assign_acl(acl &＃61;> ‘resolve.xml’, host &＃61;>’*’);
commit;

5. 检查Orabbix日志
   tailf /opt/orabbix/logs/orabbix.log

6. Zabbix 导入Orabbix模板
   

7. 创建被监控数据库主机
   

8.监控参考

[常见问题]
1.配置完毕后&＃xff0c;系统无法显示dbsize 与 dbfilesize值。
解决方案&＃xff1a;

• query.conf文件修改配置

  QueryList&＃61;archive,audit,dbblockgets,dbconsistentgets,dbhitratio,dbphysicalread,dbversion,hitratio_body,hitratio_sqlarea,hitratio_table_proc,
  lio_current_read,locks,maxprocs,maxsession,miss_latch,pga_aggregate_target, pga,phio_datafile_reads,phio_datafile_writes,phio_redo_writes,pinhitratio_body,pinhitratio_sqlarea,pinhitratio_table-proc,pinhitratio_trigger,
  pool_dict_cache,pool_free_mem,pool_lib_cache,pool_misc,pool_sql_area,procnum,session_active,session_inactive,session,session_system,sga_buffer_cache,
  sga_fixed,sga_java_pool,sga_large_pool,sga_log_buffer,sga_shared_pool,tbl_space,userconn,waits_controfileio,waits_directpath_read,
  waits_file_io,waits_latch,waits_logwrite,waits_multiblock_read,waits_singleblock_read,hitratio_trigger,lio_block_changes,lio_consistent_read,waits_other,waits_sqlnet,users_locked,uptime,dbfilesize,dbsize

• query.conf文件追加配置

dbfilesize.Query&＃61;select to_char(sum(bytes/1024/1024/10), ‘FM99999999999999990’) retvalue from dba_data_files

dbsize.Query&＃61;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 &＃61; a.tablespace_name(&＃43;) AND d.tablespace_name &＃61; f.tablespace_name(&＃43;)
AND NOT (d.extent_management like ‘LOCAL’ AND d.contents like ‘TEMPORARY’)