DBMS_REPAIR SCRIPT - ORA-26040: Data block was loaded using the NOLOGGING option

Applies to:

Oracle Server - Enterprise Edition - Version 8.1.5.0 to 11.2.0.3 [Release 8.1.5 to 11.2]

Purpose

This script is intended to provide a simple and quick way to run DBMS_REPAIR to identify and skip corrupted blocks

Script

REM Create the repair table in a given tablespace:

BEGIN
  DBMS_REPAIR.ADMIN_TABLES (
  TABLE_NAME => 'REPAIR_TABLE',
  TABLE_TYPE => dbms_repair.repair_table,
  ACTION => dbms_repair.create_action,
  TABLESPACE => '&tablespace_name');
END;
/ 

REM Identify corrupted blocks for schema.object:

set serveroutput on
DECLARE num_corrupt INT;
BEGIN
  num_corrupt := 0;
  DBMS_REPAIR.CHECK_OBJECT (
  SCHEMA_NAME => '&schema_name',
  OBJECT_NAME => '&object_name',
  REPAIR_TABLE_NAME => 'REPAIR_TABLE',
  corrupt_count => num_corrupt);
  DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));
END;
/

REM Optionally display any corrupted block identified by check_object:

select BLOCK_ID, CORRUPT_TYPE, CORRUPT_DESCRIPTION 
from REPAIR_TABLE;

REM Mark the identified blocks as corrupted

DECLARE num_fix INT;
BEGIN
  num_fix := 0;
  DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
  SCHEMA_NAME => '&schema_name',
  OBJECT_NAME=> '&object_name',
  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;
/

REM Allow future DML statements to skip the corrupted blocks:

BEGIN
  DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
  SCHEMA_NAME => '&schema_name',
  OBJECT_NAME => '&object_name',
  OBJECT_TYPE => dbms_repair.table_object,
  FLAGS => dbms_repair.SKIP_FLAG);
END;
/

Notes:

• Recreate indexes after using DBMS_REPAIR as INDEX scan may produce errors if accessing the corrupt block. If there is an unique index, then reinserting the same data may also produce error ORA-1.
• Use the dbms_repair.NOSKIP_FLAG in the FLAGS value in procedure SKIP_CORRUPT_BLOCKS if it is needed to stop skipping corrupt blocks in the object after the dbms_repair.SKIP_FLAG was used. 
• If the goal is to skip the corrupt blocks for a specific object, it is just needed to run procedure SKIP_CORRUPT_BLOCKS. Only blocks producing ORA-1578 will be skipped in that case. If different errors are produced then it is required to run these additional procedures: ADMIN_TABLES, CHECK_OBJECT and FIX_CORRUPT_BLOCKS 
• If it is needed to clear a table from corruptions and after using procedure SKIP_CORRUPT_BLOCKS, the table can be moved with: "alter table MOVE" instead of recreating or truncating it. Then use the dbms_repair.NOSKIP_FLAG described above. Note that the data inside the corrupt blocks is lost.

Reference: 

DBMS_REPAIR SCRIPT [ID 556733.1]