Oracle透明网关访问MySQL数据库

针对oracle数据库不同实例之间的数据访问，我们可以直接通过dblink访问，如果oracle数据库想访问mysql/sqlserver等数据库的数据，

我们可以通过配置oracle透明网关实现异构数据库dblink访问。

好久没做透明网关的配置了，最近有业务需求，这里将部署过程做个记录，希望对有需要的朋友有所帮助。==============================================================================================================================一、Oracle数据库通过透明网关访问MySQL数据库环境说明

oracle 数据库：[oracle@test66 hs]$ cat /etc/redhat-release

CentOS Linux release7.6.1810(Core)

通过gateway 透明网关进行连接配置

mysql 数据库：[mysql@test67 ~]$ cat /etc/redhat-release

CentOS Linux release7.6.1810(Core)

通过 odbc 进行连接==============================================================================================================================二、数据访问流程

oracle——dg4odbc——odbc——mysql

注意 odbc 可以与 gateway 安装到一台机器上。也就是说 mysql_odbc工具 可以安装到 oracle 数据库服务器上面==============================================================================================================================三、Oracle透明网关(MySQL)安装

oracle11.2.0.4默认安装了odbc透明网关

验证：[oracle@test ~]$ cd $ORACLE_HOME/hs---------------------------------------------------------------

##database gateway forodbc 简称 dg4odbc[oracle@test66 hs]$[oracle@test66 hs]$ which dg4odbc/u01/app/oracle/product/11.2.0/dbhome_1/bin/dg4odbc[oracle@test66 hs]$[oracle@test66 hs]$ dg4odbc

Oracle Corporation--- TUESDAY JAN 05 2021 13:58:09.471

Heterogeneous Agent Release11.2.0.4.0 - 64bit Production Built withOracleDatabase Gateway forODBC==================================================================================================================================四、mysql-Connector/ODBC 安装 【 可以在oracle 数据库上面 安装，也就是说 gateway 与 mysql-odbc 都可以安装到oracle数据库服务器上面 】

下载：

https://dev.mysql.com/downloads/connector/odbc/https://dev.mysql.com/get/Downloads/Connector-ODBC/8.0/mysql-connector-odbc-8.0.11-1.el6.x86_64.rpm

实际安装步骤如下：1、按照依赖包

yum install-y libodbc.so*

----------------------------------------------------------------------------------

[root@test67 software]# yum install -y libodbc.so*Loaded plugins: fastestmirror

Loading mirror speedsfromcached hostfile

Resolving Dependencies--> Running transaction check---> Package unixODBC.x86_64 0:2.3.1-11.el7 will be installed--> Finished Dependency Resolution

Dependencies Resolved=================================================================================================================================================================================================================Package Arch Version Repository Size=================================================================================================================================================================================================================Installing:

unixODBC x86_642.3.1-11.el7 local 413kTransactionSummary=================================================================================================================================================================================================================Install1Package

Total download size:413k

Installed size:1.2M

Downloading packages:

Runningtransaction checkRunningtransactiontestTransactiontest succeeded

RunningtransactionInstalling : unixODBC-2.3.1-11.el7.x86_64 1/1Verifying : unixODBC-2.3.1-11.el7.x86_64 1/1Installed:

unixODBC.x86_640:2.3.1-11.el7

Complete!----------------------------------------------------------------------------------------------------

2、按照依赖包，获取从官网获取。【【 https://dev.mysql.com/downloads/mysql/】】

rpm-ivh mysql-community-client-plugins-8.0.22-1.el7.x86_64.rpm3、安装 mysql-connector-odbc odbc 工具 ，安装完依赖之后 可以直接安装 odbc 工具了

rpm-ivh mysql-connector-odbc-8.0.22-1.el7.x86_64.rpm================================================================================================五、mysql-ODBC 的配置 【在 oracle服务器上操作】[oracle@test66 ~]$ cat /etc/odbc.ini[mysql_test]Description= ODBC forMySQL

Driver= /usr/lib64/libmyodbc8w.so

Server= 192.168.17.67Port= 3306

User =dbtest

Password=dbtestDatabase =test================================================================================================六、MySQL数据库创建账号、授权并测试连通性

账号创建与建库：create userdbtest;

(root:localhost:Fri Apr27 10:16:11 2018)[(none)]>create databasetest;

(root:localhost:Fri Apr27 10:16:22 2018)[(none)]>grant all on test.* to dbtest@'%' identified by 'dbtest';

(root:localhost:Fri Apr27 10:16:40 2018)[(none)]>flush privileges;

连通性测试：【在 oracle服务器上验证操作】[oracle@test66 ~]$ isql mysql_test+---------------------------------------+

| Connected! |

| |

| sql-statement |

| help [tablename] |

| quit |

| |

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

====================================================================================================================================

====================================================================================================================================

====================================================================================================================================七、Oracle数据库相关配置 【oracle 数据库上进行操作】

(1)hs透明网关配置[oracle@test ~]$ cd $ORACLE_HOME/hs[oracle@test hs]$ cd admin[oracle@test admin]$ vi initmysql_test.ora

##HS Configuration

HS_FDS_CONNECT_INFO=mysql_test

HS_FDS_TRACE_LEVEL=debug

HS_FDS_SHAREABLE_NAME= /usr/lib64/libodbc.so

HS_FDS_SUPPORT_STATISTICS=FALSE

HS_LANGUAGE=AMERICAN_AMERICA.zhs16gbk -------------- 这个地方很关键， 我试过很多次 只有 zhs16gbk 这个字符集才可以不报错，但是后期查询的数据有错误

##ODBC Configurationset ODBCINI=/etc/odbc.ini

##这里配置的是数据库实例名、odbc lib包，oracle数据库字符集、odbc配置文件路径

(2)监听配置 【配置静态监听】[oracle@test admin]$ vi /u01/app/oracle/product/11.2.0.4/network/admin/listener.ora[oracle@test66 admin]$ cat listener.ora

LISTENER=(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS= (PROTOCOL = TCP)(HOST = 192.168.17.66)(PORT = 1521))

)

)

)

SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=mysql)

(ORACLE_HOME= /u01/app/oracle/product/11.2.0/dbhome_1)

(PROGRAM=dg4odbc)

)

)

重启监听

lsnrctl stop

lsnrctl start[oracle@test66 admin]$ lsnrctl status

LSNRCTLfor Linux: Version 11.2.0.4.0 - Production on 05-JAN-2021 15:36:42Copyright (c)1991, 2013, Oracle. Allrights reserved.

Connectingto (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.17.66)(PORT=1521)))

STATUSofthe LISTENER------------------------

Alias LISTENER

Version TNSLSNRfor Linux: Version 11.2.0.4.0 -Production

Start Date05-JAN-2021 15:07:32Uptime0 days 0 hr. 29 min. 10sec

TraceLevel offSecurityON: Local OS Authentication

SNMPOFFListener ParameterFile /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

ListenerLog File /u01/app/oracle/diag/tnslsnr/test66/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.17.66)(PORT=1521)))

Services Summary...

Service "mysql" has1 instance(s). ----------------- 这个地方就是配置的 odbc-mysql 的监听

Instance "mysql", status UNKNOWN, has 1 handler(s) forthis service...

Service "ora11g" has1instance(s).

Instance "ora11g", status READY, has1 handler(s) forthis service...

Service "ora11gXDB" has1instance(s).

Instance "ora11g", status READY, has1 handler(s) forthis service...

The command completed successfully[oracle@test66 admin]$

(3)tnsname配置

配置tnsname[oracle@test admin]$ vi /u01/app/oracle/product/11.2.0.4/network/admin/tnsnames.ora[oracle@test66 admin]$ cat tnsnames.ora

hsmysql=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.17.66)(PORT=1521))

(CONNECT_DATA=(SID=mysql))

(HS=OK)

)

测试tnsname连接[oracle@test66 admin]$ tnsping hsmysql

TNS Ping Utilityfor Linux: Version 11.2.0.4.0 - Production on 05-JAN-2021 15:37:37Copyright (c)1997, 2013, Oracle. Allrights reserved.

Used parameter files:

Used TNSNAMES adaptertoresolve the alias

Attemptingto contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.17.66)(PORT=1521)) (CONNECT_DATA=(SID=mysql)) (HS=OK))

OK (0msec)[oracle@test66 admin]$=======================================================================================================八、dblink创建以及数据访问测试

复制代码create PUBLIC DATABASE LINK dl_mysql connect to "dbtest" identified by "dbtest" using 'hsmysql';

SQL> select 1 from "t1"@dl_mysql;

id----------

10

11SQL> insert into "t1"@dl_mysql values(30);1row created.

九、错误信息以及处理方法

(1)错误01

错误信息：

SQL> select * from t1@dlk;select * from t1@dlk

*ERROR at line1:

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

ORA-02063: preceding line fromDLK

错误原因以及处理方法：hs/admin/init[sid].ora里配置的HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so不正确，应该是odbc的Lib包

(2)错误02

错误信息：

SQL> select * from "t1"@dlk;select * from "t1"@dlk

*ERROR at line1:

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:[错误原因以及处理方法：hs/admin/init[sid].ora里配置的HS_LANGUAGE=AMERICAN_AMERICA.zhs16gbk字符集不正确，应该是oracle数据库字符集

(3)错误03：

错误信息：

SQL> select * from t1@dlk;select * from t1@dlk

*ERROR at line1:

ORA-00942: table or view does notexist[MySQL][ODBC 8.0(w) Driver][mysqld-5.7.18-log]Table 'test.T1' doesn't exist

{42S02,NativeErr = 1146}

ORA-02063: preceding 2 lines from DLK

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

(4)参考文档

https://blog.csdn.net/u012514278/article/details/51741698

http://blog.itpub.net/7728585/viewspace-2128158/

http://www.docin.com/p-113642416.html