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.
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.
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
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.
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> /
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.
Reply With Quote
Bookmarks