Oracle10g透明网关连SQL Server问题，请大虾指点！

显示全部楼层 · 2008-2-13 12:43:03

我用Oracle10g透明网关连SQL Server，Oracel、网关、SQL Server都安装在同一台机子上，配置各参数后，tnsping能通，数据库连接也能正确创建，但访问时出错，不知何故，请高手赐教。
测试情况如下：
1、各参数配置如下：
tnsname.ora配置：
# tnsnames.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.
ORACLE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = gaoxq)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oracle)
)
)
TG4MSQL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = gaoxq)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = tg4msql)
)
(HS = OK)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
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 =
(SID_NAME = tg4msql)
(ORACLE_HOME = D:\oracle\product\10.2.0\tg_1\tg4msql)
(PROGRAM = tg4msql)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = gaoxq)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = gaoxq)(PORT = 1522))
)
)
inittg4msql.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=GAOXQ.Northwind
HS_FDS_TRACE_LEVEL=ON
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
其中trace设置为on，但在trace目录下没看到任何日志文件。在网上看到说要将trace打开，但如果说上述配置不起作用的话，请问如何打开trace？
2、tnsping测试：
C:\Documents and Settings\gxq.CLEARCASE>tnsping tg4msql
TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 22-10月-
2005 15:45:49
Copyright (c) 1997, 2005, Oracle.All rights reserved.
已使用的参数文件:
D:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora

已使用 TNSNAMES 适配器来解析别名
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = gaoxq)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = tg4msql)) (HS = OK
))
OK (30 毫秒)
3、数据库连接创建成功（各服务均已重启）
4、取数据时报错：
SQL> select "CompanyName" from "customers"@tg4msql;
select "CompanyName" from "customers"@tg4msql

*
第 1 行出现错误:
ORA-28545: 连接代理时 Net8 诊断到错误
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: 紧接着 2 lines (起自 TG4MSQL)
请大虾们指点，我已经折腾好几天了，查了oracle资料和网上文章，觉得配置没有错，就是不知问题在什么地方。

千问 · 2008-2-13 12:43:03

究竟是哪儿错了呢？

千问 · 2008-2-13 12:43:03

没有人应声，自己顶一下！急啊。在线等！

千问 · 2008-2-13 12:43:03

透明网关没有用过，帮你顶一下，不过记得好像论坛里面有相关的帖子，找找看吧

千问 · 2008-2-13 12:43:03

ORA-28545: 连接代理时 Net8 诊断到错误
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: 紧接着 2 lines (起自 TG4MSQL)
我也遇到這個問題了。METALINK上也沒有答案。看來得深入研究一下了。

千问 · 2008-2-13 12:43:03

1.if both dbs are on windows,it's a easier way to use sql server staff (called link server or something )
2.in your tnsnames
this part:
TG4MSQL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = gaoxq)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = tg4msql)
)
(HS = OK)
)
you should change port number to 1522 according to your listener setup
and startup listener seperately
I suggest you read the docs

千问 · 2008-2-13 12:43:03

metalink上摘录的：
ORA-28545:

SQL> select * from all_catalog@demo;

select * from all_catalog@demo

*

ERROR at line 1:

ORA-28545: error diagnosed by Net8 when connecting to an agent

NCRO: Failed to make RSLV connection

ORA-02063: preceding 2 lines from DEMO
Resolution:
There's something wrong with the SQL*Net connection:

- checking the SQL*Net -> Listener configuration with TNSPING:

tnsping

TNS-12545: Connect failed because target host or object

does not exist

The HOSTNAME specified in the TNSNAMES.ORA is invalid

Try with the OS ping to resolve the hostname / IP adddress

TNS-12541: TNS:no listener

The hostname specified in the listener.ora points to a

machine without an Oracle listener, the listener on that

machine is not running or the port number is wrong.

NO ERROR with TNSPING:

This might be caused by an invalid SID configuration.

Please make sure, that the SID in the listener refelects the

SID specified in the TNSNAMES.ORA. Make sure you don't mix

SID syntax with SERVICE_NAME syntax.

If everything is configured well, please make sure that

lsnrctl status shows at least one service handler for

hsodbc. Probably restart the listener AFTER changing

the listener.ora file.

Still no error found, then please enable listener tracing

(level support) and have a look at the listener.trace file

after retrying the same select statement again.

In the file you should see the connect from the Oracle

database to the listener. After a few lines you should see that

the listener tries to open 2 pipes. Does this work or does it

fail and again a few lines later you see a hex/ascii block that

conatins an error stack like:

Example 1:

ERR=12500, CODE=12500, EMFI=4, CODE=12560, EMFI=4?

Solution 1:

Then please check again the listener.ora file; is the

ORACLE_HOME directory specified within the listener.ora file

correct? Or if you are using environment variables in the

listener.ora please replace them with absolute paths.

Example 2:

EMFI=4, CODE=12547, EMFI=4, CODE=12560, EMFI=4,

CODE=517, EMFI=4, BUF='Solaris.Error: 32: Broken pipe'

or similar

TNS-12500: TNS:listener failed to start a dedicated server process

TNS-12547: TNS:lost contact

TNS-12560: TNS

rotoadapter error

TNS-00517: Lost contact

Solaris Error: 32: Broken pipe

Solution 2:

check out the executable of the gateway; -> PROGRAM=

it must be loacated in the $ORACLE_HOME/bin (where $ORACLE_HOME is the

Oracle_Home of the OTG and also correctly specified in the listener.ora file).

Calling it from the DOS command line / Unix shell should not report any

error like file not found...

Make sure the LD_LIBRARY_PATH, LIBPATH, SHLIB_PATH contains the 32bit libraries

location as well.

千问 · 2008-2-13 12:43:03

搞定了。
是LISTENER.ORA 和 TNSNAMES.ORA 的 SID_NAME 的值不匹配引起的。
但是錯誤的信息就是怪。一改就好了。
METALINK 上的人還建議開SR. 看來省了。

千问 · 2008-2-13 12:43:03

好東西，學習

千问 · 2008-2-13 12:43:03

学习