ORA-01578 Oracle block corruption resolution

2010 年 9 月 2 日 — chensq

电信销账系统报ORA-01578，表明数据库有坏块。

ORA-01578: ORACLE data block corrupted (file # 500, block # 31167)
ORA-01110: data file 500: ’/dev/vx/rdsk/xzavg02/lv_xz1_02_41′

对该文件执行rman backup validate命令，发现该确实标志为坏块。

RMAN> backup validate datafile 500;

Starting backup at 31-AUG-10
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1476 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00500 name=/dev/vx/rdsk/xzavg02/lv_xz1_02_41
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 31-AUG-10

RECID      STAMP COPY_RECID COPY_STAMP  FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# MAR CORRUPTIO
———- ———- ———- ———- —— ———- ———- —————— — ———
1  705719131        355  705719131    500      31167          1                  0 NO  FRACTURED

关于数据块损坏的标志解释

  • ALL ZERO – Block header on disk contained only zeros. The block may be valid if it was never filled and if it is in an Oracle7 file. The buffer will be reformatted to the Oracle8 standard for an empty block.
  • FRACTURED – Block header looks reasonable, but the front and back of the block are different versions.
  • CHECKSUM – optional check value shows that the block is not self-consistent. It is impossible to determine exactly why the check value fails, but it probably fails because sectors in the middle of the block are from different versions.
  • CORRUPT – Block is wrongly identified or is not a data block (for example, the data block address is missing)
  • LOGICAL – Specifies the range is for logically corrupt blocks. CORRUPTION_CHANGE# will have a nonzero value.

根据这个描述，这个块应该是块头、尾不匹配。再查看这个块所对应的数据库对象信息：

select *
from dba_extents
where file_id = 500
and 31167 between block_id and block_id + blocks – 1

OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
INTERFACE AI_ACCT_ITEM_06_BAK  TABLE INTERFACE_TBS 4 500 30729 5242880 640 500

这个查询在数据量大的环境，会很慢。其实，还有一种快捷的方法：

www.ads1st.com > select * from v$bh where file#=500 and block#=31167 ;

FILE#     BLOCK#     CLASS# STATUS              XNC FORCED_READS FORCED_WRITES LOCK_ELEMENT_ADD LOCK_ELEMENT_NAME LOCK_ELEMENT_CLASS D T P S D N       OBJD TS#
—— ———- ———- ———— ———- ———— ————- —————- —————– —————— – - – - – - ———- ———-
500      31167          1 xcur                  0            0             0 00                                                    N N N N N N    2465172 71

从v$bh视图查询这个块的信息，可以得到OBJD，即这个对象的对象编号，再从dba_objects中查找相应对象的信息，比从dba_extents视图查询，要快很多。

SQL> select owner,object_type,object_name from dba_objects where object_id=2465172;

OWNER        OBJECT_TYPE        OBJECT_NAME
———— —————— ——————————
INTERFACE    TABLE              AI_ACCT_ITEM_06_BAK

dump数据块，没有报告异常：

www.ads1st.com > alter system dump datafile 500 block 31167;

*** 2010-08-31 16:52:49.998
*** SESSION ID:(2192.3913) 2010-08-31 16:52:49.990
Start dump data blocks tsn: 71 file#: 500 minblk 31167 maxblk 31167
buffer tsn: 71 rdba: 0×7d0079bf (500/31167)
scn: 0×0000.00000000 seq: 0xff flg: 0×04 tail: 0×000006ff
frmt: 0×02 chkval: 0×1cd6 type: 0×06=trans data
Block header dump:  0×7d0079bf
Object id on Block? Y
seg/obj: 0×259d94  csc: 0×9f6.e2fa6a37  itc: 2  flg: E  typ: 1 – DATA
brn: 0  bdba: 0×7d00780f ver: 0×01
inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0×01   0×0009.014.0016076c  0×27c01812.60e6.16  –U-   69  fsc 0×11c8.e2fa6b7f
0×02   0×0004.011.0020960a  0×0141230b.f2fa.24  C-U-    0  scn 0×09f6.e2b41c7c

data_block_dump,data header at 0×110297064
===============
tsiz: 0×1f98
hsiz: 0xe0
pbl: 0×110297064
bdba: 0×7d0079bf
76543210
flag=——–
ntab=1
nrow=103
frre=0
fsbo=0xe0
fseo=0×43f
avsp=0×3e8
tosp=0×163a
0xe:pti[0]      nrow=103        offs=0
0×12:pri[0]     sfll=1
0×14:pri[1]     sfll=-1
0×16:pri[2]     offs=0×1eca
0×18:pri[3]     offs=0×1e86
0×1a:pri[4]     offs=0×1e41
0×1c:pri[5]     offs=0×1dfd
0×1e:pri[6]     offs=0×1db9
0×20:pri[7]     offs=0×1d75
0×22:pri[8]     offs=0×1d31
0×24:pri[9]     offs=0xcbd
0×26:pri[10]    offs=0xd01
0×28:pri[11]    offs=0xd45
0×2a:pri[12]    offs=0xd89
0×2c:pri[13]    offs=0xdcd
0×2e:pri[14]    offs=0xe11
0×30:pri[15]    offs=0xe55
0×32:pri[16]    offs=0xe99
0×34:pri[17]    offs=0xeda
0×36:pri[18]    offs=0xf1e
0×38:pri[19]    offs=0xf62
0×3a:pri[20]    offs=0xfa6
0×3c:pri[21]    offs=0xfeb
0×3e:pri[22]    offs=0×1030
0×40:pri[23]    offs=0×1074
0×42:pri[24]    offs=0×10b8
0×44:pri[25]    offs=0×10fc
0×46:pri[26]    offs=0×1140
0×48:pri[27]    offs=0×1181
0×4a:pri[28]    offs=0×11c5
0×4c:pri[29]    offs=0×1209
0×4e:pri[30]    offs=0×124d
0×50:pri[31]    offs=0×1291
0×52:pri[32]    offs=0×12d5
0×54:pri[33]    offs=0×131a
0×56:pri[34]    offs=0×135e
0×58:pri[35]    offs=0×13a2
0×5a:pri[36]    offs=0×13e6
0×5c:pri[37]    offs=0×142a
0×5e:pri[38]    offs=0×146e
0×60:pri[39]    offs=0×14b2
0×62:pri[40]    offs=0×14f6
0×64:pri[41]    offs=0×153a
0×66:pri[42]    offs=0×157f
0×68:pri[43]    offs=0×15c3
0×6a:pri[44]    offs=0×1607
0×6c:pri[45]    offs=0×164b
0×6e:pri[46]    offs=0×1690
0×70:pri[47]    offs=0×16d4
0×72:pri[48]    offs=0×1718
0×74:pri[49]    offs=0×175c
0×76:pri[50]    offs=0×17a0
0×78:pri[51]    offs=0×17e4
0×7a:pri[52]    offs=0×1827
0×7c:pri[53]    offs=0×186b
0×7e:pri[54]    offs=0×18af
0×80:pri[55]    offs=0×18f3
0×82:pri[56]    offs=0×1936
0×84:pri[57]    offs=0×197b
0×86:pri[58]    offs=0×19bf
0×88:pri[59]    offs=0×1a03
0×8a:pri[60]    offs=0×1a47
0×8c:pri[61]    offs=0×1a8b
0×8e:pri[62]    offs=0×1acf
0×90:pri[63]    offs=0×1b13
0×92:pri[64]    offs=0×1b56
0×94:pri[65]    offs=0×1b9a
0×96:pri[66]    offs=0×1bdd
0×98:pri[67]    offs=0×1c21
0×9a:pri[68]    offs=0×1c65
0×9c:pri[69]    offs=0×1ca9
0×9e:pri[70]    offs=0×1ced
0xa0:pri[71]    offs=0×43f
0xa2:pri[72]    offs=0×483
0xa4:pri[73]    offs=0×4c7
0xa6:pri[74]    offs=0×50b
0xa8:pri[75]    offs=0×54f
0xaa:pri[76]    offs=0×593
0xac:pri[77]    offs=0×5d6
0xae:pri[78]    offs=0×61a
0xb0:pri[79]    offs=0×65e
0xb2:pri[80]    offs=0×6a2
0xb4:pri[81]    offs=0×6e6
0xb6:pri[82]    offs=0×72a
0xb8:pri[83]    offs=0×76e
0xba:pri[84]    offs=0×7b2
0xbc:pri[85]    offs=0×7f6
0xbe:pri[86]    offs=0×83a
0xc0:pri[87]    offs=0×87e
0xc2:pri[88]    offs=0×8c2
0xc4:pri[89]    offs=0×906
0xc6:pri[90]    offs=0×94a
0xc8:pri[91]    offs=0×98e
0xca:pri[92]    offs=0×9d2
0xcc:pri[93]    offs=0xa16
0xce:pri[94]    offs=0xa5a
0xd0:pri[95]    offs=0xa9e
0xd2:pri[96]    offs=0xae2
0xd4:pri[97]    offs=0xb25
0xd6:pri[98]    offs=0xb69
0xd8:pri[99]    offs=0xbad
0xda:pri[100]   offs=0xbf1
0xdc:pri[101]   offs=0xc35
0xde:pri[102]   offs=0xc79
block_row_dump:
tab 0, row 2, @0×1eca
tl: 69 fb: –H-FL– lb: 0×0  cc: 13
col  0: [ 5]  c4 5f 3e 26 04
col  1: [ 2]  c1 02
col  2: [ 7]  c6 12 18 01 1a 18 27
col  3: [11]  31 33 33 35 30 36 36 35 35 37 36
col  4: [ 4]  c3 02 0a 0a
col  5: [ 3]  c2 0a 51
col  6: [ 3]  49 30 43
col  7: [ 4]  3d 64 5c 66
col  8: [ 1]  80
col  9: [ 7]  78 6d 09 1e 05 19 16
col 10: [ 1]  32
col 11: [ 3]  31 30 58
col 12: [ 2]  c1 03

上述dump其实是直接从缓冲区读取信息的。为了确保准确，有必要从文件上直接dump:

www.ads1st.com > alter system dump datafile ‘/dev/vx/rdsk/xzavg02/lv_xz1_02_41′ block 31167;

*** 2010-08-31 17:35:42.464
*** SESSION ID:(215.8527) 2010-08-31 17:35:42.454
Start dump data block from file /dev/vx/rdsk/xzavg02/lv_xz1_02_41 minblk 31167 maxblk 31167
FILE HEADER:
Software vsn=153092096=0×9200000, Compatibility Vsn=134217728=0×8000000
Db ID=3562150002=0xd4521872, Db Name=’ACCT’
Activation ID=0=0×0
Control Seq=1958928=0×1de410, File size=327678=0×4fffe
File Number=500, Blksiz=8192, File Type=3 DATA
Dump all the blocks in range:
buffer rdba: 0×7d0079bf (500/31167)
scn: 0×0000.00000000 seq: 0xff flg: 0×04 tail: 0×000006ff
frmt: 0×02 chkval: 0×1cd6 type: 0×06=trans data
Block header dump:  0×7d0079bf
Object id on Block? Y
seg/obj: 0×259d94  csc: 0×9f6.e2fa6a37  itc: 2  flg: E  typ: 1 – DATA
brn: 0  bdba: 0×7d00780f ver: 0×01
inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0×01   0×0009.014.0016076c  0×27c01812.60e6.16  –U-   69  fsc 0×11c8.e2fa6b7f
0×02   0×0004.011.0020960a  0×0141230b.f2fa.24  C-U-    0  scn 0×09f6.e2b41c7c

data_block_dump,data header at 0×110297064
===============
tsiz: 0×1f98
hsiz: 0xe0
pbl: 0×110297064
bdba: 0×7d0079bf
76543210
flag=——–
ntab=1
nrow=103
frre=0
fsbo=0xe0
fseo=0×43f
avsp=0×3e8
tosp=0×163a
0xe:pti[0]      nrow=103        offs=0
0×12:pri[0]     sfll=1
0×14:pri[1]     sfll=-1
0×16:pri[2]     offs=0×1eca
0×18:pri[3]     offs=0×1e86
0×1a:pri[4]     offs=0×1e41
0×1c:pri[5]     offs=0×1dfd
0×1e:pri[6]     offs=0×1db9
0×20:pri[7]     offs=0×1d75
0×22:pri[8]     offs=0×1d31
0×24:pri[9]     offs=0xcbd
0×26:pri[10]    offs=0xd01
0×28:pri[11]    offs=0xd45
0×2a:pri[12]    offs=0xd89
0×2c:pri[13]    offs=0xdcd
0×2e:pri[14]    offs=0xe11
0×30:pri[15]    offs=0xe55
0×32:pri[16]    offs=0xe99
0×34:pri[17]    offs=0xeda
0×36:pri[18]    offs=0xf1e
0×38:pri[19]    offs=0xf62
0×3a:pri[20]    offs=0xfa6
0×3c:pri[21]    offs=0xfeb
0×3e:pri[22]    offs=0×1030
0×40:pri[23]    offs=0×1074
0×42:pri[24]    offs=0×10b8
0×44:pri[25]    offs=0×10fc
0×46:pri[26]    offs=0×1140
0×48:pri[27]    offs=0×1181
0×4a:pri[28]    offs=0×11c5
0×4c:pri[29]    offs=0×1209
0×4e:pri[30]    offs=0×124d
0×50:pri[31]    offs=0×1291
0×52:pri[32]    offs=0×12d5
0×54:pri[33]    offs=0×131a
0×56:pri[34]    offs=0×135e
0×58:pri[35]    offs=0×13a2
0×5a:pri[36]    offs=0×13e6
0×5c:pri[37]    offs=0×142a
0×5e:pri[38]    offs=0×146e
0×60:pri[39]    offs=0×14b2
0×62:pri[40]    offs=0×14f6
0×64:pri[41]    offs=0×153a
0×66:pri[42]    offs=0×157f
0×68:pri[43]    offs=0×15c3
0×6a:pri[44]    offs=0×1607
0×6c:pri[45]    offs=0×164b
0×6e:pri[46]    offs=0×1690
0×70:pri[47]    offs=0×16d4

……

用DBV检查该文件，有报坏块：

[oracle@/oracle$]dbv file=/dev/vx/rdsk/xzavg02/lv_xz1_02_41

DBVERIFY: Release 9.2.0.8.0 – Production on Wed Sep 1 09:12:56 2010

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
DBV-00103: Specified BLOCKSIZE (2048) differs from actual (8192)
[oracle@/oracle$]dbv file=/dev/vx/rdsk/xzavg02/lv_xz1_02_41 blocksize=8192

DBVERIFY: Release 9.2.0.8.0 – Production on Wed Sep 1 09:13:08 2010

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

DBVERIFY – Verification starting : FILE = /dev/vx/rdsk/xzavg02/lv_xz1_02_41

DBV-00200: Block, dba 2097183167, already marked corrupted
DBVERIFY – Verification complete

Total Pages Examined         : 327678
Total Pages Processed (Data) : 260961
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 64164
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 1923
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 630
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Highest block SCN            : 11258821848943 (2621.1712566127)

但是用bbed检查，居然checksum一致：

BBED> set block 31167
BLOCK#          31167

BBED> sum
Check value for File 0, Block 31167:
current = 0×1cd6, required = 0×1cd6

为了确保其他文件没有坏块，于是对该表空间执行rman backup validate 检查，没有发现其他错误：

RMAN> backup validate tablespace INTERFACE_TBS;

Starting backup at 31-AUG-10
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=49 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00446 name=/dev/vx/rdsk/xzavg08/lv_xz1_08_155
input datafile fno=00491 name=/dev/vx/rdsk/xzavg01/lv_xz1_08_160
input datafile fno=00268 name=/dev/vx/rdsk/xzavg07/lv_xz1_08_119
input datafile fno=00308 name=/dev/vx/rdsk/xzavg07/lv_xz1_04_105
input datafile fno=00364 name=/dev/vx/rdsk/xzavg08/lv_xz1_08_144
input datafile fno=00373 name=/dev/vx/rdsk/xzavg08/lv_xz1_08_146
input datafile fno=00485 name=/dev/vx/rdsk/xzavg02/lv_xz1_04_201
input datafile fno=00490 name=/dev/vx/rdsk/xzavg02/lv_xz1_04_202
input datafile fno=00493 name=/dev/vx/rdsk/xzavg02/lv_xz1_04_203
input datafile fno=00501 name=/dev/vx/rdsk/xzavg02/lv_xz1_04_206
input datafile fno=00734 name=/dev/vx/rdsk/xzavg07/lv_xz1_04_291
input datafile fno=00763 name=/dev/vx/rdsk/xzavg07/lv_xz1_04_304
input datafile fno=00792 name=/dev/vx/rdsk/xzavg07/lv_xz1_04_326
input datafile fno=00905 name=/dev/vx/rdsk/xzavg07/lv_xz1_04_391
input datafile fno=00906 name=/dev/vx/rdsk/xzavg07/lv_xz1_04_392
input datafile fno=00924 name=/dev/vx/rdsk/xzavg08/lv_xz1_04_397
input datafile fno=00926 name=/dev/vx/rdsk/xzavg08/lv_xz1_04_398
input datafile fno=00115 name=/dev/vx/rdsk/xzavg01/lv_xz1_04_014
input datafile fno=00116 name=/dev/vx/rdsk/xzavg01/lv_xz1_04_015
input datafile fno=00117 name=/dev/vx/rdsk/xzavg01/lv_xz1_04_016
input datafile fno=00118 name=/dev/vx/rdsk/xzavg01/lv_xz1_04_017
input datafile fno=00119 name=/dev/vx/rdsk/xzavg01/lv_xz1_04_018
input datafile fno=00156 name=/dev/vx/rdsk/xzavg01/lv_xz1_04_038
input datafile fno=00160 name=/dev/vx/rdsk/xzavg01/lv_xz1_04_040
input datafile fno=00174 name=/dev/vx/rdsk/xzavg01/lv_xz1_04_046
input datafile fno=00218 name=/dev/vx/rdsk/xzavg01/lv_xz1_04_060
input datafile fno=00233 name=/dev/vx/rdsk/xzavg01/lv_xz1_04_070
input datafile fno=00258 name=/dev/vx/rdsk/xzavg07/lv_xz1_04_088
input datafile fno=00259 name=/dev/vx/rdsk/xzavg07/lv_xz1_04_089
input datafile fno=00277 name=/dev/vx/rdsk/xzavg07/lv_xz1_08_120
input datafile fno=00346 name=/dev/vx/rdsk/xzavg07/lv_xz1_04_113
input datafile fno=00492 name=/dev/vx/rdsk/xzavg02/lv_xz1_02_36
input datafile fno=00496 name=/dev/vx/rdsk/xzavg02/lv_xz1_02_37
input datafile fno=00497 name=/dev/vx/rdsk/xzavg02/lv_xz1_02_38
input datafile fno=00498 name=/dev/vx/rdsk/xzavg02/lv_xz1_02_39
input datafile fno=00499 name=/dev/vx/rdsk/xzavg02/lv_xz1_02_40
input datafile fno=00500 name=/dev/vx/rdsk/xzavg02/lv_xz1_02_41
input datafile fno=00798 name=/dev/vx/rdsk/xzavg01/lv_xz1_02_282
input datafile fno=00835 name=/dev/vx/rdsk/xzavg01/lv_xz1_02_290
input datafile fno=00898 name=/dev/vx/rdsk/xzavg08/lv_xz1_02_325
input datafile fno=00849 name=/dev/vx/rdsk/xzavg06/lv_xz1_02_304
input datafile fno=00850 name=/dev/vx/rdsk/xzavg06/lv_xz1_02_305
input datafile fno=00913 name=/dev/vx/rdsk/xzavg08/lv_xz1_02_333
input datafile fno=00921 name=/dev/vx/rdsk/xzavg08/lv_xz1_02_338
channel ORA_DISK_1: backup set complete, elapsed time: 00:10:45
Finished backup at 31-AUG-10

www.ads1st.com >select * from v$backup_corruption

1599  728402344  728401034      67629          1    500      31167          1                  0 NO  FRACTURED
1600  728499253  728499240      67702          1    500      31167          1                  0 NO  FRACTURED
1601  728499930  728499916      67703          1    500      31167          1                  0 NO  FRACTURED
1602  728501255  728500611      67704          1    500      31167          1                  0 NO  FRACTURED

接下来就是偿试修复了：

www.ads1st.com > create table  INTERFACE.AI_ACCT_ITEM_06_BAK_bak tablespace INTERFACE_TBS as select * from INTERFACE.AI_ACCT_ITEM_06_BAK;

Table created.

Elapsed: 00:00:08.08
www.ads1st.com> truncate table INTERFACE.AI_ACCT_ITEM_06_BAK;

Table truncated.

Elapsed: 00:00:05.94
www.ads1st.com> insert into INTERFACE.AI_ACCT_ITEM_06_BAK select * from INTERFACE.AI_ACCT_ITEM_06_BAK_bak  ;

418591 rows created.

Elapsed: 00:00:13.39
www.ads1st.com> commit;

Commit complete.

Elapsed: 00:00:00.02

www.ads1st.com> select *
2    from v$bh
3   where file# = 500
4     and block# = 31167;

FILE#     BLOCK#     CLASS# STATUS              XNC FORCED_READS FORCED_WRITES LOCK_ELEMENT_ADD LOCK_ELEMENT_NAME LOCK_ELEMENT_CLASS D T P S D N       OBJD        TS#
—— ———- ———- ———— ———- ———— ————- —————- —————– —————— – - – - – - ———- ———-
500      31167          1 xcur                  0            0             0 00                                                    Y N N N N N    3601755 71

Elapsed: 00:00:28.84

Related Posts
Posted in Aix, Truble shooting. Tags: , , , , . 这篇文章上的评论的 RSS feed. TrackBack URL.

留下评论

« _lm_file_affinity参数
Oracle 9i tracing events »