环境准备
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