Oracle坏块ORA-01578处理案例。
应用在做报表查询时报ORA-01578错误,导致做报表不成功。

1 处理过程
1.1 检查后台数据库日志

检查数据库实例日志$ORACL_BASE/admin/jj/bdump/alert_stjj.log,从8月23日零晨4点开始就出现ORA-01578错误
Sun Aug 23 04:18:59 2009
ARC0: Evaluating archive log 4 thread 1 sequence 4924
ARC0: Beginning to archive log 4 thread 1 sequence 4924
Creating archive destination LOG_ARCHIVE_DEST_1: '/oraarch/arch/1_4924.dbf'
ARC0: Completed archiving log 4 thread 1 sequence 4924
...skipping...
ORA-12012: error on auto execute of job 183
ORA-01578: ORACLE data block corrupted (file # 13, block # 222840)
ORA-01110: data file 13: '/ora9idata01/oradata/jj/VIO_DATA_02.dbf'
ORA-06512: at "admin.PRO_GX", line 8
ORA-06512: at line 1

2.2 检查坏块所对应的对象
SQL> select segment_type,owner||'.'||segment_name from dba_extents where file_id=13 and 222840 between block_id and block_id+blocks-1;
SEGMENT_TYPE
------------------
OWNER||'.'||SEGMENT_NAME
--------------------------------------------------------------------
TABLE
admin.B_WF

file_id和block 号为上面错误日志中出现的具体值

从上面结果可以看出坏快对象为table,表名为admin下的B_WF表
SQL> select count(*) from admin.B_WF;
select count(*) from admin.B.WF
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 13, block # 222840)
ORA-01110: data file 13: '/ora9idata01/oradata/jj/VIO_DATA_02.dbf'
通过查询，对表B_WF做dml操作都不成功。

1.3 检查最近几天的逻辑备份
22号正常
exporting table B_WF 3148490 rows exported
23号开始B_WF表导出就报ORA-01578错误
exporting table B_WF
EXP-00056: ORACLE error 1578 encountered
ORA-01578: ORACLE data block corrupted (file # 13, block # 222840)
ORA-01110: data file 13: '/ora9idata01/oradata/jj/VIO_DATA_02.dbf'

1.4 检查最近的物理备份和归档日志备份
通过查看备份日志和归档日志,上周六的全备成功，每天的归档日志备份成功。

1.5 用rman检查对应数据文件的坏块

[oracle@p550:/oracle/app/oracle/]$rman target /
Recovery Manager: Release 9.2.0.8.0 - 64bit Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: STJJ (DBID=433032791)

RMAN> backup validate datafile 13;
Starting backup at 2009-08-26:20:09:03
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=20 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00013 name=/ora9idata01/oradata/jj/VIO_DATA_02.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:55
Finished backup at 2009-08-26:20:11:58

SQL> select * from v$database_block_corruption where file#=13;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
13 222840 1 0 FRACTURED

通过上面的检查，用逻辑备份来恢复是不可取的，会丢失8.23到现在的数据，
只能采取物理备份+9i新特性blockrecover来恢复。

1.6 恢复8.22到8.26号的全部归档日志
RMAN>
restore archivelog time between "to_date('2009-08-22 00:00:00','yyyy-mm-dd hh24:mi:ss')"
and "to_date('2009-08-26 23:59:59','yyyy-mm-dd hh24:mi:ss')" ;

Starting restore at 2009-08-26:20:21:26
using channel ORA_DISK_1
archive log thread 1 sequence 5071 is already on disk as file /oraarch/arch/1_5071.dbf
archive log thread 1 sequence 5072 is already on disk as file /oraarch/arch/1_5072.dbf
archive log thread 1 sequence 5073 is already on disk as file /oraarch/arch/1_5073.dbf
archive log thread 1 sequence 5074 is already on disk as file /oraarch/arch/1_5074.dbf
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4892
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4893
channel ORA_DISK_1: restored backup piece 1
piece handle=/orarman/arch/al_114_1_695520004 tag=TAG20090822T000003 params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4894
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4895
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4896
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4897
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4898
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4899
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4900
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4901
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4902
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4903
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4904
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4905
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4906
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4907
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4908
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4909
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4910
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4911
channel ORA_DISK_1: restored backup piece 1
piece handle=/orarman/arch/al_116_1_695606407 tag=TAG20090823T000007 params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4912
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4913
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4914
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4915
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4916
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4917
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4918
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4919
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4920
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4921
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4922
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4923
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4924
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4925
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4926
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4927
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4928
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4929
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4930
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4931
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4932
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4933
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4934
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4935
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4936
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4937
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4938
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4939
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4940
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4941
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4942
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4943
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4944
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4945
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4946
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4947
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4948
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4949
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4950
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4951
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4952
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4953
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4954
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4955
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4956
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4957
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4958
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4959
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4960
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4961
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4962
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4963
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4964
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4965
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4966
channel ORA_DISK_1: restored backup piece 1
piece handle=/orarman/arch/al_117_1_695692809 tag=TAG20090824T000008 params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4967
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4968
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4969
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4970
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4971
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4972
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4973
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4974
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4975
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4976
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4977
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4978
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4979
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4980
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4981
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4982
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4983
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4984
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4985
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4986
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4987
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4988
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4989
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4990
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4991
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4992
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4993
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4994
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4995
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4996
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4997
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4998
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4999
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5000
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5001
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5002
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5003
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5004
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5005
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5006
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5007
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5008
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5009
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5010
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5011
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5012
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5013
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5014
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5015
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5016
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5017
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5018
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5019
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5020
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5021
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5022
channel ORA_DISK_1: restored backup piece 1
piece handle=/orarman/arch/al_118_1_695693046 tag=TAG20090824T000008 params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5023
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5024
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5025
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5026
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5027
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5028
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5029
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5030
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5031
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5032
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5033
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5034
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5035
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5036
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5037
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5038
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5039
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5040
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5041
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5042
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5043
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5044
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5045
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5046
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5047
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5048
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5049
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5050
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5051
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5052
channel ORA_DISK_1: restored backup piece 1
piece handle=/orarman/arch/al_119_1_695779208 tag=TAG20090825T000007 params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5053
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5054
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5055
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5056
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5057
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5058
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5059
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5060
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5061
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5062
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5063
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5064
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5065
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5066
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5067
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5068
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5069
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5070
channel ORA_DISK_1: restored backup piece 1
piece handle=/orarman/arch/al_120_1_695865609 tag=TAG20090826T000008 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 2009-08-26:20:32:20

1.7 启动数据库到mount状态，做blockrecover恢复
RMAN> shutdown immediate;
database closed
database dismounted
Oracle instance shut down

RMAN> startup mount;

RMAN> blockrecover datafile 13 block 222840 from backupset;

Starting blockrecover at 2009-08-26:20:34:35
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=12 devtype=DISK
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00013
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/orarman/rman/full_115_1_695534417 tag=TAG20090822T040017 params=NULL
channel ORA_DISK_1: block restore complete
starting media recovery
media recovery complete
Finished blockrecover at 2009-08-26:20:58:17

恢复成功，整个过程用了一小时左右。

1.8 检查数据和验证坏块是否消失
SQL> select count(*) from admin.B_WF;
COUNT(*)
----------
3160945

RMAN> backup validate datafile 13;
Starting backup at 2009-08-26:21:01:18
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=18 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00013 name=/ora9idata01/oradata/jj/VIO_DATA_02.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:03:35
Finished backup at 2009-08-26:21:04:53

SQL> select * from v$database_block_corruption where file#=13;
no rows selected

对这个表的操作能正常执行，坏记录已经不存在，通过对比8.22号的导出记录到现在增加了12455条记录，如果用exp恢复的话，要丢失12455条记录，那后果不可想象。
最后，对数据库再手工做次物理全备。

2 恢复成功总结和心得

这次能成功恢复主要是有rman物理备份和归档备份，和9i新特性使用blockrecover来修复数据库中的坏块，减少恢复时间。前提必须有成功的rman物理备份。
所以制定一个良好的备份策略是非常重要的,尽量有可能的帮客户搭建物理备份和逻辑备份,搭建好后，还要尽力去检查备份日志是否成功,使损失降低到最低点。