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.testHS_FDS_TRACE_LEVEL=OFFHS_FDS_RECOVERY_ACCOUNT=RECOVERHS_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