Re: Oracle block corruption, restore and roll forward

"Andrew Riley" <himsdba_at_yahoo.co.uk> wrote in message news:30e7f1c6.0411021834.34706262_at_posting.google.com...
> We are running an Oracle 8.1.7.4.1 SE database and a datafile has a
> corrupt block. The database has been restored and we have attempted
> to roll forward using the current control files and archive logs but
> it has failed.
>
> The table concerned is fairly large at ~150Gb so selecting/inserting
> around the corrupt 2K block could be a bit of a nightmare!
>
> Has anybody else had a similar problem and been able to resolve it?
> If so, what actions did you take?
>
> Any feedback would be appreciated.
>
> Many thanks

DBMS_REPAIR is your friend in this situation. It will scan a table and report which blocks it is that are corrupt. You can also use the package to makr those blocks as corrupt, and hence make them skippable by full table scans performed by users. It means you lose the data in those blocks (some new meaning of the word 'repair', methinks!), but it's better than not having a working table at all.

Otherwise, the only thing I can think of is that you didn't restore froma suffcieintly old backup, and that the corruption was in the backup you just restored from. In which case (a) why don't you verify your backups using DBVerify (dbv) -because then you would have known of the problem in your backup set. And (b) how about restoring from a much older backup and recovering that?

Regards
HJr Received on Tue Nov 02 2004 - 21:23:36 CST