通过ODBC连接Oracle与TiDB的方法

背景

某客户现有系统大量使用dblink+物化视图+同义词的方式进行对基础代码库的访问，现基础代码库拟使用tidb进行国产化替换，因链路复杂固继续使用dblink为最稳定的方案。原业务系统调用关系图如下：

原理

透明网关概念

ORACLE透明网关（*** Transparent Gateway）可以解决ORACLE数据库和非ORACLE数据库交互数据的需求。在一个异构环境中，通过ORACLE透明网关可以访问其他类型数据库，例如***、***、 mysql。

透明网关监听

ORACLE透明网关需要*** Net与ORACLE数据库进行通信，在ORACLE透明网关安装后，必须为ORACLE透明网关配置监听。*** Net 侦听器侦听来自 *** 数据库的传入请求。为了让*** Net Listener为透明网关侦听, 必须将有关透明网关的信息添加到*** Net Listener配置文件listener.ora中。该文件默认位于$ORACLE_HOME/network/admin下，其中$ORACLE_HOME是安装透明网关的目录。如果透明网关的安装目录和ORACLE实例一致的话，那么它会和数据库共用监听文件。

***使用DG4ODBC数据网关连接其它非***数据库，利用tidb上层完全兼容mysql的特性来实现。

其数据流为***——dg4odbc——odbc——mysql（TIDB）

搭建步骤

根据原理数据流向图可知数据流向经过DG4ODBC, ODBC Driver Manager, ODBC Driver组件，下面根据组件进行配置。

若无特殊说明所有步骤均在***端进行操作。

1、确认上下游版本信息

***版本号

Release 12.2.0.1.0

tidb版本

Server version: 5.7.25-TiDB-v5.4.0 TiDB Server (Apache License 2.0)

ORACLE服务器系统版本

Red Hat Enterprise Linux Server release 7.9 (Maipo)

2、***透明网关安装

***从11G开始默认安装了odbc透明网关

验证：

***用户 bjzxtestdb:/home/***(orclbk)$cd $ORACLE_HOME/hs bjzxtestdb:/u01/app/***/product/12.2/db/hs(orclbk)$dg4odbc *** Corporation --- FRIDAY JUN 17 2022 11:43:17.621 Heterogeneous Agent Release 12.2.0.1.0 - 64bit Production Built with *** Database Gateway for ODBC bjzxtestdb:/home/***(orclbk)$file $ORACLE_HOME/bin/dg4odbc /u01/app/***/product/12.2/db/bin/dg4odbc: ELF 64-bit LSB executable, x86-64, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.32, BuildID[sha1]=644a5ecc0ded4f35826b0bb55a75871dd6203a51, not stripped

上述命令表面已经有64位的DG4ODBC

3、下载并安装ODBC Driver Manager

到http://www.unixodbc.org/下载最新的 unixodbc,当前最新版本为 unixODBC-2.3.11.tar.gz

root用户安装 tar -zxvf unixODBC-2.3.11.tar.gz cd /home/***/unixODBC-2.3.11 ./configure --prefix=/usr/local/unixODBC-2.3.11 --includedir=/usr/include --libdir=/usr/lib -bindir=/usr/bin --sysconfdir=/etc make && make install

查看是否安装后版本以及配置文件路径

[root@bjzxtestdb ~]# odbc_config --version 2.3.11 [root@bjzxtestdb~]# odbcinst -j unixODBC 2.3.11 DRIVERS............: /etc/odbcinst.ini SYSTEM DATA SOURCES: /etc/odbc.ini FILE DATA SOURCES..: /etc/ODBCDataSources USER DATA SOURCES..: /root/.odbc.ini SQLULEN Size.......: 8 SQLLEN Size........: 8 SQLSETPOSIROW Size.: 8

4、下载并安装ODBC Driver for MySQL

首先查看tidb目前支持的版本

根据实际环境下载最新兼容版本,***

https://dev.mysql.com/downloads/connector/odbc/5.2.html#downloads

笔者使用的为红帽系统下载版本为

mysql-connector-odbc-8.0.27-1.el7.x86_64.rpm

直接安装

rpm -ivh mysql-connector-odbc-8.0.27-1.el7.x86_64.rpm

5、配置 ODBC Driver

参考地址：

https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-configuration-dsn-unix.html

[root@bjzxtestdb ~]# odbcinst -j unixODBC 2.3.11 DRIVERS............: /etc/odbcinst.ini SYSTEM DATA SOURCES: /etc/odbc.ini FILE DATA SOURCES..: /etc/ODBCDataSources USER DATA SOURCES..: /root/.odbc.ini SQLULEN Size.......: 8 SQLLEN Size........: 8 SQLSETPOSIROW Size.: 8 cat /etc/odbc.ini [ODBC Data Sources] myodbc8w = MyODBC 8.0 UNICODE Driver DSN myodbc8a = MyODBC 8.0 ANSI Driver DSN [myodbc8w] Description = Connector/ODBC 8.0 UNICODE Driver DSN Driver = /usr/lib64/libmyodbc8w.so SERVER = 172.XX.XX.81 USER = root PASSWORD = XXX PORT = 4001 DATABASE = XXXX_sit OPTION = 0 TRACE = OFF

注：Database 区分大小写

测试连通性

[root@bjzxtestdb odbc]# isql myodbc8w -v +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | echo [string] | | quit | | | +---------------------------------------+ SQL> show tables; +------------------------------------+ | Tables_in_rbac_sit | +------------------------------------+ | ACT_EVT_LOG | | ACT_EVT_LOG_SEQ | | ACT_GE_BYTEARRAY | | ACT_GE_PROPERTY | | ACT_HI_ACTINST | | ACT_HI_ATTACHMENT | | ACT_HI_COMMENT | | ACT_HI_DETAIL |

5、配置tnsnames.ora

[root@bjzxtestdb odbc]# su - *** Last login: Fri Jun 17 14:50:01 CST 2022 bjzxtestdb:/home/***(orclbk)$cat $ORACLE_HOME/network/admin/tnsnames.ora# tnsnames.ora Network Configuration File: /u01/app/***/product/12.2/db/network/admin/tnsnames.ora # Generated by *** configuration tools.BJTESTDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = bjzxtestdb)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = bjtestdb) ) ) ORCLBK = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = bjzxtestdb)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orclbk) ) ) myodbc8w = (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST= bjzxtestdb) (PORT=1521) ) (CONNECT_DATA= (SID=myodbc8w) ) (HS=OK) )

注：透明网关的TNS配置是有HS=OK

6、配置透明网关

必须以init开头

bjzxtestdb:/u01/app/***/product/12.2/db/hs/admin(orclbk)$pwd /u01/app/***/product/12.2/db/hs/admin bjzxtestdb:/u01/app/***/product/12.2/db/hs/admin(orclbk)$cat initmyodbc8w.ora HS_FDS_CONNECT_INFO=myodbc8w#与listener.ora名称对应 HS_FDS_SHAREABLE_NAME=/usr/lib/libodbc.so#odbc的lib路径 HS_FDS_SUPPORT_STATISTICS=FALSE#默认#HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15 HS_LANGUAGE = AMERICAN_AMERICA.AL32UTF8#ORACLE字符集 HS_NLS_NCHAR = UCS2#异构数据库字符编码 UTF8一般对应此参数 HS_FDS_SUPPORT_STATISTICS = FALSE HS_FDS_TRACE_LEVEL = debug#日志级别 #HS_KEEP_REMOTE_COLUMN_SIZE=ALL 字符转换相关

参数含义连接

https://xy2401.com/local-doc-***-19c.zh/content/tginu/database-gateway-for-informix-initialization-parameters.html#GUID-2AB55A84-36E7-437D-BDC9-58ADDCE35F2D

7、配置listener.ora

bjzxtestdb:/u01/app/***/product/12.2/db/network/admin(orclbk)$cat listener.ora #listener.ora Network Configuration File: /u01/app/***/product/12.2/db/network/admin/listener.ora # Generated by *** configuration tools.LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = bjzxtestdb)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (SID_NAME=myodbc8w)##此处为gateway system identifier(SID)的SID，要与透明网关下INIT XX.ora中的名字对应 (ORACLE_HOME=/u01/app/***/product/12.2/db) #透明网关安装的主目录。 (PROGRAM=dg4odbc) #此处为固定格式 (ENVS=LD_LIBRARY_PATH=/usr/lib64) #odbc安装时--libdir=/usr/lib路径 ) )

重启监听并确认

lsnrctl reload LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 17-JUN-2022 15:17:42 Copyright (c) 1991, 2016, ***. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bjzxtestdb)(PORT=1521)))STATUS of the LISTENER ------------------------Alias LISTENER Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production Start Date 07-APR-2022 18:32:58 Uptime 70 days 20 hr. 44 min. 43 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/***/product/12.2/db/network/admin/listener.ora Listener Log File /u01/app/***/diag/tnslsnr/bjzxtestdb/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bjzxtestdb)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "myodbc8w" has 1 instance(s). Instance "myodbc8w", status UNKNOWN, has 1 handler(s) for this service... Service "orclbk" has 1 instance(s). Instance "orclbk", status READY, has 1 handler(s) for this service...The command completed successfully -----------------------------------------------------------TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 17-JUN-2022 15:18:25 Copyright (c) 1997, 2016, ***. All rights reserved. Used parameter files: /u01/app/***/product/12.2/db/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST= bjzxtestdb) (PORT=1521)) (CONNECT_DATA= (SID=myodbc8w)) (HS=OK)) OK (0 msec)

到此系统级别配置完成，可按需进行创建dblink进行测试

8、测试验证

创建dblink并验证

***端 create public database link tidbtest connect to "root" identified by "XXXXX" using myodbc8w; select count(*) from “rbac_user”@tidbtest; COUNT(*) ----------4567 mysql端 MySQL [(none)]> MySQL [(none)]> use rbac_sit; Database changed MySQL [rbac_sit]> select count(*) from rbac_user; +----------+ | count(*) | +----------+ | 4567 | +----------+ 1 row in set (0.03 sec)

创建物化视图并验证

create materialized view rbac_user as select * from "rbac_user"@tidbtest; select count(*) from rbac_user; COUNT(*) ---------- 4567

创建同义词并验证

CREATE SYNONYM "TEST"."RBAC_USER1" FOR "RBAC_USER"@"tidbtest"; SQL> select count(*) from rbac_user1; COUNT(*) ---------- 4567

使用限制

1、物化视图只支持全量刷新，不支持增量(tidb端无法进行创建增量日志)。

2、***中通过dblnk访问tidb时，进行select、dml操作需进行显式commit、rollback操作(意外中断不影响)，否则tidb端不释放连接影响gc。

3、暂时不支持lob字段访问，若where条件或查询列不包含lob字段不影响使用。

4、使用dblink访问时对于字段名或表名需要增加双引号进行查询，同义词也需要。否则无法识别

错误解决

1、ORA-02070、ORA-00997

ERROR at line 1: ORA-02070: database TIDBTEST does not support some function in this context ORA-00997: illegal use of LONG datatype

此类似错误为varchar字符转换时存在的问题，对于mysql中varchar类型的字符，网关默认会转换成***中nvarchar2类型但***中nvarchar2类型存在最大长度限制，当长度大于最大限制时则网关会转换成long字段类型，此时便会存在转换问题。

解决方案

查看ORACLE中nvarchar2长度限制

从12.1开始，取决于两个设置 —— MAX_STRING_SIZE和国家字符集 16383 if MAX_STRING_SIZE=EXTENDED and the national character set is AL16UTF16 32767 if MAX_STRING_SIZE = EXTENDED and the national character set is UTF8 2000 if MAX_STRING_SIZE = STANDARD and the national character set is AL16UTF16 4000 if MAX_STRING_SIZE = STANDARD and the national character set is UTF8 select parameter,value from nls_database_parameters where parameter like NLS_NCHAR_%; show parameter MAX_STRING_SIZE

建议修改静态参数MAX_STRING_SIZE

CONNNECT SYS / AS SYSDBA SHUTDOWN IMMEDIATE; STARTUP UPGRADE; ALTER SYSTEM SET max_string_size=extended; START $ORACLE_HOME/rdbms/admin/utl32k.sql SHUTDOWN IMMEDIATE; STARTUP;

2、ORA-28500\ ORA-02063

ERROR at line 1: ORA-28500: connection from ORACLE to a non-*** system returned this message: ORA-02063: preceding line from DLK

错误原因以及处理方法：hs/admin/init[sid].ora里配置错误

HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so，应该odbc的Lib包路径。

3、ORA-00942

ERROR at line 1: ORA-00942: table or view does not exist [MySQL][ODBC 8.0(w) Driver][mysqld-5.7.18-log]Table test.T1 doesnt exist {42S02,NativeErr = 1146}

错误原因以及处理方法：执行的查询操作，表名需要带双引号，因为mysql默认表名区分大小写，而***是不区分大小写。

4、无法查询出数据、数据乱码、数据不正常或ORA-28500

错误原因以及处理方法：hs/admin/init[sid].ora里配置字符集错误

HS_LANGUAGE=AMERICAN_AMERICA.zhs16gbk字符集不正确，应配置***数据库字符集

HS_NLS_NCHAR = UCS2 有奇效