Fixing Corrupt Empty Blocks
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Fixing Corrupt Empty Blocks

  1. #1
    Join Date
    Dec 2001
    Location
    Keene, NH
    Posts
    510

    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

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    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)

  3. #3
    Join Date
    Dec 2001
    Location
    Keene, NH
    Posts
    510
    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

  4. #4
    Join Date
    Oct 2002
    Posts
    807
    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..

  5. #5
    Join Date
    Dec 2001
    Location
    Keene, NH
    Posts
    510
    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.


    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.

  6. #6
    Join Date
    Oct 2002
    Posts
    807
    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.
    Last edited by Axr2; 11-24-2004 at 02:15 PM.

  7. #7
    Join Date
    Dec 2001
    Location
    Keene, NH
    Posts
    510
    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 !

  8. #8
    Join Date
    Oct 2002
    Posts
    807
    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.

  9. #9
    Join Date
    Feb 2004
    Location
    Russia
    Posts
    13

    Re: Fixing Corrupt Empty Blocks

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width