Never published this old post, still could be worthwhile for anybody. A checklist for handling ORA-600 [kdsgrp1].
First: what is going on? Checking the table on inconsistency:
SQL> analyze table DWH_TEST.DWH validate structure online;
ERROR at line 1:
ORA-01498: block check failure – see trace file. I know now that in this trace-file there is one line very intriging:
nrow=624 ==> 624 rows in corrupted blocks…. bloody hell !
Using dbms_repair for repairing this. Checklist (sources are on the bottom):
1. Connected as SYS: Creating repair table ‘REPAIR_TABLE’ and view ‘DBA_REPAIR_TABLE’:
BEGIN
DBMS_REPAIR.ADMIN_TABLES (
TABLE_NAME => ‘REPAIR_TABLE’,
TABLE_TYPE => dbms_repair.repair_table,
ACTION => dbms_repair.create_action,
TABLESPACE => ‘USERS’);
END;
/
2. Create Orphan keys table ‘ORPHAN_KEY_TABLE’ and view ‘DBA_ORPHAN_KEY_TABLE’.
BEGIN
DBMS_REPAIR.ADMIN_TABLES (
TABLE_NAME => ‘ORPHAN_KEY_TABLE’,
TABLE_TYPE => dbms_repair.orphan_table,
ACTION => dbms_repair.create_action,
TABLESPACE => ‘USERS’);
END;
/
3. Checking the object:
SET SERVEROUTPUT ON
DECLARE num_corrupt INT;
BEGIN
num_corrupt := 0;
DBMS_REPAIR.CHECK_OBJECT (
SCHEMA_NAME => ‘DWH_TEST’,
OBJECT_NAME => ‘DWH’,
REPAIR_TABLE_NAME => ‘REPAIR_TABLE’,
CORRUPT_COUNT => num_corrupt);
DBMS_OUTPUT.PUT_LINE(‘number corrupt: ‘ || TO_CHAR (num_corrupt));
END;
/
4. How many blocks are corrupt, including repair-action.
SELECT OBJECT_NAME, BLOCK_ID, CORRUPT_TYPE, MARKED_CORRUPT,
CORRUPT_DESCRIPTION, REPAIR_DESCRIPTION
FROM REPAIR_TABLE;
5. Try to get the data from the corrupt blocks:
create table temp_DWH as
select * from DWH_TEST.DWH
where dbms_rowid.rowid_block_number(rowid) = <corrupt_block>
and dbms_rowid.rowid_to_absolute_fno (rowid, ‘DWH_TEST’,’DWH’) = <file_number>;
6. Fix it, mark the blocks as corrupt (afterwards we get the ora-1578 with a full table scan):
SET SERVEROUTPUT ON
DECLARE num_fix INT;
BEGIN
num_fix := 0;
DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
SCHEMA_NAME => ‘DWH_TEST’,
OBJECT_NAME=> ‘DWH’,
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;
/
7. Has the repair been done:
SELECT OBJECT_NAME, BLOCK_ID, MARKED_CORRUPT
FROM REPAIR_TABLE;
8. Find Index entries pointing to the corrupt blocks (example of tWO indexes):
SET SERVEROUTPUT ON
DECLARE num_orphans INT;
BEGIN
num_orphans := 0;
DBMS_REPAIR.DUMP_ORPHAN_KEYS (
SCHEMA_NAME => ‘DWH_TEST’,
OBJECT_NAME => ‘IND_DWH_01’,
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;
/
DECLARE num_orphans INT;
BEGIN
num_orphans := 0;
DBMS_REPAIR.DUMP_ORPHAN_KEYS (
SCHEMA_NAME => ‘DWH_TEST’,
OBJECT_NAME => ‘IND_DWH_02’,
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;
/
9. Let Oracle skip the corrupt blocks:
BEGIN
DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
SCHEMA_NAME => ‘SCOTT’,
OBJECT_NAME => ‘DEPT’,
OBJECT_TYPE => dbms_repair.table_object,
FLAGS => dbms_repair.skip_flag);
END;
/
10. Rebuilds the free_list:
declare
begin
dbms_repair.rebuild_freelists (
schema_name => ‘DWH_TEST’,
object_name => ‘DWH’,
object_type => dbms_repair.table_object);
end;
/
11. Rebuild the indexes, which are recorded in ORPHAN_KEY_TABLE.
alter index IND_DWH_01 rebuild ONLINE;
alter index IND_DWH_02 rebuild ONLINE;
Sources:
Note.28814.1 Ext/Pub Handling Oracle Block Corruptions in Oracle7/8/8i/9i/10g/11g P1-914CU. RDBMS.CO-3-2 RDBMS.CO RDBMS.CO :
Note.68013.1 Ext/Pub DBMS_REPAIR example
Note 285586.1 – ORA-600 [kdsgrp1]
Note 468883.1 – ORA-600 [kdsgrp1]
Leave A Comment