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...
...that do not belong to any objects:
select owner,substr(segment_name,1,30) segment_name,
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)?
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.
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.