使Oracle能同时访问多个SQL Server

ZDNet软件频道 时间:2009-04-10 作者:qqread | 天新网 我要评论()
本文关键词:Mssql SQL SQL Server 数据库 SQL Server
如何在Oracle里设置访问多个SQL Server数据库?假设我们要在Oracle里同时能访问SQL Server里默认的pubs和Northwind两个数据库。

    如何在Oracle里设置访问多个SQL Server数据库?假设我们要在Oracle里同时能访问SQL Server里默认的pubs和Northwind两个数据库

1、在安装了Oracle9i Standard Edition或者Oracle9i EntERPrise Edition的Windows机器上(IP:192.168.0.2), 产品要选了透明网关(Oracle Transparent Gateway)里访问Microsoft SQL Server数据库

ORACLE9I_HOME	g4mSQLadmin下新写initpubs.ora和initnorthwind.ora配置文件.
initpubs.ora内容如下:
HS_FDS_CONNECT_INFO="SERVER=SQLSERVER_HOSTNMAE;DATABASE=pubs"
HS_DB_NAME=pubsHS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
initnorthwind.ora内容如下:
HS_FDS_CONNECT_INFO="SERVER=SQLserver_hostname;DATABASE=Northwind"
HS_DB_NAME=Northwind
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
$ORACLE9I_HOME
etworkadmin 下listener.ora内容如下:
LISTENER =
  (DESCRIPTION_LIST =
   (DESCRIPTION =
    (ADDRESS_LIST =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.2)(PORT = 1521))
  )
  )
  )
SID_LIST_LISTENER =
  (SID_LIST =
  (SID_DESC =
  (GLOBAL_DBNAME = test9)
  (ORACLE_HOME = d:oracleora92)
  (SID_NAME = test9)
 )
  (SID_DESC=
  (SID_NAME=pubs)
  (ORACLE_HOME=d:OracleOra92)
   (PROGRAM=tg4mSQL)
  )
  (SID_DESC=
  (SID_NAME=northwind)
  (ORACLE_HOME=d:OracleOra92)
  (PROGRAM=tg4mSQL)
    )
  )

重启动这台做gateway的Windows机器上(IP:192.168.0.2)TNSListener服务(凡是按此步骤新增可访问的SQL Server数据库时,TNSListener服务都要重启动)。

2、Oracle8i,Oracle9i的服务器端配置tnsnames.ora, 添加下面的内容:

pubs =
  (DESCRIPTION =
  (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.2)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SID = pubs)
    )
    (HS = pubs)
   )

  northwind =
  (DESCRIPTION =
  (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.2)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SID = northwind)
    )
    (HS = northwind)   )
    保存tnsnames.ora后,在命令行下
 tnsping pubs
 tnsping northwind

出现类似提示,即为成功:

Attempting to contact (DESCRIPTION = (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.2)
(PORT = 1521))) (CONNECT_DATA = (SID = pubs)) (HS = pubs))
OK(20毫秒)
Attempting to contact (DESCRIPTION = (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.2)
(PORT = 1521))) 
(CONNECT_DATA = (SID = northwind)) (HS = northwind))OK(20毫秒)

设置数据库参数global_names=false。

设置global_names=false不要求建立的数据库链接和目的数据库的全局名称一致。global_names=true则要求, 多少有些不方便。

Mssql

SQL

SQL Server

数据库

SQL Server


百度大联盟认证黄金会员Copyright© 1997- CNET Networks 版权所有。 ZDNet 是CNET Networks公司注册服务商标。
中华人民共和国电信与信息服务业务经营许可证编号:京ICP证010391号 京ICP备09041801号-159
京公网安备:1101082134