Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2152752
  • 博文数量: 157
  • 博客积分: 10047
  • 博客等级: 上将
  • 技术积分: 6757
  • 用 户 组: 普通用户
  • 注册时间: 2005-05-19 11:38
文章分类

全部博文(157)

文章存档

2011年(16)

2010年(50)

2009年(42)

2008年(49)

我的朋友

分类:

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段

strings /oracle/dev/oradata/CRMDEV/system01.dbf | grep _SYSSMU | cut -d $ -f 1 |sort -u
阅读(1401) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~