Oracle 访问SQL Server的数据 (通过Oracle gataway)

测试例子:通过Oracle的GateWay,访问SQLServer的数据。

Oracle 信息:

Oracle所在机器的IP : 192.168.2.28

oracle SID :test

Oracle 版本: 11.2.0.4

SQL Server 信息:

SQL Server所在机器的IP : 192.168.2.28

SQLServer SID : MSSQLSERVER   -- 其实这个不是很重要,在后面设置的时候,可以不填写。

SQL Server 数据库 : test

SQL Server 版本 : SQL Server 2008 R2

GateWay信息:

其实,就是11gr2对应的第五个安装包 p13390677_112040_MSWIN-x86-64_5of7 。

gateway所在的机器IP : 192.168.2.28  

这里做简单的测试,把SQL Server、Oracle、Gataway安装在一台机器上了 。

先看Gateway的安装  ,安装比较简单,安装过程如下:

注意,这里的截图和实际安装有点出入,时间安装的时候,下图的名称不是oragwt11g_home1,而是ORA_11g_home1,覆盖了Oracle_home,也就是和Oracle安装在一个目录了。 Oracle的MOS上有文章介绍,不建议这样安装,因为这样安装,可能会把Oracle的一些补丁给覆盖掉了。

 

配置:

监听器的配置,其中,dg4msql之类的是新增加的

# listener.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = test)
      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)
      (SID_NAME = test)
    )
	(SID_DESC =
      (GLOBAL_DBNAME = dg4msql)
	  (PROGRAM = dg4msql)
      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)
      (SID_NAME = dg4msql)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.28)(PORT = 1521))
  )

ADR_BASE_LISTENER = C:\app\Administrator\product\11.2.0\dbhome_1\log

tnsnames.ora的配置。同样的dg4msql之类的也是新增加的内容

# tnsnames.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

TEST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.28)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = test)
    )
  )

dg4msql  =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.28)(PORT=1521))
    (CONNECT_DATA=(SID=dg4msql))
    (HS=OK)
  ) 

dg4msql的配置,其实这里在上面的图形界面中已经设置了,不需要修改,内容如下:

# This is a customized agent init file that contains the HS parameters
# that are needed for the Database Gateway for Microsoft SQL Server

#
# HS init parameters
#
HS_FDS_CONNECT_INFO=[192.168.2.28]//test
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

查看监听状态

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.28)(PORT=1521)
))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.4.0 - Produ
ction
Start Date                30-6月 -2019 11:18:53
Uptime                    0 days 3 hr. 21 min. 38 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\app\Administrator\product\11.2.0\dbhome_1\network\a
dmin\listener.ora
Listener Log File         C:\app\Administrator\product\11.2.0\dbhome_1\log\diag\
tnslsnr\WIN-LPCB8UCORM9\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.28)(PORT=1521)))
Services Summary...
Service "dg4msql" has 1 instance(s).
  Instance "dg4msql", status UNKNOWN, has 1 handler(s) for this service...
Service "test" has 1 instance(s).
  Instance "test", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>

创建Oracle到SQLServer的DBLINK

SYS@test>CREATE DATABASE LINK tomssql CONNECT TO "sa" IDENTIFIED BY "sysadminXXX" USING 'dg4msql';

测试访问:可以看到,可以访问SQL Server中的数据

插入一条数据,在Oracle中可以执行,在SQL Server中也可以看到结果。

 

END

评论 4
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值