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: 0x28b110 Reread of blocknum=134641, file=/oradata/omsdb1/OMS_DATA12.dbf. found same corrupt data11 Reread of blocknum=134641, file=/oradata/omsdb1/OMS_DATA12.dbf. found same corrupt data12 Reread of blocknum=134641, file=/oradata/omsdb1/OMS_DATA12.dbf. found same corrupt data13 Reread of blocknum=134641, file=/oradata/omsdb1/OMS_DATA12.dbf. found same corrupt data14 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: 0x04c20df113 last change scn: 0x0000.189926c6 seq: 0x2 flg: 0x0414 spare1: 0x0 spare2: 0x0 spare3: 0x015 consistency value in tail: 0x26c5280216 check value in block header: 0xd25a17 computed block checksum: 0x28b118 19 20 21 DBVERIFY - Verification complete22 23 Total Pages Examined : 393216024 Total Pages Processed (Data) : 16419925 Total Pages Failing (Data) : 026 Total Pages Processed (Index): 900327 Total Pages Failing (Index): 028 Total Pages Processed (Other): 375730829 Total Pages Processed (Seg) : 030 Total Pages Failing (Seg) : 031 Total Pages Empty : 164932 Total Pages Marked Corrupt : 133 Total Pages Influx : 134 Total Pages Encrypted : 035 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 Datafiles10 =================11 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN12 ---- ------ -------------- ------------ --------------- ----------13 19 FAILED 0 1649 3932160 421804976 14 File Name: /oradata/omsdb1/OMS_DATA12.dbf15  Block Type Blocks Failing Blocks Processed16 ---------- -------------- ----------------17 Data 0 164199 18 Index 0 9001 19 Other 1 3757311 20 21 validate found one or more corrupt blocks22 See trace file /u01/app/oracle/diag/rdbms/omsdb1/omsdb1/trace/omsdb1_ora_20078.trc for details23 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=OMSADMIN2 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_LOGINFOR2 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 begin10 for cursor_lob in (select rowid rid, &lob_column from &table_owner.&table_with_lob) loop11 begin12 n:=dbms_lob.instr(cursor_lob.&lob_column,hextoraw(‘889911‘)) ;13 exception14 when ora1578 then15 bad_rows := bad_rows + 1;16 insert into bad_rows values(cursor_lob.rid,1578);17 commit;18 when others then19 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_CODE1 AABIz+AATAAAf2jAAB 15782 AABIz+AATAAAf2zAAA -15553 AABIz+AATAAAf2zAAB -15554 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