利用odbc连接oracle与tidb

利用odbc连接oracle与tidb

作者： PINO ​

背景

某客户现有系统大量使用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中。该文件默认位于$ORACLE_HOME/network/admin下，其中$ORACLE_HOME是安装透明网关的目录。如果透明网关的安装目录和ORACLE实例一致的话，那么它会和数据库共用监听文件。

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

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

搭建步骤

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

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

1、确认上下游版本信息

oracle版本号

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、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

到​​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目前支持的版本

根据实际环境-最新兼容版本,-

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

5、配置 ODBC Driver

参考地址：

​​~]# 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 字符转换相关

参数含义连接

​​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:42Copyright (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:25Copyright (c) 1997, 2016, Oracle. All rights reserved.Used parameter files:/u01/app/oracle/product/12.2/db/network/admin/sqlnet.oraUsed 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(*)---------- 4567mysql端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 UTF8select 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里配置字符集错误

HS_LANGUAGE=AMERICAN_AMERICA.zhs16gbk字符集不正确，应配置oracle数据库字符集HS_NLS_NCHAR = UCS2 有奇效

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。