alter index ... rebuild was failing -> getting corrupt blocks

[Former Member]

Database: 9.2.0.5

OS: HPUX-ia64

Hello,

i rebuilded a index, announced from UpdatStats in DB13, and get a ORA-01114: IO error writing block to file 2045 (block # 319154) and

ORA-27072: skgfdisp: I/O error in my sqlplus session.

-> i do a alter index SAPR3."xxx~0" rebuild online parallel 4 nologging;

-> System was up and running

After that, the DBVerify marked some blocks corrupt, here one example:

BR0398E DBVERIFY detected corrupted blocks in /oracle/XXX/sapdata22/btabi_95/btabi.data95

We checked all corrupted blocks - all in free space.

So we fixed that with creating a table with next extend size from the corrupted blocksize.

I think we had not enough space in the tablespace where the index is, okay ... and what we also found, the PSAPTEMP datafiles was created as SPARSE files ...!!

-> PSAPTEMP-Datafile: 10GB in the System - 2,5GB maximum on OS-Level, no more space.

But my question is why i am getting corrupt blocks when a "alter index ... rebuild ..." is failing ?!?!

Thank you for your support.

Regards,

Markus

Edited by: Markus Dinkel on Oct 9, 2008 12:56 PM

Edited by: Markus Dinkel on Oct 9, 2008 12:56 PM

Edited by: Markus Dinkel on Oct 9, 2008 12:59 PM

[markus_doehr2]

Check with Note "354293 - DBVerify reports corrupt block in freespace area" if the blocks are really corrupt.

I also HIGHLY suggest to upgrade your database to a current release. 9.2.0.5 is VERY old and is already out of support. If you want to stay with 9.2 install 9.2.0.8 plus the latest interim patches.

Markus

[Former Member]

Thany you for the answer.

I cant find any "cor" entries in the last DBVeriy log.

I think (hope) we dont' have anymore corrupted blocks in the system.

We get a response from SAP to update to 9.2.0.8.

My customer wanna do a update to oracle 10 in march/april 2009, so he send me the question if we need a update to 9.2.0.8 ASAP or can we wait for the update to oralce10.

But the important question from my customer and me is, why we get corrupt blocks after failing the alter index rebuild?

Regards,

Markus

[lbreddemann]

> But the important question from my customer and me is, why we get corrupt blocks after failing the alter index rebuild?

Hi Markus,

that is a false observation! The blocks had been marked corrupt before and independent of the index rebuild.

What happens when you rebuild an index is that the data blocks that should be used for the index are allocated as temporary segments in the index-tablespace.

When the index creation finishes sucesfully, the temp-segments are kind of "re-named" into extents of the index segment.

When the index creation fails, the temp segments are freed and released to the free space.

In fact, by writing into the blocks for the temp segments, these blocks got reformatted and cannot be corrupt after that.

Anyhow, trying to resolve freespace corruptions is in general just a big waste of time and resources.

Any block that is newly allocated from freespace gets reformatted. Always. So it does not matter at all, if there are some blocks in the "freespace" that contain rubbish. It will be overwritten anyhow.

This is the big disadvantage of using DBV - it does not make any difference between allocated and free blocks. It just checks each block on its own and tell you: OK or CORRUPT. That's it.

For a much better and more sensible corruption check on block level, you should go for the RMAN checking.

Read my blog [Some hints to make handling of oracle datablock corruptions more efficient|https://www.sdn.sap.com/irj/sdn/weblogs?blog=/pub/wlg/9285] [original link is broken] [original link is broken] [original link is broken]; on this for more information on this.

regards

Lars