oracle 透明网关 mysql_Oracle透明网关访问MySQL数据库

针对oracle数据库不同实例之间的数据访问，我们可以直接通过dblink访问，如果oracle数据库想访问mysql/sqlserver等数据库的数据，我们可以通过配置oracle透明网关实现异构数据库dblink访问。

好久没做透明网关的配置了，最近有业务需求，这里将部署过程做个记录，希望对有需要的朋友有所帮助。

一、Oracle数据库通过透明网关访问MySQL数据库环境说明

RHEL6.6  oracle 11.2.0.4

RHEL6.6 MySQL5.7

odbc

二、数据访问流程

oracle——dg4odbc——odbc——mysql

三、Oracle透明网关(MySQL)安装

oracle 11.2.0.4默认安装了odbc透明网关

验证：

[oracle@test ~]$ cd $ORACLE_HOME/hs

[oracle@test hs]$ dg4odbc

Oracle Corporation --- FRIDAY APR 27 2018 10:07:44.375

Heterogeneous Agent Release11.2.0.4.0 - 64bit Production Built withOracleDatabase Gateway for ODBC

##database gateway for odbc  简称   dg4odbc

四、mysql-connector安装

下载：

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

安装：

[root@test ~]# rpm -ivh mysql-connector-odbc-8.0.11-1.el6.x86_64.rpm

warning: mysql-connector-odbc-8.0.11-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, keyID 5072e1f5: NOKEY

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

1:mysql-connector-odbc ########################################### [100%]Success: Usagecount is 1Success: Usagecount is 1

依赖包安装：

yum install unixODBC*

rpm -qa |grep unixODBC

unixODBC-devel-2.2.14-14.el6.x86_64

unixODBC-2.2.14-14.el6.x86_64

五、ODBC配置

[root@test ~]# vi /etc/odbc.ini

[mysql_test]Description= ODBC forMySQL

Driver= /usr/lib64/libmyodbc8w.so

Server= mysql_ipaddrPort= 3306

User =dbtest

Password=abcd1234Database = test

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

账号创建：

(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 'abcd1234';

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

连通性测试：[root@test ~]# isql mysql_test+---------------------------------------+

| Connected! |

| |

| sql-statement |

| help [tablename] |

| quit |

| |

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

SQL>

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

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

SID_LIST_LISTENER =(SID_LIST=(SID_DESC=(GLOBAL_DBNAME=dbsid)

(SID_NAME=dbsid)

(ORACLE_HOME=/U01/app/oracle/product/11.2.0.4)

)

(SID_DESC=(SID_NAME=mysql_test)

(ORACLE_HOME=/U01/app/oracle/product/11.2.0.4)

(PROGRAM=dg4odbc)

)

)

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

)

)

重启监听

lsnrctl stop

lsnrctl start

(3)tnsname配置

配置tnsname

[oracle@test admin]$ vi /U01/app/oracle/product/11.2.0.4/network/admin/tnsnames.ora

dbsid_mysql =(DESCRIPTION=(ADDRESS_LIST=(ADDRESS= (PROTOCOL = TCP)(HOST = 10.1.1.1 )(PORT = 1521))

)

(CONNECT_DATA=(SID=mysql_test)

)

(HS=OK)

)

测试tnsname连接

[oracle@test admin]$ tnsping dbsid_mysql

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 27-APR-2018 12:17:58Copyright (c)1997, 2013, Oracle. Allrights reserved.

Used parameter files:

Used TNSNAMES adaptertoresolve the alias

Attemptingto contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.1)(PORT = 1521))) (CONNECT_DATA = (SID = mysql_test)) (HS =OK))

OK (0 msec)

八、dblink创建以及数据访问测试

SQL>create PUBLIC DATABASE LINK dlk connect to "dbtest" identified by "abcd1234" using 'dbsid_mysql';

SQL> select * from "t1"@dlk;

id

----------

10

11

SQL> insert into "t1"@dlk values(30);

1 row 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 from DLK

错误原因以及处理方法：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