root-123
oracle使用dblink连接mysql

需要把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

  1. 判断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

  2. 下载并安装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  

  3. 下载并按照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上解释的传送门

  4. 配置 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 区分大小写。

  5. 配置环境变量，验证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)
    ​

  6. 配置tnsnames.ora

    myodbc5 =
      (DESCRIPTION =
        (ADDRESS = 
             (PROTOCOL = TCP)(HOST = dbmsprddb222)(PORT = 1521)
        )
        (CONNECT_DATA =
          (SID = myodbc5)
        )
        (HS = OK)
       )
    ​

  7. 配置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")
         )
       )
    ​

  8. 创建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

  9. 重启监听（数据库的注册到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...

  10. 验证

    $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))

  11. 创建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
--------------------------------------------------------------------------------

  12. 使用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
posted on 2022-08-10 08:03  root-123  阅读(3143)  评论(0)  编辑  收藏  举报

Copyright © 2024 root-123
Powered by .NET 9.0 on Kubernetes Powered By博客园