Oracle HowTo: How to deal with Ora-600 4193 error

« 遭遇Referer Spam Too | Blog首页 | 谁偷走了一秒光阴? »

Oracle HowTo: How to deal with Ora-600 4193 error

作者：eygle |【转载时请以超链接形式标明文章出处和作者信息及本声明】
链接：http://www.eygle.com/archives/2005/12/oracle_howto_deal_with_ora600_4137_error.html

站内相关文章|Related Articles

DBA警世录:DBA千万不要想当然

what is SMON_SCN_TIME ?

How to repair corruption block use BBED?

重建控制文件恢复丢失表空间一例

使用ora_rowscn识别误操作数据时间点

在解决2662错误之后，经常会出现Ora-00600 4193错误，经常可以在alert文件中看到的错误号类似:

Fri Dec 16 22:37:27 2005
Errors in file /opt/oracle/admin/conner/bdump/conner_smon_22817.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4193], [1171], [1187], [], [], [], [], []
Fri Dec 16 23:28:40 2005
Errors in file /opt/oracle/admin/conner/bdump/conner_smon_22817.trc:
ORA-00600: internal error code, arguments: [4193], [1171], [1187], [], [], [], [], []

4193错误通常是因为恢复时redo与undo不一致所导致。

Oracle的解释如下:

While backing out an undo record (i.e. at the time of rollback) we found a transaction id mis-match indicating either a corruption in the rollback segment or corruption in an object which the rollback segment is trying to apply undo records on.

This would indicate a corrupted rollback segment.

检查具体的Trace文件，可以发现类似如下错误：

*** 2005-12-16 20:54:53.496
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [4193], [1171], [1187], [], [], [], [], []
Current SQL statement for this session:
UPDATE SMON_SCN_TIME SET SCN_WRP=:1, SCN_BAS=:2, TIME_MP=:3, TIME_DP=:4 
WHERE TIME_MP = :5  AND   THREAD = :6  AND   ROWNUM <= 1

由于是UNDO存在不一致，可以通过重建UNDO表空间来解决:

SQL> create undo tablespace undotbs2 
   2 datafile '/opt/oracle/oradata/conner/undotbs2.dbf' size 10m;

Tablespace created.

SQL> alter system set undo_tablespace=undotbs2 scope=both;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  114365800 bytes
Fixed Size                   451944 bytes
Variable Size              50331648 bytes
Database Buffers           62914560 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     10800
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS2

SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

至此，Ora-600 4193错误不再出现。

从alert文件中，可以看到自动控制文件备份生效：

Sun Dec 18 22:37:59 2005
drop tablespace undotbs1 including contents and datafiles
Sun Dec 18 22:37:59 2005
Deleted file /opt/oracle/oradata/conner/undotbs01.dbf
Starting control autobackup
Control autobackup written to DISK device
        handle '/opt/oracle/product/9.2.0/dbs/c-3152029224-20051218-01'
Completed: drop tablespace undotbs1 including contents and da

历史上的今天...
>> 2008-12-30文章:

结束《深入解析Oracle》技术访谈交流会

>> 2007-12-30文章:

2007 年终总结-从热点排行看世界

>> 2006-12-30文章:

2006 年终总结-个人网站的发展总结

>> 2004-12-30文章:

给自己的礼物-谁是谁的麦琪?

相关文章	随机文章
DBA警世录:DBA千万不要想当然 what is SMON_SCN_TIME ? How to repair corruption block use BBED? 重建控制文件恢复丢失表空间一例使用ora_rowscn识别误操作数据时间点	ORA-00704 与 bootstrap 错误 2007 年终总结-从热点排行看世界顺利获得赴美签证 EMC CX500硬盘再坏 How to use Oracle BFILE

搜索本站:

留言 (3)

如果此时undotbs的undo segment中还有active的事务怎么办？用_corrupted_rollback_segments参数启动数据库？

Posted by: Kamus at November 19, 2006 1:00 AM

对，可以的。

Posted by: eygle at November 19, 2006 11:47 AM

ft,在10G里面这种方式已经不行啦.

Posted by: cc59 at September 3, 2007 12:58 PM

www.eygle.com

Oracle HowTo: How to deal with Ora-600 4193 error

留言 (3)

发表留言: