DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01115 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01115: IO Error Reading Block from File — Causes, Fixes & Prevention

ORA-01115 is a critical Oracle error that occurs when the database engine fails to read a specific block from a datafile due to an I/O error at the OS or storage layer. This error often signals physical storage failures, file system corruption, or block-level data corruption within the datafile itself. Immediate investigation is essential, as this error can indicate potential data loss.

Top 3 Causes

1. Physical Storage or Disk Failure (Bad Sectors / SAN Issues)

The most common root cause is a failing disk, bad sector, or SAN/NAS storage malfunction. When Oracle attempts to read a block and the storage subsystem cannot fulfill the I/O request, ORA-01115 is raised.

-- Check datafile status immediately
SELECT FILE#, NAME, STATUS, BYTES/1024/1024 AS SIZE_MB
FROM V$DATAFILE
ORDER BY FILE#;

-- Check datafile header status
SELECT FILE#, STATUS, FUZZY, CHECKPOINT_CHANGE#
FROM V$DATAFILE_HEADER;
Enter fullscreen mode Exit fullscreen mode

Always check OS-level logs (/var/log/messages on Linux, Event Viewer on Windows) alongside Oracle's alert log to correlate storage errors.

2. Block Corruption (Logical or Physical)

A block may be physically read but contain an invalid checksum or corrupted header, resulting in a logical block corruption. This often appears alongside ORA-01578.

-- Validate database blocks using RMAN
RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE;

-- Check for corrupted blocks after validation
SELECT FILE#, BLOCK#, BLOCKS, CORRUPTION_TYPE
FROM V$DATABASE_BLOCK_CORRUPTION;

-- Recover specific corrupted blocks
RMAN> RECOVER DATAFILE 5 BLOCK 100;

-- Recover all listed corrupted blocks at once
RMAN> RECOVER CORRUPTION LIST;
Enter fullscreen mode Exit fullscreen mode

3. Missing or Inaccessible Datafile (Permissions / Accidental Deletion)

If a datafile has been accidentally deleted, moved, or had its OS-level permissions changed, Oracle cannot read from it, triggering ORA-01115.

-- Identify the affected file path
SELECT FILE#, NAME, STATUS
FROM V$DATAFILE
WHERE STATUS != 'ONLINE';

-- Attempt to bring an offline file back online
ALTER DATABASE DATAFILE '/oracle/oradata/prod/users01.dbf' ONLINE;

-- If the file was moved, rename it in the control file
ALTER DATABASE RENAME FILE '/old_path/users01.dbf'
  TO '/new_path/users01.dbf';
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Step 1 — Identify the exact file and block from the alert log:

-- Find alert log and trace file locations
SELECT NAME, VALUE FROM V$DIAG_INFO
WHERE NAME IN ('Diag Trace', 'Diag Alert');
Enter fullscreen mode Exit fullscreen mode

Step 2 — Use DBMS_REPAIR if RMAN recovery is not immediately possible:

-- Create repair table
BEGIN
  DBMS_REPAIR.ADMIN_TABLES(
    TABLE_NAME => 'REPAIR_TABLE',
    TABLE_TYPE => DBMS_REPAIR.REPAIR_TABLE,
    ACTION     => DBMS_REPAIR.CREATE_ACTION,
    TABLESPACE => 'SYSAUX'
  );
END;
/

-- Soft-mark corrupt blocks to allow table access (skip corrupt blocks)
BEGIN
  DBMS_REPAIR.FIX_CORRUPT_BLOCKS(
    SCHEMA_NAME       => 'SCOTT',
    OBJECT_NAME       => 'EMP',
    REPAIR_TABLE_NAME => 'REPAIR_TABLE'
  );
END;
/
Enter fullscreen mode Exit fullscreen mode

Step 3 — Restore from RMAN backup if corruption is unrecoverable:

RMAN> RESTORE DATAFILE 5;
RMAN> RECOVER DATAFILE 5;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

Enable Block Checksumming:

Configure Oracle to automatically validate block integrity on every write, catching corruption early before it causes read failures.

-- Enable block checksum (recommended: TYPICAL or FULL)
ALTER SYSTEM SET DB_BLOCK_CHECKSUM = TYPICAL SCOPE=BOTH;

-- Enable block checking for additional write-time validation
ALTER SYSTEM SET DB_BLOCK_CHECKING = MEDIUM SCOPE=BOTH;
Enter fullscreen mode Exit fullscreen mode

Schedule Regular RMAN Validation:

Run BACKUP VALIDATE at least once a week to proactively detect block corruption before it causes production outages. Integrate this into your standard DBA maintenance scripts.

-- Weekly validation job (run via RMAN script)
RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE;
RMAN> SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;
Enter fullscreen mode Exit fullscreen mode

Related Errors

  • ORA-01578 — Identifies the specific corrupted block (file # and block #); almost always appears alongside ORA-01115.
  • ORA-01110 — Reports the datafile name and number associated with the error.
  • ORA-27072 / ORA-27091 — OS-level I/O failure errors that appear below ORA-01115 in the error stack, providing additional OS-level diagnostics.
  • ORA-00376 — Raised when Oracle cannot read a datafile because it is offline or requires recovery.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)