数据库服务器掉电后重启时出现错误ORA-01172

数据库服务器掉电后重启时出现错误ORA-01172: recovery of thread 1 stuck at block 121 of file 14
Error:  ORA 1172
Text:   recovery of thread stuck at block of file
-------------------------------------------------------------------------------
Cause:  Crash recovery or instance recovery could not apply a change to a
        block because it was not the next change.
        This can happen if the block was corrupted and then repaired during
        recovery.
Action: Perform a RECOVER datafile for the file containing the block.
        If this does not resolve the problem, then restore the file from a
        backup and recover it again.
这是数据库启动时进行实例恢复时出现的Stuck Recovery。在进行实例恢复时会出现前滚和后滚，
这分别需要用到redo和undo。
Stuck Recovery就是进行故障恢复或实例恢复时需要恢复的数据块比redo的数据旧。
Stuck Recovery产生原因如下：
- Recovery was not started early enough in the redo stream
- Some redo is lost
- Block corruption in data or redo
- Possible recovery from stuck recovery:
- Retry recovery with older data and archive log files
- Corrupt the block
这个错误一般会伴随ora-600 [3020]错误
 
1、日志内容如下
  Mem# 0 errs 0: /data/cache2/app/oracle/oradata/npm/redo01.log
Mon May 10 18:42:15 2010
RECOVERY OF THREAD 1 STUCK AT BLOCK 121 OF FILE 14
Mon May 10 18:42:15 2010
Completed redo application
Mon May 10 18:42:22 2010
Errors in file /data/cache2/app/oracle/admin/npm/bdump/npm_p001_25055.trc:
ORA-01172: recovery of thread 1 stuck at block 121 of file 14
ORA-01151: use media recovery to recover block, restore backup if needed
Mon May 10 18:42:22 2010
Aborting crash recovery due to slave death, attempting serial crash recovery
Mon May 10 18:42:22 2010
Beginning crash recovery of 1 threads
Mon May 10 18:42:22 2010
Started redo scan
Mon May 10 18:42:23 2010
Completed redo scan
 190887 redo blocks read, 5052 data blocks need recovery
Mon May 10 18:42:23 2010
Started redo application at
 Thread 1: logseq 123840, block 13872
Mon May 10 18:42:23 2010
Recovery of Online Redo Log: Thread 1 Group 2 Seq 123840 Reading mem 0
  Mem# 0 errs 0: /data/cache2/app/oracle/oradata/npm/redo02.log
Mon May 10 18:42:23 2010
Recovery of Online Redo Log: Thread 1 Group 3 Seq 123841 Reading mem 0
  Mem# 0 errs 0: /data/cache2/app/oracle/oradata/npm/redo03.log
Mon May 10 18:42:23 2010
Recovery of Online Redo Log: Thread 1 Group 1 Seq 123842 Reading mem 0
  Mem# 0 errs 0: /data/cache2/app/oracle/oradata/npm/redo01.log
RECOVERY OF THREAD 1 STUCK AT BLOCK 121 OF FILE 14
Mon May 10 18:42:25 2010
Aborting crash recovery due to error 1172
Mon May 10 18:42:25 2010
Errors in file /data/cache2/app/oracle/admin/npm/udump/npm_ora_24979.trc:
ORA-01172: recovery of thread 1 stuck at block 121 of file 14
ORA-01151: use media recovery to recover block, restore backup if needed
ORA-1172 signalled during: alter database open...

2、查看file 14对应的数据文件，发现正好是undo的数据文件
SQL> select file#,name from v$datafile where file#=&n;

14 /data/cache1/oradata/npm/undotbs02.dbf

3、检查该数据文件是否有坏块，未发现坏块
[oracle@NPM-DB ~]$ dbv file=/data/cache1/oradata/npm/undotbs02.dbf blocksize=8192

DBVERIFY: Release 10.2.0.1.0 - Production on Mon May 10 18:54:16 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

DBVERIFY - Verification starting : FILE = /data/cache1/oradata/npm/undotbs02.dbf

DBVERIFY - Verification complete

Total Pages Examined         : 524288
Total Pages Processed (Data) : 0
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 524288
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 0
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 3383142476 (1.3383142476)
[oracle@NPM-DB ~]$

4、由于没有坏块且redo没有丢失，故只需要进行介质恢复就行
SQL> recover datafile 14;
Media recovery complete.
SQL> alter database open;

Database altered.

数据库成功启动，