针对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; 连通性测试：
[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 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
Copyright (c) 1997, 2013, Oracle. All rights reserved.
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;
