Corrupt block relative dba: 0x04c20df1 (file 19, block 134641)

 Fractured block found during backing up datafile

 Data in bad block:

  type: 40 format: 2 rdba: 0x04c20df1

  last change scn: 0x0000.189926c6 seq: 0x2 flg: 0x04

  spare1: 0x0 spare2: 0x0 spare3: 0x0

  consistency value in tail: 0x26c52802

  check value in block header: 0xd25a

  computed block checksum: 0x28b1

 Reread of blocknum=134641, file=/oradata/omsdb1/OMS_DATA12.dbf. found same corrupt data

 Reread of blocknum=134641, file=/oradata/omsdb1/OMS_DATA12.dbf. found same corrupt data

 Reread of blocknum=134641, file=/oradata/omsdb1/OMS_DATA12.dbf. found same corrupt data

 Reread of blocknum=134641, file=/oradata/omsdb1/OMS_DATA12.dbf. found same corrupt data

 Reread of blocknum=134641, file=/oradata/omsdb1/OMS_DATA12.dbf. found same corrupt data

 [oracle@ASZAAS-OMS01 ~]$ dbv file=/oradata/omsdb1/OMS_DATA12.dbf

 DBVERIFY: Release 11.2.0.4.0 - Production on Fri Jun 29 10:57:30 2018

 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 DBVERIFY - Verification starting : FILE = /oradata/omsdb1/OMS_DATA12.dbf

 Page 134641 is influx - most likely media corrupt

 Corrupt block relative dba: 0x04c20df1 (file 19, block 134641)

 Fractured block found during dbv:

 Data in bad block:

  type: 40 format: 2 rdba: 0x04c20df1

  last change scn: 0x0000.189926c6 seq: 0x2 flg: 0x04

  spare1: 0x0 spare2: 0x0 spare3: 0x0

  consistency value in tail: 0x26c52802

  check value in block header: 0xd25a

  computed block checksum: 0x28b1

 DBVERIFY - Verification complete

 Total Pages Examined         : 3932160

 Total Pages Processed (Data) : 164199

 Total Pages Failing   (Data) : 0

 Total Pages Processed (Index): 9003

 Total Pages Failing   (Index): 0

 Total Pages Processed (Other): 3757308

 Total Pages Processed (Seg)  : 0

 Total Pages Failing   (Seg)  : 0

 Total Pages Empty            : 1649

 Total Pages Marked Corrupt   : 1

 Total Pages Influx           : 1

 Total Pages Encrypted        : 0

 Highest block SCN            : 421782991 (0.421782991)

 RMAN> backup validate datafile 19;

 Starting backup at 29-JUN-18

 using channel ORA_DISK_1

 channel ORA_DISK_1: starting full datafile backup set

 channel ORA_DISK_1: specifying datafile(s) in backup set

 input datafile file number=00019 name=/oradata/omsdb1/OMS_DATA12.dbf

 channel ORA_DISK_1: backup set complete, elapsed time: 00:08:16

 List of Datafiles

 =================

 File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

 ---- ------ -------------- ------------ --------------- ----------

 19   FAILED 0              1649         3932160         421804976

   File Name: /oradata/omsdb1/OMS_DATA12.dbf

   Block Type Blocks Failing Blocks Processed

   ---------- -------------- ----------------

   Data       0              164199

   Index      0              9001

   Other      1              3757311         

 validate found one or more corrupt blocks

 See trace file /u01/app/oracle/diag/rdbms/omsdb1/omsdb1/trace/omsdb1_ora_20078.trc for details

 Finished backup at 29-JUN-18

 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;

alter index indexname rebuild

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';

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

 owner=OMSADMIN

 segment_name=SYS_LOB0000087489C00007$$

 segment_type=LOBSEGMENT

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

 table_name = OMS_LOGINFOR

 column_name = CONTENT

 create table bad_rows (row_id ROWID,oracle_error_code number);

 declare

 n number;

 error_code number;

 bad_rows number := 0;

 ora1578 EXCEPTION;

 PRAGMA EXCEPTION_INIT(ora1578, -1578);

 begin

 for cursor_lob in (select rowid rid, &lob_column from &table_owner.&table_with_lob) loop

 begin

 n:=dbms_lob.instr(cursor_lob.&lob_column,hextoraw('')) ;

 exception

 when ora1578 then

 bad_rows := bad_rows + 1;

 insert into bad_rows values(cursor_lob.rid,1578);

 commit;

 when others then

 error_code:=SQLCODE;

 bad_rows := bad_rows + 1;

 insert into bad_rows values(cursor_lob.rid,error_code);

 commit;

 end;

 end loop;

 dbms_output.put_line('Total Rows identified with errors in LOB column: '||bad_rows);

 end;

 /

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

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

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