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
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 .
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; /
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?