centos 6 odbc mysql_CentOS6配置odbc访问Oracle

安装unixODBC

yum install unixODBC

安装Oracle odbc client

下载如下四个文件

oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm

oracle-instantclient11.2-odbc-11.2.0.4.0-1.x86_64.rpm

oracle-instantclient11.2-devel-11.2.0.4.0-1.x86_64.rpm

oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm

执行yum localinstall oracle-instantclient11.2-* 安装

配置环境变量

/usr/lib/oracle/11.2/client64目录下执行 mkdir -p network/admin

配置如下环境变量

export ORACLE_HOME=/usr/lib/oracle/11.2/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

验证oracle odbc 驱动

# dltest /usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1

[dltest] ERROR dlopen: file not found

发现报错了 执行

# ldd /usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1

linux-vdso.so.1 => (0x00007fffd77ff000)

libdl.so.2 => /lib64/libdl.so.2 (0x00007f19777f5000)

libm.so.6 => /lib64/libm.so.6 (0x00007f1977570000)

libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f1977353000)

libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f197713a000)

libclntsh.so.11.1 => /usr/lib/oracle/11.2/client64/lib/libclntsh.so.11.1 (0x00007f19747ca000)

libodbcinst.so.1 => not found

libc.so.6 => /lib64/libc.so.6 (0x00007f1974436000)

/lib64/ld-linux-x86-64.so.2 (0x00007f1977bce000)

libnnz11.so => /usr/lib/oracle/11.2/client64/lib/libnnz11.so (0x00007f1974068000)

libaio.so.1 => /lib64/libaio.so.1 (0x00007f1973e67000)

可以看到 libodbcinst.so.1 => not found ，这是redhat的一个问题，参考：https://bugzilla.redhat.com/show_bug.cgi?id=498311

解决该问题：

cd /usr/lib64

ln -s libodbcinst.so.2 libodbcinst.so.1

为libodbcinst.so.2文件创建一个软连接 libodbcinst.so.1，再执行：

ldd /usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1

linux-vdso.so.1 => (0x00007fff735ff000)

libdl.so.2 => /lib64/libdl.so.2 (0x00007f3a7da69000)

libm.so.6 => /lib64/libm.so.6 (0x00007f3a7d7e4000)

libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f3a7d5c7000)

libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f3a7d3ae000)

libclntsh.so.11.1 => /usr/lib/oracle/11.2/client64/lib/libclntsh.so.11.1 (0x00007f3a7aa3e000)

libodbcinst.so.1 => /usr/lib64/libodbcinst.so.1 (0x00007f3a7a82d000)

libc.so.6 => /lib64/libc.so.6 (0x00007f3a7a499000)

/lib64/ld-linux-x86-64.so.2 (0x00007f3a7de42000)

libnnz11.so => /usr/lib/oracle/11.2/client64/lib/libnnz11.so (0x00007f3a7a0cb000)

libaio.so.1 => /lib64/libaio.so.1 (0x00007f3a79eca000)

libltdl.so.7 => /usr/lib64/libltdl.so.7 (0x00007f3a79cc1000)

没有再报 not found。

# dltest /usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1

SUCCESS: Loaded /usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1

验证通过。

配置odbc

在$ORACLE_HOME/network/admin目录中创建文件tnsnames.ora

LOCAL_SERVICE_NAME =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = oradb)

)

)

/etc/odbcinst.ini 文件：

[Oracle]

Description = ODBC for Oracle

Driver = /usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1

FileUsage = 1

Driver Logging = 7

/etc/odbc.init 文件：

[test]

Driver = Oracle ## 对应odbcinst.ini文件中的第一行[Oracle]

DSN = OracleODBC-11g

ServerName = LOCAL_SERVICE_NAME ## tnsnames.ora文件中对应的本地服务名

UserID = oracle用户名

Password =oracle密码

执行

# isql test

+---------------------------------------+

| Connected! |

| |

| sql-statement |

| help [tablename] |

| quit |

| |

+---------------------------------------+

select * from v$version;

+---------------------------------------------------------------------------------+

| BANNER |

+---------------------------------------------------------------------------------+

| Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production |

| PL/SQL Release 11.2.0.3.0 - Production |

| CORE11.2.0.3.0Production |

| TNS for Linux: Version 11.2.0.3.0 - Production |

| NLSRTL Version 11.2.0.3.0 - Production |

+---------------------------------------------------------------------------------+

SQLRowCount returns -1

5 rows fetched

至此说明配置成功。

如果zabbix要添加odbc sql监控的话，须要重启zabbix_agent