Two ways to recover data in an Oracle database table containing corruption blocks

1. Creating a table for testing

sqlplus> create table sunower.tab01 as select * from dba_extents;

2. Creation backup
rman> backup as compressed backupset INCREMENTAL LEVEL=0 FORMAT '/u01/backup/bk_u%u_s%s_p%p_t%t' DATABASE NOEXCLUDE;

3. Getting the location of the segment
sqlplus>  select dbms_rowid.rowid_relative_fno(rowid) file_number, 

                         dbms_rowid.rowid_block_number(ROWID) block_number,
                         count(*) FROM sunower.tab01 a group by                  

                         dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(ROWID) 

                order by dbms_rowid.rowid_block_number(ROWID);

* 129 rows in the block 134

4. Example of data block corruption
[oracle$] dd if=/dev/zero of=/u01/oradata/bank/test_ts01.dbf bs=8k conv=notrunc seek=134 count=1
sqlplus> alter system flush buffer_cache;
rman> backup validate check logical datafile 5;

5. Attempt to read data
sqlplus>  select a.* FROM sunower.tab01 a where dbms_rowid.rowid_relative_fno(rowid)=5 and dbms_rowid.rowid_block_number(ROWID)=134;

6. View
sqlplus> select * from V$DATABASE_BLOCK_CORRUPTION;

**********************************************************

Solution. Method 1.
rman> RECOVER DATAFILE 5 BLOCK 134;

**********************************************************

Solution. Method 2. (DBMS_REPAIR)

The DBMS_REPAIR package contains data corruption repair procedures that enable you to detect and repair corrupt blocks in tables and indexes. You can address corruptions where possible and continue to use objects while you attempt to rebuild or repair them.

• CHECK_OBJECT – checking and collecting information about bad blocks in a table or index
• FIX_CORRUPT_BLOCKS - marks blocks identified with CHECK_OBJECT as logically damaged
• DUMP_ORPHAN_KEYS –dumps index keys indicating bad blocks
• REBUILD_FREELISTS - перестраивает списки свободных блоков
• SKIP_CORRUPT_BLOCKS - allows you to skip blocks marked as bad during a table or index scan. If you do not use it, you can get the ORA-01578 error.

2.1. First, you need to create two tables that will contain information about bad blocks and index keys pointing to these blocks. Tables are created as follows:

sqlplus> BEGIN

    DBMS_REPAIR.admin_tables (

        table_name => 'REPAIR_TABLE',

        table_type => DBMS_REPAIR.repair_table,

        action     => DBMS_REPAIR.create_action,

        tablespace => 'TEST_TS');

    DBMS_REPAIR.admin_tables (

        table_name => 'ORPHAN_KEY_TABLE',

        table_type => DBMS_REPAIR.orphan_table,

        action     => DBMS_REPAIR.create_action,

        tablespace => 'TEST_TS');

   END;

/

2.2. The table is checked like this:

sqlplus> SET SERVEROUTPUT ON

DECLARE

  num_corrupt INT;

BEGIN

  num_corrupt := 0;

  DBMS_REPAIR.check_object (

    schema_name       => 'SUNOWER',

    object_name       => 'TAB01',

    repair_table_name => 'REPAIR_TABLE',

    corrupt_count     =>  num_corrupt);

  DBMS_OUTPUT.put_line('corrupt: ' || TO_CHAR (num_corrupt));

END;

/

2.3. After that we get a list of bad blocks:

sqlplus> select object_name, block_id, corrupt_type, marked_corrupt,    corrupt_description, repair_description from repair_table;

* CORRUPTION_DESCRIPTION and REPAIR_DESCRIPTION in the table REPAIR_TABLE contain complete information about the damaged blocks.

2.4. Revealed blocks now need to be marked as broken so that DML commands will skip them and execute correctly.

sqlplus> SET SERVEROUTPUT ON

DECLARE

  n_fix INT;

BEGIN

  n_fix := 0;

  DBMS_REPAIR.fix_corrupt_blocks (

    schema_name       => 'SUNOWER',

    object_name       => 'TAB01',

    object_type       => Dbms_Repair.table_object,

    repair_table_name => 'REPAIR_TABLE',

    fix_count         => n_fix);

  DBMS_OUTPUT.put_line('n fix: ' || TO_CHAR(n_fix));

END;

/

2.5. If bad blocks were found in the table, then you can try to salvage the information that is contained in the indexes that refer to the bad blocks.

sqlplus> SET SERVEROUTPUT ON

DECLARE

  num_orphans INT;

BEGIN

  num_orphans := 0;

  DBMS_REPAIR.dump_orphan_keys (

    schema_name       => 'SUNOWER',

    object_name       => 'TAB01',

    object_type       => DBMS_REPAIR.index_object,

    repair_table_name => 'REPAIR_TABLE',

    orphan_table_name => 'ORPHAN_KEY_TABLE',

    key_count         => num_orphans);

  DBMS_OUTPUT.put_line('orphan key count: ' || TO_CHAR(num_orphans));

END;

/

2.6. Now you can see the keys:

sqlplus> SELECT SCHEMA_NAME, INDEX_NAME, INDEX_ID, TABLE_NAME, KEYROWID, KEY, DUMP_TIMESTAMP FROM ORPHAN_KEY_TABLE;

2.7. If keys were found, then the index must be rebuilt.

The DBMS_REPAIR.rebuild_freelists procedure is used to rebuild the free list. 

This procedure will scan the table and place free blocks in freelists. 

All blocks marked as broken will be skipped during scanning.

sqlplus> BEGIN

  DBMS_REPAIR.rebuild_freelists (

    schema_name => 'SUNOWER',

    object_name => 'TAB01',

    object_type => DBMS_REPAIR.table_object);

END;

/

2.8. To make DML commands ignore blocks marked as broken, use the procedure

sqlplus> DBMS_REPAIR.skip_corrupt_blocks

 BEGIN

  DBMS_REPAIR.skip_corrupt_blocks (

    schema_name => 'SUNOWER',

    object_name => 'TAB01',

    object_type => DBMS_REPAIR.table_object,

    flags       => DBMS_REPAIR.skip_flag);

END;

/