linux ora报错 0001,Oracle数据库出现ORA-00600[4097]报错的解决方法

Oracle数据库出现ORA-00600[4097]报错的解决方法

对一套几个TB的ORACLE数据库断电通过_allow_resetlogs_corruption隐藏参数强制打开数据库后，对某些表操作时(包括select,delete等)会出现ORA-00600[4097]的报错，而且后台出现大量的ORA-00600[4097]报错，报错如下：

Tue Jul0 7 08:59:40 BEIST 2013

Errors in file /app/oracle/admin/rac/udump/rac1_ora_8323834.trc:

ORA-00600: internal error code, arguments: [4097], [], [], [], [], [], [], []

Tue Jul 07 08:59:46 BEIST 2013

Trace dumping is performing id=[cdmp_20140107085946]

Tue Jul 07 09:00:02 BEIST 2013

Errors in file /app/oracle/admin/rac/udump/rac1_ora_8323834.trc:

ORA-00600: internal error code, arguments: [4097], [], [], [], [], [], [], []

查看trc文件，重要信息如下：

Dump of buffer cache at level 4 for tsn=1, rdba=8388649

BH (0x2b7e986c) file#: 2 rdba: 0x00800029 (2/41) class: 21 ba: 0x2b4a4000

set: 3 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 0

dbwrid: 0 obj: -1 objn: 0 tsn: 1 afn: 2

hash: [2b7fb19c,300f6bcc] lru: [2b7e9970,2b7e9810]

ckptq: [2b7e93c4,2b7e9f74] fileq: [2b7e93cc,301929f0] objq: [2e726648,2b7e9444]

st: XCURRENT md: NULL tch: 1

flags: buffer_dirty gotten_in_current_mode redo_since_read

LRBA: [0x332a8.3.0] HSCN: [0x1.d03bf318] HSUB: [1]

buffer tsn: 1 rdba: 0x00800029 (2/41)

scn: 0x0001.d03bf318 seq: 0x01 flg: 0x00 tail: 0xf3182601

frmt: 0x02 chkval: 0x0000 type: 0x26=KTU SMU HEADER BLOCK

可以看到它的类型是KTU SMU HEADER BLOCK即某个回滚段头，根据这个ORA-00600[4097]的描述，可以参考Oracle Metalink文档，如下：

Problem Description:

====================

An ORA-600 [4097] can be encountered through various activities that use

rollback segments.

Solution Description:

=====================

The most likely cause of this is BUG 427389.  This BUG is fixed in

version 7.3.3.3.  The BUG is caused when Rollback Segments are dropped and

recreated after a shutdown abort.  It is encountered through a very specific

set of circumstances:

When an instance has a rollback segment offline and the instance crashes, or

the user does a shutdown abort, the rollback segment wrap number does not get

updated.  If that segment is then dropped and recreated immediately after the

instance is restarted, the wrap number could be lower than existing wrap

numbers.  This will cause the ORA-600[4097] to occur in subsequent

transactions using Rollback.

To avoid encountering this bug, rollback segments should only be dropped and

recreated after the instance has been shutdown normal and restarted.  If you

have already encountered the bug, use the following workaround:

Select segment_name, segment_id from dba_rollback_segs;

Drop all Rollback Segments except for SYSTEM.

Recreate dummy (small) rollback segments with the same names in their place.

Then, recreate additional rollback segments you want to keep with their

permanent storage parameters.

Now drop the dummy ones. This should ensure that the segment_ids are not

reused.

If you ever want to add a rollback segment you have to use the workaround steps

again.  If you do not fill the dummy slots you may see the problem re-appear.

References:

===========

Bug:427389

Bug:486350

要处理这个情况，可以尝试删除一些存在问题的rollback segment来规避这个问题，虽然在Oracle 10g下使用automatic managed undo，但是通过_smu_debug_mode隐含参数但仍可以做到这一点:

设置 "_smu_debug_mode"=4;设置SMU debug模式为4以便能够手动管理回滚段：

SQL> alter system set "_smu_debug_mode"=4;

System altered.

要记得处理完这个后，将_smu_debug_mode隐含参数还原为默认的:alter system set "_smu_debug_mode"=0;

依次执行以下面的drop rollback segment回滚段的命令，当前撤销表空间上的回滚段仅能offline而无法drop掉，实际上我们需要做的也仅仅是把之前undo表空间上有问题的回滚段drop掉

SQL>select 'alter rollback segment '||'"'||segment_name||'" offline;' from dba_rollback_segs where tablespace_name <> 'SYSTEM';

drop rollback segment "_SYSSMU1$";

drop rollback segment "_SYSSMU2$";

drop rollback segment "_SYSSMU3$";

drop rollback segment "_SYSSMU4$";

drop rollback segment "_SYSSMU5$";

drop rollback segment "_SYSSMU6$";

drop rollback segment "_SYSSMU7$";

drop rollback segment "_SYSSMU8$";

drop rollback segment "_SYSSMU9$";

drop rollback segment "_SYSSMU10$";

drop rollback segment "_SYSSMU11$";

drop rollback segment "_SYSSMU12$";

drop rollback segment "_SYSSMU13$";

drop rollback segment "_SYSSMU14$";

drop rollback segment "_SYSSMU15$";

drop rollback segment "_SYSSMU16$";

drop rollback segment "_SYSSMU17$";

drop rollback segment "_SYSSMU18$";

drop rollback segment "_SYSSMU19$";

drop rollback segment "_SYSSMU20$";

如果状态为online，则alter rollback segment "_SYSSMU8$" offline;

删除以上有问题回滚段rollback segment后，系统不再出现ORA-00600:[4097]内部错误，数据库实例恢复正常。