undo坏块导致数据库异常终止案例

发表于 2012 年 6 月 1 日由惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：undo坏块导致数据库异常终止案例

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

在处理的众多undo问题的数据库中,这个是第一例遇到因为undo出现坏块导致数据库自动down案例(oracle 9.2.0.8 aix)
undo坏块导致数据库down

Fri Jun  1 00:45:13 2012
Successfully onlined Undo Tablespace 1.
Fri Jun  1 00:45:13 2012
SMON: enabling tx recovery
Fri Jun  1 00:45:13 2012
Database Characterset is ZHS16GBK
Fri Jun  1 00:45:13 2012
SMON: about to recover undo segment 52
SMON: about to recover undo segment 52
SMON: mark undo segment 52 as available
SMON: Restarting fast_start parallel rollback
SMON: about to recover undo segment 52
SMON: mark undo segment 52 as available
SMON: ignoring slave err,downgrading to serial rollback
SMON: about to recover undo segment 52
ORACLE Instance acc1 (pid = 9) - Error 1578 encountered while recovering transaction (52, 29).
Fri Jun  1 00:45:14 2012
Errors in file /oraacc/app/admin/acc/bdump/acc1_smon_734734.trc:
ORA-01578: ORACLE data block corrupted (file # 169, block # 55887)
ORA-01110: data file 169: '/dev/raccount07_01lv'
Fri Jun  1 00:45:15 2012
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: alter database open
Fri Jun  1 00:45:16 2012
Errors in file /oraacc/app/admin/acc/bdump/acc1_pmon_766940.trc:
ORA-00474: SMON process terminated with error
Fri Jun  1 00:45:16 2012
PMON: terminating instance due to error 474
Instance terminated by PMON, pid = 766940

这里可以看出数据库因为回滚段52出现坏块导致回滚的时候smon终止,数据库down

检测坏块

$ dbv file='/dev/raccount07_01lv' blocksize=8192

DBVERIFY: Release 9.2.0.8.0 - Production on Fri Jun 1 01:25:10 2012

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

DBVERIFY - Verification starting : FILE = /dev/raccount07_01lv

DBV-00200: Block, dba 708893135, already marked corrupted

DBV-00200: Block, dba 708893151, already marked corrupted

DBV-00200: Block, dba 708893263, already marked corrupted


DBVERIFY - Verification complete

Total Pages Examined         : 1048320
Total Pages Processed (Data) : 0
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 1048320
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 0
Total Pages Marked Corrupt   : 3
Total Pages Influx           : 0
Highest block SCN            : 12843497215383 (2990.1545000343)

检测对应文件号/数据块号

SQL> select DBMS_UTILITY.data_block_address_file(708893135) file#,
  2  DBMS_UTILITY.data_block_address_block(708893135) block#  from dual;

     FILE#     BLOCK#
---------- ----------
       169      55759

SQL> select DBMS_UTILITY.data_block_address_file(708893151) file#,
  2  DBMS_UTILITY.data_block_address_block(708893151) block#  from dual;

     FILE#     BLOCK#
---------- ----------
       169      55775

SQL> select DBMS_UTILITY.data_block_address_file(708893263) file#,
  2  DBMS_UTILITY.data_block_address_block(708893263) block#  from dual;

     FILE#     BLOCK#
---------- ----------
       169      55887

解决办法

--隐含参数
_corrupted_rollback_segments= _SYSSMU52$

--open库后
alter session set "_smu_debug_mode"=4;
drop rollback segment "_SYSSMU52$";

此条目发表在 Oracle备份恢复分类目录。将固定链接加入收藏夹。

评论功能已关闭。

17813235971