Oracle通过透明网关访问MySQL数据库配置详解

161次阅读

共计 4380 个字符，预计需要花费 11 分钟才能阅读完成。

针对 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 Release 11.2.0.4.0 – 64bit Production Built with
Oracle Database 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, key ID 5072e1f5: NOKEY
Preparing… ########################################### [100%]
1:mysql-connector-odbc ########################################### [100%]
Success: Usage count is 1
Success: Usage count 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 for MySQL
Driver = /usr/lib64/libmyodbc8w.so
Server = mysql_ipaddr
Port = 3306
User = dbtest
Password = abcd1234
Database = test

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

账号创建：
(root:localhost:Fri Apr 27 10:16:11 2018)[(none)]>create database test;
(root:localhost:Fri Apr 27 10:16:22 2018)[(none)]>grant all on test.* to dbtest@’%’ identified by ‘abcd1234’;
(root:localhost:Fri Apr 27 10:16:40 2018)[(none)]>flush privileges;
连通性测试：

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

set 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:58

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to 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 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 包
（2）错误 02
错误信息：

SQL> select * from “t1″@dlk;
select * from “t1″@dlk
*
ERROR at line 1:
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 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}
ORA-02063: preceding 2 lines from DLK

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

正文完

星哥说事-微信公众号