Oracle: dbms_repair outputs zero errors about known corrupted object

Question

In an Oracle 9i instance we have a datafile with corrupted blocks as evidenced by the log generated by the exp utility:

. . exporting table                  IMPORTANT_TABLE
EXP-00056: ORACLE error 1578 encountered
ORA-01578: ORACLE data block corrupted (file # 120, block # 249555)
ORA-26040: Data block was loaded using the NOLOGGING option
ORA-01110: data file 120: '/ora/data/important_datafile.dbs'

We tried to move all good rows to another table by means of ifentifiying the corrupted row rowid using dbms_rowid.rowid_create(1,a,b,c,0) where a is the DATA_OBJECT_ID of the table, b is the datafile number and c is the block number. Then trying to copy all rows but the damaged one to another table everything fails because most probably there are other corrupted blocks and there are other corrupted rows.

exp doesn't write to the log a list of all corrupted blocks, it just stops exporting that table an continues with the next one.

We tried to use dbv to identify all corrupted blocks with the idea of further filtering out bad rows and be able to copy the good ones, but the output of dbv doesn't include datafile number, and what seems to be a block number doesn't make sense since 249555 is nowhere to be found. Also trying to use dbv to connect to the database providing a SEGMENT_ID for it to report all corrupted blocks seems buggy since it insists in asking for the dmp file instead, even when the documentation states they are mutually exclusive.

Out last attempt was to use dbms_repair.check_object(...) and then dbms_repair.fix_corrupt_blocks(...). We followed this procedure by Burleson Consulting, we created the repair table and the orphans table .

But dbms_repair.check_object(...) yields zero corrupted errors, even when it's demonstrated that there are corrupted blocks in the table.

This is how we ran it:

set serveroutput on
declare corr_count binary_integer;
begin
corr_count := 0;
dbms_repair.CHECK_OBJECT('IMPORTANT_OWNER','IMPORTANT_TABLE',null,dbms_repair.table_object,'MY_REPAIR_TABLE',null,null,null,null,corr_count);
dbms_output.put_line(to_char(corr_count));
end;
/

Obviuosly running dbms_repair.fix_corrupt_blocks(...) does nothing because the package thinks there are no corrupted blocks to begin with.

The datafile is corrupted in all backups and all exports, because of a disk array failure than happened sometime ago.

I know Oracle 9i is end-of-life but the client uses it and I have to support it.

The question is, having attempted all the above:

• What's left to attempt?
• What are we doing wrong?

Answer 1

What's left to attempt?

You can just instruct the database to skip corrupt blocks on a table scan:

begin
dbms_repair.skip_corrupt_blocks(
  'USER',
  'TABLE_NAME',
  dbms_repair.table_object,
  dbms_repair.SKIP_FLAG);
end;
/

Then scan and move the whole table (except corrupt parts):

alter table user.table_name move;

Finally disable skipping corrupt blocks:

begin
dbms_repair.skip_corrupt_blocks(
  'USER',
  'TABLE_NAME',
  dbms_repair.table_object,
  dbms_repair.NOSKIP_FLAG);
end;
/

Alternatively, you can set event 10231 and run the export:

alter system set events '10231 trace name context forever, level 10';

Then run your export. Finally, you can disable the event for future sessions by:

alter system set events '10231 trace name context forever, off'

and

dbms_system.set_ev for existing sessions. Or just simply restart the instance.