2群基础群 适合刚入门的同学，会共享最佳入门实践和资料 QQ群 # QQ群号:171092051 # 已经升级到 500人的超级群，空位多多，无需面试
ora-00600:[17281], [1001]一例
检查告警日志发现出现ora-600:[17281],[1001]记录，该数据库版本为10.2.0.4:
ORA-00600: internal error code, arguments: [17281], [1001], [0x70000042F5E54F8], [], [], [], [], []
ORA-01001: invalid cursor
分析该600错误产生的trace文件，发现当时运行的语句是一段匿名块:
Current SQL statement for this session: declare t_owner varchar2(30); t_name varchar2(30); procedure check_mview is dummy integer; begin if :object_type = 'TABLE' then select 1 into dummy from sys.all_objects where owner = :object_owner and object_name = :object_name and object_type = 'MATERIALIZED VIEW' and rownum = 1; :object_type := 'MATERIALIZED VIEW'; end if; exception when others then null; end; begin :sub_object := null; if :deep != 0 then begin if :part2 is null then select constraint_type, owner, constraint_name into :object_type, :object_owner, :object_name from sys.all_constraints c where c.constraint_name = :part1 and c.owner = user and rownum = 1; else select constraint_type, owner, constraint_name, :part3 into :object_type, :object_owner, :object_name, :sub_object from sys.all_constraints c where c.constraint_name = :part2 and c.owner = :part1 and rownum = 1; end if; if :object_type = 'P' then :object_type := 'PRIMARY KEY'; end if; if :object_type = 'U' then :object_type := 'UNIQUE KEY'; end if; if :object_type = 'R' then :object_type := 'FOREIGN KEY'; end if; if :object_type = 'C' then :object_type := 'CHECK CONSTRAINT'; end if; return; exception when no_data_found then null; end; end if; :sub_object := :part2; if (:part2 is null) or (:part1 != user) then begin select object_type, user, :part1 into :object_type, :object_owner, :object_name from sys.all_objects where owner = user and object_name = :part1 and object_type in ('MATERIALIZED VIEW', 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TYPE', 'TRIGGER', 'SYNONYM') and rownum = 1; if :object_type = 'SYNONYM' then select s.table_owner, s.table_name into t_owner, t_name from sys.all_synonyms s where s.synonym_name = :part1 and s.owner = user and rownum = 1; select o.object_type, o.owner, o.object_name into :object_type, :object_owner, :object_name from sys.all_objects o where o.owner = t_owner and o.object_name = t_name and object_type in ('MATERIALIZED VIEW', 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TYPE', 'TRIGGER', 'SYNONYM') and rownum = 1; end if; :sub_object := :part2; if :part3 is not null then :sub_object := :sub_object || '.' || :part3; end if; check_mview; return; exception when no_data_found then null; end; end if; begin select s.table_owner, s.table_name into t_owner, t_name from sys.all_synonyms s where s.synonym_name = :part1 and s.owner = 'PUBLIC' and rownum = 1; select o.object_type, o.owner, o.object_name into :object_type, :object_owner, :object_name from sys.all_objects o where o.owner = t_owner and o.object_name = t_name and object_type in ('MATERIALIZED VIEW', 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TYPE', 'TRIGGER', 'SYNONYM') and rownum = 1; check_mview; return; exception when no_data_found then null; end; :sub_object := :part3; begin select o.object_type, o.owner, o.object_name into :object_type, :object_owner, :object_name from sys.all_objects o where o.owner = :part1 and o.object_name = :part2 and object_type in ('MATERIALIZED VIEW', 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TYPE', 'TRIGGER', 'SYNONYM') and rownum = 1; check_mview; return; exception when no_data_found then null; end; begin if :part2 is null and :part3 is null then select 'USER', null, :part1 into :object_type, :object_owner, :object_name from sys.all_users u where u.username = :part1 and rownum = 1; return; end if; exception when no_data_found then null; end; begin if :part2 is null and :part3 is null and :deep != 0 then select 'ROLE', null, :part1 into :object_type, :object_owner, :object_name from sys.session_roles r where r.role = :part1 and rownum = 1; return; end if; exception when no_data_found then null; end; :object_owner := null; :object_type := null; :object_name := null; :sub_object := null; end; ----- Call Stack Trace ----- calling call entry argument values in hex location type point (? means dubious value) -------------------- -------- -------------------- ---------------------------- ksedst+001c bl ksedst1 088424844 ? 041124844 ? ksedmp+0290 bl ksedst 104A54870 ? ksfdmp+0018 bl 03F30204 kgeriv+0108 bl _ptrgl kgeasi+0118 bl kgeriv 1104722C8 ? 1101B87C0 ? 104AFA0FC ? 7000000100067F8 ? 000000000 ? kgicli+0188 bl kgeasi 110195A58 ? 110447310 ? 438100004381 ? 200000002 ? 200000002 ? 000000000 ? 0000003E9 ? 000000002 ? kgidlt+0398 bl kgicli 110450A70 ? 104C734E0 ? kgidel+0018 bl kgidlt FFFFFFFFFFF90B8 ? 000000000 ? 000000003 ? 000000000 ? FFFFFFFFFFF9468 ? perabo+00ac bl kgidel FFFFFFFFFFF8D20 ? 0000000FF ? perdcs+0050 bl perabo 000000000 ? 000000820 ? 000000000 ? peidcs+01dc bl perdcs 110477618 ? 000000000 ? kkxcls+00a4 bl peidcs FFFFFFFFFFF9468 ? 110477618 ? kxsClean+0044 bl kkxcls 1100DD338 ? kxsCloseXsc+0444 bl kxsClean FFFFFFFFFFF9760 ? kksCloseCursor+031c bl kxsCloseXsc 110478688 ? 110281FB0 ? opicca+00c4 bl kksCloseCursor 104BD9640 ? opiclo+00a0 bl opicca 10013D940 ? kpoclsa+0050 bl 03F32B00 opiodr+0ae0 bl _ptrgl ttcpip+1020 bl _ptrgl opitsk+1124 bl 01F9F2A0 opiino+0990 bl opitsk 000000000 ? 000000000 ? opiodr+0ae0 bl _ptrgl opidrv+0484 bl 01F9E0E8 sou2o+0090 bl opidrv 3C02DC1BBC ? 44065F000 ? FFFFFFFFFFFF3A0 ? opimai_real+01bc bl 01F9B9F4 main+0098 bl opimai_real 000000000 ? 000000000 ? __start+0098 bl main 000000000 ? 000000000 ?first argument为17281,该代码对应为在关闭游标时发生错误事件。 发生错误时的调用栈为:kkxcls->peidcs->perdcs->perabo->kgidel->kgidlt->kgicli,通过以上调用栈与argument信息在600 lookup工具中查询，可以发现bug:[6051353]:
Hdr: 6051353 10.1.0.45 THIN 10.1.0.5 PRODID-972 PORTID-212 ORA-600
Abstract: ORA-600[17281] ORA-[1001]
*** 05/14/07 07:09 am ***
TAR:
----
17450130.600
PROBLEM:
--------
Oracle 10.1.0.5 64-bit
AIX5L 64-bit server
Following the application of CPUJAN2007 patch, the database is giving
internal errors.
Tha alert log sjows:
ORA-600: internal error code, arguments: [17281], [1001],
[0x70000001E792DC8], [], [], [], [], []
ORA-1001: invalid cursor
Trace file shows the failing statement is an insert.
INSERT INTO V_RPMORGRESOURCEPOSITION
(ORGID, RESOURCEID, EFFECTIVEDAY, TERMINATIONDAY, OWNEDMW,
COMMITTEDMW, AVAILABLEMW, UNOFFEREDMW, FRRCOMMITTEDMW )
SELECT :B12 , :B11 , EFFECTIVEDAY, LEAST(:B10 , :B9 ),
NVL(OWNEDMW,0) + NVL(:B8 ,0), NVL(COMMITTEDMW,0) + NVL(:B7 ,0),
NVL(AVAILABLEMW,0) + NVL(:B6 ,0), NVL(UNOFFEREDMW,0) + NVL(:B5 ,0),
NVL(FRRCOMMITTEDMW,0) + NVL(:B4 ,0)
FROM RPMORGRESOURCEPOSITION
WHERE ORGID = :B3 AND RESOURCEID = :B2 AND EFFECTIVEDAY = :B1
Other information:
O/S info: user: , term: , ospid: 1234, machine: esu03als
client info: smartino@PJM
application name: eRpm
action name: QueryZonalLoadObligationDetail
last wait for 'SQL*Net message from client'
DIAGNOSTIC ANALYSIS:
--------------------
the function stack exactly matches 4359111
kgicli kgidlt kgidel perabo
Bug 4359111 - STRESS ORA-600[17281] WHEN RUNNING GMT APPLICATION13
But this is already fixed in 10.1.0.5
The 'client' (esu03als) is a Linux server and there is no oracle running
there.
The AIX SysAdmin. who manages the esu03als server says this about the
application:
"The way RPM connects to our database is by using DBCP (Apache Jakarta
Project -- see
As far as I can tell, the release (jar) contained within the RPM
delivery is version 1.1 of DBCP which was released on 2003-10-20."
This couls also be Bug 5392685/5413487 but tis doesn't seem to have a
resolution.
Also could be Bug 5366763
possible workaround - set session_cached_cursors to 0
But this is already set to 0.
Originally the Customer thought this error started after applying CPUJAN2007,
but it is appearing on another database where the CPU patch is not applied.
WORKAROUND:
-----------
none
RELATED BUGS:
-------------
4359111
5413487
5366763
REPRODUCIBILITY:
----------------
intermittent
TEST CASE:
----------
none
STACK TRACE:
------------
ksedmp ksfdmp kgeriv kgeasi
kgicli kgidlt kgidel perabo perdcs peidcs kkxcls2 kxtcln
kxsClean kksCloseCursor opicca opiclo kpoclsa opiodr
ttcpip opitsk opiino opiodr opidrv sou2o
main
其调用栈完全一致，可以基本确定2者的关联性。但该文档叙述bug发生在10.1.0.5的AIX版本上，且据称该Bug之前已在“Bug 4359111 - STRESS ORA-600[17281] WHEN RUNNING GMT APPLICATION13”中声明并在10.1.0.5上修复，看起来又是一个伪修复的漏洞。另外一个文档叙述了同样的错误发生在10.2.0.4上:
Hdr: 8337808 10.2.0.4 RDBMS 10.2.0.4 PRG INTERFACE PRODID-5 PORTID-46 ORA-600 4359111
Abstract: ORA-600 [17281] [1001] EVEN AFTER APPLYING THE PATCH 4359111
In prod_ora_12985.trc we see that:
O/S info: user: Arun?Sharma, term: ARUN, ospid: 2556:3300,
machine: BVM-EDP\ARUN
Got the ORA-600 at 2009-04-17 13:27:21 .
From the trace this was likely because the PLSQL block in
cursor #2 has an instantiation entry indicating that it
has cursor #3 open:
INSTANTIATION OBJECT: object=0xf60e9ed0
type="PL/SQL"[0] lock=0xa383a928 handle=0xb48def6c body=(nil)
flags=[40] executions=0
CURSORS: size=4 count=1 next=3
index cursor tag context flags
----- ------ -------- -------- ---------------
2 3 0xf60d56f4 0xf60f832c LRU/PRS/[03]
^here
But there is no cursor#3 so it has likely been closed independently
So the immediate thing to do would be to find out what this OS
user (Arun Sharma) was doing at 13.27 on 17th April from the
ARUN machine under OS pid 2556:3300 ?
- what was the client program ?
- what is this clients Oracle RSF version ?
- what was being done in that client at that time.
It is unlikely that the user will remember such fine detail
so you may want to track the alert log closely and as soon
as you seen the ORA-600 find the O/S user , machine etc..
and try to contact them ASAP to confirm what they were doing
etc.. If we can get a handle on the client version / program /
actions that may help. Beyond that the next step is likely
to need a diagnostic on the server side to note cursor close
operations from the client without extranoues additional trace.
From the above update his client is TOAD using 8.0.6.0 on Windows.
TOAD is known to be affected by bug 4359111 so
you should upgrade this client to a version where
bug 4359111 is fixed. (4359111 is a CLIENT SIDE fix)
Marking as an unconfirmed duplicate of bug 4359111
as it looks like some specific client may be connecting
which does not have that patch in place.
与以上文档描述相同，trace中存在以下记录:
INSTANTIATION OBJECT: object=1105e4fe8
type="PL/SQL"[0] lock=70000044155a8b0 handle=70000042f5e54f8 body=0 level=0
flags=[40] executions=0
CURSORS: size=4 count=3 next=5
index cursor tag context flags
----- ------ -------- -------- ---------------
2 4 11049ecc8 110525f60 LRU/PRS/[03]
3 6 11049ecc8 1105261f0 LRU/PRS/[03]
4 5 11049ecc8 110526338 LRU/PRS/[03]
但实际上这里cursor 3所打开的cursor#:4,5,6均不存在，所以cursor# 3也被单独关闭了。文档中问题是由toad引起的，首先toad连接数据库是不需要安装Oracle client的，它通过一些客制化过的c/c++的接口连接到DB;如文档所述可以确定toad V8.0.6.0受到 Bug4359111的影响，而我们的环境中是通过PL/SQL developer连接到数据库的,该工具需要用到Oracle client，而开发人员安装的Oracle client一般为9.2.0.1，极有可能是这一较低版本的客户端软件造成了问题发生，到这里触发Bug的条件基本清晰了。
8i/9i的oracle client虽然仍能够连接到10g，但难保不发生一些兼容性问题或者将早期版本中的Bug再次代入，Oracle对这些连接形式或已不提供技术支持，或提供扩展模式(可能收费)的技术支持。以下列表列出了各版本Server-client的兼容性:
- #1 - See Note 207319.1
- #2 - An ORA-3134 error is incorrectly reported if a 10g client tries to connect to an 8.1.7.3 or lower server. See Note 3437884.8 .
- #3 - An ORA-3134 error is correctly reported when attempting to connect to this version.
- #4 - There are problems connecting from a 10g client to 8i/9i where one is EBCDIC based. See Note 3564573.8
- #5 - For connections between 10.2 (or higher) and 9.2 the 9.2 end MUST be at 9.2.0.4 or higher. Connections between 10.2 (or higher) and 9.2.0.1, 9.2.0.2 or 9.2.0.3 are not supported.
- #6 - For connections between 11.1 (or higher) and 10.1 / 10.2 the 10g end MUST be at 10.1.0.5 / 10.2.0.2 (or higher) respectively in order to use PLSQL between those versions. See Note 4511371.8 for more details.
posted on 2010-07-21 20:32 Oracle和MySQL 阅读(382) 评论(0) 编辑 收藏 举报
