系统：HP-UX B.11.31
是否RAC：YES
数据库详细版本：10.2.0.4
1. 问题背景
2014-02-26上午10:13:39dbrac库节点2，在alert日志中发现很多ORA-600错误，经分析是表test_wait的统计信息出错导致，重新分析该表的统计信息，错误消除。
2. DBRAC库错误处理过程
1、查看alert日志如下：
|
Wed Feb 26 10:11:18 2014 Errors in file /oracle/admin/dbrac/udump/dbrac2_ora_14794.trc: ORA-00600: internal error code, arguments: [15160], [], [], [], [], [], [], [] Wed Feb 26 10:12:29 2014 Errors in file /oracle/admin/dbrac/udump/dbrac2_ora_14794.trc: ORA-00600: internal error code, arguments: [15160], [], [], [], [], [], [], [] Wed Feb 26 10:13:39 2014 Errors in file /oracle/admin/dbrac/udump/dbrac2_ora_29891.trc: ORA-00600: internal error code, arguments: [15160], [], [], [], [], [], [], [] |
2、继续分析trace日志如下：
|
/oracle/admin/dbrac/udump/dbrac2_ora_29891.trc Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Data Mining and Real Application Testing options ORACLE_HOME = /oracle/product/10.2.0/db System name: HP-UX Node name: dbrac2 Release: B.11.31 Version: U Machine: ia64 Instance name: dbrac2 Redo thread mounted by this instance: 2 Oracle process number: 121 Unix process pid: 29891, image: oracle@dbrac2
*** ACTION NAME:() 2014-02-26 10:13:39.698 *** MODULE NAME:(BusiMonitor@ccjkp1 (TNS V1-V3)) 2014-02-26 10:13:39.698 *** SERVICE NAME:(dbrac) 2014-02-26 10:13:39.698 *** SESSION ID:(1491.11581) 2014-02-26 10:13:39.698 *** 2014-02-26 10:13:39.698 ksedmp: internal or fatal error ORA-00600: internal error code, arguments: [15160], [], [], [], [], [], [], [] Current SQL statement for this session: select 'M-6M-LM-PM-EM-IM-OM-PM-PM-JM-}M->M-]test_waitM-SM-P'||count(1)||'M-LM-uM-<M-GM-BM-<M-QM-OM-VM-XM-3M-,M-JM-1M-#M-,M-NM-4M-<M-0M-JM-1M-GM-eM-@M-mM-#M-!' fr om test_wait where recvtime < sysdate -10/1440 and stepnow=0 having count(1) >=1 ----- Call Stack Trace ----- calling call entry argument values in hex location type point (? means dubious value) -------------------- -------- -------------------- ---------------------------- ksedst()+64 call ksedst1() 000000000 ? 000000001 ? ksedmp()+2176 call ksedst() 000000000 ? C000000000000C9F ? 4000000003ECA500 ? 000000000 ? 000000000 ? 000000000 ? ksfdmp()+48 call ksedmp() 000000003 ? kgeriv()+336 call ksfdmp() C000000000000695 ? 000000003 ? 4000000009382BA0 ? 000020127 ? 000000000 ? 000000000 ? 000000000 ? 000000000 ? kgesiv()+192 call kgeriv() 6000000000031370 ? 6000000000032428 ? 40000000018D14C0 ? 000000000 ? 9FFFFFFFFFFEE7E8 ? |
3、进一步分析是有以下SQL触发ORA-600
|
select 'M-6M-LM-PM-EM-IM-OM-PM-PM-JM-}M->M-]test_waitM-SM-P'||count(1)||'M-LM-uM-<M-GM-BM-<M-QM-OM-VM-XM-3M-,M-JM-1M-#M-,M-NM-4M-<M-0M-JM-1M-GM-eM-@M-mM-#M-!' fr om test_wait where recvtime < sysdate -10/1440 and stepnow=0 having count(1) >=1 |
4、分析及处理
|
基于表查询报出的ORA-00600 [15160]一般和SQL的执行相关，关于这种类型问题可以重新收集表统计信息解决。 具体处理过程如下： 1)查看该表的OWNER SQL> select owner,table_name from dba_tables where table_name = 'TEST_WAIT';
OWNER TABLE_NAME ------------------------------ ------------------------------ TEST_OWNER TEST_WAIT 2）收集该表统计信息 SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'TEST_OWNER',tabname=>'TEST_WAIT',estimate_percent=>10,no_invalidate=>false,cascade=>true,degree => 10);
PL/SQL procedure successfully completed. 3)、查看alert日志验证结果，发现从收集完统计信息之后ORA-600没再出现，问题解决！ |
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30633755/viewspace-2127676/ ，如需转载，请注明出处，否则将追究法律责任。
