Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Mapping block # to object - we have block corruption

    Our SAN was getting low on space (1.8Gb left or so) and we started getting msgs in the alert log like

    Code:
    ORA-3297 signalled during: ALTER DATABASE DATAFILE '/u02/oradata/dev/claims_m...
    I wasn't able to find out what these error msgs actually meant, but I think I know now. We took a cold backup, extended the amount of space available on the SAN, and restarted the database. When it came up, it signalled that there were about 70 corrupt blocks, which we found after running

    Code:
    RMAN> BACKUP VALIDATE DATABASE ARCHIVELOG ALL;
    and then

    Code:
    SQL> select *
      2  from v$database_block_corruption;
     
         FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
    ---------- ---------- ---------- ------------------ ---------
             1      63550          1                  0 CHECKSUM
             2      31878          1                  0 CHECKSUM
             2      91205          1                  0 FRACTURED
    ...
    So the sol'n on metalink is to export the problem objects and then import them, but I don't know how to map the BLOCK# to the object it belongs to. Could someone help me with that query?

    -Thanks
    Chuck

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,628
    Alternatively, you might consider the following:
    http://download-west.oracle.com/docs...8/d_repair.htm

    or
    Code:
    SELECT owner, segment_name, segment_type
    FROM dba_extents
    WHERE file_id = <filenumber>
    AND <blocknumber> BETWEEN block_id AND block_id + blocks - 1;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    There are no stupid questions, but there are a LOT of Inquisitive Idiots.

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    So, I'm looking into using DBMS_REPAIR first, in order to fix this problem. I'm following the instructions and looking at a "low-risk" table. I've gone and set up the REPAIR_TABLE, and I've fed the problem table into the DBMS_REPAIR.CHECK_OBJECT procedure. Then I queries REPAIR_TABLE as indicated in the instructions.

    According to the instructions, the table should be inducate that the block has a MARKED_CORRUPT values = FALSE.

    http://download-west.oracle.com/docs...htm#sthref2912

    For us, though, the values is TRUE
    Code:
    SQL> SELECT OBJECT_NAME, BLOCK_ID, CORRUPT_TYPE, MARKED_CORRUPT,
      2         CORRUPT_DESCRIPTION, REPAIR_DESCRIPTION
      3       FROM REPAIR_TABLE;
    
    OBJECT_NAME                      BLOCK_ID CORRUPT_TYPE MARKED_COR
    ------------------------------ ---------- ------------ ----------
    CORRUPT_DESCRIPTION
    --------------------------------------------------------------------------------
    REPAIR_DESCRIPTION
    --------------------------------------------------------------------------------
    PA_COVERAGE_VAP                      6164         6148 TRUE
    
    mark block software corrupt
    The main reason it confuses me is that the instructions claim that after you run DBMS_REPAIR.FIX_CORRUPT_BLOCKS, you'll be notified of success using the same query - because you'd notice that MARKED_CORRUPT had changed from FALSE to TRUE. The following is a quote from the Oracle manual

    Code:
    This example fixes the corrupt block in table scott.dept that was reported by the CHECK_OBJECT procedure.
    
    SET SERVEROUTPUT ON
    DECLARE num_fix INT;
    BEGIN 
    num_fix := 0;
    DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
         SCHEMA_NAME => 'SCOTT',
         OBJECT_NAME=> 'DEPT',
         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;
    /
    
    SQL*Plus outputs the following line:
    
    num fix: 1
    
    To following query confirms that the repair was done.
    
    SELECT OBJECT_NAME, BLOCK_ID, MARKED_CORRUPT
         FROM REPAIR_TABLE;
    
    OBJECT_NAME                      BLOCK_ID MARKED_COR
    ------------------------------ ---------- ----------
    DEPT                                    3 TRUE
    So, I haven't run the FIX_CORRUPT_BLOCKS yet, as I'm not sure if that piece has already been performed by ORacle in some other step?

    -Chuck

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    Tried the FIX_CORRUPT_BLOCKS procedure anyway, but Oracle thought it had to fix 0 blocks.

    So, now I'm trying to retrieve the data from an expdp two days ago, which should contain the corrupt blocks.

    -cf

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    Turns out after I started restoring Tables via datapump, which was working, we decided to try another approach via RMAN, which worked.
    Code:
    RMAN> BACKUP VALIDATE DATABASE;
    RMAN> BLOCKRECOVER CORRUPTION LIST;
    This goes through all the blocks listed in v$database_block_corruption and restores them, and in this case, fixed the corruption. I'm not sure why recovering a datablock from a backupset that RMAN says already contains a corrupt block would fix it, but it does. Perhaps the RMAN command should really say something like FIX CORRUPT BLOCKS CORRUPTION LIST.

    -Chuck

  6. #6
    Join Date
    Jan 2010
    Posts
    2
    YOu can try out the fix_corrupt_blocks procedure to solve out the issue:

    dbms_repair.FIX_CORRUPT_BLOCKS (
    schema_name IN VARCHAR2,
    object_name IN VARCHAR2,
    partition_name IN VARCHAR2 DEFAULT NULL,
    object_type IN BINARY_INTEGER DEFAULT TABLE_OBJECT,
    repair_table_name IN VARCHAR2 DEFAULT 'REPAIR_TABLE',
    flags IN BINARY_INTEGER DEFAULT NULL,
    fix_count OUT BINARY_INTEGER);

    If after running this procedure if the problem still persists then you need to go for any oracle repair software third party software program that will do the job of repairing the oracle database tables.

Posting Permissions

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