当前页面位置: » 丰搜网 » 文档中心 » 详细内容
在oracle里设置访问多个sql server
如何在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\tg4msql\admin下新写initpubs.ora和initnorthwind.ora配置文件.
initpubs.ora内容如下:
hs_fds_connect_info="server=sqlserver_hostnmae;database=pubs"
hs_db_name=pubs
hs_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.network\admin 下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:\oracle\ora92)
(sid_name = test9)
)
(sid_desc=
(sid_name=pubs)
(oracle_home=d:\oracle\ora92)
(program=tg4msql)
)
(sid_desc=
(sid_name=northwind)
(oracle_home=d:\oracle\ora92)
(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则要求, 多少有些不方便。
oracle9i和oracle8i都可以在dba用户下用sql命令改变global_names参数
alter system set global_names=false;
建立公有的数据库链接:
create public database link pubs
connect to testuser identified by testuser_pwd using 'pubs';
create public database link northwind
