Fixing BLOCK CORRUPTION in oracle

-- Temporary tables creation for identifying the block corruptions

BEGIN
DBMS_REPAIR.ADMIN_TABLES(
        TABLE_NAME      => 'REPAIR_TABLE1',
        TABLE_TYPE      => dbms_repair.repair_table,
        ACTION          => dbms_repair.create_action,
        TABLESPACE              => ''    ); 
END;
/

BEGIN
DBMS_REPAIR.ADMIN_TABLES(
  TABLE_NAME   => 'ORPHAN_TABLE1',
  TABLE_TYPE   => dbms_repair.orphan_table,
  ACTION   => dbms_repair.create_action,
  TABLESPACE   => ''    ); 
END;
/

-- checking and storing in REPAIR_TABLE1

SET SERVEROUTPUT ON
DECLARE CORRUPTED_BLOCKS INT;
BEGIN
CORRUPTED_BLOCKS := 0;
DBMS_REPAIR.CHECK_OBJECT(
    SCHEMA_NAME        => '',
    OBJECT_NAME        => '',
    REPAIR_TABLE_NAME    => 'REPAIR_TABLE1',
    CORRUPT_COUNT        => CORRUPTED_BLOCKS
                    );
DBMS_OUTPUT.PUT_LINE('You have ' || TO_CHAR (CORRUPTED_BLOCKS) || ' corrupt block(s)');
end;
/
You have 1 corrupt block(s)

PL/SQL procedure successfully completed.

SELECT OBJECT_NAME, OBJECT_ID,
CORRUPT_TYPE,REPAIR_DESCRIPTION,
CORRUPT_DESCRIPTION FROM sys.REPAIR_TABLE1;



OBJECT_NAME                     OBJECT_ID CORRUPT_TYPE REPAIR_DESCRIPTION
------------------------------ ---------- ------------ ----------------------------
CORRUPT_DESCRIPTION
-----------------------------------------------------------------------------------
COMPLAINT_HISTORY                   32507         6148 mark block software corrupt



-- fixing / Repairing

SET SERVEROUTPUT ON
DECLARE
FIXED_BLOCKS INT;
BEGIN
FIXED_BLOCKS := 0;
DBMS_REPAIR.FIX_CORRUPT_BLOCKS(
        SCHEMA_NAME              => 'CDRPAY',
        OBJECT_NAME              => 'COMPLAINT_HISTORY',
        OBJECT_TYPE              => DBMS_REPAIR.TABLE_OBJECT,
    REPAIR_TABLE_NAME        => 'REPAIR_TABLE1',
        FIX_COUNT                => FIXED_BLOCKS);

DBMS_OUTPUT.PUT_LINE(TO_CHAR(FIXED_BLOCKS) || ' repaired');
END;
/


SELECT OBJECT_NAME, OBJECT_ID, CORRUPT_TYPE,
REPAIR_DESCRIPTION,CORRUPT_DESCRIPTION,
MARKED_CORRUPT FROM sys.REPAIR_TABLE1;


If u select the complaint_history table now you still get the error ORA-1578.

EXEC DBMS_REPAIR.SKIP_CORRUPT_BLOCKS ('CDRPAY','COMPLAINT_HISTORY',1,1);

PL/SQL procedure successfully completed.



-- related indexes of the table


SET SERVEROUTPUT ON
DECLARE
BROKEN_KEY INT;
BEGIN
BROKEN_KEY := 0;
DBMS_REPAIR.DUMP_ORPHAN_KEYS(
        SCHEMA_NAME              => '' ,
        OBJECT_NAME              => '',
         OBJECT_TYPE             => DBMS_REPAIR.INDEX_OBJECT,
        REPAIR_TABLE_NAME        => 'REPAIR_TABLE1',
        ORPHAN_TABLE_NAME        => 'ORPHAN_TABLE1',
        KEY_COUNT                => BROKEN_KEY);
DBMS_OUTPUT.PUT_LINE('Number of broken key(s)' || TO_CHAR (BROKEN_KEY));
END;
/
Number of broken key(s)73

PL/SQL procedure successfully completed.

if broken keys >0 then rebuild the index , do this for all indexes of that table

SQL> ALTER INDEX REBUILD;

Index altered.


Once you repair the block corruption(s) for table(s)
and index(es) you should use the following to fix the freelist and bitmap entry.
The freelist is a list of free blocks associated with a segment,
which are eligible for accepting data when a new insert request comes.
The freelist needs to be repaired to allow Oracle to properly use the repair blocks.
Essentially by rebuilding the freelist recreates the header of the datafile.

The syntax below shows how to use DBMS_REPAIR.REBUILD_FREELISTS:

-- fixing the freelist

BEGIN     
DBMS_REPAIR.REBUILD_FREELISTS(
              SCHEMA_NAME              => '' ,
              OBJECT_NAME              => '' ,
              OBJECT_TYPE              => DBMS_REPAIR.TABLE_OBJECT);     
END;     
/


BEGIN
*
ERROR at line 1:
ORA-10614: Operation not allowed on this segment
ORA-06512: at "SYS.DBMS_REPAIR", line 284
ORA-06512: at line 2

if it is not repaired u will get the above message.


BEGIN     
DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
              SCHEMA_NAME              => '' ,
              OBJECT_NAME              => '' ,
              OBJECT_TYPE             => DBMS_REPAIR.TABLE_OBJECT,
              FLAGS                   => DBMS_REPAIR.SKIP_FLAG );     
END;     
/