zabbix 结合Orabbix 监控oracle

此文档参考官方文档
http://www.smartmarmot.com/wiki/index.php/Orabbix
1.安装zabbix(略)
2.安装orabbix
下载orabbix
mkdir -p /opt/orabbix;cd /opt/orabbix
wget http://ncu.dl.sourceforge.net/project/orabbix/orabbix-1.2.3.zip
unzip orabbix-1.2.3.zip;rm -rvf orabbix-1.2.3.zip;

3.配置orabbix
修改config.repo
#comma separed list of Zabbix servers
ZabbixServerList=ZabbixServer1
#,ZabbixServer2

ZabbixServer1.Address=192.168.1.25
ZabbixServer1.Port=10051

#ZabbixServer2.Address=IP_ADDRESS_OF_ZABBIX_SERVER
#ZabbixServer2.Port=PORT_OF_ZABBIX_SERVER

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

#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
DatabaseList.MaxWait=100
DatabaseList.MaxIdle=1

#define here your connection string for each database
xyxdb_test.Url=jdbc:oracle:thin:@192.168.1.13:1521:xyxdb
xyxdb_test.User=ZABBIX
xyxdb_test.Password=zabbiXJm2015Q
#Those values are optionals if not specified Orabbix is going to use the general values
xyxdb_test.MaxActive=10
xyxdb_test.MaxWait=100
xyxdb_test.MaxIdle=1
xyxdb_test.QueryListFile=./conf/query.props

此文件配置说明
ZabbixServerList=ZabbixServer1指定zabbix服务器列表
ZabbixServer1.Address=192.168.1.25 zabbix服务器ip
ZabbixServer1.Port=10051 zabbix服务器端口
DatabaseList=xyxdb_test 指定监控的数据库列表名称,这个参数很重要，要跟在zabbix图形下添加的主机的主机名称一致,否则会出现无法收集到数据
以下指定
#define here your connection string for each database
xyxdb_test.Url=jdbc:oracle:thin:@192.168.1.13:1521:xyxdb　jdbc连接名
xyxdb_test.User=ZABBIX　连接数据库用户
xyxdb_test.Password=zabbix　连接数据库密码
#Those values are optionals if not specified Orabbix is going to use the general values
xyxdb_test.MaxActive=10
xyxdb_test.MaxWait=100
xyxdb_test.MaxIdle=1
xyxdb_test.QueryListFile=./conf/query.props 这个指定的是查询文件路径，所有key值都是通过这里设置的,包括算定义查询文件

4.在监控的DB端添加用户及权限
以下是最小化权限
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;
如果有新的视图访问必须要添加权限

如果需要简单一点直接用下面
CREATE USER ZABBIX
 IDENTIFIED BY zabbix
 DEFAULT TABLESPACE USERS
 TEMPORARY TABLESPACE TEMP
 PROFILE DEFAULT
 ACCOUNT UNLOCK;
 – 2 Roles for ZABBIX
 GRANT CONNECT TO ZABBIX;
 GRANT RESOURCE TO ZABBIX;
 ALTER USER ZABBIX DEFAULT ROLE ALL;
 – 5 System Privileges for ZABBIX
 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;
 
 如果是oracle 11g需要做如下操作用sys登录
 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;
 select utl_inaddr.get_host_name('127.0.0.1') from dual;
 
 配置启动
 cp /opt/orabbix/init.d/orabbix /etc/init.d/orabbix
 service orabbix start
 chkconfig orabbix on
 
 在zabbix端导入xml
 Orabbix_export_full.xml    全部导入（图表 监控项 触发器）
Orabbix_export_graphs.xml    图表
Orabbix_export_items.xml      监控项
Orabbix_export_triggers.xml   触发器
Orabbix_export_full.xml直接导入

 解析query.props
 You have to set the query name under the QueryList in the query.props file. Each query name is comma separated. For example;
 定义一个查询名称，每个查询以逗号分割,例如
 QueryList=queryName1,queryName2,queryName3
 
 You must identify the query by the unique item name you specified in the QueryList, followed by '.Query' for Orabbix to recognize
 that this is the query string. Its important to remember NOT to add the semi-colon “;” to the end of your custom query.
 你可以在querylist定义一个查询唯一项目名称,以.Query结尾的查询字段orabbix才可以识别,在每一个自定义查询结尾不能添加分号结尾
 事例：
  customQueryItemName.Query=yourQueryHere
  Now you can define what you want Orabbix to return to your Zabbix Server if no data is found for your query.
  你可以定义一个你希望orabbix返回一个空值，如果查询是空值给zabbix服务器
   customQueryItemName.NoDataFound=none
 
 You can specify if you want a different execution period for your query
 你可以自定义一个执行周期
  customQueryItemName.Period=<Express a period in minute>
 
  Next you can specify a query that will be executed and if return RaceCondiftionValue the query customQueryItemName is executed otherwise is skipped
  你可以指定一个查询如果为真就查询否则就跳过
  customQueryItemName.ACTIVE=[true|false] if true query is executed otherwise skipped
 
  如下
  archive.Query=select round( A.LOGS*B.AVG/1024/1024/10 ) \
 from ( SELECT  COUNT (*)  LOGS FROM V$LOG_HISTORY WHERE \
 FIRST_TIME >= (sysdate -10/60/24)) A, \
 ( SELECT Avg(BYTES) AVG,  Count(1), Max(BYTES) Max_Bytes,Min(BYTES) Min_Bytes  FROM  v$log) B
 
  archive.RaceConditionQuery=select value \
 from \
 v$parameter where name='log_archive_start'
 
  archive.RaceConditionValue=FALSE
 
  TIP:In the example above, Orabbix would execute “archive.Query” only if the query “archive.RaceConditionQuery” return the value “archive.RaceConditionValue”.
  Pratically the “Archive” query is execute only if database is in arhivelogmode (Parameter “log_archive_start” set to TRUE)
  以上事例，orabbix将要执行在查询archive.Query仅在数据库为归档的模式才会执行
 
  <QueryName>.Trim=[true|false]

if true the resultset is trimmed (default is true)

 <QueryName>.AddSpaces=[true|false]

if true the add a space between columns of resultset(default is true)

 <QueryName>.ExcludeColumnsList=[1,2,3,..n]

exclude from result set the 1st, the 2nd etc.. columns from resultset

 <QueryName>.Period

 <QUERY_NAME>.WhenNotAlive = <VALUE>

is used to specify a value or a string to send if a database is not working, doing such will "clean" the graphs generated in Zabbix with a "set" value.
最后监控样图：

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24486203/viewspace-1788791/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24486203/viewspace-1788791/