DBMS_REPAIR - marking blocks corrupt
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: DBMS_REPAIR - marking blocks corrupt

Hybrid View

  1. #1
    Join Date
    Oct 2002
    Posts
    807

    DBMS_REPAIR - marking blocks corrupt

    I haven't used dbms_repair extensively. I've got a few fractured corrupt blocks on a huge table. I intending marking the blocks corrupt and leaving things as is.

    Quick question - From what I read, marking a block corrupt and enabling skip corrupt blocks will skip the relevent blocks during index and full scans. Seems okay to me, if I can't access rows on 10blocks.

    Any catches/gotchas to doing this?

  2. #2
    Join Date
    Oct 2002
    Posts
    807
    Update :
    The whole idea of doing this exercise was to allow RMAN to backup this tablespace successfully. RMAN failed when it hit these corrupted blocks. Despite marking these blocks corrupt, RMAN continues to "see" them..and fail! Aargh..just great.

  3. #3
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    Do you know what object(s) uses the blocks in question? Could you find that out and then export those objects before dropping and re-creating them using the export?

  4. #4
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    You can use this query to find the objects in the crroupt block,

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

    where file_id, and block_id are the numbers that are show in the alert.log

    If the segment_type is INDEX, it would be good to drop the index and create in a different tablespace. If its table, mark the block to be skipped using the DBMS_REPAIR package.

    regards
    anandkl
    anandkl

  5. #5
    Join Date
    Oct 2002
    Posts
    807
    I haven't used dbms_repair extensively. I've got a few fractured corrupt blocks on a huge table. I intending marking the blocks corrupt and leaving things as is.
    So, yes - I obviously know the objects. 2 blocks of tabledata and 2 blocks of index data. I can't get the damn dbms_repair.fix_corrupt_blocks to work. See "num fix : 0" at bottom.


    SET SERVEROUTPUT ON
    DECLARE num_corrupt INT;
    BEGIN
    num_corrupt := 0;
    DBMS_REPAIR.CHECK_OBJECT (
    SCHEMA_NAME => 'COURT',
    OBJECT_NAME => 'CASE_DOCKET_PARAMETERS',
    PARTITION_NAME => 'CDP_8',
    REPAIR_TABLE_NAME => 'REPAIR_TABLE',
    CORRUPT_COUNT => num_corrupt);
    DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));
    END;
    number corrupt: 2

    PL/SQL procedure successfully completed.

    ----------

    SET SERVEROUTPUT ON
    DECLARE num_orphans INT;
    BEGIN
    num_orphans := 0;
    DBMS_REPAIR.DUMP_ORPHAN_KEYS (
    SCHEMA_NAME => 'COURT',
    OBJECT_NAME => 'PK_CASE_DOCKET_PARAMETERS',
    OBJECT_TYPE => dbms_repair.index_object,
    REPAIR_TABLE_NAME => 'REPAIR_TABLE',
    ORPHAN_TABLE_NAME=> 'ORPHAN_KEY_TABLE',
    KEY_COUNT => num_orphans);
    DBMS_OUTPUT.PUT_LINE('orphan key count: ' || TO_CHAR(num_orphans));
    END;
    /

    orphan key count: 177

    PL/SQL procedure successfully completed.
    ------------


    BEGIN
    DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
    SCHEMA_NAME => 'COURT',
    OBJECT_NAME => 'CASE_DOCKET_PARAMETERS',
    OBJECT_TYPE => dbms_repair.table_object,
    FLAGS => dbms_repair.skip_flag);
    END;
    /
    PL/SQL procedure successfully completed.
    ------

    SET SERVEROUTPUT ON
    DECLARE num_fix INT;
    BEGIN
    num_fix := 2;
    DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
    SCHEMA_NAME => 'COURT',
    OBJECT_NAME=> 'CASE_DOCKET_PARAMETERS',
    PARTITION_NAME => 'CDP_8',
    OBJECT_TYPE => dbms_repair.table_object,
    REPAIR_TABLE_NAME => 'REPAIR_TABLE',
    FIX_COUNT=> num_fix);
    DBMS_OUTPUT.PUT_LINE('num fix: ' || TO_CHAR(num_fix));
    END;
    /
    num fix: 0

    PL/SQL procedure successfully completed
    Last edited by Axr2; 09-17-2004 at 12:37 PM.

  6. #6
    Join Date
    Oct 2002
    Posts
    807
    Originally posted by waitecj
    Do you know what object(s) uses the blocks in question? Could you find that out and then export those objects before dropping and re-creating them using the export?
    It is a 10G table. I can't be bothered exporting it. I don't care about data in those 14 blocks of data. I just want to mark them corrupt and move on.

  7. #7
    Join Date
    Oct 2002
    Posts
    807
    Originally posted by anandkl
    If its table, mark the block to be skipped using the DBMS_REPAIR package.
    That's my problem. I can't get it to work. See posted code.

    Thanks.

  8. #8
    Join Date
    Oct 2002
    Posts
    807
    Originally posted by Axr2
    I can't get the damn dbms_repair.fix_corrupt_blocks to work.
    Oracle support gave me some gobbledygook about dbms_repair not being able to fix "fractured" blocks. No mention about it the manuals though..

    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."


    Details of my corruption :
    SQL> l
    1 SELECT OBJECT_NAME, BLOCK_ID, CORRUPT_TYPE, MARKED_CORRUPT,
    2 CORRUPT_DESCRIPTION, REPAIR_DESCRIPTION
    3* FROM REPAIR_TABLE
    SQL> /

    OBJECT_NAME BLOCK_ID CORRUPT_TYPE MARKED_COR
    ------------------------------ ---------- ------------ ----------
    CORRUPT_DESCRIPTION
    --------------------------------------------------------------------------------
    REPAIR_DESCRIPTION
    --------------------------------------------------------------------------------
    CASE_DOCKET_PARAMETERS 114774 6148 TRUE

    mark block software corrupt

    CASE_DOCKET_PARAMETERS 114775 6148 TRUE

    mark block software corrupt

    CASE_DOCKET_PARAMETERS 114774 6148 TRUE

    mark block software corrupt

    CASE_DOCKET_PARAMETERS 114775 6148 TRUE

    mark block software corrupt

    CASE_DOCKET_PARAMETERS 114774 6148 TRUE

    mark block software corrupt

    CASE_DOCKET_PARAMETERS 114775 6148 TRUE

    mark block software corrupt


    6 rows selected.

    SQL> select * from V$DATABASE_BLOCK_CORRUPTION;

    FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
    ---------- ---------- ---------- ------------------ ---------
    57 114774 1 0 FRACTURED
    57 114775 1 0 FRACTURED
    67 147737 1 0 FRACTURED
    67 147738 2 0 FRACTURED
    70 217721 1 0 FRACTURED
    70 217722 2 0 FRACTURED
    72 105401 1 0 FRACTURED
    72 105402 2 0 FRACTURED
    84 83409 1 0 FRACTURED
    84 83410 2 0 FRACTURED

    10 rows selected.

    PS : Oh and btw file# 70,72 don't even exist on the database..

  9. #9
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    After runing the above fix,
    How r u saying the FIX is not WORKING. Any errors?

    regards
    anandkl
    anandkl

  10. #10
    Join Date
    Oct 2002
    Posts
    807
    Originally posted by anandkl
    After runing the above fix,
    How r u saying the FIX is not WORKING. Any errors?
    For all of the below reasons :
    1) See posted results of DBMS_REPAIR.FIX_CORRUPT_BLOCKS. Num rows = 0 NOT 2. Like I said, fractured blocks I understand cannot be "fixed".

    2) The blocks continue to remain on v$database_block_corruption. A block that is "fixed", is supposed to get removed from this view. See documentation.

    3) FIX_TIMESTAMP on REPAIR_TABLE is null. When a row gets fixed, this timestamp gets updated.

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