Oracle 11.2.0.4 透過透明閘道器訪問mysql 8.0.16

環境準備

oracle 伺服器 :

IP:192.168.142.11 （ redhel 6.4 ）

Database Version:11.2.0.4

mysql 伺服器

IP:192.168.142.12 （ centos7 ）

mysql version: 5.6.35-log LLL for mysqltest

1. 首先要檢查 Oracle 和 DG4ODBC 是 32 位還是 64 位 .

 

[oracle@ljw ~]$ file /oracle/app/product/11.2.0/db_1/bin/dg4odbc

 

/oracle/app/product/11.2.0/db_1/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

 

這裡可以看到是 64 位的。所以必須使用 64 位的 ODBC Driver Manager 和 64 位的 ODBC Driver.

 

2. 下載並安裝 64 位的 ODBC Driver Manager UnixODBC

 

下載地址 :

請參看官方文件   Detailed Overview of Connecting Oracle to MySQL Using DG4ODBC Database Link ( 文件  ID 1320645.1)

 

下載最新版本

 

3. 下載並安裝 ODBC Driver

http://dev.mysql.com/downloads/connector/odbc/#downloads

 

以上兩個介質上傳至： /tmp

 

Root 使用者  :

 

[root@ljw tmp]# ls –rtl

 

total 12756

-rw-r--r-- 1 root root 11230673 Jul  1 00:17 mysql-connector-odbc-8.0.16-1.el6.x86_64.rpm

-rw-r--r-- 1 root root  1830660 Jul  1 00:21 unixODBC-2.3.4.tar.gz

 

[root@ljw tmp]# chmod 777 *

[root@ljw tmp]# ls -rtl

total 12756

-rwxrwxrwx 1 root root 11230673 Jul  1 00:17 mysql-connector-odbc-8.0.16-1.el6.x86_64.rpm

-rwxrwxrwx 1 root root  1830660 Jul  1 00:21 unixODBC-2.3.4.tar.gz

 

 

Root 安裝：

[root@ljw /]# tar -zxvf unixODBC-2.3.4.tar.gz

[root@ljw /]# mkdir -p /usr/local/unixODBC

[root@ljw /]# cd /tmp/unixODBC-2.3.4

[root@ljw unixODBC-2.3.4]# ./configure --prefix=/usr/local/unixODBC

[root@ljw unixODBC-2.3.4]# make & make install

[root@ljw unixODBC-2.3.4]# rpm -ivh  mysql-connector-odbc-8.0.16-1.el6.x86_64.rpm

 

4 ．在安裝的 /usr/local/unixODBC/etc/ 路徑下配置 odbc.ini 配置檔案

 

[root@ljw /]# cd  /usr/local/unixODBC/etc/

[root@ljw etc]# ls –rtl

 

total 4

-rw-r--r-- 1 root root    0 Jul  1 14:08 odbcinst.ini

-rw-r--r-- 1 root root    0 Jul  1 14:08 odbc.ini

drwxr-xr-x 2 root root 4096 Jul  1 14:08 ODBCDataSources

 

[root@ljw etc]# vi odbc.ini

 

[myodbc5]
Driver=/usr/lib64/libmyodbc8w.so
Description=Connector/ODBC 8.0.16 Driver DSN
SERVER=192.168.142.12
PORT=3306
USER=mysql
PASSWORD=mysql
DATABASE=ljw
OPTION=0
TRACE=OFF

5.  在 .bash_profile 配置相關環節變數 LD_LIBRARY_PATH 、 UNIXODBC_DIR 、 ODBCINI 及 ODBCSYSINI

 

[oracle@ljw etc]$ cd

[oracle@ljw ~]$ vi .bash_profile

 

# .bash_profile

 

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

        . ~/.bashrc

fi

 

# User specific environment and startup programs

 

PATH=$PATH:$HOME/bin

 

export PATH

 

umask 022

export ORACLE_BASE=/oracle/app

export ORACLE_HOME=/oracle/app/product/11.2.0/db_1

export ORACLE_SID=orcl

export PATH=$ORACLE_HOME/bin:$PATH

 

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

UNIXODBC_DIR=/usr/local/unixODBC

ODBCINI=$UNIXODBC_DIR/etc/odbc.ini

ODBCSYSINI=$UNIXODBC_DIR/etc

export ORACLE_BASE ORACLE_HOME ORACLE_SID PATH LD_LIBRARY_PATH UNIXODBC_DIR ODBCINI ODBCSYSINI

6.  驗證 ODBC 連線

 

Mysql 伺服器端：

 

mysql> create user mysql@ 192.168.142.11 identified by 'mysql';

Query OK, 0 rows affected (0.00 sec)

 

mysql> grant all on  ljw .* to 'mysql'@'192.168.142.11';

Query OK, 0 rows affected (0.00 sec)

 

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

 

[oracle@ljw ~]$ cd /usr/local/unixODBC/bin/

[oracle@ljw bin]$ ./isql myodbc5 -v

 

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

| Connected!                            |

|                                       |

| sql-statement                         |

| help [tablename]                      |

| quit                                  |

|                                       |

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

 

SQL> quit;

 

7.  配置 tnsnames.ora

 

[oracle@ljw /]$ vi /oracle/app/product/11.2.0/db_1/network/admin/tnsnames.ora

 

myodbc5 =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SID = myodbc5)

    )

        (HS=OK)

  )

8.  配置 listener.ora

 

[oracle@ljw /]$ vi /oracle/app/product/11.2.0/db_1/network/admin/listener.ora

 

# listener.ora Network Configuration File: /oracle/app/product/11.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

 

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

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

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

 

ADR_BASE_LISTENER = /oracle/app

 

SID_LIST_LISTENER=

     (SID_LIST=

        (SID_DESC=

             (SID_NAME=myodbc5)

             (ORACLE_HOME=/oracle/app/product/11.2.0/db_1)

             (PROGRAM=dg4odbc)

             (ENV="LD_LIBRARY_PATH=/usr/local/unixODBC/lib:/oracle/app/product/11.2.0/db_1/lib:/usr/lib")

       )

   )

9.  建立 oracle 相關 init<sid>.ora 檔案

 

[oracle@ljw /]$ vi /oracle/app/product/11.2.0/db_1/hs/admin/initmyodbc5.ora

 

HS_FDS_CONNECT_INFO = myodbc5
HS_FDS_TRACE_LEVEL = off
HS_FDS_TRACE_FILE_NAME = myodbc5.trc
HS_LANGUAGE = AMERICAN_AMERICA.UTF8
HS_NLS_NCHAR = UCS2
HS_FDS_SHAREABLE_NAME =/usr/local/unixODBC/lib/libodbc.so
set LD_LIBRARY_PATH = $LD_LIBRARY_PATH:/usr/lib:/usr/lib64
set ODBCINI=/usr/local/unixODBC/etc/odbc.ini

10.  使上述配置檔案生效

 

[oracle@ljw /]$ lsnrctl reload

11.  驗證配置是否正確

 

[oracle@ljw /]$ tnsping myodbc5

 

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 01-JUL-2017 14:45:39

 

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

 

Used parameter files:

/oracle/app/product/11.2.0/db_1/network/admin/sqlnet.ora

 

 

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ljw)(PORT = 1521)) (CONNECT_DATA = (SID = myodbc5)) (HS=OK))

OK (0 msec)

12.  建立 dblink 名稱 mysql_link 並查詢 ljw 資料庫 table_test 表中的資料

 

[oracle@ljw /]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production on Sat Jul 1 14:47:45 2017

 

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 

 

Connected to:

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

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> create public database link mysql_dblink connect to "mysql" identified by "mysql" using 'myodbc5';

 

Database link created.

 

SQL> select * from "table_test"@mysql_dblink;

 

id                   name

-------------------- --------------------

1                    hzmcdba