最近需要用oracle连接sql server,于是用所谓的“透明网关”真是把我搞伤了。连续N多次,只有一次成功了,oracle真把我搞的没脾气了。
1、安装oracle 11g gateway(也就是所谓的透明网关),需要改变的文件是
E:\product\11.2.0\tg_1\dg4msql\admin下的initerpjk.ora文件,内容如下:
# 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=Kyle-PC//master
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER2、修改透明网关E:\product\11.2.0\tg_1\NETWORK\ADMIN下的listener.ora,内容如下:
# listener.ora Network Configuration File: E:\product\11.2.0\tg_1\network\admin\listener.ora
# Generated by Oracle configuration tools.LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Kyle-PC)(PORT = 1522))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
)
)SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = master) --对应上面红色的
(ORACLE_HOME = E:\product\11.2.0\tg_1\)
(PROGRAM = dg4msql)
)
)ADR_BASE_LISTENER = E:\product\11.2.0\tg_1
3、重启LISTENER在这里发现,即使第1步中的文件名与第2步中的实例名不一致,也可以启动。不知道怎么说了。。4、修改oracle 11g数据库(注意是数据库不是透明网关)的
E:\oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN下的tnsnames.ora,内容如下:
master =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST=Kyle-PC)(PORT=1521))
)
(CONNECT_DATA =
(SID = master)
)
(HS=OK)
)
5、登录oracle,创建db link,查询sql server表:
(1)、create database link master connect to sa identified by hx using 'master';
成功,没问题。
(2)、select * from spt_values@master;结果报错,如下:
求大神啊,试了N次,真的找不出哪儿错了。
1、安装oracle 11g gateway(也就是所谓的透明网关),需要改变的文件是
E:\product\11.2.0\tg_1\dg4msql\admin下的initerpjk.ora文件,内容如下:
# 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=Kyle-PC//master
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER2、修改透明网关E:\product\11.2.0\tg_1\NETWORK\ADMIN下的listener.ora,内容如下:
# listener.ora Network Configuration File: E:\product\11.2.0\tg_1\network\admin\listener.ora
# Generated by Oracle configuration tools.LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Kyle-PC)(PORT = 1522))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
)
)SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = master) --对应上面红色的
(ORACLE_HOME = E:\product\11.2.0\tg_1\)
(PROGRAM = dg4msql)
)
)ADR_BASE_LISTENER = E:\product\11.2.0\tg_1
3、重启LISTENER在这里发现,即使第1步中的文件名与第2步中的实例名不一致,也可以启动。不知道怎么说了。。4、修改oracle 11g数据库(注意是数据库不是透明网关)的
E:\oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN下的tnsnames.ora,内容如下:
master =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST=Kyle-PC)(PORT=1521))
)
(CONNECT_DATA =
(SID = master)
)
(HS=OK)
)
5、登录oracle,创建db link,查询sql server表:
(1)、create database link master connect to sa identified by hx using 'master';
成功,没问题。
(2)、select * from spt_values@master;结果报错,如下:
求大神啊,试了N次,真的找不出哪儿错了。
电脑文件没错了。
--建立ORACLE到SQL SERVER的分布式--
1、建立SQL SERVER数据源TEST_SQLSERVER
2、配置tnsnames.ora. (ORACLE_HOME\NETWORK\ADMIN)
TEST_SQLSERVER =
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=tcp)
(HOST=<IP地址>)
(PORT=1521)
)
(CONNECT_DATA=
(SID=TEST_SQLSERVER)
)
(HS=)
)
3、配置listener.ora. (ORACLE_HOME\NETWORK\ADMIN)
(SID_DESC=
(SID_NAME=TEST_SQLSERVER)
(ORACLE_HOME=D:\Oracle\iSuites)
(PROGRAM=hsodbc)
)
4、重启ORACLE监听.
CMD
LSNRCTL STOP
LSNRCTL START
LSNRCTL STATUS
5、添加网关配置文件. (ORACLE_HOME\HS\ADMIN)
文件名为:initTEST_SQLSERVER.ora
文件内容:
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = TEST_SQLSERVER
HS_FDS_TRACE_LEVEL = 0
#HS_FDS_TRACE_FILE_NAME = TEST_SQLSERVER.trc
#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>
6、修改DBMS初始文件
Global_names = false
7、进入ORACLE SQL*PLUS,建立数据库链.
CREATE DATABASE LINK TOSQL USING 'TEST_SQLSERVER';
透明网关没试过