ITPUX技术网

交流 . 资讯 . 分享
Make progress together!
Oracle数据库DBA高级工程师培训视频
Oracle数据库培训-备份恢复-性能优化-集群容灾
mysql数据库视频教程

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

内容发布:风哥| 发布时间:2014-1-9 22:09:35
Oracle数据库出现ORA-00600[4097]报错的解决方法
对一套几个TBORACLE数据库断电通过_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]内部错误,数据库实例恢复正常。



上一篇:Oracle数据库报错ORA-00600[4193][4194]的解决方法与处理过程
下一篇:Oracle数据库报错Ora-00600 [4193] When Opening Or Shutting Down A Database
189070296,150201289

专业提供Oracle数据库服务、主机、存储、备份、中间件等相关技术支持服务,QQ号:176140749
关注ITPUX技术网微信公众号itpux_com  ,了解本站最新技术资料的分享.

欢迎加QQ群,提供超多高质量Oracle/Unix/Linux技术文档与视频教程的下载。

Oracle/MySQL/Linux群4-5:189070296  150201289  
Oracle/MySQL/Linux群6-8:244609803   522261684   522651731
备注:请勿重复加群,另请注明 from itpux

加群分享视频教程部分如下:

1、公开课视频:Oracle/MySQL数据库工程师职业发展前景讲解(免费)
http://edu.51cto.com/course/7015.html

2、51CTO学院Oracle数据库高级工程师培训(高薪就业.课程介绍)
http://edu.51cto.com/px/train/131?xiaotu

3、Oracle DBA数据库高级工程师培训视频课程1.1(系列78套+七大阶段+上千案例)
套餐视频地址: http://edu.51cto.com/topic/1121.html

4、MySQL数据库(终身门徒)套餐:http://edu.51cto.com/sd/1e1a6

回复

使用道具 举报

内容发布:xjcydf909| 发布时间:2014-10-28 13:16:58
路过~~~~~~~~
回复

使用道具 举报

1框架
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

快速回复 返回顶部 返回列表