标签：lib GRANT ODBC zabbix Zabbix agent2 oracle SELECT mon

环境

CentOS7.5 + Zabbix 5.0 LTS

agent2方式监控

前提

官方提供的agent2支持的Oracle版本需要满足Oralce12C（12.2）及以上，低版本不支持，可能会报如下错

服务端导入模板

可以从官方下载对应版本的模板文件，比如这里下载5.0的模板：https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/templates/db/oracle_agent2?at=release%2F5.0

注意：zabbix5.0及以前模板是用的xml格式，之后的版本模板用的是yml格式。

客户端安装agent2

安装官方源

# rpm -Uvh https://repo.zabbix.com/zabbix/5.0/rhel/7/x86_64/zabbix-release-5.0-1.el7.noarch.rpm
# yum clean all

安装agent2

# yum install zabbix-agent2 zabbix-agent2-plugin-*

修改配置

# vim /etc/zabbix/zabbix_agent2.conf
#设置服务端白名单
Server=192.168.15.122,192.168.15.96
#设置主动主动监控的服务端地址
ServerActive=192.168.15.122

启动agent2，设置开机启动

# systemctl restart zabbix-agent2
# systemctl enable zabbix-agent2

安装Oralce客户端

instantclient安装，根据实际需要安装sqlplus（若被监控端已有Oralce则最好不要安装）

wget https://download.oracle.com/otn_software/linux/instantclient/199000/oracle-instantclient19.9-basic-19.9.0.0.0-1.x86_64.rpm
wget https://download.oracle.com/otn_software/linux/instantclient/199000/oracle-instantclient19.9-sqlplus-19.9.0.0.0-1.x86_64.rpm
wget https://download.oracle.com/otn_software/linux/instantclient/199000/oracle-instantclient19.9-devel-19.9.0.0.0-1.x86_64.rpm
wget https://download.oracle.com/otn_software/linux/instantclient/199000/oracle-instantclient19.9-odbc-19.9.0.0.0-1.x86_64.rpm
yum localinstall oracle-instantclient19.9-*

环境变量配置

vim /etc/sysconfig/zabbix-agent2
LD_LIBRARY_PATH=/usr/lib/oracle/19.9/client64/lib:$LD_LIBRARY_PATH

监控账号配置

CREATE USER zabbix_mon IDENTIFIED BY zabbix_mon;
-- Grant access to the zabbix_mon user.
GRANT CONNECT, CREATE SESSION TO zabbix_mon;
GRANT SELECT ON DBA_TABLESPACE_USAGE_METRICS TO zabbix_mon;
GRANT SELECT ON DBA_TABLESPACES TO zabbix_mon;
GRANT SELECT ON DBA_FREE_SPACE TO zabbix_mon;
GRANT SELECT ON DBA_USERS TO zabbix_mon;
GRANT SELECT ON SYS.DBA_DATA_FILES TO zabbix_mon;
GRANT SELECT ON SYS.DBA_TEMP_FILES TO zabbix_mon;
GRANT SELECT ON GV_$SORT_SEGMENT TO zabbix_mon;
GRANT SELECT ON V_$ACTIVE_SESSION_HISTORY TO zabbix_mon;
GRANT SELECT ON V_$ARCHIVE_DEST TO zabbix_mon;
GRANT SELECT ON V_$ASM_DISKGROUP TO zabbix_mon;
GRANT SELECT ON V_$DATABASE TO zabbix_mon;
GRANT SELECT ON V_$DATAFILE TO zabbix_mon;
GRANT SELECT ON V_$INSTANCE TO zabbix_mon;
GRANT SELECT ON V_$LOG TO zabbix_mon;
GRANT SELECT ON V_$OSSTAT TO zabbix_mon;
GRANT SELECT ON V_$PGASTAT TO zabbix_mon;
GRANT SELECT ON V_$PROCESS TO zabbix_mon;
GRANT SELECT ON V_$RECOVERY_FILE_DEST TO zabbix_mon;
GRANT SELECT ON V_$RESTORE_POINT TO zabbix_mon;
GRANT SELECT ON V_$SESSION TO zabbix_mon;
GRANT SELECT ON V_$SGASTAT TO zabbix_mon;
GRANT SELECT ON V_$SYSMETRIC TO zabbix_mon;
GRANT SELECT ON V_$SYSTEM_PARAMETER TO zabbix_mon;
GRANT SELECT ON V_$PARAMETER TO zabbix_mon;
GRANT SELECT_CATALOG_ROLE TO zabbix_mon;

测试监控

ODBC监控方式

odbc监控方式是从Server端直接访问目标数据库进行数据采集，不需要经过目标服务器的agent，故下面的操作均是在Zabbix Server端操作。

安装odbc及client

这里可参考上面安装Oralce客户端的部分，安装相应的rpm包即可。

另外还有一些依赖包和ODBC连接器需要安装，比如libaio、libaio-devel、unixODBC unixODBC-devel,已经安装的就忽略。

配置LD路径(默认一般都有，不用手动配置)

#文件不存在，就手动创建，增加下面一行内容
vim /etc/ld.so.conf.d/oracle-instantclient.conf
/usr/lib/oracle/12.2/client64/lib

#执行命令
ldconfig

#检查全局的dll链接库配置是否正确
ldconfig -p | grep oracle
        libsqora.so.19.1 (libc6,x86-64) => /usr/lib/oracle/19.9/client64/lib/libsqora.so.19.1
        liboramysql19.so (libc6,x86-64) => /usr/lib/oracle/19.9/client64/lib/liboramysql19.so
        libocijdbc19.so (libc6,x86-64) => /usr/lib/oracle/19.9/client64/lib/libocijdbc19.so
        libociei.so (libc6,x86-64) => /usr/lib/oracle/19.9/client64/lib/libociei.so
        libocci.so.19.1 (libc6,x86-64) => /usr/lib/oracle/19.9/client64/lib/libocci.so.19.1
        libnnz19.so (libc6,x86-64) => /usr/lib/oracle/19.9/client64/lib/libnnz19.so
        libmql1.so (libc6,x86-64) => /usr/lib/oracle/19.9/client64/lib/libmql1.so
        libipc1.so (libc6,x86-64) => /usr/lib/oracle/19.9/client64/lib/libipc1.so
        libclntshcore.so.19.1 (libc6,x86-64) => /usr/lib/oracle/19.9/client64/lib/libclntshcore.so.19.1
        libclntshcore.so (libc6,x86-64) => /usr/lib/oracle/19.9/client64/lib/libclntshcore.so
        libclntsh.so.19.1 (libc6,x86-64) => /usr/lib/oracle/19.9/client64/lib/libclntsh.so.19.1
        libclntsh.so (libc6,x86-64) => /usr/lib/oracle/19.9/client64/lib/libclntsh.so

配置环境变量

vi /etc/profile

export ORACLE_HOME=/usr/lib/oracle/19.9/client64
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib64:$LD_LIBRARY_PATH
export TNS_ADMIN=$ORACLE_HOME/network/admin
export PATH=$PATH:$ORACLE_HOME/bin:$HOME/bin

#配置生效
source /etc/profile
#赋予可执行权限（若已有则跳过）
chmod +x /usr/lib/oracle/19.9/client64/lib/libsqora.so.19.1

添加Oralce驱动

vi /etc/odbcinst.ini

[Oracle]
Description     = ODBC for Oracle
Driver          = /usr/lib/oracle/19.9/client64/lib/libsqora.so.19.1

添加ODBC DSN

vi /etc/odbc.ini

[12c]
Driver = Oracle                        #刚才添加的驱动名称
ServerName = 10.11.100.211:1523/oracle12c    #ip:port/sid
UserID = zabbix_mon 
Password = zabbix_mon


#下面的内容可以不做，这里只是为了如果后续测试tnsnames的连接可以方便使用，不做也没关系
# mkdir -p $ORACLE_HOME/network/admin/
# vi $ORACLE_HOME/network/admin/tnsnames.ora
test=
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 10.11.100.211)(PORT = 1523))
     (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = oracle12c)   
     )
    )

检查数据库连接

首先确保，Zabbix Server到目标数据库的网络是通的。

# isql -v 12c
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select * from dual;
+------+
| DUMMY|
+------+
| X    |
+------+
SQLRowCount returns -1
1 rows fetched
SQL> quit

配置监控模板

这里可以参考上面服务端导入模板部分的操作，然后就可以应用到主机上，在主机的宏里面配置好三个宏变量：

{$ORACLE.DSN}        =    12c     ##这里就是在odbc.ini文件配置的名字
{$ORACLE.PASSWORD}   =  zabbix_mon
{$ORACLE.USER}       =  zabbix_mon

遇到的问题

权限问题

官方提供的监控账号赋权语句不全，会导致在进行监控SQL查询获取数据的时候报表不存在、权限不足等错误，故增加了部分赋权语句，完整的赋权语句参见前面监控账号配置部分。

ERROR at line 1:
ORA-00942: table or view does not exist


select * from V$RESTORE_POINT where rownum<2
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL语句问题

因为官网的监控模板支持的数据库版本是12C及以上，所以在应用到低版本数据库的时候就会出现一些表或视图没有相应的字段，或不存在对应的表或视图的情况，这个时候根据实际情况进行调整即可。

在11g环境测试遇到的有两个地方需要做调整：

键：Oracle: Get instance state

监控语句如下：

--官方语句
SELECT
INSTANCE_NAME,
HOST_NAME,
VERSION || '-' || EDITION AS VERSION,
floor((SYSDATE - startup_time)*60*60*24) AS UPTIME,
decode(status,'STARTED',1,'MOUNTED',2,'OPEN',3,'OPEN MIGRATE',4, 0) AS STATUS,
decode(archiver,'STOPPED',1,'STARTED',2,'FAILED',3, 0) AS  ARCHIVER,
decode(instance_role,'PRIMARY_INSTANCE',1,'SECONDARY_INSTANCE',2, 0) AS  INSTANCE_ROLE
FROM v$instance;

--因为11g没有EDITION字段，故修改后如下
SELECT
INSTANCE_NAME,
HOST_NAME,
VERSION,
floor((SYSDATE - startup_time)*60*60*24) AS UPTIME,
decode(status,'STARTED',1,'MOUNTED',2,'OPEN',3,'OPEN MIGRATE',4, 0) AS STATUS,
decode(archiver,'STOPPED',1,'STARTED',2,'FAILED',3, 0) AS  ARCHIVER,
decode(instance_role,'PRIMARY_INSTANCE',1,'SECONDARY_INSTANCE',2, 0) AS  INSTANCE_ROLE
FROM v$instance;

自动发现项：PDB discovery

因PDB是12C引入的特性，故11g不需要该项的监控，禁用掉即可。

标签：lib,GRANT,ODBC,zabbix,Zabbix,agent2,oracle,SELECT,mon
From： https://www.cnblogs.com/Before/p/17245036.html

Zabbix安装Oracle监控(agent2+ODBC)

环境