windows环境下配置前要保证mysql数据库可以远程连接。
oracle11g自带gateway,
1、下载Mysql的odbc驱动:
下载时要下载符合自己服务器版本的驱动包Connector-ODBC,然后安装即可。下载地址:
http://dev.mysql.com/downloads/connector/odbc/,通过odbc配置对应数据库的数据源 数据源的SID为mysql
2.测试dg4odbc
,11g为dg4odbc
doc命令下 执行dg4odbc
Oracle Corporation --- 星期四 6月 23 2016 10:30:55.908
Heterogeneous Agent Release 11.2.0.1.0 - 64bit Production Built with
Oracle Database Gateway for ODBC
3.配置HS
编辑dg4odbc.ora文件,如果是拷贝$ORACLE_HOME/hs/admin/initdg4odbc.ora这个文件,要把该文件的内容都注释掉,最后的文件名 为 initmysql.ora:
# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC
#
# HS init parameters
#
#HS_FDS_CONNECT_INFO =
#HS_FDS_TRACE_LEVEL =
#HS_FDS_SHAREABLE_NAME =
#
# ODBC specific environment variables
#
#set ODBCINI=
#
# Environment variables required for the non-Oracle system
#
#set =
HS_FDS_CONNECT_INFO=mysql--为odbc中配置的DataSource Name
HS_FDS_TRACE_LEVEL=on
4.配置监听文件
# listener.ora Network Configuration File: /opt/oracle/product/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
)
LISTENER =
(DESCRIPTION_LIST =
)
ADR_BASE_LISTENER = /opt/oracle
6、配置tnsnames.ora文件
# tnsnames.ora Network Configuration File: /opt/oracle/product/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
mysql =
(DESCRIPTION =
)
orcl =
(DESCRIPTION =
7、重启ORACLE的监听服务
8、测试mysql服务,在dos窗口中tnsping tnsnames.ora中的服务名
9、创建dblink:
create public database link mysql connect to "root" identified by "admin"
using '(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = localhost) (PORT =1521) )
(CONNECT_DATA = (SID = mysql))
(HS=OK))';
10、在pl/sql中测试连接:
select * from test@mysql
能查到结果即配置成功
此链接过程主要配置地方有1.hs/admin/init(+SID+).ora,建议测试的时候HS_FDS_TRACE_LEVEL=on,可以看到错误日志。2.NETWORK/ADMIN/listener.ora和tnsnames.ora
3.Dblink创建数据链 4.odbc数据源的配置
最后有个问题就是这种方法需要手动配置不能够根据SID和Ip动态的创建DBLINK来访问mysql或oracle