Oracle 透過透明閘道器 訪問 mysql

  部署環境說明

oracle 伺服器:

IP:192.168.142.13 （centos7.2 ）

Database Version:11.2.0.4

mysql 伺服器

IP:192.168.142.12 （centos7.2 ）

mysql version: 5.6.35

 

  部署具體步驟

和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.

 

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

下載地址:

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

 

下載最新版本

 

下載並安裝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-5.3.8-linux-el6-x86-64bit.tar.gz

-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-5.3.8-linux-el6-x86-64bit.tar.gz

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

 

Oracle 使用者：

 

tar -zxvf mysql-connector-odbc-5.3.8-linux-el6-x86-64bit.tar.gz

mv mysql-connector-odbc-5.3.8-linux-el6-x86-64bit  /oracle/app/my-odbc-drive-5.3.8

tar -zxvf unixODBC-2.3.4.tar.gz

 

Root 安裝：

[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

 

在安裝的/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=/oracle/app/my-odbc-drive-5.3.8/lib/libmyodbc5w.so

Description=Connector/ODBC 5.3.8 Driver DSN

SERVER=192.168.142.12

PORT=3306

USER=mysql

PASSWORD=mysql

DATABASE=ljw

OPTION=0

TRACE=OFF 

 

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

Oracle 使用者：

 

[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

 

驗證ODBC 連線

Mysql 伺服器端

 

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

Query OK, 0 rows affected (0.00 sec)

 

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

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;

 

配置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)

  )

 

配置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")

       )

   )

 

建立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

 

使上述配置檔案生效

[oracle@ljw /]$ lsnrctl reload

 

驗證配置是否正確

[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)

 

建立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