Re: corrupt block

"Ralf Zwanziger" <goldensurfer_at_gmx.de> wrote in message news:3b246695.170537850_at_news.fth.sbs.de...
> Hi,
> we've got an entry in the alertXXX.log file every day, saying:
>
> ***
> Corrupt block relative dba: 0x06400c09 (file 25, block 3081)
> Bad header found during buffer read
> Data in bad block -
> type: 16 format: 2 rdba: 0x06001609
> last change scn: 0x0000.0b32fdb4 seq: 0x1 flg: 0x00
> consistency value in tail: 0xfdb41001
> check value in block header: 0x0, block checksum disabled
> spare1: 0x0, spare2: 0x0, spare3: 0x0
> ***
>
> Is there a way to find out more information on corrupt datafiles?

What more information do you want? Basically, I would have thought you might want to know what segment has just been stuffed, and you can do that by querying dba_extents, and doing a bit of maths (ie, dba_extents shows you the starting block of each extent [and you can select for a specific tablespace], and you can then do some interpolation to find out which extent [and hence which segment] owns block 3081). In fact, if you have the course notes handy for the DBA course (unfortunately, I do not right now), you'll find that Question 5b (I think) in Practice 9 has a rather nice bit of SQL which prompts you to enter a file number and block number, and it will then return you the segment name involved straight away -it's intended, I think, for precisely this sort of situation.

If no-one else can post the relevant code, I'll dig it out tomorrow.

Once you know that it is table X that it stuffed, you might decide to drop it, recreate it, and then import from an earlier dump file. Or you may want to create a table Y as select from X for all blocks up to 3081, and a table Z for all blocks after 3081, and kiss goodbye to whatever was in block 3081. Or all sorts of other possibilities might present themselves.

> Is it possible to repair the datafile?
>

Short answer is 'no'. Slightly longer answer is that it depends what you mean by 'repair'. Oracle supplied a new package called dbms_repair in 8i, but Oracle's interpretation of the word 'repair' is somewhat at odds with the rest of the world's! Basically, it allows you simply to mark a corrupt block as being skippable, and hence full table scans can successfully scan across the entire table once more (instead of bombing out half way through when it encounters the corrupt block).

If 'skip' means the same thing as 'repair', then I suppose you can indeed 'repair' the corrupt block. But according to my dictionary, they don't so you can't.

Accordingly, the even longer answer is that of course you can 'repair' a datafile by the simple expedient of replacing it with a good copy taken during a previous backup, and then applying redo to it by issuing the 'recover datafile 25' command to bring it bang up to date. That merely requires you to have verified, clean backups handy, and for you to be running in archivelog mode, and for you to have retained all archives since the time of the last clean backup. Oh, and for you to be comfortable with performing media recoveries. If all of that is true for you, then it won't be a problem.

Regards
HJR
> Bye,
> Ralf
Received on Tue Jun 12 2001 - 02:10:56 CDT