Oracle 11g透明网关连接Sqlserver 2000

时间:2022-01-29 07:45:27

 

一、环境

公司网站系统使用的是IIS + Oracle

但公司某系统使用的是Sqlserver 2000, 但其数据需要做成报表放到网站上,为简化编程,使用Oracle做透明网关,定期从Sqlserver 抓取数据的方式

A: Web Server

  • OS: Windows Server 2008
  • IP: 10.108.3.10
  • 安装软体:IIS, Oracle Client
  • Oracle 11g透明网关连接Sqlserver 2000

B: Oralce DB Server

  • OS: Windows Server 2008
  • IP: 10.108.3.20
  • 安装软体: Oracle DB, Oracle Gateway
  • Oracle 11g透明网关连接Sqlserver 2000

C: Sqlserver 2000 DB Server

  • OS: Windows Server 2003
  • IP: 10.108.5.100
  • 安装软体: Sqlserver 2000

这里面A和C的安装配置都很简单,主要说下B机器 Oracle透明网关的配置

 

二、Oracle透明网关配置

1. gateway安装

当然是要先安装好我们的Oracle DB,做好其它业务。然后再来安装Gateway

Oracle 11g透明网关连接Sqlserver 2000

Oracle 11g透明网关连接Sqlserver 2000

2. 配置 E:\Oracle\product\11.2.0\tg_1\dg4msql\admin\initdg4msql.ora

[ 10.108.5.100].MSSQLSERVER.SQLDB HS_FDS_TRACE_LEVEL=OFF HS_FDS_RECOVERY_ACCOUNT=RECOVER HS_FDS_RECOVERY_PWD=RECOVER

Oracle Gateway在此文件中默认将sqlserver 2000的IP,实例名,数据库名以 / 分隔,这种设置后续会报错,改为以 . 分隔则OK

#HS_FDS_CONNECT_INFO=[10.108.5.100]/MSSQLSERVER/SQLDB

3. 配置E:\Oracle\product\11.2.0\tg_1\NETWORK\ADMIN\listener.ora

Oracle 11g透明网关连接Sqlserver 2000
dg4msql =
  (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.108.3.20)(PORT = 1522)) ) (CONNECT_DATA = (SERVICE_NAME = dg4msql) ) (HS = OK) )
Oracle 11g透明网关连接Sqlserver 2000

5. 配置E:\Oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora

Oracle 11g透明网关连接Sqlserver 2000
dg4msql = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.108.3.20)(PORT = 1522)) ) (CONNECT_DATA = (SERVICE_NAME = dg4msql) ) (HS = OK) ) ORACLR_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) (CONNECT_DATA = (SID = CLRExtProc) (PRESENTATION = RO) ) )
Oracle 11g透明网关连接Sqlserver 2000

7. 建立DB link

-- Create database link create database link SQLSERVERLINK connect to sa identified by password using 'dg4msql';