Printable View
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?
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.
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?
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
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.Quote:
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.
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
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.Quote:
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?
That's my problem. I can't get it to work. See posted code.Quote:
Originally posted by anandkl
If its table, mark the block to be skipped using the DBMS_REPAIR package.
Thanks.
Oracle support gave me some gobbledygook about dbms_repair not being able to fix "fractured" blocks. No mention about it the manuals though..Quote:
Originally posted by Axr2
I can't get the damn dbms_repair.fix_corrupt_blocks to work.
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..
After runing the above fix,
How r u saying the FIX is not WORKING. Any errors?
regards
anandkl
For all of the below reasons :Quote:
Originally posted by anandkl
After runing the above fix,
How r u saying the FIX is not WORKING. Any errors?
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.