ORA-01578: ORACLE Data Block Corrupted with RMAN or DBMS_REPAIR package

How to restore the Oracle corrupted Data block without backup

When Oracle detects corruption in a database block, it throws the error:

ORA-01578: ORACLE data block corrupted (file # <file>, block # <block>)

This error means Oracle found invalid data inside a physical data block stored in a datafile.
If you don’t fix it, it can lead to data loss, incorrect queries, OR downtime.

1. Find the Corrupted Blocks:

Oracle shows file and block number in the error.

Example:

ORA-01578: ORACLE data block corrupted (file # 5, block # 30123)
ORA-01110: data file 5: '/u01/oradata/PROD/users01.dbf'

Now we know:

• File ID = 5
• Block number = 30123

To find table/object impacted:

Command: Find Object Name

SELECT owner, segment_name, segment_type
FROM dba_extents
WHERE file_id = 5
AND 30123 BETWEEN block_id AND block_id + blocks - 1;

Two option one is having RMAN backup or other if table is not important the we can skipped the corrupted damaged rows using DBMS_REPAIR Method

Method 1: Rman

Verify Corruption Using RMAN

-- Check the corruption with RMAN commands:
-- Scan whole database for corruption
RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE;

-- Scan a single tablespace:
RMAN> BACKUP VALIDATE CHECK LOGICAL TABLESPACE users;

Fixing the corruption using RMAN commands:

-- Recover the single block in datafile 5
RMAN> BLOCKRECOVER DATAFILE 5 BLOCK 30123;

-- Recover multiple blocks if you find in validation command:
RMAN> BLOCKRECOVER DATAFILE 5 BLOCK 30123, 30124, 30125;

Method 2: Mark Block Corrupt (No Backup)

If the table data is not important or you can skip damaged rows, use DBMS_REPAIR.

Step 1: Create repair table

BEGIN
  DBMS_REPAIR.ADMIN_TABLES(
    table_name => 'REPAIR_TABLE',
    table_type => DBMS_REPAIR.ADMIN_TABLE_TYPE,
    action     => DBMS_REPAIR.CREATE_ACTION,
    tablespace => 'USERS'
  );
END;
/

Step 2: Detect corruption

BEGIN
  DBMS_REPAIR.CHECK_OBJECT(
    schema_name => 'HR',
    object_name => 'EMPLOYEES',
    repair_table_name => 'REPAIR_TABLE'
  );
END;
/

Step 3: Mark block as corrupt (skips errors)

BEGIN
  DBMS_REPAIR.FIX_CORRUPT_BLOCKS(
    schema_name => 'HR',
    object_name => 'EMPLOYEES',
    repair_table_name => 'REPAIR_TABLE'
  );
END;
/

Step 4: Skip corrupt rows automatically

BEGIN
  DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(
    schema_name => 'HR',
    object_name => 'EMPLOYEES',
    flags       => DBMS_REPAIR.SKIP_FLAG
  );
END;
/

Now the table can be queried and Oracle will skip the damaged rows.