【案例】Oracle报错ORA-01578 ORA-01110 处理undo文件坏块数据库宕掉案例

天萃荷净 运维DBA反映，生产数据库运行时发现undo文件出现坏块，alert日志报错ORA-01578 ORA-01110，从而数据库异常终止

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

1.undo坏块导致数据库异常关闭

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

2.检测Oracle数据库坏块

$ 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)

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

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

4.Oracle数据库undo坏块的解决办法

--隐含参数
_corrupted_rollback_segments= _SYSSMU52$

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

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle报错ORA-01578 ORA-01110 处理undo文件坏块数据库宕掉案例

本文由大师惜分飞原创分享，网址：http://www.oracleplus.net/arch/623.html

Oracle研究中心

关键词：

Oracle报错ORA-01578 ORA-01110

处理undo文件坏块数据库宕掉案例

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

Oracle数据库undo坏块的解决办法