作者： PINO 原文来源：https://tidb.net/blog/3be8dd2f
背景
某客户现有系统大量使用 dblink+ 物化视图 + 同义词的方式进行对基础代码库的访问，现基础代码库拟使用 tidb 进行国产化替换，因链路复杂固继续使用 dblink 为最稳定的方案。原业务系统调用关系图如下：
原理
透明网关概念
ORACLE 透明网关（Oracle Transparent Gateway）可以解决 ORACLE 数据库和非 ORACLE 数据库交互数据的需求。在一个异构环境中，通过 ORACLE 透明网关可以访问其他类型数据库，例如 DB2、SQL Server、 mysql。
透明网关监听
ORACLE 透明网关需要 Oracle Net 与 ORACLE 数据库进行通信，在 ORACLE 透明网关安装后，必须为 ORACLE 透明网关配置监听。Oracle Net 侦听器侦听来自 Oracle 数据库的传入请求。为了让 Oracle Net Listener 为透明网关侦听, 必须将有关透明网关的信息添加到 Oracle Net Listener 配置文件 listener.ora 中。该文件默认位于 ORACLEHOME/network/admin下，其中ORACLE_HOME 是安装透明网关的目录。如果透明网关的安装目录和 ORACLE 实例一致的话，那么它会和数据库共用监听文件。
Oracle 使用 DG4ODBC 数据网关连接其它非 Oracle 数据库，利用 tidb 上层完全兼容 mysql 的特性来实现。
其数据流为 oracle——dg4odbc——odbc——mysql（TIDB）
搭建步骤
根据原理数据流向图可知数据流向经过 DG4ODBC, ODBC Driver Manager, ODBC Driver 组件，下面根据组件进行配置。
若无特殊说明所有步骤均在 oracle 端进行操作。
1、确认上下游版本信息
oracle 版本号
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、Oracle 透明网关安装
oracle 从 11G 开始默认安装了 odbc 透明网关
验证：
oracle用户bjzxtestdb:/home/oracle(orclbk)$cd $ORACLE_HOME/hsbjzxtestdb:/u01/app/oracle/product/12.2/db/hs(orclbk)$dg4odbc Oracle Corporation --- FRIDAY JUN 17 2022 11:43:17.621Heterogeneous Agent Release 12.2.0.1.0 - 64bit Production Built with Oracle Database Gateway for ODBCbjzxtestdb:/home/oracle(orclbk)$file $ORACLE_HOME/bin/dg4odbc/u01/app/oracle/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.gzcd /home/oracle/unixODBC-2.3.11./configure --prefix=/usr/local/unixODBC-2.3.11 --includedir=/usr/include --libdir=/usr/lib -bindir=/usr/bin --sysconfdir=/etcmake && make install
复制代码
查看是否安装后版本以及配置文件路径
[root@bjzxtestdb ~]# odbc_config --version2.3.11[root@bjzxtestdb ~]# odbcinst -junixODBC 2.3.11DRIVERS............: /etc/odbcinst.iniSYSTEM DATA SOURCES: /etc/odbc.iniFILE DATA SOURCES..: /etc/ODBCDataSourcesUSER DATA SOURCES..: /root/.odbc.iniSQLULEN Size.......: 8SQLLEN Size........: 8SQLSETPOSIROW 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 -junixODBC 2.3.11DRIVERS............: /etc/odbcinst.iniSYSTEM DATA SOURCES: /etc/odbc.iniFILE DATA SOURCES..: /etc/ODBCDataSourcesUSER DATA SOURCES..: /root/.odbc.iniSQLULEN Size.......: 8SQLLEN Size........: 8SQLSETPOSIROW Size.: 8cat /etc/odbc.ini[ODBC Data Sources]myodbc8w = MyODBC 8.0 UNICODE Driver DSNmyodbc8a = MyODBC 8.0 ANSI Driver DSN[myodbc8w]Description = Connector/ODBC 8.0 UNICODE Driver DSNDriver = /usr/lib64/libmyodbc8w.so SERVER = 172.XX.XX.81USER = rootPASSWORD = XXX PORT = 4001DATABASE = XXXX_sitOPTION = 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 - oracleLast login: Fri Jun 17 14:50:01 CST 2022bjzxtestdb:/home/oracle(orclbk)$cat $ORACLE_HOME/network/admin/tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2/db/network/admin/tnsnames.ora# Generated by Oracle 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/oracle/product/12.2/db/hs/admin(orclbk)$pwd /u01/app/oracle/product/12.2/db/hs/adminbjzxtestdb:/u01/app/oracle/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.WE8ISO8859P15HS_LANGUAGE = AMERICAN_AMERICA.AL32UTF8#ORACLE字符集HS_NLS_NCHAR = UCS2#异构数据库字符编码 UTF8一般对应此参数HS_FDS_SUPPORT_STATISTICS = FALSEHS_FDS_TRACE_LEVEL = debug#日志级别#HS_KEEP_REMOTE_COLUMN_SIZE=ALL 字符转换相关
复制代码
参数含义连接
https://xy2401.com/local-doc-oracle-19c.zh/content/tginu/database-gateway-for-informix-initialization-parameters.html#GUID-2AB55A84-36E7-437D-BDC9-58ADDCE35F2D
7、配置 listener.ora
bjzxtestdb:/u01/app/oracle/product/12.2/db/network/admin(orclbk)$cat listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/12.2/db/network/admin/listener.ora# Generated by Oracle 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/oracle/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, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bjzxtestdb)(PORT=1521)))STATUS of the LISTENER------------------------Alias LISTENERVersion TNSLSNR for Linux: Version 12.2.0.1.0 - ProductionStart Date 07-APR-2022 18:32:58Uptime 70 days 20 hr. 44 min. 43 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /u01/app/oracle/product/12.2/db/network/admin/listener.oraListener Log File /u01/app/oracle/diag/tnslsnr/bjzxtestdb/listener/alert/log.xmlListening 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, Oracle. All rights reserved.
Used parameter files:/u01/app/oracle/product/12.2/db/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST= bjzxtestdb) (PORT=1521)) (CONNECT_DATA= (SID=myodbc8w)) (HS=OK))OK (0 msec)
复制代码
到此系统级别配置完成，可按需进行创建 dblink 进行测试
8、测试验证
创建 dblink 并验证
oracle端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 changedMySQL [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、oracle 中通过 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 contextORA-00997: illegal use of LONG datatype
复制代码
此类似错误为 varchar 字符转换时存在的问题，对于 mysql 中 varchar 类型的字符，网关默认会转换成 oracle 中 nvarchar2 类型但 oracle 中 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-Oracle 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' doesn't exist{42S02,NativeErr = 1146}
复制代码
错误原因以及处理方法：执行的查询操作，表名需要带双引号，因为 mysql 默认表名区分大小写，而 oracle 是不区分大小写。
4、无法查询出数据、数据乱码、数据不正常或 ORA-28500
错误原因以及处理方法：hs/admin/init[sid].ora 里配置字符集错误
评论