0
3
3
0
专栏/.../

利用odbc连接oracle与tidb

 PINO  发表于  2022-06-21
原创迁移数据库连接实践案例应用适配数据库架构选型

背景

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

image.png

原理

透明网关概念

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实例一致的话，那么它会和数据库共用监听文件。

image.png

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/hs
bjzxtestdb:/u01/app/oracle/product/12.2/db/hs(orclbk)$dg4odbc 
Oracle Corporation --- FRIDAY    JUN 17 2022 11:43:17.621
Heterogeneous Agent Release 12.2.0.1.0 - 64bit Production  Built with
   Oracle Database Gateway for ODBC
bjzxtestdb:/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.gz
cd /home/oracle/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目前支持的版本

image.png

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

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 - oracle
Last login: Fri Jun 17 14:50:01 CST 2022
bjzxtestdb:/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/admin
bjzxtestdb:/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.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-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                     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/oracle/product/12.2/db/network/admin/listener.ora
Listener Log File         /u01/app/oracle/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, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/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并验证

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 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、oracle中通过dblnk访问tidb时，进行select、dml操作需进行显式commit、rollback操作(意外中断不影响)，否则tidb端不释放连接影响gc。

image.png

image.png

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类型的字符，网关默认会转换成oracle中nvarchar2类型但oracle中nvarchar2类型存在最大长度限制，当长度大于最大限制时则网关会转换成long字段类型，此时便会存在转换问题。

image.png

解决方案

查看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里配置字符集错误

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

0
3
3
0

版权声明：本文为 TiDB 社区用户原创文章，遵循 CC BY-NC-SA 4.0 版权协议，转载请附上原文出处链接和本声明。

评论
暂无评论
背景原理透明网关概念透明网关监听搭建步骤1、确认上下游版本信息2、Oracle透明网关安装3、下载并安装ODBC Driver Manager4、下载并安装ODBC Driver for MySQL5、配置tnsnames.ora6、配置透明网关7、配置listener.ora8、测试验证使用限制1、物化视图只支持全量刷新，不支持增量(tidb端无法进行创建增量日志)。2、oracle中通过dblnk访问tidb时，进行select、dml操作需进行显式commit、rollback操作(意外中断不影响)，否则tidb端不释放连接影响gc。3、暂时不支持lob字段访问，若where条件或查询列不包含lob字段不影响使用。4、使用dblink访问时对于字段名或表名需要增加双引号进行查询，同义词也需要。否则无法识别错误解决1、ORA-02070、ORA-009972、ORA-28500\ ORA-020633、ORA-009424、无法查询出数据、数据乱码、数据不正常或ORA-28500