http://blog.csdn.net/dbanote/article/details/10488581
版权声明：本文为博主原创文章，未经博主允许不得转载。
业务上有这么一个需求，需要把Oracle的一些数据同步到MySQL，如果每次都是手动同步的话，实在太麻烦，因此花了点时间研究了下Oracle直连MySQL的方式。
参考文档：Detailed Overview of Connecting Oracle to MySQL Using DG4ODBC Database Link (Doc ID 1320645.1)
版本信息：
Oracle: 11.2.0.1.0 OS: CentOS 5.9
MySQL: 5.5.27 OS: CentOS 5.8
原理：
Oracle使用DG4ODBC数据网关连接其它非Oracle数据库，其原理图如下：
从上图可知，Oracle连接MySQL需要涉及到如下组件：DG4ODBC, ODBC Driver Manager, ODBC Driver，本文将一一讲解它们的配置。
1）判断32位还是64位
因为32位和64位的配置不一样，64位更复杂一些，因此我们首先得确定Oracle和DG4ODBC是32位还是64位：
- [oracle@lx16 ~]$ file $ORACLE_HOME/bin/dg4odbc
- /home/oracle/app/oracle/product/11.2.0/dbhome_1/bin/dg4odbc: ELF 64-bit LSB executable, AMD x86-64, version 1 (SYSV), for GNU/Linux 2.6.9, dynamically linked (uses shared libs), not stripped
2）下载并安装ODBC Driver Manager
到这个页面（http://www.unixodbc.org/download.html）根据你的OS下载unixodbc（注意：版本不能低于2.2.14）
- $ wget http://sourceforge.net/projects/unixodbc/files/unixODBC/2.2.14/unixODBC-2.2.14-linux-x86-64.tar.gz/download
- $ tar -zxvf unixODBC-2.2.14-linux-x86-64.tar.gz
- $ mkdir ~/app/unixodbc-2.2.14
- $ mv usr ~/app/unixodbc-2.2.14
到这个页面（http://dev.mysql.com/downloads/connector/odbc/5.2.html#downloads）根据你的OS下载ODBC-5.2.5，本例选择64位tar版本：
- $ wget http://dev.mysql.com/get/Downloads/Connector-ODBC/5.2/mysql-connector-odbc-5.2.5-linux-glibc2.5-x86-64bit.tar.gz/from/http://cdn.mysql.com/
- $ tar -zxvf mysql-connector-odbc-5.2.5-linux-glibc2.5-x86-64bit.tar.gz
- $ mv mysql-connector-odbc-5.2.5-linux-glibc2.5-x86-64bit ~/app
- $ cd ~/app
- $ ln -s mysql-connector-odbc-5.2.5-linux-glibc2.5-x86-64bit myodbc-5.2.5
在~/etc目录下创建odbc.ini如下：
- [myodbc5]
- Driver = /home/oracle/app/myodbc-5.2.5/lib/libmyodbc5w.so
- Description = Connector/ODBC 5.2 Driver DSN
- SERVER = 192.168.1.15
- PORT = 3306
- USER = mysql_user
- PASSWORD = mysql_pwd
- DATABASE = mysql_db
- OPTION = 0
- TRACE = OFF
5）验证ODBC连接
- $ export ODBCINI=/home/oracle/etc/odbc.ini
- $ export LD_LIBRARY_PATH=/home/oracle/app/unixodbc-2.2.14/usr/local/lib:$LD_LIBRARY_PATH
- $ cd ~/app/unixodbc-2.2.14/usr/local/bin
- $ ./isql myodbc5 -v
- +---------------------------------------+
- | Connected! |
- | |
- | sql-statement |
- | help [tablename] |
- | quit |
- | |
- +---------------------------------------+
6）配置tnsnames.ora
- myodbc5 =
- (DESCRIPTION=
- (ADDRESS=
- (PROTOCOL=TCP) (HOST=localhost) (PORT=1521)
- )
- (CONNECT_DATA=
- (SID=myodbc5)
- )
- (HS=OK)
- )
- SID_LIST_LISTENER=
- (SID_LIST=
- (SID_DESC=
- (SID_NAME=myodbc5)
- (ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1)
- (PROGRAM=dg4odbc)
- (ENVS=LD_LIBRARY_PATH=/home/oracle/app/unixodbc-2.2.14/usr/local/lib:/home/oracle/app/oracle/product/11.2.0/dbhome_1/lib)
- )
- )
8）创建init<sid>.ora文件
创建文件$ORACLE_HOME/hs/admin/initmyodbc5.ora，内容如下：
- HS_FDS_CONNECT_INFO=myodbc5 # Data source name in odbc.ini
- HS_FDS_SHAREABLE_NAME=/home/oracle/app/unixodbc-2.2.14/usr/local/lib/libodbc.so
- HS_FDS_SUPPORT_STATISTICS=FALSE
- HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15
- # ODBC env variables
- set ODBCINI=/home/oracle/etc/odbc.ini
- $ lsnrctl reload
- $ lsnrctl status
- Service "myodbc5" has 1 instance(s).
- Instance "myodbc5", status UNKNOWN, has 1 handler(s) for this service...
- $ tnsping myodbc5
- TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 29-AUG-2013 10:54:46
- Copyright (c) 1997, 2009, Oracle. All rights reserved.
- Used parameter files:
- /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
- Used TNSNAMES adapter to resolve the alias
- Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=localhost) (PORT=1521)) (CONNECT_DATA= (SID=myodbc5)) (HS=OK))
- OK (0 msec)
- SQL> create public database link mysqltest connect to "mysql_user" identified by "mysql_pwd" using 'myodbc5';
- SQL> select count(*) from trans_expert_map@mysqltest;
- COUNT(*)
- ----------
- 371
- 顶
- 4
- 踩
- 0