db block corruption
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: db block corruption

  1. #1
    Join Date
    Oct 2000
    Posts
    57
    In my database there is two corrupted blocks are there .
    I want to instruct my database to not to use that blocks
    while table/index scan

    IS there any way ?

    thanks


  2. #2
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    Not really. I suggest you contact Oracle Tech Support to resolve this issue asap.

  3. #3
    Join Date
    Jan 2001
    Posts
    72

    Unhappy

    How do you find corrupt blocks in the database?

    Also if the block is corrupt how does it affect the database?

    [Edited by mybear on 01-24-2001 at 12:33 PM]

  4. #4
    Join Date
    Sep 2000
    Posts
    384
    One of the ways is to do a full export.If there is any block corruption there will be errors in the export log...with the file id,block id details...
    Radhakrishnan.M

  5. #5
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    as Halo suggested contact Oracle support. As a temporary fix though you may want to try the following --

    I believe that you found out about the corrupt data blocks by using DBV. It must have told you what blocks are corrupted.
    Use dba_extents to find out what objects (table, index) are using those blocks.

    You need to recreate those objects. For indexes it should be as simple as creating a new index; For tables you may try Export/import. In case of corrupt block, even Export may fail. In that case you may write a little pl/sql procedure to read one record at a time and insert it in a new temp table; Reading one record will give you error while reading the corrupt block (but will not terminate the procedure like Export may do); just skip that record.

    Another thing to keep in mind is that if there is a corrupt block and you know about it, this may be the time to "Block" it from being used by any other new objects created in the database. You can do this by leaving those objects that contain the corrupt block intact; just rename them so that they sit there forever holding that corrupt block until you find a more permanent solution.
    Of course it may not be as simple as I sounded (there may be constraints issues blocking you from renaming the tables)

    - Rajeev

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Did you try DBMS_REPAIR.SKIP_CORRUPT_BLOCK?

  7. #7
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843

    mybear,

    [url]http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76955/ch09.htm#1936[/url]

    [url]http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76956/repair.htm#8310[/url]

    Few more months, our life is gonna be little easier with oracle9i new feature 'block meadia recovery' which allows only corrupt blocks to be recovered while the table is online, with the coordinated effort of LogMiner utility.


    [Edited by sreddy on 01-24-2001 at 01:41 PM]

  8. #8
    Join Date
    Oct 2000
    Posts
    57

    BEGIN
    DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
    SCHEMA_NAME => 'SCOTT',
    OBJECT_NAME => 'DEPT',
    OBJECT_TYPE => dbms_repair.table_object,
    FLAGS => dbms_repair.skip_flag);
    END;


    I can not used DBMS_REPAIR.SKIP_CORRUPT_BLOCK, the reason is there is no object related to this currepted block .
    I don't want any object to use these blocks in future while creating new objects .
    I sthere any way to mark these blocks as unusable ?

    thanks



  9. #9
    Join Date
    Nov 2000
    Posts
    26

    Thumbs down

    hi,
    there are 2 possibilities in blk.corruption cases
    1. the corrupt blocks do not belong to any object(which is what u have told.
    2. the corrupt blocks belong to a database object
    if the blocks would have been of a db object it would not have been much of a bother to u, all u would have to do was to drop the object and restore it from uer backup or take any other route to get back uer data, which is pretty well documented, but since this is not uer case and that want to instruct oracle to forget that these blocks exist, then that would be asking for a lot, since these things are internal to the oracle kernel, we would have no way of controlling how oracle would pick blocks at its discretion, hence your only fallback is to get in touch with oracle support and sort out uer problem
    cheers
    soren

  10. #10
    Join Date
    Jan 2001
    Posts
    2

    Thumbs up

    Read for help on DBMS_REPAIR Package. This has a bunch of procedures you can use to deal with corrupt blocks. It seems like this utility is to repair corrupt blocks but for the time being until 8.1.7 it can only be used to mark corrupt blocks in your tables so that they can be skipped during FTS. You can also mark orphan keys in the indexes using this package. don't forget to rebuild indexes......

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