How to create DBLink from Oracle to Mysql?

查看Oracle和DG4ODBC是32位还是64位

$ file $ORACLE_HOME/bin/dg4odbc

/oracle/app/12.2.0/bin/dg4odbc: ELF 64-bit LSB executable, x86-64, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.18, not stripped

安装ODBC Driver Manager

下载路径www.unixodbc.org

将下载文件传到Oracle服务器上，解压安装

$ cd /soft

$ gunzip -c unixODBC-2.3.6.tar.gz | tar -xvf

root用户执行安装，安装目录/usr/local/unixODBC

# cd /usr/local

# mkdir unixODBC

# cd /soft/unixODBC-2.3.6

# ./configure --prefix=/usr/local/unixODBC

# make

# make install

# chown -R oracle:oinstall /usr/local/unixODBC

安装ODBC Driver

下载路径http://dev.mysql.com/downloads/connector/odbc/#downloads

$ cd /soft

$ gunzip -c mysql-connector-odbc-8.0.11-linux-glibc2.12-x86-64bit.tar.gz

root用户执行

# cd /soft/mysql-connector-odbc-8.0.11-linux-glibc2.12-x86-64bit

# cp bin/* /usr/local/bin

# cp lib/* /usr/local/lib

# myodbc-installer -a -d -n "MySQL ODBC 8.0 Driver" -t "Driver=/usr/local/lib/libmyodbc8w.so"

# myodbc-installer -a -d -n "MySQL ODBC 8.0" -t "Driver=/usr/local/lib/libmyodbc8a.so"

确认是否正确安装

# myodbc-installer -d -l

MySQL ODBC 8.0 Driver

MySQL ODBC 8.0

配置ODBC数据源

oracle用户执行

$ cd /usr/local/unixODBC/etc

$ vi odbc.ini

[test]

Driver = /usr/local/lib/libmyodbc8a.so

Description = Connector/ODBC 8.0 ANSI Driver DSN

SERVER = 192.168.0.2

PORT = 3306

USER = test

PASSWORD = *****

DATABASE = test

OPTION = 0

TRACE = OFF

测试ODBC连接是否正确，oracle用户执行

$ export ODBCINI=/usr/local/unixODBC/etc/odbc.ini

$ export LD_LIBRARY_PATH=/usr/local/unixODBC/lib:$LD_LIBRARY_PATH

$ isql test -v

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

| Connected!                            |

|                                       |

| sql-statement                         |

| help [tablename]                      |

| quit                                  |

|                                       |

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

以上返回结果表明连接正常

在listener.ora和tnsnames.ora中增加mysql库连接信息

vi $ORACLE_HOME/network/admin/listener.ora

如果为RAC数据库，listener位置在grid下

SID_LIST_LISTENER=

     (SID_LIST=

        (SID_DESC=

             (SID_NAME=test)

             (ORACLE_HOME=/oracle/app/12.2.0)

             (PROGRAM=dg4odbc)      (ENV="LD_LIBRARY_PATH=/usr/local/unixODBC/lib:/oradata/app/12.2.0/lib:/lib:/usr/lib")

       )

   )

重启listener，完成后会看到test已在listener中注册。

Service "test" has 1 instance(s).

  Instance "test", status UNKNOWN, has 1 handler(s) for this service...

vi $ORACLE_HOME/network/admin/tnsnames.ora

test =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1526))

    )

    (CONNECT_DATA =

      (SID = test)

    )

    (HS=OK)

  )

备注：

• HOST为Oracle数据库的HOST

• PORT为Oracle数据库的PORT

• SID为/usr/local/unixODBC/etc/odbc.ini文件中定义的名称

• (HS=OK)必须加上

测试tns是否能通

$ tnsping test

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1526))) (CONNECT_DATA = (SID = test)) (HS=OK))

OK (0 msec)

配置gateway inittemptest.ora文件

$ vi $ORACLE_HOME/hs/admin/inittest.ora

HS_FDS_CONNECT_INFO=test

# Data source name in odbc.ini

HS_FDS_TRACE_LEVEL=OFF

HS_FDS_SHAREABLE_NAME=/usr/local/unixODBC/lib/libodbc.so

HS_FDS_SUPPORT_STATISTICS=FALSE

HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15

#

# ODBC env variables

set ODBCINI=/usr/local/unixODBC/etc/odbc.ini

创建dblink

SQL> create database link oracletomysql connect to "test" identified by "******" using 'test';

SQL> select count(*) from "a"@oracletomysql;

  COUNT(*)

----------

        1

DBlink终于建完了，想想从Oracle到Oracle的步骤，是多么简单。