Zabbix 5~6~7 监控Oracle数据库BY ODBC 替换 orabbix(20250407)

原创楚枫默寒 2025-04-02

ODBC监控方式替换 orabbix

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

安装odbc

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

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-*复制

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

#文件不存在，就手动创建，增加下面一行内容
复制

vim /etc/ld.so.conf.d/oracle-instantclient.conf
/usr/lib/oracle/19.9/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复制

配置环境变量

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

配置SQLPlus连接

在设置环境变量之前，我们需要在/usr/lib/oracle/19.9/client64下创建 network/admin 文件夹，用来配置 tnsnames.ora

创建 network/admin 文件夹

mkdir /usr/lib/oracle/19.9/client64/network/admin -p复制

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

[test]
Driver = Oracle                        #刚才添加的驱动名称
ServerName = 127.1.1.2:1521/test       #ip:port/sid
UserID = zabbix 
Password = zabbix


vi /usr/lib/oracle/19.9/client64/network/admin/tnsnames.ora
test=
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 127.1.1.2)(PORT = 1521))
     (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = test)   
     )
    )复制

监控账号配置

CREATE USER zabbix IDENTIFIED BY zabbix;
-- Grant access to the zabbix user.
GRANT CONNECT, CREATE SESSION TO zabbix;
GRANT SELECT ON DBA_TABLESPACE_USAGE_METRICS TO zabbix;
GRANT SELECT ON DBA_TABLESPACES TO zabbix;
GRANT SELECT ON DBA_FREE_SPACE TO zabbix;
GRANT SELECT ON DBA_USERS TO zabbix;
GRANT SELECT ON SYS.DBA_DATA_FILES TO zabbix;
GRANT SELECT ON SYS.DBA_TEMP_FILES TO zabbix;
GRANT SELECT ON GV_$SORT_SEGMENT TO zabbix;
GRANT SELECT ON V_$ACTIVE_SESSION_HISTORY TO zabbix;
GRANT SELECT ON V_$ARCHIVE_DEST TO zabbix;
GRANT SELECT ON V_$ASM_DISKGROUP TO zabbix;
GRANT SELECT ON V_$DATABASE TO zabbix;
GRANT SELECT ON V_$DATAFILE TO zabbix;
GRANT SELECT ON V_$INSTANCE TO zabbix;
GRANT SELECT ON V_$LOG TO zabbix;
GRANT SELECT ON V_$OSSTAT TO zabbix;
GRANT SELECT ON V_$PGASTAT TO zabbix;
GRANT SELECT ON V_$PROCESS TO zabbix;
GRANT SELECT ON V_$RECOVERY_FILE_DEST TO zabbix;
GRANT SELECT ON V_$RESTORE_POINT TO zabbix;
GRANT SELECT ON V_$SESSION TO zabbix;
GRANT SELECT ON V_$SGASTAT TO zabbix;
GRANT SELECT ON V_$SYSMETRIC TO zabbix;
GRANT SELECT ON V_$SYSTEM_PARAMETER TO zabbix;
GRANT SELECT ON V_$PARAMETER TO zabbix;
GRANT SELECT_CATALOG_ROLE TO zabbix;exec dbms_network_acl_admin.create_acl(acl=> 'zabbix.xml',description =>'zabbix acl', principal =>'ZABBIX', is_grant => true, privilege =>'resolve'); 
exec dbms_network_acl_admin.assign_acl(acl=> 'zabbix.xml', host =>'*'); 
commit; 
复制

检查数据库连接

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

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

自动生成odbc.ini及tnsnames.ora脚本

#!/bin/bash
#==============================================================#
# File    :  odbc_tnsnames.sh.sh 
# Ctime   :  2025/04/02
# Mtime   :  2025/04/02
# Version :  1.0.0
# Copyright (C) 2025-2099 
#==============================================================#

DT=$(date +"%Y%m%d%H%M%S")
odbc_file="/etc/odbc.ini"
tns_file="/usr/lib/oracle/19.9/client64/network/admin/tnsnames.ora"

/bin/mv ${odbc_file} ${odbc_file}.${DT}
/bin/mv ${tns_file}  ${tns_file}.${DT}

mon_user="zabbix"
mon_pass="zabbix"

dblist="TEST_127.1.1.2:1521/TEST        #servername_IP:port/sid"

for odbc_a in $dblist;do
   odbc_tname=`echo ${odbc_a}|awk -F '[_]' '{print $1}'`
   odbc_sname=`echo ${odbc_a}|awk -F '[_]' '{print $2}'`
   echo -e "[${odbc_tname}]
Driver = Oracle
ServerName = ${odbc_sname}
UserID = ${mon_user} 
Password = ${mon_pass}\n" >> ${odbc_file}  
done

for tns_a in $dblist;do
   tns_name=$(echo ${tns_a}|awk -F '[_]' '{print $1}')
   tns_addr=$(echo ${tns_a}|awk -F '[_]' '{print $2}'|awk -F '[:]' '{print $1}')
   tns_port=$(echo ${tns_a}|awk -F '[:]' '{print $2}'|awk -F '[/]' '{print $1}')
   echo -e "${tns_name} = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ${tns_addr})(PORT = ${tns_port}))) (CONNECT_DATA = (SERVICE_NAME = ${tns_name})))" >> ${tns_file}  
donesas复制