CentOS6配置odbc访问Oracle

囚兔

发布于 2018-05-30 11:32:04

1.5K0

发布于 2018-05-30 11:32:04

文章被收录于专栏：IT杂记IT杂记

安装unixODBC

yum install unixODBC

安装Oracle odbc client

到oracle官网下载连接为http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html

下载如下四个文件

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                                          |
| CORE	11.2.0.3.0	Production                                                      |
| 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

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2018.05.15，如有侵权请联系 cloudcommunity@tencent.com 删除

oracle