客户10.2.0.5 RAC for HP-UX ia64出现ORA-600(ksnpost:ksnigb)的错误。
错误信息如下:
Wed Apr 25 16:32:21 UTC 2012 Errors IN file /oracle/app/admin/orcl/bdump/orcl2_ora_17014.trc: ORA-00600: Message 600 NOT found; No message file FOR product=RDBMS, facility=ORA; arguments: [ksnpost:ksnigb] Wed Apr 25 16:32:55 UTC 2012 Trace dumping IS performing id=[cdmp_20120425163255] Wed Apr 25 16:32:56 UTC 2012 Errors IN file /oracle/app/admin/orcl/bdump/orcl2_ora_5511.trc: ORA-12012: 自动执行作业 8938 出错 ORA-12801: 并行查询服务器 ORA-12801: 并行查询服务器 P122, instance orcldb2:orcl2 (2) 中发出错误信号 ORA-00600: Message 600 NOT found; No message file FOR product=RDBMS, facility=ORA; arguments: [ksnpost:ksnigb] ORA-06512: 在 "SYS.DBMS_STATS", line 21275 中发出错误信号 Wed Apr 25 16:35:41 UTC 2012 Errors IN file /oracle/app/admin/orcl/bdump/orcl2_ora_19966.trc: ORA-27041: unable TO OPEN file HPUX-ia64 Error: 23: File TABLE overflow Additional information: 44 Additional information: 65598 Additional information: 2734688 Wed Apr 25 16:35:41 UTC 2012 Errors IN file /oracle/app/admin/orcl/bdump/orcl2_ora_19966.trc: ORA-27041: unable TO OPEN file HPUX-ia64 Error: 23: File TABLE overflow Additional information: 44 Additional information: 65598 Additional information: 2734688 Wed Apr 25 16:35:41 UTC 2012 Errors IN file /oracle/app/admin/orcl/bdump/orcl2_ora_19966.trc: ORA-27041: unable TO OPEN file HPUX-ia64 Error: 23: File TABLE overflow Additional information: 44 Additional information: 65598 Additional information: 2734688 |
详细TRACE信息为:
orcldb2>more /oracle/app/admin/orcl/bdump/orcl2_ora_17014.trc Dump file /oracle/app/admin/orcl/bdump/orcl2_ora_17014.trc Oracle DATABASE 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production WITH the Partitioning, REAL Application Clusters, OLAP, DATA Mining AND REAL Application Testing options ORACLE_HOME = /oracle/app/product/10.2.0/db_1 System name: HP-UX Node name: orcldb2 Release: B.11.31 Version: U Machine: ia64 Instance name: orcl2 Redo thread mounted BY this instance: 2 Oracle process NUMBER: 0 Unix process pid: 17014, image: oracle@orcldb2 Ioctl ASYNC_CONFIG error, errno = 1 *** SERVICE NAME:(SYS$USERS) 2012-04-25 16:32:21.472 *** SESSION ID:(377.37550) 2012-04-25 16:32:21.472 *** 2012-04-25 16:32:21.472 ksedmp: internal OR fatal error ORA-00600: Message 600 NOT found; No message file FOR product=RDBMS, facility=ORA; arguments: [ksnpost:ksnigb] CURRENT SQL statement FOR this SESSION: SELECT /*+ parallel_index(t,"IDX_PRODUCT_ID",96) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_expand index_ffs(t," IDX_PRODUCT_ID") */ COUNT(*) AS nrw,COUNT(DISTINCT sys_op_lbid(82769,'L',t.rowid)) AS nlb,COUNT(DISTINCT "PRODUCT_ID") AS ndk,sys_op_countchg(substrb(t.rowid ,1,15),1) AS clf FROM "ORCL_UNICOM"."MESSAGE_LOG" sample block ( .4377057550,1) t WHERE ("PRODUCT_ID" IS NOT NULL) AND (TBL$OR$IDX$PART$NUM("ORCL_UNICOM"."M ESSAGE_LOG",0,4,0,"ROWID") = :objn) ----- Call Stack Trace ----- calling CALL entry argument VALUES IN hex location TYPE point (? means dubious VALUE) -------------------- -------- -------------------- ---------------------------- 40000000040333a0 CALL 4000000004035b60 000000000 ? 000000001 ? 4000000004032b40 CALL 4000000004033360 000000000 ? C000000000000D20 ? 4000000004032B40 ? 000000000 ? 000000000 ? 000000000 ? 400000000407abb0 CALL 40000000040322c0 000000003 ? 9FFFFFFFFFFEFC10 ? 60000000000BA268 ? 9FFFFFFFFFFF01E0 ? C000000000000999 ? 400000000407ABB0 ? 40000000098c33d0 CALL 400000000407ab40 9FFFFFFFFFFF0770 ? 000000003 ? 9FFFFFFFFFFF01F0 ? 60000000000BA268 ? C000000000000612 ? 40000000098C33D0 ? 40000000098c41c0 CALL 40000000098c32a0 60000000000318D0 ? 4000000001ADC4E0 ? 6000000000032988 ? 4000000001ADC4E0 ? 9FFFFFFFFFFF07A0 ? 4000000004030400 CALL 40000000098c4180 60000000000318D0 ? 9FFFFFFFFD364638 ? 9FFFFFFFFD364648 ? 6000000000032D00 ? 9FFFFFFFFFFF07A0 ? 4000000002071430 CALL 4000000004030350 4000000000F67410 ? 000000000 ? 60000000000C6C98 ? 40000000030c7ce0 CALL 40000000020713d0 C000001FF2838160 ? 60000000000BA268 ? C000000000000797 ? 40000000030C7CE0 ? 9FFFFFFFFFFF07DC ? 9FFFFFFFFFFF07E0 ? 9FFFFFFFFFFF07D0 ? 000000001 ? 40000000030c8940 CALL 40000000030c7a50 C0000020351ED5A0 ? |
查询MOS,发现和问题文档Get Various ORA-600 Error Followed By “HPUX-ia64 Error: 24: Too many open files” [ID 1061351.1]描述得比较接近。虽然文档描述的问题是HPUX-ia64 Error: 24,而当前的错误是HPUX-ia64 Error: 23: File table overflow,但是二者的本质是一样的。
从错误信息上看65598已经超过了设置值65536,针对这个问题,可以通过调整内核参数的方式进行解决,可以参考文档How to Increase the Number of Open Files on HP-UX [ID 1264834.1]。
而对于当前的情况,问题发生在并行执行上,并行度开到96显然过大,将其降至并行度16应该就可以避免该错误。