oracle配置验证odbc,oracle通过ODBC访问mysql配置参考

1)版本信息：

Oracle: 11.2.0.4.0 OS: CentOS 7.5

MySQL: 5.7.24 OS: Redhat 6.8

2)查看dg4odbc版本位数

$ file $ORACLE_HOME/bin/dg4odbc

3)下载并安装ODBC Driver Manager

$ wget http://sourceforge.net/projects/unixodbc/files/unixODBC/2.2.14/unixODBC-2.2.14-linux-x86-64.tar.gz/download

$ tar -zxvf unixODBC-2.2.14-linux-x86-64.tar.gz

$ mkdir -p ~/app/unixodbc-2.2.14

$ mv usr ~/app/unixodbc-2.2.14

4)下载并按照ODBC Driver for MySQL

$ wget http://dev.mysql.com/get/Downloads/Connector-ODBC/5.2/mysql-connector-odbc-5.2.5-linux-glibc2.5-x86-64bit.tar.gz/from/http://cdn.mysql.com/

$ tar -zxvf mysql-connector-odbc-5.2.5-linux-glibc2.5-x86-64bit.tar.gz

$ mv mysql-connector-odbc-5.2.5-linux-glibc2.5-x86-64bit ~/app

$ cd ~/app

$ ln -s mysql-connector-odbc-5.2.5-linux-glibc2.5-x86-64bit myodbc-5.2.5

5)配置ODBC Driver

$ mkdir ~/etc

$ vi ~/etc/odbc.ini --添加如下行

[myodbc5]

Driver = /home/oracle/app/myodbc-5.2.5/lib/libmyodbc5w.so

Description = Connector/ODBC 5.2 Driver DSN

SERVER = 192.168.8.68

PORT = 3306

USER = user_test

PASSWORD = user_test_pwd

DATABASE = db_test

OPTION = 0

TRACE = OFF

6)验证之前ODBC配置

$ export ODBCINI=/home/oracle/etc/odbc.ini

$ export LD_LIBRARY_PATH=/home/oracle/app/unixodbc-2.2.14/usr/local/lib:$LD_LIBRARY_PATH

$ cd ~/app/unixodbc-2.2.14/usr/local/bin

$ ./isql myodbc5 -v --no errors and show SQL> prompt

--注:

1)如果报出如下错误:mysql: error while loading shared libraries: libreadline.so.5: cannot open shared object file: No such file or directory。可通过如下方法解决。

# yum install readline-devel readline

# ll *libreadline*

# ln -s ../../lib64/libreadline.so.6 libreadline.so.5

# ll *libreadline*

7)配置listener for mysql

vi $ORACLE_HOME/network/admin/listener.ora --添加如下行

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(SID_NAME=myodbc5)

(ORACLE_HOME=/u01/oracle/app/product/11.2.0/dbhome_1)

(PROGRAM=dg4odbc)

(ENVS=LD_LIBRARY_PATH=/home/oracle/app/unixodbc-2.2.14/usr/local/lib:/u01/oracle/app/product/11.2.0/dbhome_1)

)

)

8)配置tnsname for mysql

$ vi $ORACLE_HOME/network/admin/tnsnames.ora --添加如下行

myodbc5 =

(DESCRIPTION=

(ADDRESS=

(PROTOCOL=TCP) (HOST=localhost) (PORT=1521)

)

(CONNECT_DATA=

(SID=myodbc5)

)

(HS=OK)

)

9)创建init.ora for myodbc5

$ vi $ORACLE_HOME/hs/admin/initmyodbc5.ora --添加如下行

HS_FDS_CONNECT_INFO=myodbc5 # Data source name in odbc.ini

HS_FDS_SHAREABLE_NAME=/home/oracle/app/unixodbc-2.2.14/usr/local/lib/libodbc.so

HS_FDS_SUPPORT_STATISTICS=FALSE

HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15

# ODBC env variables

set ODBCINI=/home/oracle/etc/odbc.ini

10)使上述配置生效并验证

lsnrctl reload

lsnrctl status

tnsping myodbc5

11)创建dblink并验证访问

$sqlplus / as sysdba

SQL> create public database link mysqltest connect to "user_test" identified by "user_test_pwd" using 'myodbc5';

SQL> select * from t1@mysqltest;

--注：

1)如果报错，可为表名添加双引号，例如：

select * from "t1"@mysqltest;

insert into "t1"@mysqltest values(...);

参考文档：Detailed Overview of Connecting Oracle to MySQL Using DG4ODBC Database Link (Doc ID 1320645.1)