oracle gateways 访问 sql server

相关系统环境

操作系统：windows server 2008
服务器：oracle database 11.2.0.1.0
gateways：oracle database gateways 11.2.0.1.0
sql server：microsoft SQL server 2008

oracle gateways 安装 (主要说明重点安装选择步骤)

• 选择产品安装目录
  
  默认选择如上图，gateways 可以单独配置监听 (单独配置则此处直接点击下一步即可，会在后面的安装步骤中出现配置监听的步骤)，为了不必要的麻烦；此处主要把 gateways 的监听涵盖到监听中去。直接点击名称后的下拉框，如下图：
  
  选择之后路径则变成了，本机 oracle 数据库服务器安装的路径
  
• 进入选择安装的组件
  此处主要确定要访问的异构数据库，因此直接选择 sql server
  
• 进入配置连接 sql server 信息界面
  
  注意：如果此处不进行配置或者安装完成后有修改，则进入到安装目录下的 dg4msql\init4msql.ora 配置文件中，进行相关的设置即可。

安装完成后的相关配置

• 配置 initdg4msql.ora 文件
  具体文件位置为：安装目录下\dg4msql\init4msql.ora

# 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.1.115].MSSQLSERVER.test
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

oracle gateways 在此文件中HS_FDS_CONNECT_INFO 对应的行默认将 sql server 2008 的 IP、实例名、数据库以 / 分隔，这种设置后续会报错；改为以 .分隔则 OK。

• 配置监听器 (可以文件配置和图形化工具配置，此处采用文件配置)
  具体文件位置为：安装目录下\NETWORK\ADMIN\listener.ora

配置前

# 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 =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  )

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

ADR_BASE_LISTENER = C:\app\Administrator

配置后

# 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 =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
    (SID_DESC =
      (SID_NAME = dg4msql)  #此处为配置文件SID，要与initdg4msql.ora中的名字对应
      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)
      (PROGRAM = dg4msql)  #此处为配置文件目录名称，Oracle 11g中都为dg4msql，不需变更（注1）
    )
  )

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

ADR_BASE_LISTENER = C:\app\Administrator

注1：如果将此处的dg4msql变掉，在最后通过Oracle访问SQL SERVER时会报以下错误

ORA-28545: 连接代理时 Net8 诊断到错误
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: 紧接着 2 lines

• 配置 TNSNAMES
  具体文件位置为：安装目录下\NETWORK\ADMIN\tnsnames.ora

配置前

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

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

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

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

配置后

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

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

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

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

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

重启 oracle 服务

以上配置完成之后，需要重新启动一下 oracle 的服务。

创建 DB link

例如以下创建：

create public database link MS_LINK
  connect to "sa" identified by "123456"
  using 'dg4msql';

操作 sql server 数据库

select * from test@ms_link

---

参考列表

• 参考1
• 参考2