分类:
2010-03-26 14:07:31
ora-600 4194 错误一般是由于undo表空间损坏导致部分事务无法回滚,因而导致数据库无法启动或启动后又被PMON中止。
解决此问题关键是确定损坏的回滚段,一般报警日志中会记录损坏的回滚段,见红色字体。
如果数据库能够侥幸打开,我们也可以在打开的瞬间执行一个脚本来确定回滚段。
1、报警日志、重点关注红色字体
Mem# 0: /data/oradata/rcms/redo02.log
Block recovery completed at rba 11.69.16, scn 13.1485530024
Thu Mar 25 17:36:07 2010
Errors in file /u01/app/oracle/admin/rcms/bdump/rcms_smon_1721.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/rcms/bdump/rcms_j000_2233.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/rcms/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/rcms/bdump/rcms_j000_2233.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/rcms/bdump/rcms_j000_2233.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/rcms/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/rcms_pmon_1709.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/rcms_pmon_1709.trc:
ORA-00600: internal error code, arguments: [4194], [19], [23], [], [], [], [], []
PMON: terminating instance due to error 472
Instance terminated by PMON, pid = 1709
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'
*._corrupted_rollback_segments=(_SYSSMU3$)
4、启动数据库,并重新创建undo表空间
$ !sql
sqlplus "/as sysdba"
SQL*Plus: Release 10.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/rcms/undotbs02.dbf' size 2g;
Tablespace created.
SQL> drop tablespace undotbs1;
Tablespace dropped.
SQL> alter tablespace undotbs2 rename to undotbs1;
Tablespace altered.
SQL>
至此基本搞定
linux 下可以通过这个确定system中正在使用的undo段