How to fix corrupt Oracle blocks

[yakum0]

We have two production datafiles that have block corruptions. DBVERIFY reports one oracle block corruption against each datafile. The two block_ids reported by dbverify belong to the same table TST03 (as checked in dba_extents).

From our nightly backups we restored the two datafiles in question and ran the dbverify command to check they were okay. We then tried to do a "recover datafile…" only to find that one of the required archive redo logs is also corrupt! A point in time recovery is not an option as this would have meant losing a days work. Looking at the timestamp on the corrupt redo log, we think that all these corruptions occurred at the same time that we had a disk problem on our disk array.

What I would like to do is to "fix" the two corrupt blocks belonging to the table TST03. I realise this would mean losing some data from table TST03 but we can live with that as the table isn't a key table.

Can anyone come up with some suggestions on how we can resolve our corrupt blocks problem? (By the way truncating table TST03 is not an option!)

Thanks

Yakumo

 

[alexhu]

Do you have access to Metalink?

There is an extensive procedure on there

Alex

 

[yakum0]

Hi Alex,

I looked on Metalink, there was a note I found (Doc ID 28814.1) on handling block corruptions. I tried everything in the note but no joy the table is still corrupt. I am trying an approach of creating a separate table and copying around the corrupt records. However the target table has a long raw column which means I cannot use "insert into ... select * from..." SQL command. Can you or anyone else suggest a way of copying records with a long raw column into another table?... Also, do you know of any specific Doc ID (other than the one I found above) that describes in detail a procedure for recovering data froma table with corrupt blocks.

Many Thanks

Yakumo.

 

[alexhu]

I don't have a number but the title of the one I've saved is Handling Block Corruptions in Oracle7 / 8 / 8i / 9i

Sorry can't help with your long raw problem - I have no experience of those

Alex