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]