ORACLE + ODBC + DBLINK 连接 MYSQL

一、 Oracle数据库ODBC网关概述

透明网关技术由异构服务和Oracle数据库ODBC网关两部分组成,二者共同支持从Oracle环境对非Oracle系统的透明访问。异构服务是数据库的集成组件,提供了连接到非Oracle系统的通用技术。Oracle ODBC网关包括SQL映射、数据类型转换等能力,通过动态查询功能访问与ODBC标准兼容的数据源。

Oracle Gateway for ODBC架构(Oracle与异构数据库部署在不同主机上):

连接过程:

客户端通过Oracle Net连接到Oracle数据库;

异构服务(Heterogeneous Services)组件通过Oracle Net连接到Oracle ODBC 网关;

网关与ODBC driver managerODBC driver通信;

当用户会话第一次使用dblink连接到非Oracle系统时会产生一个专用的代理进程,每个用户会话接收自己的代理进程。当用户会话终止代理进程也终止。

二、Oracle-MySQL透明网关配置方法

1、环境和版本

本文主要介绍Oracle-MySQL透明网管的配置,涉及的服务器环境及操作系统数据库版本如下:

服务器1CENTOS RHEL7.2):Oracle 11.2.0.4

服务器2CENTOS RHEL7.2):MySQL 5.7

2、配置步骤

Oracle-MySQL透明网关的工作方式可简述为:

Oracle数据库 --> DBLINK --> TNS别名 --> Listener监听 --> ODBC --> MySQL connector --> MySQL监听 --> MySQL数据库。

Gateway配置主要包括以下步骤(在Oracle服务器上进行):

1)验证Oracle透明网关已安装

Oracle 11.2.0.4默认安装了odbc透明网关dg4odbc,验证:

[oratest@hker db]$ dg4odbc

Oracle Corporation --- WEDNESDAY MAR 17 2021 14:13:06.616

Heterogeneous Agent Release 11.2.0.4.0 - 64bit Production  Built with

   Oracle Database Gateway for ODBC

2)安装Driver Manager

Driver Manager负责管理应用程序和驱动程序间的通信,包括:解析DSN (数据源名称,ODBC的数据源名称在ODBC.INI文件中配置),加载和卸载驱动程序,处理ODBC调用,将其传递给驱动程序。

root用户安装Driver Manager ,本案例使用unixODBC

[oraoratest@hker db]$ rpm -qa |grep unixODBC

unixODBC-2.3.1-11.el7.x86_64

unixODBC-devel-2.3.1-11.el7.x86_64

核查安装情况:

[oratest@hker db]$ odbcinst -j

unixODBC 2.3.1

DRIVERS............: /etc/odbcinst.ini

SYSTEM DATA SOURCES: /etc/odbc.ini

FILE DATA SOURCES..: /etc/ODBCDataSources

USER DATA SOURCES..: /home/oratest/.odbc.ini

SQLULEN Size.......: 8

SQLLEN Size........: 8

SQLSETPOSIROW Size.: 8

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包下载地址:MySQL :: Download Connector/ODBC

[root@hker soft]# rpm -ivh mysql-connector-odbc-8.0.19-1.el7.x86_64.rpm

warning: mysql-connector-odbc-8.0.19-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

Preparing...                          ################################# [100%]

Updating / installing...

   1:mysql-connector-odbc-8.0.19-1.el7################################# [100%]

Success: Usage count is 1

Success: Usage count is 1

[root@hker soft]# rpm -qa | grep mysql

qt-mysql-4.8.5-11.el7.x86_64

mysql-connector-odbc-8.0.19-1.el7.x86_64

pcp-pmda-mysql-3.10.6-2.el7.x86_64

4odbc.ini配置

ODBC.INIODBC配置文件,记录了连接到服务器所需的驱动信息和数据库信息。Driver Manager将使用它来确定加载哪个驱动程序(使用数据源名DSN)。驱动程序将根据指定的DSN来读取连接参数。

/etc/odbc.ini文件中定义DSN(数据源名称,如mysql_link),配置数据源信息(驱动、IP、端口、用户名、口令、库名等)

vi /etc/odbc.ini

[mysql_link]

Description = ODBC for MySQL

Driver = /usr/lib64/libmyodbc8a.so

Server = 172.77.88.99

Port = 3800

User = u_skkeejerv_dev

Password = dkr78er32rjkle

Database = skkejerv_dev

CHARSET = utf8mb4

5)测试mysql odbc是否配置成功

isql测试mysql odbc是否配置成功,isqlunixODBC带的一个ODBC客户端访问工具,可使用isql +数据源名来访问目标数据库。

[root@hker etc]# isql mysql_link

+---------------------------------------+

| Connected!                            |

|                                       |

| sql-statement                         |

| help [tablename]                      |

| quit                                  |

|                                       |

+---------------------------------------+

SQL> show tables;

+-----------------------------------------------------------------+

| Tables_in_skke_dev                                   |

+-----------------------------------------------------------------+

| d_bom                                                           |

| d_bom_detail                                                    |

6hs透明网关配置

"ORACLE_HOMEhsadmin"目录下,默认存在名为"initdg4odbc.ora"的文件,每个使用DG4ODBC的实例,都必须对应一个"init*.ora"文件,文件命名规则:init+<网关sid>+.ora,文件主要参数:

HS_FDS_CONNECT_INFOODBC数据源管理中的DSN名称

HS_FDS_TRACE_LEVEL:追踪级别参数,出于性能影响一般不配置或者配置为"OFF",若遇到网关问题需要跟踪日志,则配置为"Debug"

[oratest@hker admin]$ vi initmysql_link.ora

##HS Configuration

HS_FDS_CONNECT_INFO = mysql_link

HS_FDS_TRACE_LEVEL = OFF

HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so

HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8

HS_NLS_DATE_FORMAT=YYYY-MM-DD

HS_NLS_NCHAR = UCS2

HS_FDS_FETCH_ROWS=1000

HS_RPC_FETCH_REBLOCKING=OFF

##ODBC Configuration

set ODBCINI=/etc/odbc.ini

7)网关监听配置

listener.ora中增加如下配置,sid_name要与上面hs网关配置的网关sid一致,红色部分是新增加。可以新建个单独的监听,也可以在原有的监听中加入(建议单独建立监听)

TEST =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = hker.oracle.com)(PORT = 1541))

    )

  )

SID_LIST_TEST =

  (SID_LIST =

    (SID_DESC =

      (ORACLE_HOME= /home/TEST/db/11.2.0)

      (SID_NAME = TEST)

    )

    (SID_DESC =

      (ORACLE_HOME= /home/TEST/db/11.2.0)

      (SID_NAME = mysql_link)

      (PROGRAM=dg4odbc)

    )

  )

8tns配置

ADDRESS:填写透明网关的IP地址和端口

SID:指定连接网关的SID

HS:指定连接的是非ORACLE数据库

vi tnsname.ora (注意:HS=OK,CONNECT 右括号后面)

mysql_link=

 (DESCRIPTION=

                (ADDRESS=(PROTOCOL=tcp)(HOST=hker.oracle.com)(PORT=1541))

   (CONNECT_DATA=

         (SID=mysql_link)

    )

(HS=OK)

 )

重启监听并测试

lsnrctl reload TEST

[oratest@hker TEST_hker]$ lsnrctl service TEST

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-MAR-2021 16:16:51

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hker.oracle.com)(PORT=1541)))

Services Summary...

Service "TEST" has 2 instance(s).

  Instance "TEST", status UNKNOWN, has 1 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:0 refused:0

         LOCAL SERVER

  Instance "TEST", status READY, has 1 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:2 refused:0 state:ready

         LOCAL SERVER

Service "TEST_ebs_patch" has 1 instance(s).

  Instance "TEST", status READY, has 1 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:2 refused:0 state:ready

         LOCAL SERVER

Service "mysql_link" has 1 instance(s).

  Instance "mysql_link", status UNKNOWN, has 1 handler(s) for this service...

    Handler(s):

      "DEDICATED" established:0 refused:0

         LOCAL SERVER

The command completed successfully

[oratest@hker admin]$ tnsping mysql_link

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 17-MAR-2021 16:18:12

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:

/home/TEST/db/11.2.0/network/admin/TEST_hker /sqlnet_ifile.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=hker.oracle.com)(PORT=1541)) (CONNECT_DATA= (SID=mysql_link) (HS=OK))

OK (0 msec)

成功则说明没问题了. oracle 11gDG4ODBC这段成功了.

9dblink创建以及透明网关测试

SQL> create DATABASE LINK ek_mysql connect to "kll1dkfewfef" identified by "xxdff " using 'mysql_link';

注意:用户名和密码一定的加””,申明为小写(Oracle默认大小,Mysql默认小写),不然不匹配。

问题排查:hs/log

测试在Oracle端通过透明网关访问MySQL库数据并建表(8千万+记录,2.5G数据量,约4分钟,等待事件多为:“HS message to agent”):

中文乱码问题:initmysql_link.ora 加入

HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8

HS_NLS_NCHAR = UCS2

时间格式乱码问题:

select to_char(to_timestamp("analysis_time"),'yyyy-mm-dd hh24:mi:ss'),to_char(to_timestamp("allocation_time"),'yyyy-mm-dd hh24:mi:ss') from d_deduct@ek_mysql

请使用格式转换

本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
THE END
分享
二维码
< <上一篇
下一篇>>