2-7 监控Oracle · 运维工作笔记

插件官方：http://www.smartmarmot.com/product/orabbix/ #### 部署dbforbix（1.2.3版） #### 现状 smartmarmot里面的插件可以监控所有常见数据库，但2016年之前很久没有更新， 2016年底升级到2.2版本，整合了之前零散的数据库监控插件，但我并没有监控成功，留待以后再试吧。 #### 前提条件安装jdk1.6以上版本（1.6.45）配置Zabbix客户端3.0.4 #### 上传到/opt目录下 ```shell 复制/opt/dbforbix/conf/config.properties.sample到config.properties，并编辑 ``` #### 样例 ```shell #comma separed list of Zabbix servers ZabbixServerList=ZabbixServer1 ZabbixServer1.Address=192.168.0.220 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=Monitor-Client6,Monitor-Client5 #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 Monitor-Client6.Url=jdbc:oracle:thin:@192.168.0.237:1521:orcl Monitor-Client6.User=zabbix Monitor-Client6.Password=zabbix Monitor-Client6.MaxActive=10 Monitor-Client6.MaxWait=100 Monitor-Client6.MaxIdle=1 Monitor-Client6.QueryListFile=./conf/query.props Monitor-Client5.Url=jdbc:oracle:thin:@192.168.0.236:1521:orcl Monitor-Client5.User=zabbix Monitor-Client5.Password=vT77$mEdY Monitor-Client5.MaxActive=10 Monitor-Client5.MaxWait=100 Monitor-Client5.MaxIdle=1 Monitor-Client5.QueryListFile=./conf/query.props ``` ##### 坑：闲的没事把QueryListFile=./conf/query.props给删了，就不好用了，原来人家那么重要，所有监控数据都要从里面的语句来执行，天啊，我干了什么！！！备注1：Monitor-Client5要和zabbix中的主机名一致备注2： ```shell Oracle = jdbc:oracle:thin:@<host>:<LISTENER_PORT>:<instance> PostgreSQL = jdbc:postgresql://<host>:<port>/<database> MS Sql Server = jdbc:jtds:sqlserver://<host>:<port>/<instancename> MySQL Server = jdbc:mysql://[host:port],[host:port].../[database] DB2 = jdbc:db2://<servername>:<port>/<installation> ``` ### 根据需求修改/conf/query.props 可以根据自己的需求，在里面增加key和对应语句，然后在模板中增加Item ### 数据库添加权限标准授权 ```shell CREATE USER ZABBIX IDENTIFIED BY vT77$mEdY DEFAULT TABLESPACE SYSTEM TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK; --Roles for ZABBIX GRANT CONNECT TO ZABBIX; GRANT RESOURCE TO ZABBIX; ALTER USER ZABBIX DEFAULT ROLE ALL; --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; ``` 最小授权 ```shell CREATE USER ZABBIX IDENTIFIED BY vT77$mEdY 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; ``` ### 1.1.4 监控Oracle11需要在comment中执行 ```shell 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; ``` Oracle12中我并没有执行这个语句，也没问题。 ### 1.1.5 配置启动脚本 ```shell /opt/orabbix/init.d/orabbix to /etc/init.d/orabbix ``` ### 1.1.6 增加服务自启动 ```shell chkconfig -add orabbix ``` ### 1.1.7 zabbix添加主机和模板