oracle透明网关通过dblink连接slq server和mysql

来源：互联网发布：微博关键词优化编辑：程序博客网时间：2024/05/18 03:30

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 =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = /opt/oracle/product/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
     (SID_NAME = mysql)--为odbc中配置的DataSource Name
    (ORACLE_HOME = /opt/oracle/product/db_1)
   (PROGRAM = dg4odbc)
   )
)

LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
)

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 =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SID = mysql))--为odbc中配置的DataSource Name
    (HS=OK)
)

orcl =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = orcl)
    )
  )

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

0 0