写点什么

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

  • 2021-03-23
  • 本文字数:4791 字

    阅读完需:约 16 分钟

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 unixODBCunixODBC-devel-2.3.1-11.el7.x86_64unixODBC-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 MySQLDriver          = /usr/lib64/libmyodbc8w.soServer          = xx.xx.xx.86Port            = 3306User            = usernamePassword        = passwordDatabase        = testCHARSET         = 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 ConfigurationHS_FDS_CONNECT_INFO = mysql_iom86HS_FDS_TRACE_LEVEL = OFFHS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.soHS_LANGUAGE=AMERICAN_AMERICA.zhs16gbkHS_NLS_NCHAR = UCS2HS_FDS_FETCH_ROWS=1000HS_RPC_FETCH_REBLOCKING=OFF##ODBC Configurationset 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:30Column:9(SERIAL_NUMBER): dtype:-9 (WVARCHAR), prc/scl:50/0, nullbl:1, octet:450, sign:1, radix:10Exiting hgopcda, rc=0 at 2020/09/30-17:54:30:Entered hgopars, cursor id 1 at 2020/09/30-17:54:30type:0SQL 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:06Column:9(SERIAL_NUMBER): dtype:12 (VARCHAR), prc/scl:50/0, nullbl:1, octet:450, sign:1, radix:10Exiting hgopcda, rc=0 at 2020/09/30-17:53:06:Entered hgopars, cursor id 1 at 2020/09/30-17:53:06type:0SQL 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的坑

2021-03-23 08:001713

评论

发布
暂无评论
发现更多内容

架构师0期Week5Work2

Nan Jiang

架构师训练营-第五周-作业2

A Matt

第五周课后作业

iHai

极客大学架构师训练营

架构师训练营week5 总结+作业

林毋梦

分布式缓存架构

Jeannette

week05-作业

seki

架构师训练营第 0 期 - 第 5 周 - 学习总结

week5.学习总结

个人练习生niki👍

一致性哈希算法Java实现

dapaul

架构师

多线程获取结果还在使用Future轮询获取结果吗?CompletionService快来了解下吧。

root

Java 多线程 Feature CompletionService

架构师训练营 week05 学习总结

GunShotPanda

架构学习第5周作业

乐天

架构训练营week05-总结

尔东雨田

架构师训练营 0 期第五周

Blink

架构培训 -05 学习总结 缓存、消息和负载均衡

刘敏

第五周学习总结

iHai

极客大学架构师训练营

架构师训练营第五周 - 作业

桔子

架构师训练营第五周 - 总结

桔子

技术选型 - 第五周(初稿)

X﹏X

第五周·总结

刘璐

架构师第五周

Tulane

实现一致性 hash 算法

Jeannette

架构师训练营第五周总结

养乐多

架构学习第5周作业总结

乐天

架构师训练营第五周总结

zongbin

极客大学架构师训练营

第四周系统架构演进和案例分享笔记

秤须苑

极客大学架构师训练营

架构师训练营第5周课后作业

Just顾

第四周课后练习

秤须苑

极客大学架构师训练营

week05-总结

seki

第五周·命题作业·一致性hash

刘璐

架构师训练营 week05 作业

GunShotPanda

Oracle-MySQL透明网关配置中关于Driver的坑_架构_dbaplus社群_InfoQ精选文章