HarmonyOS开发者限时福利来啦!最高10w+现金激励等你拿~ 了解详情
写点什么

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:001691

评论

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

你没有必要完全辞去工作

宇宙之一粟

创业 个人成长 思维方式 工作 打工人

云图说丨Astro Canvas一站式数据可视化开发,分钟级构建业务大屏

华为云开发者联盟

云计算 后端 华为云 华为云开发者联盟 企业号 3 月 PK 榜

DTO、VO、BO、PO、DO的用法区别,居然这么多人搞不清楚.....

程序知音

坚如磐石:TiDB 基于时间点的恢复(PiTR)特性优化之路丨6.5 新特性解析

TiDB 社区干货传送门

新版本/特性解读

通过Chaos-Mesh打造更稳定TiDB数据库高可用架构(二)

TiDB 社区干货传送门

实践案例 集群管理 管理与运维 故障排查/诊断 安装 & 部署

CI/CD | 不可忽略的Jenkins基础架构修复问题

龙智—DevSecOps解决方案

ci cicd jenkins CI/CD CloudBees

AI+人类,实现高效网络安全

HummerCloud

人工智能 网络安全

Atlassian Server用户新选择 | 云版和本地部署的数据中心版,总有一个适合您

龙智—DevSecOps解决方案

迁移 Server Atlassian

DBT 收购 Transform,指标平台已成现代数据栈关键拼图

Kyligence

数据分析 指标管理

Oracle ASM磁盘组配置、日常运维、故障处理等操作资料汇总

墨天轮

数据库 oracle asm 磁盘管理

从代理机制到Spring AOP,这篇给你安排得明明白白的

做梦都在改BUG

Java spring aop 代理机制

关于加解密、加签验签的那些事 | 得物技术

得物技术

java

通过Chaos-Mesh打造更稳定TiDB数据库高可用架构(一)

TiDB 社区干货传送门

实践案例 集群管理 管理与运维 扩/缩容 数据库架构设计

TiDB 的事务和一致性校验工具 BANK

TiDB 社区干货传送门

实践案例 故障排查/诊断 数据库架构选型

物理机安装 TiKV 时 RAID 卡在线配置方式

TiDB 社区干货传送门

实践案例 集群管理 安装 & 部署

PCB焊盘设计应掌握哪些要素?

华秋电子

Region is unavailable的排查总结

TiDB 社区干货传送门

管理与运维 故障排查/诊断 扩/缩容

MQTT 5.0连接属性

EMQ映云科技

物联网 IoT mqtt 企业号 3 月 PK 榜 连接属性

详解命令模式本质及其在高复杂调用中的实践案例

阿里技术

设计模式 命令模式

GitHub上线重量级分布式事务笔记,再也不怕面试官问分布式了

小小怪下士

Java 程序员 分布式 分布式事务 后端

首届玄铁 RISC-V 生态大会上海举办 龙蜥操作系统持续深度参与标准共建

OpenAnolis小助手

芯片 risc-v 龙蜥操作系统 平头哥 生态大会

数据标注工具,多维度体验优化|ModelWhale 版本更新

ModelWhale

人工智能 标注 标注工具 团队协同 模型管理

TIDB升级发生故障时,快速强行回退方案

TiDB 社区干货传送门

实践案例

【征文大赛】TiDB 社区第二届征文大赛,一次性带走社区全部新周边,还有bose 降噪耳机、倍轻松按摩仪等你拿!

TiDB 社区干货传送门

通过TiDB Operator为已有TiDB集群部署异构集群

TiDB 社区干货传送门

集群管理 管理与运维 故障排查/诊断 安装 & 部署 扩/缩容

课程作业及比赛任务,已支持 Notebook 内直接提交|ModelWhale 版本更新

ModelWhale

人工智能 机器学习 数据分析 canvas 模型管理

研讨会回顾 | Perforce发布数字资产管理工具Helix DAM,帮助您按时按预算交付虚拟产品

龙智—DevSecOps解决方案

版本控制 数字资产 游戏开发 数字资产管理 芯片研发

代码质量与安全 | SAST与DAST有什么区别?

龙智—DevSecOps解决方案

klocwork SAST DAST 静态应用程序安全测试 动态应用程序安全测试

对TiDB监控方式的一点点研究

TiDB 社区干货传送门

监控 TiDB 源码解读

手把手教你改 sysbench 代码

TiDB 社区干货传送门

开发语言 管理与运维

Stable Diffusion原理详解

jarodyv

人工智能 机器学习 计算机视觉 Stable Diffusion 生成式AI

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