Oracle连接Sql Server （win32_11gR2_gateways）

备注：已经安装好Oracle11和Sql Server 2008数据库

oracle : IP: 10.45.64.239  数据库名：orcl  账户密码 **yc / **yc   监听端口：1521

Sql: IP:10.45.70.10 数据库名：ZJYC_LS 账户密码 sa/hu*ke

gateways: IP 10.45.64.239  监听端口：1521

注意事项：

• gateways安装的时候，服务器原先就安装了Oracle,在安装gateways的时候，安装目录要放到Oracle安装目录下面（透明网关本身也存在监听，而Oracle本身也有监听，为了不必要的麻烦，可以把Gateway的监听涵盖到Oracle监听中）：

I:\app\Administrator\product\11.2.0\dbhome_1

• gateways具体安装32位还是64的，安装之前一定要确定原先oracle安装版本，根据其选择安装的版本。

一、安装软件

1、第一步现在安装好，win32_11gR2_gateways；

2、

3、（重点）到这一步的时候，如果你已经在这台电脑上安装了Oracle数据库，

显示的指定主目录详细资料页面为图：3-2；

                                                           图：3-1

图：3-2

4、

5、填写Sql Server数据库IP地址及数据库名

6、

 

二、安装完成后，开始配置：

 

*修改配置文件：

 一、Gateways软件配置文件修改

1、I:\app\Administrator\product\11.2.0\dbhome_1\dg4msql\admin\initdg4msql.ora

注：HS_FDS_CONNECT_INFO=主机名:端口//数据库

  

# 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=10.22.1.245:1433//GXYC_GLOLD
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

 

2、I:\app\Administrator\product\11.2.0\dbhome_1\dg4msql\admin\listener.ora.sample

# This is a sample listener.ora that contains the NET8 parameters that are
# needed to connect to an HS Agent

LISTENER =
 (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
 )

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

#CONNECT_TIMEOUT_LISTENER = 0 

3、I:\app\Administrator\product\11.2.0\dbhome_1\dg4msql\admin\tnsnames.ora.sample

# This is a sample tnsnames.ora that contains the NET8 parameters that are
# needed to connect to an HS Agent

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

 

4、I:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora

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

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (PROGRAM = extproc)
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = I:\app\Administrator\product\11.2.0\dbhome_1)
    )
    (SID_DESC =
      (PROGRAM = dg4msql)
      (SID_NAME = dg4msql)
      (ORACLE_HOME = I:\app\Administrator\product\11.2.0\dbhome_1)
    )
  )

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

ADR_BASE_LISTENER = I:\app\Administrator

5、I:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora

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

dg4msql =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = dg4msql)
    )
	(HS = OK)
  )

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

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

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

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

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


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

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

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

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

 

最后重启数据库服务

三、创建DBLink

 

五、查询数据

select * from MC_Sort_ErrorRecord@Fj_Mg

 

备注：如果需要删除该工具，直接在卸载页面卸载就行，不需要删除其他配置文件；