一 部署环境说明
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
二 部署具体步骤
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.
下载并安装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
