遭遇ora_00600 [4042] [ktssdrp1]处理过程

ora_00600 [4042] [ktssdrp1]
2012-06-01 10:12

1       OS及版本Red Hat Enterprise Linux AS release 4 (Nahant Update 7) Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production 2       现象 Errors in file /data/oracle9/admin/JDB/bdump/msdb_j000_18901.trc: ORA-00600: internal error code, arguments: [4042], [18785], [], [], [], [], [], [] Thu May 31 16:15:10 2012 Thread 1 advanced to log sequence 49321   Current log# 22 seq# 49321 mem# 0: /dev2/oradata/log22.ora Thu May 31 16:15:10 2012 ARC0: Evaluating archive   log 21 thread 1 sequence 49320 ARC0: Beginning to archive log 21 thread 1 sequence 49320 Creating archive destination LOG_ARCHIVE_DEST_1: '/dev2/arch/1_49320.dbf' ARC0: Completed archiving  log 21 thread 1 sequence 49320 Thu May 31 16:25:19 2012 Errors in file /data/oracle9/admin/JDB/bdump/msdb_j042_17320.trc: ORA-00600: internal error code, arguments: [4042], [18785], [], [], [], [], [], [] Thu May 31 16:29:22 2012 Thread 1 advanced to log sequence 49322   Current log# 23 seq# 49322 mem# 0: /dev2/oradata/log23.ora Thu May 31 16:29:22 2012 ARC1: Evaluating archive   log 22 thread 1 sequence 49321 ARC1: Beginning to archive log 22 thread 1 sequence 49321 Creating archive destination LOG_ARCHIVE_DEST_1: '/dev2/arch/1_49321.dbf' ARC1: Completed archiving  log 22 thread 1 sequence 49321 Thu May 31 16:29:27 2012 Errors in file /data/oracle9/admin/JDB/bdump/msdb_j004_10918.trc: ORA-00600: internal error code, arguments: [4042], [18785], [], [], [], [], [], [] Thu May 31 16:36:41 2012 3       处理过程3.1      查找资料3.1.1    MOS ORA-600 [4042] Reported [ID 1248762.1] 修改时间 14-SEP-2011     类型 HOWTO     状态 PUBLISHED In this Document   Goal   Solution   References Applies to: Oracle Server - Enterprise Edition - Version: 8.1.7.0 to 11.2.0.2.0 - Release: 8.1.7 to 11.2 Information in this document applies to any platform. Goal What steps should be followed if an ORA-600 [4042] is encountered in the alert.log or client application? Solution It is important to upload to Global Software Support: the alert.log from at least the startup before the error the first trace file written by ORA-600 [4042] the output of select us#, name, ts#, file#, block#,status$ from undo$ where (ts#, file#, block#) not in (select ts#, file#, block# from seg$ where type# in (1,10)) and status$ > 1; One possible cause of this error is undo segments which do not have corresponding entries in SEG$. The above query will identify if this is the case. If the query returns rows, please report this to Global Software Support. 从mos上看，只提供了一个有用的SQL,其他没多大意义，让你提交给oracle技术支持！ 3.1.2    网上其他资料大致的做法： （1）       先新建新的回滚表空间，然后再把切换过去，结合设置隐含参数：_offline_rollback_segments、_corrupted_rollback_segments，然后删除掉这个表空间！ （2）       直接改数据字典 具体参考： http://www.killdb.com/2011/07/30/%E6%89%8B%E5%B7%A5%E6%B8%85%E9%99%A4%E5%9B%9E%E6%BB%9A%E6%AE%B5%E7%9A%84%E5%87%A0%E7%A7%8D%E6%96%B9%E6%B3%95.html 3.1.3    查看回滚段的信息根据MOS提供的SQL先查询 SQL> select us#, name, ts#, file#, block#,status$ from undo$   2 where (ts#, file#, block#) not in   3 (select ts#, file#, block# from seg$ where type# in (1,10)) and status$ > 1;        US# NAME TS# FILE# BLOCK# STATUS$ ---------- ------------------------------ ---------- ---------- ---------- ----------         42 _SYSSMU42$ 11 151 30385 2 3.1.4    创建新的回滚表空间，并切换 /* OracleOEM */ CREATE UNDO TABLESPACE "UNDO06" DATAFILE '/data/oracle9/oradata/JDB/UNDO06.dbf' SIZE 106M AUTOEXTEND ON NEXT  16M MAXSIZE UNLIMITED Thu May 31 16:36:42 2012 Created Undo Segment _SYSSMU86$ …… Completed: /* OracleOEM */ CREATE UNDO TABLESPACE "UNDO06" DA Thu May 31 16:37:14 2012 3.1.5    切换回滚表空间 ALTER SYSTEM SET undo_tablespace='UNDO06' SCOPE=BOTH; Thu May 31 16:37:34 2012 Created Undo Segment _SYSSMU96$ Undo Segment 96 Onlined Thu May 31 16:37:34 2012 Created Undo Segment _SYSSMU97$ Thu May 31 16:37:34 2012 Undo Segment 97 Onlined Thu May 31 16:37:34 2012 …… Undo Segment 117 Onlined Thu May 31 16:38:15 2012 3.1.6    查看原回滚表空间是否有在线的回滚段SELECT     a.owner || '.' || a.segment_name          roll_name   , a.tablespace_name                         tablespace   , TO_CHAR(a.initial_extent) || ' / ' ||     TO_CHAR(a.next_extent)                    in_extents   , TO_CHAR(a.min_extents)    || ' / ' ||     TO_CHAR(a.max_extents)                    m_extents   , a.status                                  status   , b.bytes/1024/1024                                   "bytes(M)"   , b.extents                                 extents   , d.shrinks                                 shrinks   , d.wraps                                   wraps   , d.optsize                                 opt FROM     dba_rollback_segs a   , dba_segments b   , v$rollname c   , v$rollstat d WHERE        a.segment_name = b.segment_name   AND  a.segment_name = c.name (+)   AND  c.usn          = d.usn (+) ORDERBY a.tablespace_name,B.segment_name 发现原回滚表空间有两个回滚段在线 3.1.7    看那些应用导致两个回滚段在线 SELECT     r.name                          roll_name   , s.osuser || '/' ||  s.username  userID   , s.sid || '/' || s.serial#       usercode   , s.program                       program   , s.status                        status   , s.machine                       machine FROM     v$lock     l   , v$rollname r   , v$session  s WHERE       s.sid = l.sid   AND TRUNC (l.id1(+)/65536) = r.usn   AND l.type(+) = 'TX'   AND l.lmode(+) = 6 ORDERBY r.name / 结果发现是两个在执行查询的连个会话，可以kill掉 SELECT 'kill -9 '||spid,v1.* FROM v$session v1,v$process v2 WHERE v1.PADDR=v2.addr 3.1.8    再次观察是原回滚表空间是否有在线回滚段 运行3.1.6发现原回滚表空间没有在线回滚段。 结合日志看： Undo Tablespace 98 moved to Pending Switch-Out state. Thu May 31 16:43:22 2012 Undo Tablespace 98 moved to Pending Switch-Out state. Thu May 31 16:43:43 2012 3.1.9    删除原回滚表空间 发现删不了，并继续报错！ /* OracleOEM */ DROP TABLESPACE "UNDOTBS2" INCLUDING CONTENTS  AND DATAFILES  CASCADE CONSTRAINTS Thu May 31 18:21:48 2012 Errors in file /data/oracle9/admin/JDB/udump/msdb_ora_13977.trc: ORA-00600: internal error code, arguments: [ktssdrp1], [11], [151], [30385], [], [], [], [] ORA-600 signalled during: /* OracleOEM */ DROP TABLESPACE "UNDOTBS2" INCLUDI... Thu May 31 18:24:35 2012 /* OracleOEM */ DROP TABLESPACE "UNDOTBS2" INCLUDING CONTENTS  AND DATAFILES  CASCADE CONSTRAINTS Thu May 31 18:24:35 2012 Errors in file /data/oracle9/admin/JDB/udump/msdb_ora_20126.trc: ORA-00600: internal error code, arguments: [ktssdrp1], [11], [151], [30385], [], [], [], [] Thu May 31 18:24:35 2012 ORA-600 signalled during: /* OracleOEM */ DROP TABLESPACE "UNDOTBS2" INCLUDI... Thu May 31 18:31:29 2012 Errors in file /data/oracle9/admin/JDB/bdump/msdb_j000_18901.trc: ORA-00600: internal error code, arguments: [4042], [18785], [], [], [], [], [], [] Thu May 31 18:40:32 2012 3.1.10   发通知准备晚上维护 为使应用正常过渡，目前虽然报错，但暂时不应用应用，准备在晚上9点左右加隐含参数，重启后删除原回滚表空间。 Thread 1 advanced to log sequence 49330   Current log# 27 seq# 49330 mem# 0: /dev2/oradata/log27.ora Thu May 31 18:40:32 2012 ARC1: Evaluating archive   log 26 thread 1 sequence 49329 ARC1: Beginning to archive log 26 thread 1 sequence 49329 Creating archive destination LOG_ARCHIVE_DEST_1: '/dev2/arch/1_49329.dbf' Thu May 31 18:40:33 2012 Errors in file /data/oracle9/admin/JDB/bdump/msdb_j007_32665.trc: 3.1.11   计划赶不上变化 8点不到就出现数据库异常 ü  客户端：连不上数据库 ü  服务端：执行命令报“-bash: start_pipeline: pgrp pipe: Too many open files in system”，执行任何命令都报类似错误，命令已经不可运行。 ü  后台日志：连续出现大量的ORA-00600错误 Errors in file /data/oracle9/admin/JDB/udump/msdb_ora_7905.trc: ORA-00600: internal error code, arguments: [4042], [18785], [], [], [], [], [], [] Thu May 31 19:52:16 2012 Errors in file /data/oracle9/admin/JDB/bdump/msdb_j000_18901.trc: ORA-00600: internal error code, arguments: [4042], [18785], [], [], [], [], [], [] Thu May 31 19:53:44 2012 Errors in file /data/oracle9/admin/JDB/bdump/msdb_j001_18903.trc: ORA-00600: internal error code, arguments: [4042], [18785], [], [], [], [], [], [] Thu May 31 19:54:19 2012 Errors in file /data/oracle9/admin/JDB/bdump/msdb_j000_18901.trc: ORA-00600: internal error code, arguments: [4042], [18785], [], [], [], [], [], [] Thu May 31 19:55:00 2012 3.1.12   关闭数据库，准备重启数据库 PS:init.ora中的隐含参数_offline_rollback_segments、_corrupted_rollback_segments在2小时前准备好，所以这里直接重启就好了 由于在oracle账号下无法执行SQLPLUS命令，所以准备用root账号登录再执行！ 3.1.12.1          关闭数据库，发现有长时间的等待提示 errors in file /data/oracle9/admin/JDB/bdump/msdb_j004_10918.trc: ORA-00600: internal error code, arguments: [4042], [18785], [], [], [], [], [], [] Thu May 31 19:55:41 2012 Shutting down instance: further logons disabled Thu May 31 19:55:59 2012 Shutting down instance (immediate) License high water mark = 295 Thu May 31 20:01:06 2012 Active call for process 18879 user 'oracle' program 'oracle@FDB02 (S000)' Active call for process 1421 user 'oracle' program 'oracle@FDB02 (S014)' Active call for process 31909 user 'oracle' program 'oracle@FDB02 (S008)' ...... Active call for process 3943 user 'oracle' program 'oracle@FDB02 (S019)' SHUTDOWN: waiting for active calls to complete. 3.1.12.2          Kill掉非后台进程后 Archive process shutdown avoided: 0 active ARCH: Archiving is disabled Shutting down archive processes Archiving is disabled Archive process shutdown avoided: 0 active Thu May 31 20:12:01 2012 Starting ORACLE instance (normal) 3.1.13   重新启动数据库 仍然报ORA-00600 [4042],看来隐含参数不起作用,类似问题处理过不少，这次是第一次出现隐含参数无效的情况！而且删除原回滚表空间时仍然是原来的提示，删不掉！ Successfully onlined Undo Tablespace 99. Errors in file /data/oracle9/admin/JDB/bdump/msdb_j000_19610.trc: ORA-00600: internal error code, arguments: [4042], [18785], [], [], [], [], [], [] Thu May 31 20:12:36 2012 Thu May 31 20:13:33 2012 …… Errors in file /data/oracle9/admin/JDB/udump/msdb_ora_20138.trc: ORA-00600: internal error code, arguments: [ktssdrp1], [11], [151], [30385], [], [], [], [] 试了几次仍然是报上述错误！ 3.1.14   死马当活马医 实现已经对undo$中us#=42的记录做备份        US# NAME TS# FILE# BLOCK# STATUS$ ---------- ------------------------------ ---------- ---------- ---------- ----------         42 _SYSSMU42$ 11 151 30385 2 delete from undo$ where us#=42; commit; 直接删除undo$中这条记录发现正常

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/751051/viewspace-731720/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/751051/viewspace-731720/