oracle 通过透明网关gateway 连接sqlserver

时间:2024-01-08 20:16:44

真实配置如下:

1.安装gateway 在一台单独的机器上,ip:172.30.40.29

2.配置listener.ora如下:

路径:  D:\product\11.2.0\tg_1\NETWORK\ADMIN\listener.ora

msqlnstanceName为sqlserver 的实例名称
172.30.40.129为安装gateway的地址,这里如果换成127.0.0.1 会造成通过dblink无法连接(报错:ORA-28545 ORA-02063)

内容:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME=dg4msql)
(ORACLE_HOME=D:\product\11.2.\tg_1)
(PROGRAM=dg4msql)
)
(SID_DESC =
(SID_NAME=msqlnstanceName) (ORACLE_HOME=D:\product\11.2.\tg_1)
(PROGRAM=dg4msql) ) )

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.30.40.129)(PORT = 1522))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
)
)
)

ADR_BASE_LISTENER = D:\product\11.2.\tg_1

3.配置initdg4msql.ora

路径:D:\product\11.2.0\tg_1\dg4msql\admin\initdg4msql.ora

IP地址172.30.10.10为sqlserver服务器地址

内容:

# 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=[172.30.10.10]//msqlnstanceName

HS_FDS_CONNECT_INFO=[172.30.10.10]//msqlnstanceName

HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

4. 在oracle端登陆sqlplus 创建dblink

CREATE PUBLIC DATABASE LINK mssqlnk CONNECT TO  "sa" identified by "sqlserverPassword" using
'(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=172.30.40.129)(PORT=1522))
(CONNECT_DATA=(SID=dg4msql))
(HS=OK)
)';

  

完成以上后就可以在oracle端通过dblink 正常访问sqlserver了。

select * from dual@mssqlnk;