针对 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;
来源: http://www.linuxidc.com/Linux/2018-04/152116.htm