Corrupt block relative dba: 0x04c20df1

alert日志报以下提示：

 1 Corrupt block relative dba: 0x04c20df1 (file 19, block 134641)
 2 Fractured block found during backing up datafile
 3 Data in bad block:
 4  type: 40 format: 2 rdba: 0x04c20df1
 5  last change scn: 0x0000.189926c6 seq: 0x2 flg: 0x04
 6  spare1: 0x0 spare2: 0x0 spare3: 0x0
 7  consistency value in tail: 0x26c52802
 8  check value in block header: 0xd25a
 9  computed block checksum: 0x28b1
10 Reread of blocknum=134641, file=/oradata/omsdb1/OMS_DATA12.dbf. found same corrupt data
11 Reread of blocknum=134641, file=/oradata/omsdb1/OMS_DATA12.dbf. found same corrupt data
12 Reread of blocknum=134641, file=/oradata/omsdb1/OMS_DATA12.dbf. found same corrupt data
13 Reread of blocknum=134641, file=/oradata/omsdb1/OMS_DATA12.dbf. found same corrupt data
14 Reread of blocknum=134641, file=/oradata/omsdb1/OMS_DATA12.dbf. found same corrupt data

根据上述信息得知19号数据文件的134641为坏块，可以使用DBV工具或者RMAN来检查坏块信息

dbv:

 1 [oracle@ASZAAS-OMS01 ~]$ dbv file=/oradata/omsdb1/OMS_DATA12.dbf
 2 
 3 DBVERIFY: Release 11.2.0.4.0 - Production on Fri Jun 29 10:57:30 2018
 4 
 5 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
 6 
 7 DBVERIFY - Verification starting : FILE = /oradata/omsdb1/OMS_DATA12.dbf
 8 Page 134641 is influx - most likely media corrupt
 9 Corrupt block relative dba: 0x04c20df1 (file 19, block 134641)
10 Fractured block found during dbv: 
11 Data in bad block:
12  type: 40 format: 2 rdba: 0x04c20df1
13  last change scn: 0x0000.189926c6 seq: 0x2 flg: 0x04
14  spare1: 0x0 spare2: 0x0 spare3: 0x0
15  consistency value in tail: 0x26c52802
16  check value in block header: 0xd25a
17  computed block checksum: 0x28b1
18 
19 
20 
21 DBVERIFY - Verification complete
22 
23 Total Pages Examined         : 3932160
24 Total Pages Processed (Data) : 164199
25 Total Pages Failing   (Data) : 0
26 Total Pages Processed (Index): 9003
27 Total Pages Failing   (Index): 0
28 Total Pages Processed (Other): 3757308
29 Total Pages Processed (Seg)  : 0
30 Total Pages Failing   (Seg)  : 0
31 Total Pages Empty            : 1649
32 Total Pages Marked Corrupt   : 1
33 Total Pages Influx           : 1
34 Total Pages Encrypted        : 0
35 Highest block SCN            : 421782991 (0.421782991)

rman:

 1 RMAN> backup validate datafile 19;
 2 
 3 Starting backup at 29-JUN-18
 4 using channel ORA_DISK_1
 5 channel ORA_DISK_1: starting full datafile backup set
 6 channel ORA_DISK_1: specifying datafile(s) in backup set
 7 input datafile file number=00019 name=/oradata/omsdb1/OMS_DATA12.dbf
 8 channel ORA_DISK_1: backup set complete, elapsed time: 00:08:16
 9 List of Datafiles
10 =================
11 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
12 ---- ------ -------------- ------------ --------------- ----------
13 19   FAILED 0              1649         3932160         421804976 
14   File Name: /oradata/omsdb1/OMS_DATA12.dbf
15   Block Type Blocks Failing Blocks Processed
16   ---------- -------------- ----------------
17   Data       0              164199          
18   Index      0              9001            
19   Other      1              3757311         
20 
21 validate found one or more corrupt blocks
22 See trace file /u01/app/oracle/diag/rdbms/omsdb1/omsdb1/trace/omsdb1_ora_20078.trc for details
23 Finished backup at 29-JUN-18

可以根据文件号和块号查出损坏的是对象,表还是LOB segment

1 select tablespace_name,segment_type,owner,segment_name from dba_extents where file_id=19 and 134641 between block_id AND block_id + blocks - 1;

19是文件号,134641是block号

如果是对象，可以重建:

alter index indexname rebuild

如果是表，可以使用10231事件忽略坏块，然后使用CTAS方式重建表最后rename table，别忘记rebuild index

alter session SET EVENTS '10231 trace name context forever,level 10';
create table tab_new as select * from tab;
rename tab to tab_bak;
rename tab_new to new;
alter index indexname rebuild;
alter session SET EVENTS '10231 trace name context off';

如果损坏的是LOB segment,先找出segment信息:

select owner, segment_name, segment_type from dba_extents where file_id = 19 and 134641 between block_id and block_id + blocks - 1;

输出如下:

1 owner=OMSADMIN
2 segment_name=SYS_LOB0000087489C00007$$
3 segment_type=LOBSEGMENT

找到表名和LOB字段：

1 select table_name, column_name from dba_lobs where segment_name = 'SYS_LOB0000087489C00007$$' and owner = 'OMSADMIN';

输出如下：

1 table_name = OMS_LOGINFOR
2 column_name = CONTENT

找到坏块的bad rowid,使用以下plsql脚本:

 1 create table bad_rows (row_id ROWID,oracle_error_code number);
 2 
 3 declare
 4 n number;
 5 error_code number;
 6 bad_rows number := 0;
 7 ora1578 EXCEPTION;
 8 PRAGMA EXCEPTION_INIT(ora1578, -1578);
 9 begin
10 for cursor_lob in (select rowid rid, &lob_column from &table_owner.&table_with_lob) loop
11 begin
12 n:=dbms_lob.instr(cursor_lob.&lob_column,hextoraw('889911')) ;
13 exception
14 when ora1578 then
15 bad_rows := bad_rows + 1;
16 insert into bad_rows values(cursor_lob.rid,1578);
17 commit;
18 when others then
19 error_code:=SQLCODE;
20 bad_rows := bad_rows + 1;
21 insert into bad_rows values(cursor_lob.rid,error_code);
22 commit;
23 end;
24 end loop;
25 dbms_output.put_line('Total Rows identified with errors in LOB column: '||bad_rows);
26 end;
27 /

Enter value for lob_column: CONTENT
Enter value for table_owner: OMSADMIN
Enter value for table_with_lob: OMS_LOGINFOR
可以查询bad rowid

select * from bad_rows;
       ROW_ID    ORACLE_ERROR_CODE
1    AABIz+AATAAAf2jAAB    1578
2    AABIz+AATAAAf2zAAA    -1555
3    AABIz+AATAAAf2zAAB    -1555
4    AABIz+AATAAAf7kAAA    -1555

更新空LOB字段来避免ORA-1578,ORA-26040,如果是CLOB类型，将empty_blob()改为empty_clob()

update &table_owner.&table_with_lob set &lob_column = empty_clob() where rowid in (select row_id from bad_rows);

将bad rowid lob块移到其他表空间:

1 alter table &table_owner.&table_with_lob move LOB (&lob_column) store as (tablespace &tablespace_name);

最后重建索引rebuild index