ora-600 4194 错误处理分析解决

ora-600 4194 错误一般是由于undo表空间损坏导致部分事务无法回滚，因而导致数据库无法启动或启动后又被PMON中止。 解决此问题关键是确定损坏的回滚段，一般报警日志中会记录损坏的回滚段，见红色字体。 如果数据库能够侥幸打开，我们也可以在打开的瞬间执行一个脚本来确定回滚段。 1、报警日志、重点关注红色字体 Mem# 0: /data/oradata/ecms/redo02.log Block recovery completed at rba 11.69.16, scn 13.1485530026 Thu Mar 25 17:36:07 2010 Errors in file /u01/app/oracle/admin/ecms/bdump/ecms_smon_1722.trc: ORA-01595: error freeing extent (75) of rollback segment (3)) ORA-00607: Internal error occurred while making a change to a data block ORA-00600: internal error code, arguments: [4194], [19], [23], [], [], [], [], [] Thu Mar 25 17:37:07 2010 Errors in file /u01/app/oracle/admin/ecms/bdump/ecms_j000_2236.trc: ORA-00600: internal error code, arguments: [4194], [19], [23], [], [], [], [], [] Thu Mar 25 17:37:08 2010 DEBUG: Replaying xcb 0x31621de4, pmd 0x3179c928 for failed op 8 Doing block recovery for file 2 block 58981 Block recovery from logseq 11, block 68 to scn 57320104895 Thu Mar 25 17:37:08 2010 Recovery of Online Redo Log: Thread 1 Group 2 Seq 11 Reading mem 0   Mem# 0: /data/oradata/ecms/redo02.log Block recovery completed at rba 11.84.16, scn 13.1485530049 Thu Mar 25 17:37:08 2010 Errors in file /u01/app/oracle/admin/ecms/bdump/ecms_j000_2236.trc: ORA-00600: internal error code, arguments: [4194], [19], [23], [], [], [], [], [] ORA-00600: internal error code, arguments: [4194], [19], [23], [], [], [], [], [] Thu Mar 25 17:37:09 2010 Errors in file /u01/app/oracle/admin/ecms/bdump/ecms_j000_2236.trc: ORA-00600: internal error code, arguments: [4194], [19], [23], [], [], [], [], [] ORA-00600: internal error code, arguments: [4194], [19], [23], [], [], [], [], [] Thu Mar 25 17:37:13 2010 DEBUG: Replaying xcb 0x31621de4, pmd 0x3179c928 for failed op 8 Doing block recovery for file 2 block 58981 Block recovery from logseq 11, block 68 to scn 57320104895 Thu Mar 25 17:37:13 2010 Recovery of Online Redo Log: Thread 1 Group 2 Seq 11 Reading mem 0   Mem# 0: /data/oradata/ecms/redo02.log Block recovery completed at rba 11.84.16, scn 13.1485530049 Thu Mar 25 17:37:13 2010 Errors in file /u01/app/oracle/admin/rcms/bdump/ecms_pmon_1710.trc: ORA-00600: internal error code, arguments: [4194], [19], [23], [], [], [], [], [] Thu Mar 25 17:37:13 2010 Errors in file /u01/app/oracle/admin/rcms/bdump/ecms_pmon_1710.trc: ORA-00600: internal error code, arguments: [4194], [19], [23], [], [], [], [], [] PMON: terminating instance due to error 472 Instance terminated by PMON, pid = 1710 2、确定损坏的回滚段 error freeing extent (75) of rollback segment (3)) 根据该日志可以判断是第三个回滚段的第75个区损坏 Doing block recovery for file 2 block 58981 SELECT segment_name,segment_type,extent_id,block_id, blocks from dba_extents t where  file_id = 2 AND 58981 between block_id and (block_id + blocks - 1) ; SEGMENT_NAME   SEGMENT_TYPE        EXTENT_ID   BLOCK_ID     BLOCKS -------------------------------------------------------------------- _SYSSMU3$      TYPE2 UNDO               74      58761       1024                  根据那个错误日志基本可以判断是在读取file 2的58981数据块时出错的，可以执行上面sql确定损坏的回滚段 3、修改pfile如下 *.undo_management='MANUAL' *._offline_rollback_segments='_SYSSMU57$' *._corrupted_rollback_segments='_SYSSMU3$' 4、启动数据库，并重新创建undo表空间 $ !sql sqlplus "/as sysdba" SQL*Plus: Release 11.2.0.4.0 - Production on Fri Mar 26 13:46:28 2010 Copyright (c) 1982, 2007, Oracle.  All Rights Reserved. Connected to an idle instance. SQL> startup pfile='/tmp/a.txt'; ORACLE instance started. Total System Global Area  314572800 bytes Fixed Size                  1267212 bytes Variable Size             188746228 bytes Database Buffers          117440512 bytes Redo Buffers                7118848 bytes Database mounted. Database opened. SQL> create undo tablespace undotbs2 datafile '/data/oradata/ecms/undotbs02.dbf' size 2g; Tablespace created. SQL> drop tablespace undotbs1; Tablespace dropped. SQL> alter tablespace undotbs2 rename to undotbs1; Tablespace altered. SQL> 至此基本搞定