需要把MySQL的一些数据同步到Oracle，用ETL工具太麻烦了，通过参考官方文档实现了oracle to mysql dblink。每次修改配置后，需要重启监听才能生效，或者 reload一下，确保监听状态正常（按照文档配置的，没什么多余的解释，只是有乱码问题是换了mysql-odbc的版本得以解决的）
参考文档：Detailed Overview of Connecting Oracle to MySQL Using DG4ODBC Database Link (Doc ID 1320645.1)
参考文档可通过 MOS 下载
DB，OS版本：
Oracle: 11.2.0.3.0 OS: RHEL 6.3
MySQL: 5.6.29 OS: RHEL 6.3
Oracle 字符集
PARAMETER VALUE
------------------------------ ------------------------------
NLS_CHARACTERSET AL32UTF8
NLS_NCHAR_CHARACTERSET UTF8
MySQL 字符集
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
实现 oracle to mysql dblink 涉及以下概念：DG4ODBC, ODBC, unixODBC，Mysql，ODBCINI，DBLINK
-
判断Oracle 和 DG4ODBC 是 32位还是64位（本次未测试 32位）
$ file $ORACLE_HOME/bin/dg4odbc
/mysql/data/u01/app/oracle/product/11.2.0/dbhome_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
-
下载并安装ODBC Driver Manager
到http://www.unixodbc.org/下载最新的 unixodbc，当前最新版是
unixODBC-2.3.4.tar.gz，本次OS已经预安装 unixODBC-2.2.14-11.el6.x86_64，所以下载了对应的版本。其中，unixODBC-2.3.4.tar.gz中的目录结构与unixODBC-2.2.14-linux-x86-64.tar.gz完全不同。解压 unixODBC-2.2.14-linux-x86-64.tar.gz 后会在当前目录下自动创建 usr/local 的目录，然后把 usr/local 下的文件移到/home/oracle/unixODBC-2.2.14下
$ tar -zxvf unixODBC-2.2.14-linux-x86-64.tar.gz
$ mkdir -p /home/oracle/unixODBC-2.2.14
$ mv usr/local/* /home/oracle/unixodbc-2.2.14 -
下载并按照ODBC Driver for MySQL
到https://dev.mysql.com/downloads/connector/odbc/5.2.html#downloads 下载Linux-Generic的64位的 ODBC 5.2.7 的tar包：$ tar -zxvf mysql-connector-odbc-5.2.5-linux-glibc2.5-x86-64bit.tar.gz
$ mv mysql-connector-odbc-5.2.7-linux-glibc2.5-x86-64bit/ mysql-odbc-5.2.7尝试过使用 ODBC 5.1.13 的安装包，5.1.13 的 lib 下面只有 libmyodbc5.so，ODBC 5.2.7 的 lib 下面有 libmyodbc5a.so 和 libmyodbc5w.so 两个，本次使用的是 libmyodbc5w.so，使用 5.1.13 的 libmyodbc5.so 会导致乱码问题，后面解释，给出sourceforge上解释的传送门
-
配置 ODBC Driver
在 /home/oracle 创建odbc.ini如下：
[myodbc5]
Driver = /home/oracle/mysql-odbc-5.2.7/lib/libmyodbc5w.so
Description = Connector/ODBC 5.2.7 Driver DSN
SERVER = 10.100.132.196
PORT = 3306
USER = XXXX
Password = XXXXXX
Database = ant
OPTION = 0
TRACE = OFF
CHARSET = UTF8注：Database 区分大小写。
-
配置环境变量，验证ODBC连接
修改.bash_profile，增加以下内容
export LD_LIBRARY_PATH=/home/oracle/unixODBC-2.2.14/lib
export PATH=/usr/sbin:/home/oracle/unixODBC-2.2.14/bin:$PATH
export ODBCINI=/home/oracle/odbc.ini 可以用过以下命令测试
$ cd unixODBC-2.2.14/bin/
$ ./isql myodbc5 -v
因为环境文件，本次未完成测试
$ ldd isql
linux-vdso.so.1 => (0x00007fffbcb08000)
libodbc.so.1 => /home/oracle/unixODBC-2.2.14/lib/libodbc.so.1 (0x00007fd16d250000)
libdl.so.2 => /lib64/libdl.so.2 (0x000000369e600000)
libreadline.so.5 => not found
libncurses.so.5 => /lib64/libncurses.so.5 (0x000000369ee00000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x000000369ea00000)
libc.so.6 => /lib64/libc.so.6 (0x000000369e200000)
/lib64/ld-linux-x86-64.so.2 (0x000000369de00000)
libtinfo.so.5 => /lib64/libtinfo.so.5 (0x00000036a0200000) -
配置tnsnames.ora
myodbc5 =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)(HOST = dbmsprddb222)(PORT = 1521)
)
(CONNECT_DATA =
(SID = myodbc5)
)
(HS = OK)
) -
配置listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = myodbc5)
(ORACLE_HOME =/u01/app/oracle/product/11.2.0/dbhome_1)
(PROGRAM = dg4odbc)
(ENVS="LD_LIBRARY_PATH=/home/oracle/unixODBC-2.2.14/lib:/mysql/data/u01/app/oracle/product/11.2.0/dbhome_1/lib")
)
) -
创建init.ora文件
创建文件$ORACLE_HOME/hs/admin/initmyodbc5.ora
HS_FDS_CONNECT_INFO = myodbc5
HS_FDS_TRACE_LEVEL = OFF
HS_FDS_SHAREABLE_NAME = /home/oracle/unixODBC-2.2.14/lib/libodbc.so
#HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P15
HS_LANGUAGE = AMERICAN_AMERICA.UTF8
HS_NLS_NCHAR = UCS2
HS_FDS_SUPPORT_STATISTICS = FALSE
set ODBCINI=/home/oracle/odbc.ini
set LD_LIBRARY_PATH=/home/oracle/unixODBC-2.2.14/lib -
重启监听（数据库的注册到3306了，MySQL 的注册到1521）
$ lsnrctl stop
$ lsnrctl start
$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 24-AUG-2017 14:18:57 Copyright (c) 1991, 2011, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbmsprddb222)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 24-AUG-2017 08:56:23
Uptime 0 days 5 hr. 22 min. 34 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /mysql/data/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /mysql/data/u01/app/oracle/diag/tnslsnr/dbmsprddb222/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbmsprddb222)(PORT=1521)))
Services Summary...
Service "myodbc5" has 1 instance(s).
Instance "myodbc5", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully $lsnrctl status LSNR2 LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 24-AUG-2017 14:19:42 Copyright (c) 1991, 2011, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbmsprddb222)(PORT=3306)))
STATUS of the LISTENER
------------------------
Alias LSNR2
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 23-AUG-2017 12:15:14
Uptime 1 days 2 hr. 4 min. 28 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /mysql/data/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /mysql/data/u01/app/oracle/diag/tnslsnr/dbmsprddb222/lsnr2/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbmsprddb222)(PORT=3306)))
Services Summary...
Service "dbms" has 1 instance(s).
Instance "dbms", status READY, has 1 handler(s) for this service...
Service "dbmsXDB" has 1 instance(s).
Instance "dbms", status READY, has 1 handler(s) for this service...
The command completed successfully -
验证
$tnsping myodbc5 TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 24-AUG-2017 14:29:43 Copyright (c) 1997, 2011, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dbmsprddb222)(PORT = 1521)) (CONNECT_DATA = (SID = myodbc5)) (HS = OK))
OK (0 msec) -
创建dblink
注意用户名和密码一定要用双引号引起来，查询时表名和字段名也需要引号
SQL> create public database link ant connect to "XXXX" identified by "XXXXXX" using'myodbc5';
SQL> select count(1) from "t_info_basinfo"@ant; COUNT(1)
----------
4262
SQL> select "sys_name" from "t_info_database"@ant where "db_id"=1; sys_name
--------------------------------------------------------------------------------
项目与事物跟踪系统(JIRA) -
使用HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P15 乱码问题的官方解释以及解决方案（传送门）
Oracle Note:
Symptoms
using DG4ODBC and MySQL ODBC driver with UnixODBC Driver Manager on Linux corupts multibyte characters stored in a MySQL database using UTF8 charset.
Cause
Root cause of the corrupted characters is the unixODBC Driver Manager which forces the gateway to specify an 8bit language like HS_LANGUAGE=american_america.we8iso8859p1
Commonly when DG4ODBC uses HS_LANGUAGE=american_america.utf8 it uses the unicode ODBC functions like SQLDriverConnectW. This works well when using DataDirect ODBC driver. When using unixODBC Driver manager it expects a double byte ODBC connect string as soon as SQLDriverConnectW is being called. If a single byte connect string is sent (as it is done by DG4ODBC and handled by several commercial ODBC driver vendors) unixODBC is only interpreting every second character and thus corrupts the previously sent connect string which will leqad in an ORA-28500 error message.
Thus it is mandatory to use HS_LANGUAGE with a single byte character with unixODBC driver manager to correctly handle the connect string - but this forces the driver manager to use the SQLDriverConnect function instead of its unicode equivalent.
This SQLDriverConnect function is not able to handle UTF8 character sets and thus corrupts the content.
Solution
To solve your problem you now have 3 options:
- report this issue to the unixODBC vendor => this was already done a couple of times by other customers, but the problem still exists in the latest 2.3 release.
- use a commercial ODBC driver like the ODBC drivers from Data Direct
- use DG4ODBC on Windows with the MySQL ODBC driver. You can connect from a suitable Oracle database release on any platform to DG4ODBC on Windows which then uses the MySQL ODBC driver to connect to the MySQL database.