Oracle运维看的报刊,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:@::

PostgreSQL = jdbc:postgresql://:/

MS Sql Server = jdbc:jtds:sqlserver://:/

MySQL Server = jdbc:mysql://[host:port],[host:port].../[database]

DB2 = jdbc:db2://:/

```

### 根据需求修改/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添加主机和模板