Oracle透明网关访问MySQL数据库 针对oracle数据库不同实例之间的数据访问，我们可以直接通过dblink访问，如果oracle数据库想访问mysql/sqlserver等数据库的数据， 我们可以通过配置oracle透明网关实现异构数据库dblink访问。 好久没做透明网关的配置了，最近有业务需求，这里将部署过程做个记录，希望对有需要的朋友有所帮助。 ============================================================================================================================== 一、Oracle数据库通过透明网关访问MySQL数据库环境说明 oracle 数据库： [oracle@test66 hs]$ cat /etc/redhat-release CentOS Linux release 7.6.1810 (Core) 通过gateway 透明网关进行连接配置 mysql 数据库： [mysql@test67 ~]$ cat /etc/redhat-release CentOS Linux release 7.6.1810 (Core) 通过 odbc 进行连接 ============================================================================================================================== 二、数据访问流程 oracle——dg4odbc——odbc——mysql 注意 odbc 可以与 gateway 安装到一台机器上。也就是说 mysql_odbc工具 可以安装到 oracle 数据库服务器上面 ============================================================================================================================== 三、Oracle透明网关（MySQL）安装 oracle 11.2.0.4默认安装了odbc透明网关 验证： [oracle@test ~]$ cd $ORACLE_HOME/hs --------------------------------------------------------------- ##database gateway for odbc 简称 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 Release 11.2.0.4.0 - 64bit Production Built with Oracle Database Gateway for ODBC ================================================================================================================================== 四、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 speeds from cached 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_64 2.3.1-11.el7 local 413 k Transaction Summary ================================================================================================================================================================================================================= Install 1 Package Total download size: 413 k Installed size: 1.2 M Downloading packages: Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : unixODBC-2.3.1-11.el7.x86_64 1/1 Verifying : unixODBC-2.3.1-11.el7.x86_64 1/1 Installed: unixODBC.x86_64 0: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.rpm 3、安装 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 for MySQL Driver = /usr/lib64/libmyodbc8w.so Server = 192.168.17.67 Port = 3306 User = dbtest Password = dbtest Database = test ================================================================================================ 六、MySQL数据库创建账号、授权并测试连通性 账号创建与建库： create user dbtest; (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 'dbtest'; (root:localhost:Fri Apr 27 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 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 [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 LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 05-JAN-2021 15:36:42 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.17.66)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 05-JAN-2021 15:07:32 Uptime 0 days 0 hr. 29 min. 10 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora Listener Log 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" has 1 instance(s). ----------------- 这个地方就是配置的 odbc-mysql 的监听 Instance "mysql", status UNKNOWN, has 1 handler(s) for this service... Service "ora11g" has 1 instance(s). Instance "ora11g", status READY, has 1 handler(s) for this service... Service "ora11gXDB" has 1 instance(s). Instance "ora11g", status READY, has 1 handler(s) for this 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 Utility for Linux: Version 11.2.0.4.0 - Production on 05-JAN-2021 15:37:37 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.17.66)(PORT=1521)) (CONNECT_DATA=(SID=mysql)) (HS=OK)) OK (0 msec) [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 11 SQL> insert into "t1"@dl_mysql 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; （4）参考文档 https://blog.csdn.net/u012514278/article/details/51741698 http://blog.itpub.net/7728585/viewspace-2128158/ http://www.docin.com/p-113642416.html