Zabbix监控系统系列之十二:监控Oracle数据库

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

我比较常用是Orabbix工具，它是基于JDBC方式。它的模板已经实现如下监控：

DBVersion (and relative validity of package) => 数据库版本

Archive ( archive log production with relative trend) => 归档文件

EventWaits (monitor Files I/O,single block read, multiblock read, direct path read,SQLNet messages, Controlfile I/O,LogWrite) => 等待的事件

HitRatio (monitor Hit Ratio on Trigger, Tables/Procedures, SQLArea,Body) => 命中率

Logical I/O (monitor Logical I/O values of : Current Read, Consistent Read, Block Change) => 逻辑I/O

PGA > PGA运用情况

SGA (in particolar: Fixed Buffer, Java Pool, Large Pool, Log Buffer,Shared Pool,Buffer Cache) => SGA运用情况

Physical I/O (Redo Writes,Datafiles Writes,Datafiles Read) => 物理I/O

SharedPool (Pool Dictionary Cache, Pool Free Memory, Library Cache,Sql Area ,Misc.) => 共享池

Pin Hit Ratio (monitor Hit Ratio on Trigger, Tables/Procedures, SQLArea,Body)

Session/Processes (monitor Sessions and processes) => 进程与会话

Session (Active Session, Inactive Sessions, System Session) => 会话状态

在这里插入图片描述

[部署步骤]

1.JAVA组件部署

yum install java

2.Orabbix部署（当前最新版本是1.2.3）

mkdir /opt/orabbix

cp orabbix-1.2.3.zip /opt/orabbix/

cd /opt/orabbix/

unzip orabbix-1.2.3.zip

chmod -R a+x *

cp /opt/orabbix/conf/config.props.sample /opt/orabbix/conf/config.props

vi conf/config.props

---- 配置开始 ----

#comma separed list of Zabbix servers

ZabbixServerList=ZabbixServer1 # Zabbix服务器信息

ZabbixServer1.Address=127.0.0.1

ZabbixServer1.Port=10051

#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=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=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

192.168.0.94.Url=jdbc:oracle:thin:@192.168.0.94:1521:ORCL # Zabbix监控对象信息

192.168.0.94.User=ZABBIX

192.168.0.94.Password=ZABBIX

#Those values are optionals if not specified Orabbix is going to use the general values

192.168.0.94.MaxActive=10

192.168.0.94.MaxWait=100

192.168.0.94.MaxIdle=1

192.168.0.94.QueryListFile=./conf/query.props # 监控项的SQL语句记录在此文件

DB2.Url=jdbc:oracle:thin:@server2.domain.example.com:<LISTENER_PORT>:DB2

DB2.User=zabbix

DB2.Password=zabbix_password

DB2.QueryListFile=./conf/query.props

DB3.Url=jdbc:oracle:thin:@server3.domain.example.com:<LISTENER_PORT>:DB3

DB3.User=zabbix

DB3.Password=zabbix_password

DB3.QueryListFile=./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或以上版本，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;

检查Orabbix日志

tailf /opt/orabbix/logs/orabbix.log

Zabbix 导入Orabbix模板

创建被监控数据库主机

8.监控参考

[常见问题]

1.配置完毕后，系统无法显示dbsize 与 dbfilesize值。

解决方案：

query.conf文件修改配置

QueryList=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=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’)

————————————————

Tags：jdbc for oracle