oracle92 sqlserver2000 dblink报错,求救!(tnsping成功)

william3033 2009-05-22 12:20:00
参考网上的配置步骤安装、配置的。tnsping也测试正常。
创建语句也能执行,提示链接已创建。但测试查询时总是提示错误(ORA-28545: 连接代理时 Net8 诊断到错误
NCRO:无法执行 RSLV 连接 ORA-02063: 紧接着2 lines(源于HRMISDB))。

详细情况如下(oracle和sqlserver在不同服务器上):
oracle server ip:192.168.108.86
sqlserver server ip:192.168.123.20

inithrmisdb.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=192.168.123.20;DATABASE=hrmisdb"
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER


listener.ora
# LISTENER.ORA Network Configuration File: D:\oracle\ora92\network\admin\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hjserver)(PORT = 1521))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\ora92)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = dbcrm)
(ORACLE_HOME = D:\oracle\ora92)
(SID_NAME = dbcrm)
)
(SID_DESC =
(GLOBAL_DBNAME=tg4msql)
(PROGRAM=tg4msql)
(SID_NAME=hrmisdb)
(ORACLE_HOME = D:\oracle\ora92)
)
)



tnsnames.ora

# TNSNAMES.ORA Network Configuration File: D:\oracle\ora92\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

DBCRM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hjserver)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dbcrm)
)
)

INST1_HTTP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hjserver)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = MODOSE)
(PRESENTATION = http://HRService)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

hrmisdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.123.20)(PORT =1521))
)
(CONNECT_DATA =
(SID=hrmisdb)
)
(HS=OK)
)



C:\>tnsping hrmisdb

TNS Ping Utility for 32-bit Windows: Version 9.2.0.1.0 - Production on 22-5月 -2
009 10:56:20

Copyright (c) 1997 Oracle Corporation. All rights reserved.

已使用的参数文件:
D:\oracle\ora92\network\admin\sqlnet.ora


已使用 TNSNAMES 适配器来解析别名
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = 192.168.123.20)(PORT =1521))) (CONNECT_DATA = (SID=hrmisdb)) (HS=OK))
OK(10毫秒)


SQL> drop public database link mylink
2 /

数据库链接已丢弃。

SQL> create public database link hrmisdb connect to "sa" identified by "sa" usin
g 'hrmisdb'
2 /

数据库链接已创建。

SQL> select * from emp_basicinfo@hrmisdb
2 .
SQL> select * from emp_basicinfo@hrmisdb
2 /
select * from emp_basicinfo@hrmisdb
*
ERROR 位于第 1 行:
ORA-28545: 连接代理时 Net8 诊断到错误
NCRO:无法执行 RSLV 连接
ORA-02063: 紧接着2 lines(源于HRMISDB)

...全文
322 14 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
liuyi8903 2009-05-26
  • 打赏
  • 举报
回复
我指的trace不是在sqlplus中的跟踪级别的.

william3033 2009-05-25
  • 打赏
  • 举报
回复
现在本地连本地测试sql,隔大概1分钟响应提示报错。

SQL> describe emp_basicinfo@mylink
ERROR:
ORA-28500: 连接 ORACLE 到非 Oracle 系统时返回此信息:
[Transparent gateway for MSSQL]DRV_InitTdp: [Microsoft][ODBC SQL Server
Driver][Shared Memory]SQL Server 不存在或访问被拒绝 (SQL State: 08001; SQL
Code: 17)
ORA-02063: 紧接着2 lines(源于MYLINK)

分不够我另外再加。
william3033 2009-05-25
  • 打赏
  • 举报
回复

TKPROF: Release 9.2.0.1.0 - Production on 星期一 5月 25 10:24:56 2009

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Trace file: D:\Oracle\ora92\tg4msql\trace\hrmisdb_agt_6060.trc
Sort options: default

********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
Trace file: D:\Oracle\ora92\tg4msql\trace\hrmisdb_agt_6060.trc
Trace file compatibility: 9.00.01
Sort options: default

0 session in tracefile.
0 user SQL statements in trace file.
0 internal SQL statements in trace file.
0 SQL statements in trace file.
0 unique SQL statements in trace file.
40 lines in trace file.
william3033 2009-05-25
  • 打赏
  • 举报
回复
我参考trace设置的帖子设置了本session的。
现在的环境是本地的oracle连本地的sqlserver,创建链接后测试报错不一样了。


SQL> describe emp_basicinfo@mylink
ERROR:
ORA-28500: 连接 ORACLE 到非 Oracle 系统时返回此信息:
[Transparent gateway for MSSQL]DRV_InitTdp: [Microsoft][ODBC SQL Server
Driver][Shared Memory]SQL Server 不存在或访问被拒绝 (SQL State: 08001; SQL
Code: 17)
ORA-02063: 紧接着2 lines(源于MYLINK)
liuyi8903 2009-05-23
  • 打赏
  • 举报
回复
所以才让你trace啊,跟踪啊.
william3033 2009-05-23
  • 打赏
  • 举报
回复
[Quote=引用 8 楼 liuyi8903 的回复:]

这个很难说了,

最好是在client端,server 端设置trace

如在客户端的sqlnet.ora中加入

TRACE_LEVEL_SERVER=16
TRACE_LEVEL_CLIENT=16
TRACE_DIRECTORY_SERVER=[directory]
TRACE_DIRECTORY_CLIENT=[directory]


这样会在相关目录下(根据你的设置)产生一个*.trc文件,然后你再重现一下错误,再把*.trc帖一下吧,

记住trace完成之后请关闭,否则这个文件会增加很快的.
[/Quote]
我机器上面貌似就sample里面有sqlnet.ora啊,改那个吗?
我这个错误和端口有关系不?另外我的配置有问题吗?
william3033 2009-05-22
  • 打赏
  • 举报
回复
自己up一下,中午都休息了吗?没有高人来啊。
liuyi8903 2009-05-22
  • 打赏
  • 举报
回复

这个很难说了,

最好是在client端,server 端设置trace

如在客户端的sqlnet.ora中加入

TRACE_LEVEL_SERVER=16
TRACE_LEVEL_CLIENT=16
TRACE_DIRECTORY_SERVER=[directory]
TRACE_DIRECTORY_CLIENT=[directory]



这样会在相关目录下(根据你的设置)产生一个*.trc文件,然后你再重现一下错误,再把*.trc帖一下吧,

记住trace完成之后请关闭,否则这个文件会增加很快的.
william3033 2009-05-22
  • 打赏
  • 举报
回复
我换了做两个sqlserver数据库dblink测试,一个tnsnames里面的host用oracle的ip,另一个用sqlserver的ip,报不一样的错,但始终还是不行。
superhsj 2009-05-22
  • 打赏
  • 举报
回复
这个我也不是很懂,lz看下这个帖子
http://topic.csdn.net/u/20081013/17/bd89d576-1673-4c2f-b8a9-8afa04986acc.html
william3033 2009-05-22
  • 打赏
  • 举报
回复
引号没有关系,刚测试了,结果还是一样。
william3033 2009-05-22
  • 打赏
  • 举报
回复
去开会了,回来看答案,先谢过了!
william3033 2009-05-22
  • 打赏
  • 举报
回复
我的tnsnames里面的
hrmisdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.123.20)(PORT =1521))
)
(CONNECT_DATA =
(SID=hrmisdb)
)
(HS=OK)
)
没有问题吧?
这个是sqlserver数据库,host应该是sqlserver的主机地址吧?
刚才看一个教程上面host配置的是oracle的主机地址。。
superhsj 2009-05-22
  • 打赏
  • 举报
回复
create public database link hrmisdb connect to sa identified by sa using 'hrmisdb'
把引号去了试试

3,494

社区成员

发帖
与我相关
我的任务
社区描述
Oracle 高级技术相关讨论专区
社区管理员
  • 高级技术社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧