17,377
社区成员
发帖
与我相关
我的任务
分享Orcale与其他数据库的连接
大虾们！
冰天雪地跪求：
如何实现从Orcale 10g 连接到Sql server 2005。
提供资料一样有分。
冰天雪地跪求：
如何实现从Orcale 10g 连接到Sql server 2005。
提供资料一样有分。
...全文
请发表友善的回复…
发表回复
dgl058098 2010-01-29
- 打赏
- 举报
前几天问ORACLE 的工程师了。以下为记录，发出来大家共享一下。。
Oracle 技术支持 - 2010年1月27日 下午08时32分24秒
Email Subject
-------------
SR #3-1363334372: When trying to connect with sql server 2005, ora-28545 occurred
Email Body
----------
Hello hui,
Please note that your Service Request Number 3-1363334372 has been updated. To view the update, please access My Oracle Support at https://support.oracle.com/ and choose the Service Requests tab.
Regards,
Marcello Giliberti Calderon, Oracle Customer Support
Note: Please do not reply to this email. This mailbox does not allow incoming messages.
Oracle 技术支持 - 2010年1月27日 下午08时32分03秒
Comments
--------
=== ODM Action Plan ===
Hello,
at first I would like to remind you that tg4msql is no longer supported by oracle,
so I strongly encourage you to install dg4msql 11.x, this is the currently supportd
version and needs to be accessed by an oracle rdbms 10.2.0.4 or above.
I see you are using tg4msql 10.2.0.1 (base release), please apply 10.2.0.4
patchset to gateway home.
The patchset you will need to download is the rdbms 10.2.0.4 for windows, and
at the time of install you need to point the installer to the gateway oracle_home.
This is an essential step since tg4msql v 10.2.0.1 was released back in june2005
and most likely sqlserver 2005 was not out. So in order to use it at full features
and with all the service level to access sqlserver 2005 you will need to install
10.2.0.4 patchset to it.
The error you report may be due to a malformed configuration.
Did your sqlserver 2000 and 2005 reside on the same box ?
Did your sqlserver 2005 run on a box where several sqserver instances
are running ?
If one of the above assumptions are true please check metalink note
Note.231997.1 TG4MSQL and MS SQL Server Instances
Let me know the outcome.
Please also, enable tracing by adding the following line to
your gateway init file
HS_FDS_TRACE_LEVEL=DEBUG
run a query from sqlplus and upload gateway trace file
(should be in gateway's oracle_home/tg4msq/trace or log directory)
Uplaod also sqlserver 2000 gateway init file for reference.
Thanks
Regards,
Marcello Giliberti Calderon
Oracle Support Services
HEAVENHUA@HOTMAIL.COM - 2010年1月27日 下午03时05分39秒
Step1. path= %ORACLE_HOME%\tg4msql\admin
Step2&Step3 path= %ORACLE_HOME%\network\admin
Oracle 技术支持 - 2010年1月27日 下午02时31分33秒
Comments
--------
=== ODM Issue Verification ===
Justified the issue by the following from data collected section:
select * from test@msql
oracle-28545:连接代理时 Net8 诊断到错误
Unable to retrieve text to NETWORK/NCR message 65535
ORA-02063：紧接着2 lines(源于MSQL)
Oracle 技术支持 - 2010年1月27日 下午02时30分49秒
Comments
--------
=== ODM Issue Clarification ===
On : 10.2.0.1 version, Transparent Gateway for MS SQL
When attempting to access sql server with transparent gateway,
the following error occurs.
ERROR
-----------------------
oracle-28545:连接代理时 Net8 诊断到错误
Unable to retrieve text to NETWORK/NCR message 65535
ORA-02063：紧接着2 lines(源于MSQL)
STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. select * from test@msql
BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot access data in sql server 2005
Oracle 技术支持 - 2010年1月27日 下午02时18分47秒
Name
----
=== ODM Data Collection ===
Comments
--------
FILE VERSION
--------------------
1. inithztest.ora under %ORACLE_HOME%\tg4msql\admin
HS_FDS_CONNECT_INFO="SERVER=172.30.59.178;DATABASE=test"
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
2. listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = d:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME =hztest)
(SID_NAME = hztest)
(ORACLE_HOME = d:\oracle\product\10.2.0\db_1)
(PROGRAM = tg4msql)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.30.59.102)(PORT = 1521))
)
)
3. tnsnames.ora
MSQL=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =172.30.59.178)(PORT = 1521))
)
(CONNECT_DATA =
(SID = hztest)
)
(HS = OK)
)
SCRIPT OUTPUT
------------------------
select * from test@msql
oracle-28545:连接代理时 Net8 诊断到错误
Unable to retrieve text to NETWORK/NCR message 65535
ORA-02063：紧接着2 lines(源于MSQL)
FileName
--------
FileComment
-----------
HEAVENHUA@HOTMAIL.COM - 2010年1月27日 下午02时05分42秒
我现在的配置是这样的
在D:\oracle\product\10.2.0\db_1\tg4msql\admin 目录下建立了一个inithztest.ora文件
里面的配置是
# This is a sample agent init file that contains the HS parameters that are
# needed for the Transparent Gateway for SQL Server
#
# HS init parameters
#
HS_FDS_CONNECT_INFO="SERVER=172.30.59.178;DATABASE=test"
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
然后在listener.ora 文件中修改如下：
# listener.ora Network Configuration File: d:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = d:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME =hztest)
(SID_NAME = hztest)
(ORACLE_HOME = d:\oracle\product\10.2.0\db_1)
(PROGRAM = tg4msql)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.30.59.102)(PORT = 1521))
)
)
完了以后修改tnsnames.ora文件
添加了一个
MSQL=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =172.30.59.178)(PORT = 1521))
)
(CONNECT_DATA =
(SID = hztest)
)
(HS = OK)
)
最后我用了
select *
from test@msql
就报错了
oracle-28545:连接代理时 Net8 诊断到错误
Unable to retrieve text to NETWORK/NCR message 65535
ORA-02063：紧接着2 lines(源于MSQL)
Oracle 技术支持 - 2010年1月27日 下午08时32分24秒
Email Subject
-------------
SR #3-1363334372: When trying to connect with sql server 2005, ora-28545 occurred
Email Body
----------
Hello hui,
Please note that your Service Request Number 3-1363334372 has been updated. To view the update, please access My Oracle Support at https://support.oracle.com/ and choose the Service Requests tab.
Regards,
Marcello Giliberti Calderon, Oracle Customer Support
Note: Please do not reply to this email. This mailbox does not allow incoming messages.
Oracle 技术支持 - 2010年1月27日 下午08时32分03秒
Comments
--------
=== ODM Action Plan ===
Hello,
at first I would like to remind you that tg4msql is no longer supported by oracle,
so I strongly encourage you to install dg4msql 11.x, this is the currently supportd
version and needs to be accessed by an oracle rdbms 10.2.0.4 or above.
I see you are using tg4msql 10.2.0.1 (base release), please apply 10.2.0.4
patchset to gateway home.
The patchset you will need to download is the rdbms 10.2.0.4 for windows, and
at the time of install you need to point the installer to the gateway oracle_home.
This is an essential step since tg4msql v 10.2.0.1 was released back in june2005
and most likely sqlserver 2005 was not out. So in order to use it at full features
and with all the service level to access sqlserver 2005 you will need to install
10.2.0.4 patchset to it.
The error you report may be due to a malformed configuration.
Did your sqlserver 2000 and 2005 reside on the same box ?
Did your sqlserver 2005 run on a box where several sqserver instances
are running ?
If one of the above assumptions are true please check metalink note
Note.231997.1 TG4MSQL and MS SQL Server Instances
Let me know the outcome.
Please also, enable tracing by adding the following line to
your gateway init file
HS_FDS_TRACE_LEVEL=DEBUG
run a query from sqlplus and upload gateway trace file
(should be in gateway's oracle_home/tg4msq/trace or log directory)
Uplaod also sqlserver 2000 gateway init file for reference.
Thanks
Regards,
Marcello Giliberti Calderon
Oracle Support Services
HEAVENHUA@HOTMAIL.COM - 2010年1月27日 下午03时05分39秒
Step1. path= %ORACLE_HOME%\tg4msql\admin
Step2&Step3 path= %ORACLE_HOME%\network\admin
Oracle 技术支持 - 2010年1月27日 下午02时31分33秒
Comments
--------
=== ODM Issue Verification ===
Justified the issue by the following from data collected section:
select * from test@msql
oracle-28545:连接代理时 Net8 诊断到错误
Unable to retrieve text to NETWORK/NCR message 65535
ORA-02063：紧接着2 lines(源于MSQL)
Oracle 技术支持 - 2010年1月27日 下午02时30分49秒
Comments
--------
=== ODM Issue Clarification ===
On : 10.2.0.1 version, Transparent Gateway for MS SQL
When attempting to access sql server with transparent gateway,
the following error occurs.
ERROR
-----------------------
oracle-28545:连接代理时 Net8 诊断到错误
Unable to retrieve text to NETWORK/NCR message 65535
ORA-02063：紧接着2 lines(源于MSQL)
STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. select * from test@msql
BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot access data in sql server 2005
Oracle 技术支持 - 2010年1月27日 下午02时18分47秒
Name
----
=== ODM Data Collection ===
Comments
--------
FILE VERSION
--------------------
1. inithztest.ora under %ORACLE_HOME%\tg4msql\admin
HS_FDS_CONNECT_INFO="SERVER=172.30.59.178;DATABASE=test"
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
2. listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = d:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME =hztest)
(SID_NAME = hztest)
(ORACLE_HOME = d:\oracle\product\10.2.0\db_1)
(PROGRAM = tg4msql)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.30.59.102)(PORT = 1521))
)
)
3. tnsnames.ora
MSQL=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =172.30.59.178)(PORT = 1521))
)
(CONNECT_DATA =
(SID = hztest)
)
(HS = OK)
)
SCRIPT OUTPUT
------------------------
select * from test@msql
oracle-28545:连接代理时 Net8 诊断到错误
Unable to retrieve text to NETWORK/NCR message 65535
ORA-02063：紧接着2 lines(源于MSQL)
FileName
--------
FileComment
-----------
HEAVENHUA@HOTMAIL.COM - 2010年1月27日 下午02时05分42秒
我现在的配置是这样的
在D:\oracle\product\10.2.0\db_1\tg4msql\admin 目录下建立了一个inithztest.ora文件
里面的配置是
# This is a sample agent init file that contains the HS parameters that are
# needed for the Transparent Gateway for SQL Server
#
# HS init parameters
#
HS_FDS_CONNECT_INFO="SERVER=172.30.59.178;DATABASE=test"
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
然后在listener.ora 文件中修改如下：
# listener.ora Network Configuration File: d:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = d:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME =hztest)
(SID_NAME = hztest)
(ORACLE_HOME = d:\oracle\product\10.2.0\db_1)
(PROGRAM = tg4msql)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.30.59.102)(PORT = 1521))
)
)
完了以后修改tnsnames.ora文件
添加了一个
MSQL=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =172.30.59.178)(PORT = 1521))
)
(CONNECT_DATA =
(SID = hztest)
)
(HS = OK)
)
最后我用了
select *
from test@msql
就报错了
oracle-28545:连接代理时 Net8 诊断到错误
Unable to retrieve text to NETWORK/NCR message 65535
ORA-02063：紧接着2 lines(源于MSQL)
myl525 2010-01-26
- 打赏
- 举报
本人有相关资料：oracle10G+SqlServer 透明网关的配置文档
自己写的，有需要的话可以加我qq 传你一份
442128134
偶在上学吧上发布的是要收费的，这里免费提供
自己写的，有需要的话可以加我qq 传你一份
442128134
偶在上学吧上发布的是要收费的，这里免费提供
suiziguo 2010-01-25
- 打赏
- 举报
汗，研究了半天的ORACLE安装程序，没找到。
做的时候还是9i。网上看了下，在9i中，透明网关是包含在数据库安装程序中的，在安装数据库软件过程中可以选择；10g中，oracle把透明网关独立出来，需要单独下载安装。
你去ORACLE官网上下载安装吧。
做的时候还是9i。网上看了下，在9i中，透明网关是包含在数据库安装程序中的，在安装数据库软件过程中可以选择；10g中，oracle把透明网关独立出来，需要单独下载安装。
你去ORACLE官网上下载安装吧。
tangren 2010-01-25
- 打赏
- 举报
10g下，透明网管是一个单独组件，可以到oracle官方网站下载
http://www.oracle.com/technology/software/products/database/oracle10g/htdocs/10201winsoft.html
这个安装包有300m。
http://www.oracle.com/technology/software/products/database/oracle10g/htdocs/10201winsoft.html
这个安装包有300m。
dgl058098 2010-01-25
- 打赏
- 举报
那个组件叫啥名啊？
谢谢
谢谢
ngx20080110 2010-01-25
- 打赏
- 举报
关注
suiziguo 2010-01-25
- 打赏
- 举报
http://www.west263.com/www/info/66264-1.htm
oracle 10g + sql server 2000 透明网关配置。
好久没玩透明网关了，不过以前做过，记得步骤还是很简单的。
oracle 10g + sql server 2000 透明网关配置。
好久没玩透明网关了，不过以前做过，记得步骤还是很简单的。
oraclemch 2010-01-25
- 打赏
- 举报
[Quote=引用楼主 dgl058098 的回复:]
大虾们！
冰天雪地跪求：
如何实现从Orcale 10g 连接到Sql server 2005。
提供资料一样有分。
[/Quote]
用dblink吧，试试如何？
大虾们！
冰天雪地跪求：
如何实现从Orcale 10g 连接到Sql server 2005。
提供资料一样有分。
[/Quote]
用dblink吧，试试如何？
dgl058098 2010-01-25
- 打赏
- 举报
不好意思，
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = test)
(ORACLE_HOME = D:\oracle\product\10.1.0\db_1)
(PROGRAM = tg4msql)
)
)
应该是
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = test)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(PROGRAM = tg4msql)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = test)
(ORACLE_HOME = D:\oracle\product\10.1.0\db_1)
(PROGRAM = tg4msql)
)
)
应该是
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = test)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(PROGRAM = tg4msql)
)
)
dgl058098 2010-01-25
- 打赏
- 举报
感谢suiziguo和tangren 的回复。
现在我按照资料的说明。
安装了透明网关程序。
我的sql server 2005 安装在IP为：192.168.1.100 机器上，而orcale 10g安装在 192.168.1.101的机器上。
我在 sql server 2005 机器中建立了test 的数据库。
在orcale端设置如下：
1.建立inittest.ora
内容如下：
HS_FDS_CONNECT_INFO="SERVER=192.168.1.100;DATABASE=test"
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
2.添加listiner.ora 文件
# listener.ora Network Configuration File: d:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = d:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = test)
(ORACLE_HOME = D:\oracle\product\10.1.0\db_1)
(PROGRAM = tg4msql)
)
)
3.修改tnsnames.ora 文件
内容如下：
MSQL=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521))
)
(CONNECT_DATA =
(SID = test)
)
(HS = test)
)
全都做完以后，我打开了PLSQL,建了一个DB-LINK。结果不可以。
请问中间有什么设置错了吗？
谢谢！
现在我按照资料的说明。
安装了透明网关程序。
我的sql server 2005 安装在IP为：192.168.1.100 机器上，而orcale 10g安装在 192.168.1.101的机器上。
我在 sql server 2005 机器中建立了test 的数据库。
在orcale端设置如下：
1.建立inittest.ora
内容如下：
HS_FDS_CONNECT_INFO="SERVER=192.168.1.100;DATABASE=test"
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
2.添加listiner.ora 文件
# listener.ora Network Configuration File: d:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = d:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = test)
(ORACLE_HOME = D:\oracle\product\10.1.0\db_1)
(PROGRAM = tg4msql)
)
)
3.修改tnsnames.ora 文件
内容如下：
MSQL=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521))
)
(CONNECT_DATA =
(SID = test)
)
(HS = test)
)
全都做完以后，我打开了PLSQL,建了一个DB-LINK。结果不可以。
请问中间有什么设置错了吗？
谢谢！
sorry0481 2010-01-25
- 打赏
- 举报
学习
