环境准备
oracle 服务器 :
IP:192.168.142.11 （ redhel 6.4 ）
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
-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
-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
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@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
4 ．在安装的 /usr/local/unixODBC/etc/ 路径下配置 odbc.ini 配置文件
[root@ljw /]# cd /usr/local/unixODBC/etc/
-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
Driver=/oracle/app/my-odbc-drive-5.3.8/lib/libmyodbc5w.so
Description=Connector/ODBC 5.3.8 Driver DSN
5. 在 .bash_profile 配置相关环节变量 LD_LIBRARY_PATH 、 UNIXODBC_DIR 、 ODBCINI 及 ODBCSYSINI
[oracle@ljw ~]$ vi .bash_profile
# Get the aliases and functions
# User specific environment and startup programs
export ORACLE_BASE=/oracle/app
export ORACLE_HOME=/oracle/app/product/11.2.0/db_1
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
export ORACLE_BASE ORACLE_HOME ORACLE_SID PATH LD_LIBRARY_PATH UNIXODBC_DIR ODBCINI ODBCSYSINI
6. 验证 ODBC 连接
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)
Query OK, 0 rows affected (0.00 sec)
[oracle@ljw ~]$ cd /usr/local/unixODBC/bin/
[oracle@ljw bin]$ ./isql myodbc5 -v
+---------------------------------------+
+---------------------------------------+
7. 配置 tnsnames.ora
[oracle@ljw /]$ vi /oracle/app/product/11.2.0/db_1/network/admin/tnsnames.ora
(ADDRESS = (PROTOCOL = TCP)(HOST = ljw)(PORT = 1521))
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.
(ADDRESS = (PROTOCOL = TCP)(HOST = ljw)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
ADR_BASE_LISTENER = /oracle/app
(ORACLE_HOME=/oracle/app/product/11.2.0/db_1)
(ENV="LD_LIBRARY_PATH=/usr/local/unixODBC/lib:/oracle/app/product/11.2.0/db_1/lib:/usr/lib")
9.
创建
oracle
相关
init.ora
文件
[oracle@ljw /]$ vi /oracle/app/product/11.2.0/db_1/hs/admin/initmyodbc5.ora
HS_FDS_TRACE_FILE_NAME = myodbc5.trc
HS_LANGUAGE = AMERICAN_AMERICA.UTF8
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.
/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))
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.
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';
SQL> select * from "table_test"@mysql_dblink;
-------------------- --------------------