How to Diagnose and Repair Bad Blocks in Oracle Databases

Understanding Bad Blocks in Oracle

Oracle stores data in fixed‑size blocks that consist of a cache layer, a transaction layer, and a data layer. During read/write operations the database checks block consistency; mismatches cause the block to be marked as corrupt. Bad blocks are classified as logical or physical.

Impact of Bad Blocks

When a bad block is encountered the alert log records errors such as ORA‑1578, ORA‑1110, and ORA‑600. The numeric parameter of ORA‑600 indicates which layer (cache 2000‑4000, transaction 4000‑6000, data 6000‑8000) is affected.

Common Causes

Hardware I/O errors

Operating‑system I/O errors or buffering issues

Memory or paging problems

Disk‑repair utilities

Partial overwrites of datafiles

Oracle attempting to access an unformatted system block

Datafile overflow

Bugs in Oracle or the OS

Detection and Information Gathering

Collect details from alertSID.log or trace files. Useful queries include:

SELECT file_name, tablespace_name, file_id "AFN", relative_fno "RFN" FROM dba_data_files;

SELECT file_name, tablespace_name, file_id, relative_fno "RFN" FROM dba_temp_files;

Identify the object containing the bad block:

SELECT tablespace_name, segment_type, owner, segment_name, partition_name FROM dba_extents WHERE file_id = :file_id AND block_id BETWEEN :start_block AND :start_block + :blocks - 1;

If the bad block resides in a temporary file, no row is returned.

Recovery Methods

Restore the entire datafile (requires archived mode, a complete physical backup, and RMAN catalog). Steps:

Take the affected file offline: ALTER DATABASE DATAFILE 'name_file' OFFLINE; Copy or rename the backup file if needed: ALTER DATABASE RENAME FILE 'old_name' TO 'new_name'; Recover the file: RECOVER DATAFILE 'name_of_file'; Bring the file back online: ALTER DATABASE DATAFILE 'name_of_file' ONLINE; Recover specific bad blocks (Oracle 9i+) . Requires RMAN 9.2.0+, a catalog, archived mode, and a full backup. Example command: RMAN> RUN { BLOCKRECOVER DATAFILE 5 BLOCK 11,16; } Optionally restore to a prior SCN:

RMAN> RUN { BLOCKRECOVER DATAFILE 5 BLOCK 11,16 RESTORE UNTIL SEQUENCE 8505; }

Salvage data with ROWID RANGE SCAN . Steps:

Determine the minimum and maximum ROWIDs of the corrupt block.

Create a temporary table to hold good rows:

CREATE TABLE salvage_table AS SELECT * FROM corrupt_tab WHERE 1=2;

Insert good rows before and after the bad ROWID range using

INSERT INTO salvage_table SELECT /*+ ROWID(A) */ * FROM A WHERE rowid < 'min_rowid';

and similar for >= 'max_rowid'.

Rebuild the original table from the temporary table and recreate indexes and constraints.

Skip corrupt blocks during scans . Two approaches:

Set session‑level event 10231:

ALTER SESSION SET EVENTS '10231 TRACE NAME CONTEXT FOREVER, LEVEL 10';

Or add event="10231 trace name context forever, level 10" to the init.ora and restart.

Use the DBMS_REPAIR.SKIP_CORRUPT_BLOCKS package: EXEC DBMS_REPAIR.SKIP_CORRUPT_BLOCKS('schema','table'); Then export or recreate the table without the bad blocks.

Pre‑emptive Detection

To locate bad blocks before they cause failures:

Export the whole database with exp (does not detect blocks above the high‑water mark, index blocks, or dictionary blocks).

Validate specific tables with ANALYZE TABLE tablename VALIDATE STRUCTURE CASCADE; Results are written to trace files in USER_DUMP_DEST.

Use the Oracle DBVERIFY utility. Example: dbv file=system01.dbf blocksize=8192 The tool reports total pages, processed pages, and any corrupted pages.