针对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