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

教程发布:风哥 教程分类:ITPUX技术网 更新日期:2022-02-12 浏览学习:223

Oracle数据库出现ORA-00600[4097]报错的解决方法 [color=green]对一套几个TB的ORACLE数据库断电通过_allow_resetlogs_corruption隐藏参数强制打开数据库后,对某些表操作时(包括select,delete等)会出现ORA-00600[4097]的报错,而且后台出现大量的ORA-00600[4097]报错,报错如下:Tue Jul0 7 08:59:40 BEIST 2013Errors 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 2013Trace dumping is performing id=[cdmp_20140107085946]Tue Jul 07 09:00:02 BEIST 2013Errors 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文档,如下:
[color=slategray]Problem Description: [color=slategray]==================== [color=slategray]An ORA-600 [4097] can be encountered through various activities that use [color=slategray]rollback segments.[color=slategray]Solution Description: [color=slategray]===================== [color=slategray]The most likely cause of this is BUG 427389. This BUG is fixed in[color=slategray]version 7.3.3.3. The BUG is caused when Rollback Segments are dropped and [color=slategray]recreated after a shutdown abort. It is encountered through a very specific [color=slategray]set of circumstances: [color=slategray]When an instance has a rollback segment offline and the instance crashes, or [color=slategray]the user does a shutdown abort, the rollback segment wrap number does not get [color=slategray]updated. If that segment is then dropped and recreated immediately after the [color=slategray]instance is restarted, the wrap number could be lower than existing wrap [color=slategray]numbers. This will cause the ORA-600[4097] to occur in subsequent [color=slategray]transactions using Rollback. [color=slategray]To avoid encountering this bug, rollback segments should only be dropped and [color=slategray]recreated after the instance has been shutdown normal and restarted. If you [color=slategray]have already encountered the bug, use the following workaround: [color=slategray] Select segment_name, segment_id from dba_rollback_segs; [color=slategray] Drop all Rollback Segments except for SYSTEM. [color=slategray] Recreate dummy (small) rollback segments with the same names in their place. [color=slategray] Then, recreate additional rollback segments you want to keep with their [color=slategray] permanent storage parameters. [color=slategray] Now drop the dummy ones. This should ensure that the segment_ids are not [color=slategray] reused. [color=slategray]If you ever want to add a rollback segment you have to use the workaround steps[color=slategray]again. If you do not fill the dummy slots you may see the problem re-appear.[color=slategray]References: [color=slategray]===========[color=slategray]Bug:427389 [color=slategray]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]内部错误,数据库实例恢复正常。

本文标签:
网站声明:本文由风哥整理发布,转载请保留此段声明,本站所有内容将不对其使用后果做任何承诺,请读者谨慎使用!
【上一篇】
【下一篇】