真实配置如下:
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;