Oracle10g下配置Transparent Gateway for MS SQLServer

教程发布:风哥 教程分类:ITPUX技术网 更新日期:2022-02-12 浏览学习:24

1、 安装Transparent Gateway组件
Oracle10g本身已经集成了Transparent Gateway产品,但是缺省安装并没有安装这个组件,首先必须安装TRANSPARENT GATEWAY 组件(我安装的是FOR MSSQL)。安装后会产生这样一个目录:$ORACLE_HOME\tg4msql,即D:\oracle\product\10.1.0\Db_1\tg4msql

2、配置服务器上的Listener.ora文件
在安装有TransparentGateway组件的服务器上配置 LISTENER.ORA
(SID_DESC =
(GLOBAL_DBNAME =tg4sql) # 可自己命名,此项可以去掉
(PROGRAM = tg4msql)
(SID_NAME = sql7) #SID 自己命名
(ORACLE_HOME = D:\oracle\product\10.1.0\Db_1)
)

完整的Listener.ora文件如下:
# Begin
#listener.ora
#Network ConfigurationFile: #d:\oracle\product\10.1.0\Db_1\network\admin\listener.ora
# Generated byOracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = d:\oracle\product\10.1.0\Db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = TGSQL7)
(ORACLE_HOME = d:\oracle\product\10.1.0\Db_1)
(SID_NAME = SQL7)
(PROGRAM = TG4MSQL)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY =EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =10.1.10.121)(PORT = 1521))
)
)
)
# End
注:因为在此台服务器上同时还安装了Oracle10g数据库,因此在1521端口只需要保证有一个侦听即可,不必为Oracle配置一个侦听,再为SQL Server配置一个侦听。(其实客户端本来就是连接到Oracle10g的数据库上,然后通过Oracle10g的HS服务来访问MS SQLServer,所以在服务器上只配置一个1521端口的侦听是正确的。)

3、创建透明网关配置文件
在 $ORACLE_HOME\tg4msql\admin中有一个模版文件inittg4msql.ora。将此文件拷贝为 init.ora, 由LISTENER.ORA中定义的SID_NAME确定。由于我们在Listener.ora中将SID定义为sql7,因此透明网关配置文件名应为:initsql7.ora
其中内容为:
HS_FDS_CONNECT_INFO=zouyu.pubs# zouyu为SQL服务器的主机名
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
注:HS_FDS_CONNECT_INFO=”SERVER=10.2.5.224;DATABASE=pubs”也可。

4、 重新启动 DATABASE 和 LISTENER

5、配置Oracle10g服务器上的tnsnames.ora
tg_sql =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL= TCP)(HOST = orasvr)(PORT = 1521))
)
(CONNECT_DATA =
(SID = sql2000)
)
(HS=OK)
)
完整的tnsnames.ora文件如下:
# Begin
# tnsnames.ora
#Network ConfigurationFile: d:\oracle\product\10.1.0\Db_1\network\admin\tnsnames.ora
# Generated byOracle configuration tools.

KEOGC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =[color=#00ffff][backcolor=rgb(128, 0, 128)] 10.1.10.121)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = keogc)
)
)

EXTPROC_CONNECTION_DATA=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY =EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

TG_SQL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.10.121)(PORT = 1521))
(CONNECT_DATA =
(SID = SQL7)
)
(HS = OK)
)
# End

6、建立 DATABASE LINK
CREATE PUBLICDATABASE LINK LK_SQL7 CONNECT TO SA IDENTIFIED BY PASSWORD
USING 'tg_sql';

7、完成,测试
select * from authors@lk_sql7
Select * fromemployee@lk_sql7 where “lname” like ‘P%’;
注:字段必须用双引号括起来。

本文标签:
网站声明:本文由风哥整理发布,转载请保留此段声明,本站所有内容将不对其使用后果做任何承诺,请读者谨慎使用!
【上一篇】
【下一篇】