Oracle-MySQL透明网关配置中关于Driver的坑

当数据存储在不同种类的数据库中，我们便会面临异类数据访问的问题。Oracle 数据库网关提供了在 Oracle 环境中透明地访问异构数据库的能力，这个特性可以减少应用程序定制化开发工作提升应用程序的可移植性，也可以用于异构数据库间的数据访问及数据迁移。

本文将介绍 Oracle-MySQL 透明网关的配置方法及 odbc.ini 中 Driver 选择带来的影响。

一、 Oracle 数据库 ODBC 网关概述

透明网关技术由异构服务和 Oracle 数据库 ODBC 网关两部分组成，二者共同支持从 Oracle 环境对非 Oracle 系统的透明访问。异构服务是数据库的集成组件，提供了连接到非 Oracle 系统的通用技术。Oracle ODBC 网关包括 SQL 映射、数据类型转换等能力，通过动态查询功能访问与 ODBC 标准兼容的数据源。

Oracle Gateway for ODBC 架构（Oracle 与异构数据库部署在不同主机上）： 

在以上架构中：

1. 客户端通过 Oracle Net 连接到 Oracle 数据库；
2. 异构服务（Heterogeneous Services）组件通过 Oracle Net 连接到 Oracle ODBC 网关；
3. 网关与 ODBC driver manager 和 ODBC driver 通信；
4. 当用户会话第一次使用 dblink 连接到非 Oracle 系统时会产生一个专用的代理进程，每个用户会话接收自己的代理进程。当用户会话终止代理进程也终止。

二、Oracle-MySQL 透明网关配置方法

1、环境及版本

本文主要介绍 Oracle-MySQL 透明网管的配置，涉及的服务器环境及操作系统数据库版本如下：

• 服务器 1（OS RHEL7.3）：Oracle 11.2.0.4
• 服务器 2（OS RHEL7.7）：MySQL 8.0

2、配置步骤

Oracle-MySQL 透明网关的工作方式可简述为：

Oracle 数据库 --> DBLINK --> TNS 别名 --> Listener 监听 --> ODBC --> MySQL connector --> MySQL 监听 --> MySQL 数据库。

Gateway 配置主要包括以下步骤（在 Oracle 服务器上进行）：

1）验证 Oracle 透明网关已安装

Oracle 11.2.0.4 默认安装了 odbc 透明网关 dg4odbc，验证：

2）安装 Driver Manager

Driver Manager 负责管理应用程序和驱动程序间的通信，包括：解析 DSN (数据源名称,ODBC 的数据源名称在 ODBC.INI 文件中配置)，加载和卸载驱动程序，处理 ODBC 调用，将其传递给驱动程序。

root 用户安装 Driver Manager ,本案例使用 unixODBC：

[root@MySQL-Test-1 ]# yum install unixODBC*
[root@MySQL-Test-1 ]#rpm -qa |grep unixODBC
unixODBC-devel-2.3.1-11.el7.x86_64
unixODBC-2.3.1-11.el7.x86_64
[root@MySQL-Test-1 ~]#

核查安装情况：

3）安装 mysql odbc 驱动

Connector/ODBC(MyODBC 驱动程序)实现 ODBC API 所提供的功能，它负责处理 ODBC 函数调用，将 SQL 请求提交给 MySQL 服务器，并将结果返回给应用程序。

mysql-connector-odbc rpm 包：mysql-connector-odbc-8.0.19-1.el7.x86_64.rpm

Rpm 包下载地址：https://dev.mysql.com/downloads/connector/odbc/

[root@MySQL-Test-1 ]# rpm -ivh mysql-connector-odbc-8.0.19-1.el7.x86_64.rpm

4）odbc.ini 配置

ODBC.INI 是 ODBC 配置文件，记录了连接到服务器所需的驱动信息和数据库信息。Driver Manager 将使用它来确定加载哪个驱动程序（使用数据源名 DSN）。驱动程序将根据指定的 DSN 来读取连接参数。

在/etc/odbc.ini 文件中定义 DSN（数据源名称，如 mysql_ iom86），配置数据源信息（驱动、IP、端口、用户名、口令、库名等）：

[oracle@MySQL-Test-1 etc]$ more odbc.ini
[mysql_iom86]
Description     = ODBC for MySQL
Driver          = /usr/lib64/libmyodbc8w.so
Server          = xx.xx.xx.86
Port            = 3306
User            = username
Password        = password
Database        = test
CHARSET         = utf8
[root@MySQL-Test-1 etc]# 

5）测试 mysql odbc 是否配置成功

用 isql 测试 mysql odbc 是否配置成功，isql 是 unixODBC 带的一个 ODBC 客户端访问工具，可使用 isql +数据源名来访问目标数据库。

6）hs 透明网关配置

在"ORACLE_HOME\hs\admin"目录下，默认存在名为"initdg4odbc.ora"的文件，每个使用 DG4ODBC 的实例，都必须对应一个"init*.ora"文件，文件命名规则：init+<网关 sid>+.ora，文件主要参数：

• HS_FDS_CONNECT_INFO：ODBC 数据源管理中的 DSN 名称
• HS_FDS_TRACE_LEVEL：追踪级别参数，出于性能影响一般不配置或者配置为"OFF"，若遇到网关问题需要跟踪日志，则配置为"Debug"

[oracle@MySQL-Test-1 admin]$ more initdg4mysql86.ora
##HS Configuration
HS_FDS_CONNECT_INFO = mysql_iom86
HS_FDS_TRACE_LEVEL = OFF
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_LANGUAGE=AMERICAN_AMERICA.zhs16gbk
HS_NLS_NCHAR = UCS2
HS_FDS_FETCH_ROWS=1000
HS_RPC_FETCH_REBLOCKING=OFF
##ODBC Configuration
set ODBCINI=/etc/odbc.ini
[oracle@MySQL-Test-1 admin]$

7）网关监听配置

在 listener.ora 中增加如下配置，sid_name 要与上面 hs 网关配置的网关 sid 一致（dg4mysql86）：

SID_LIST_LISTENER =
(SID_LIST =
  (SID_DESC=
  (SID_NAME=dg4mysql86)
  (ORACLE_HOME=/oracle/app/product/11.2.0/dbhome_1)
  (PROGRAM=dg4odbc)
  )
)

8）tns 配置

• ADDRESS：填写透明网关的 IP 地址和端口
• SID：指定连接网关的 SID
• HS：指定连接的是非 ORACLE 数据库

在 tnsnames.ora 中配置：

Mysql86 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost )(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = dg4mysql86)
    )
   (HS = OK)
  )

9）dblink 创建以及透明网关测试

SQL> create DATABASE LINK link_mysql86 connect to "username" identified by "password" using 'mysql86';

测试在 Oracle 端通过透明网关访问 MySQL 库数据并建表（8 千万+记录，2.5G 数据量，约 4 分钟，等待事件多为：“HS message to agent”）：

三、 Where 条件子句未传到 MySQL 端的问题

透明网关的配置本身比较简单，但是在使用时也容易遇到一些意想不到问题，比如“SQL 语句中的 where 条件未传到 MySQL 端导致全表扫描”的问题。

1、问题描述

在使用透明网关过程中发现当通过透明网关访问 MySQL 数据库中某张千万级大表时，SQL 语句中虽然包含了有索引字段的 where 条件，执行时却不能较快返回结果（而这种情况并不是在所有 SQL 中都会发生），如：

select SERIAL_NUMBER from  "to_b_olcomwork"@link_mysql86 where SERIAL_NUMBER='18602031223';

语句在 MySQL 中执行按索引扫描，很快返回结果：

但通过透明网关执行较长时间后因网络中断报错：

SQL> select SERIAL_NUMBER from  "to_b_olcomwork"@link_mysql86 where SERIAL_NUMBER='18602031223';

ERROR at line 1:

ORA-03113: end-of-file on communication channel

Process ID: 20130

Session ID: 956 Serial number: 12917

Elapsed: 00:07:54.49

SQL> 

核查 MySQL 中发现传入的语句没有 where 条件，做全表扫描：

MySQL [iom]> show full processlist;
+--------+-------------+---------------------+------+---------+------+-----------+----------------------------------------------+
| Id     | User        | Host                | db   | Command | Time | State     | Info                                         |
+--------+-------------+---------------------+------+---------+------+-----------+----------------------------------------------+
| 191413 | qyr_iom0020 | unicom86:40251      | iom  | Query   |    0 | starting  | show full processlist                        |
| 191472 | qyr_iom0020 | 132.121.26.26:42444 | iom  | Sleep   |   59 |           | NULL                                         |
| 191473 | qyr_iom0020 | 132.98.26.17:40979  | iom  | Query   |   24 | executing | SELECT `SERIAL_NUMBER` FROM `to_b_olcomwork` |
+--------+-------------+---------------------+------+---------+------+-----------+----------------------------------------------+
3 rows in set (0.00 sec)
MySQL [iom]>

在 Oracle 端查看执行计划也可见是将所有数据取到本地后再进行条件过滤：

2、问题分析及解决

诚然我们可以通过 DBMS_HS_PASSTHROUGH 包将原始的完整 SQL 直接传递到 MySQL 端执行，但这并未从根本上解决问题。

笔者针对这个问题在 MOS 上开了 SR，在与后台支持工程师一同进行了数轮测试及 trace 跟踪（比如调整 HS_FDS_FETCH_ROWS，HS_RPC_FETCH_REBLOCKING 的设置、针对不同版本的 MySQL 库及不同的表测试等）后，最终定位这个问题与我们在 odbc.ini 中设置的 Driver 有关：当设置 Driver = /usr/lib64/libmyodbc8a.so （而不是/usr/lib64/libmyodbc8w.so）时，之前的问题 SQL 可传送 where 条件到 MySQL 端。

进一步研究，MySQL 有两个 ODBC driver：libmyodbc8a.so 是 ANSI ODBC driver，libmyodbc8w.so 是 UNICODE ODBC driver，二者描述解析数据的方式是有不同的：

• 使用 SQL_C_CHAR 检索 SQL_CHAR/SQL_VARCHAR 的数据，这意味着数据将位于 non-Unicode 连接字符集中，并映射为 Oracle 的 CHAR/ VARCHAR2 类型；
• 使用 SQL_C_WCHAR 检索 SQL_WCHAR/SQL_WVARCHAR 的数据，这意味着数据将位于 Unicode 连接字符集中，并映射为 Oracle 的 NHCHAR/ NVARCHAR2 类型。

所以当使用"libmyodbc8w.so" UNICODE driver 运行问题 SQL 时，将使用 SQL_C_WCHAR 检索列并映射为 Oracle 的 NHCHAR/ NVARCHAR2 类型，而基于 N*CHAR 列的 where 子句会被从 SELECT 语句中 drop 掉再发送到外部数据库；而当使用" libmyodbc8a.so " ANSI driver 时，基于 CHAR/VARCHAR 列的 where 子句则不会被中 drop 掉。

由于 SERIAL_NUMBER 字段是 varchar 类型，使用"libmyodbc8w.so"UNICODE driver 时，where 子句被 drop 掉了，但改为" libmyodbc8a.so " ANSI driver 时，where 子句则被传到了远端 MySQL 库中，如下：

这一点我们在网关的 trace 文件中也可以看到，如：

使用"libmyodbc8w.so" UNICODE driver：

=> dg4mysql86w_agt_630.trc
--------------------------------------
Entered hgopcda at 2020/09/30-17:54:30
Column:9(SERIAL_NUMBER): dtype:-9 (WVARCHAR), prc/scl:50/0, nullbl:1, octet:450, sign:1, radix:10
Exiting hgopcda, rc=0 at 2020/09/30-17:54:30
:
Entered hgopars, cursor id 1 at 2020/09/30-17:54:30
type:0
SQL text from hgopars, id=1, len=44 ...
00: 53454C45 43542060 53455249 414C5F4E [SELECT `SERIAL_N]
10: 554D4245 52602046 524F4D20 60746F5F [UMBER` FROM `to_]
20: 625F6F6C 636F6D77 6F726B60 [b_olcomwork`]

使用" libmyodbc8a.so " ANSI driver：

=> dg4mysql86a_agt_32490.trc
----------------------------------------------------
Entered hgopcda at 2020/09/30-17:53:06
Column:9(SERIAL_NUMBER): dtype:12 (VARCHAR), prc/scl:50/0, nullbl:1, octet:450, sign:1, radix:10
Exiting hgopcda, rc=0 at 2020/09/30-17:53:06
:
Entered hgopars, cursor id 1 at 2020/09/30-17:53:06
type:0
SQL text from hgopars, id=1, len=89 ...
00: 53454C45 43542041 312E6053 45524941 [SELECT A1.`SERIA]
10: 4C5F4E55 4D424552 60204652 4F4D2060 [L_NUMBER` FROM `]
20: 746F5F62 5F6F6C63 6F6D776F 726B6020 [to_b_olcomwork` ]
30: 41312057 48455245 2041312E 60534552 [A1 WHERE A1.`SER]
40: 49414C5F 4E554D42 4552603D 27313836 [IAL_NUMBER`='186]
50: 30323033 31323233 27 [02031223']

因此，在这个 case 中，我们使用 ANSI ODBC Driver "libmyodbc8a.so"可以解决 where 条件子句问题。

四、小结

Oracle 数据库网关提供了在 Oracle 环境中透明地访问异构数据库的能力，可以用于异构数据库间的数据访问及数据迁移。本文介绍了 Oracle-MySQL 透明网关的配置方法，也记录了在使用透明网关过程中遇到的“Where 条件子句未传到 MySQL 端”问题以及解决过程，讲述了 ODBC Driver 选择带来的影响，希望在此分享给有需要的同学。

作者介绍：

张忆蔚，广东联通数字与智能化创新中心 DBA。

本文转载自：dbaplus 社群（ID：dbaplus）

原文链接：Oracle-MySQL透明网关配置中关于Driver的坑