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?