Oracle使用 ODBC+DBLINK 访问 Mysql

 接到一个需求要在oracle中访问MySql。

实现过程：

一、oracle主机上安装unixODBC      和mysql-connector-odbc驱动。

二、oracle中建立到MySQL的DBlink。

 

实验环境和驱动：

[OS Version]

Oracle Linux Server release 5.7

[ORACLE Version]

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

[MySql Version]:

MySQL 5.1.54-1

[ODBC Driver Manager]

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

[MySQL Connector/ODBC driver ]

mysql-connector-odbc-5.1.10-linux-rhel5-x86-64bit.tar.gz

 

驱动的安装和配置都在oracle的主机上进行。配置后要重启oracle监听。

Step1. 确定使用32/64-bits的驱动

$ file $ORACLE_HOME/bin/dg4odbc

/opt/ora11g/product/11.2.3/db_1/bin/dg4odbc:ELF 64-bit LSB executable, AMD x86-64, version 1 (SYSV), for GNU/Linux 2.6.9,dynamically linked (uses shared libs), for GNU/Linux 2.6.9, not stripped

以上信息表明我们要使用64-bit的ODBC Driver Manager

 

 

Step 2 下载和安装 [ODBC Driver Manager] UnixODBC 2.2.14

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

$ tar zxvfunixODBC-2.2.14-linux-x86-64.tar.gz

默认会解压到当前目录usr/local目录下，包含bin,include ,lib 三个目录

$ ls usr

bin include  lib

创建目录unixodbc-2.2.14

$ mkdir unixodbc-2.2.14

进入unixodbc-2.2.14

$ cd unixodbc-2.2.14

把 刚解压的 usr/local/目录 下的文件移动到unixodbc-2.2.14(注意不是/usr/local)

$ mv ~/tools/usr/local/*  .

$ ll

total 12

drwxr-xr-x 2oracle dba 4096 Nov 20  2008 bin

drwxr-xr-x 2 oracle dba 4096 Nov 20  2008 include

drwxr-xr-x 2 oracle dba 4096 Nov 20  2008 lib

$ cd ..

$ rm -r usr/

编辑profile文件，设置LD_LIBRARY_PATH

$ vi ~/.bash_profile

export LD_LIBRARY_PATH=/app/oracle/tools/unixodbc-2.2.14/lib

export PATH=/usr/sbin:/app/oracle/tools/unixodbc-2.2.14/bin:$PATH

$ source ~/.bash_profile

 

Step 3 下载和安装 [ODBC Driver]

url:http://www.mysql.com/downloads/connector/odbc/5.1.html

mysql-connector-odbc-3.51.30-linux-rhel5-x86-64bit.tar.gz

$ tar zxvfmysql-connector-odbc-3.51.30-linux-rhel5-x86-64bit.tar.gz

$ mvmysql-connector-odbc-5.1.10-linux-rhel5-x86-64bit   mysqlodbc_5.1.0

 

Step 4  配置 ODBC data source for MySQLConnector/ODBC driver

http://dev.mysql.com/doc/refman/5.5/en/connector-odbc-configuration-dsn-unix.html

创建odbc.ini

Database 指定的mysql数据库名区分大小写。

$ vi odbc.ini

[ Data Sources]

myodbc3    = MyODBC 3.51 Driver DSN

[myodbc3]

Driver      = /app/oracle/tools/mysqlodbc_3.51/lib/libmyodbc3.so

Description = Connector/ODBC 3.51 Driver DSN

SERVER      = 192.168.10.10

PORT        =3306

USER        =test_user

Password    =test_admin

Database    = test

OPTION      = 3

SOCKET      =

 

 

Step 5  oracle主机用isql测试oracle到mysql的连接

把odbc.ini加到环境变量中

$ vi  ~/.bash_profile

export ODBCINI=/app/oracle/tools/odbc.ini

$ source  ~/.bash_profile

$ cd unixodbc-2.2.14/bin/

[oracle@CNPEKVS43 bin]$  ./isql myodbc3 -v

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

| Connected!                 |

| sql-statement                |

| help [tablename]             |

| quit                       |

|                           |

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

 

Step6 配置 tnsnames.ora

Add the following line to  $ORACLE_HOME/network/admin/tnsnames.ora

myodbc3 =

 (DESCRIPTION =

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

   (CONNECT_DATA =

     (SID =myodbc3))

     (HS=OK)

    )

  )

 

Step 7 配置 listener.ora

Open$ORACLE_HOME/network/admin/listener.ora

find SID_LIST_LISTENER definition and add anew entry for myodbc5

 

SID_LIST_LISTENER =

 (SID_LIST =

   (SID_DESC =

     (SID_NAME = myodbc3)

     (ORACLE_HOME =/opt/ora11g/product/11.2.3/db_1)

     (PROGRAM = dg4odbc)

      (ENVS=LD_LIBRARY_PATH=/app/oracle/tools/unixodbc-2.2.14/lib)

    )

  )

 

注意：ENVS=LD_LIBRARY_PATH一定要指到lib目录下，否则会出现下述错误

ERROR at line 1:

ORA-28500: connection from ORACLE to anon-Oracle system returned this message:

[unixODBC][Driver Manager]Can't open lib

'/app/oracle/tools/mysqlodbc_5.1.0/lib/libmyodbc5.so': libodbcinst.so.1:

cannot open shared object file: No suchfile or directory {01000}

ORA-02063: preceding 2 lines from MYODBC3

 

Step 8: 配置监听的初始化文件

This file does not exist and you have tocreate it.

 

vi $ORACLE_HOME/hs/admin/initmyodbc3.ora

HS_FDS_CONNECT_INFO = myodbc3

HS_FDS_TRACE_LEVEL =user

HS_FDS_SHAREABLE_NAME = /app/oracle/tools/unixodbc-2.2.14/lib/libodbc.so

set ODBCINI=/app/oracle/tools/odbc.ini

 

Step 9: 重启监听

$ lsnrctl stop

$ lsnrctl start

$ lsnrctl status

$ tnsping myodbc3

如果tnsping 有错误，检查listener.ora，tnsnames.ora

 

Step 10 创建dblink

SQL>create public database link myodbc3  connect to "mysql_user" identified by "password" using'myodbc3';

SQL> select * from"dual"@myodbc3;

 

 

 

[MOS]参考文档：

Detailed Overview of Connecting Oracle to MySQL Using DG4ODBC DatabaseLink [ID 1320645.1]