oracle通过透明网关访问mysql

Oracle Database Support
Oracle 通过透明网关
访问mysql
（仅限美创科技内部人员浏览,不得外传）
工程师：施嘉伟
创建日期：2017-07-01
最后修改：2017-07-01
服务商：XXXX科技有限公司
文档控制修改记录
审阅
目录
文档控制 (2)
修改记录 (2)
审阅 (2)
目录 (3)
一部署环境说明 (4)
二部署具体步骤 (4)
1.首先要检查Oracle和DG4ODBC是32位还是64位. (4)
2.下载并安装64位的ODBC Driver Manager UnixODBC (4)
3.下载并安装ODBC Driver (4)
4．在安装的/usr/local/unixODBC/etc/路径下配置odbc.ini配置文件 (5)
5. 在.bash_profile配置相关环节变量LD_LIBRARY_PA TH、UNIXODBC_DIR、ODBCINI
及ODBCSYSINI (6)
6. 验证ODBC连接 (7)
7. 配置tnsnames.ora (7)
8. 配置listener.ora (8)
9. 创建oracle相关init<sid>.ora文件 (8)
10. 使上述配置文件生效 (9)
11. 验证配置是否正确 (9)
12. 创建dblink名称mysql_link并查询ljw数据库table_test表中的数据 (9)
一部署环境说明
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.
2. 下载并安装64位的ODBC Driver
Manager UnixODBC
下载地址:
请参看官方文档Detailed Overview of Connecting Oracle to MySQL Using DG4ODBC Database Link (文档ID 1320645.1)
下载最新版本
3. 下载并安装ODBC Driver
以上两个介质上传至：/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
[********************.4]#./configure--prefix=/usr/local/unixODBC
[********************.4]#make&makeinstall
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=/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
DA TABASE=ljw
OPTION=0
TRACE=OFF
5. 在.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=$PA TH:$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:$PA TH
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.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;
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_PA TH=/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_DA TA = (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。