-
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?
-
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
anandkl
-
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 11:37 AM.
-
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 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.
-
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..
-
After runing the above fix,
How r u saying the FIX is not WORKING. Any errors?
regards
anandkl
anandkl
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|