Fixing Corrupt Empty Blocks

9.2 on Solaris 8
My what a lovely day yesterday was. The server and the disk array had a marital dispute and decided not to talk to one another causing the prod database to crash. Mr. Sun engineer came and replaced the controller on the sunfire 4800.

Luckily, the only recovery needed was instance recovery.
Unluckily, I had fractured blocks in two datafiles
Luckily, those two datafiles were associated with index tablespaces.

I dropped and recreated an index associated with one of the corrupt blocks in one of the two datafiles. Now, I am stuck with 2 blocks...

select file#, block#, marked_corrupt, corruption_type
from v$backup_corruption;
5 152564 YES FRACTURED
5 152564 YES FRACTURED

...that do not belong to any objects:
select owner,substr(segment_name,1,30) segment_name,
segment_type,tablespace_name
from dba_extents
where file_id=5 and
(152564 between block_id and block_id + blocks - 1)

0 rows returned.

My question is: Since DBMS_REPAIR works on intentified objects (and I don't have one), how do I fix fractured blocks associated with no objects (empty blocks)?

Thanks,
Frank

dont know if dbms_repair can do it,

but why not create a new tablespace and move the indexes then drop the original one? (can do a rebuild online in 9.2)

good point - it would work. just too intense a task for our critical, very busy, 24x7, micro-managed (for good reason), hundreds of indexes, production database.

I'll tar the question too. Backups are okay thanks to rman skip corruption.

all i've read dbms_repair works on identified objects
thnx

dbms_repair, I believe, will not *fix* "fractured blocks". The dbms_repair.fix_corrupt_blocks won't do its thing. All you might be able to do is skip them.

Since you use rman, you might want to try blockrecover too. Like it has already been suggested, your best bet is to ofcourse recreate the indexes. Can't go wrong with it..

PS : One other thing - if these fractured blocks are beyond the max(blockid) used by an object on the file, you can shrink (resize) the datafile to work around this. That's if you're lucky..

Quote:

---

dbms_repair, I believe, will not *fix* "fractured blocks". The dbms_repair.fix_corrupt_blocks won't do its thing. All you might be able to do is skip them.

---

It won't fix it because it's a bug ??? The documenation seems to say that the block is fixable isth this package.

Quote:

---

Since you use rman, you might want to try blockrecover too. Like it has already been suggested, your best bet is to ofcourse recreate the indexes. Can't go wrong with it..

---

i have thought about blockrecover - i'll consider it. i have mentioned over and again, that I do not have an object associated with the blocks, therefore, i cannot drop and recreate.

(I pressume these blocks are empty and on the freelist but will never be used because Oracle will detect and bypass):

select owner,substr(segment_name,1,30) segment_name,
segment_type,tablespace_name from dba_extents where file_id=5 and
(152564 between block_id and block_id + blocks - 1)

0 rows returned.

Quote:

---

Originally posted by gopi
It won't fix it because it's a bug ??? The documenation seems to say that the block is fixable isth this package.

---

Per support :
"The DBMS_REPAIR package is used to work with corruption in the
transaction layer and the data layer only (software corrupt blocks).
Blocks with physical corruption (ex. fractured block) are marked as
the block is read into the buffer cache and DBMS_REPAIR ignores all
blocks marked corrupt."


> i have thought about blockrecover - i'll consider it. i have mentioned over and again, that I do not have an object associated with the blocks, therefore, i cannot drop and recreate.

If it's an index tablespace, create a new one and MOVE all your indexes to this new tablespace; drop old ts. That was what was suggested.

As for my PS: comment, you might want to run this :
select max(block_id+blocks-1) from dba_extents where file_id=5;. If thats a number less than 152564 (your corrupt block), its your lucky day. Simply shrink the datafile appropriately and you should be set. I've gotten lucky with fractured blocks a few times in the past.

ahhh, i now understand the meaning on the dbms_repair. and yeah, i realized you were talking about the move to another TS after I posted my comment.

max(block_id+blocks-1) returned 1142032 so i'm not in luck with that possibility. thanks for your help !

Quote:

---

Originally posted by gopi
ahhh, i now understand the meaning on the dbms_repair. and yeah, i realized you were talking about the move to another TS after I posted my comment.

max(block_id+blocks-1) returned 1142032 so i'm not in luck with that possibility. thanks for your help !

---

One other less obvious thing that people often overlook - make sure your tablespace is not in a nologging mode. You can't perform blockrecovery (or any other meaningful recovery) on it. While the rman recovery will technically "succeed", a routine dbverify will throw corruption again.

Quote:

---

Originally posted by gopi
...that do not belong to any objects...

---

There is no need to correct it.

These blocks will be reformatted by Oracle when they will be allocated to a new segment.