DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01114 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01114: IO Error Writing Block to File — Causes, Fixes & Prevention

ORA-01114 is a serious Oracle database error that occurs when the database engine fails to write a data block to a datafile due to an I/O failure at the operating system or storage level. This error is often accompanied by ORA-27072, which provides the underlying OS-level error code for more precise diagnosis. Because this error directly threatens data integrity, it requires immediate investigation and action.

Top 3 Causes & SQL Examples

Cause 1: Disk Space Exhausted

The most common cause is the filesystem running out of space, preventing Oracle from extending or writing to a datafile.

-- Check tablespace usage to identify full tablespaces
SELECT
    df.tablespace_name,
    ROUND(df.total_mb, 2)                          AS total_mb,
    ROUND(df.total_mb - NVL(fs.free_mb, 0), 2)    AS used_mb,
    ROUND(NVL(fs.free_mb, 0), 2)                   AS free_mb,
    ROUND((df.total_mb - NVL(fs.free_mb,0))
          / df.total_mb * 100, 1)                   AS used_pct
FROM
    (SELECT tablespace_name, SUM(bytes)/1024/1024 AS total_mb
     FROM dba_data_files GROUP BY tablespace_name) df
LEFT JOIN
    (SELECT tablespace_name, SUM(bytes)/1024/1024 AS free_mb
     FROM dba_free_space GROUP BY tablespace_name) fs
ON df.tablespace_name = fs.tablespace_name
ORDER BY used_pct DESC;

-- Fix: Add a new datafile to the tablespace
ALTER TABLESPACE USERS
ADD DATAFILE '/oradata/ORCL/users02.dbf'
SIZE 5G AUTOEXTEND ON NEXT 512M MAXSIZE 20G;

-- Fix: Resize an existing datafile
ALTER DATABASE DATAFILE '/oradata/ORCL/users01.dbf' RESIZE 10G;
Enter fullscreen mode Exit fullscreen mode

Cause 2: Hardware / Storage Failure

Bad disk sectors, HBA failures, or broken SAN/NAS connectivity can cause I/O errors at the block level. Always check OS-level logs (dmesg, /var/log/messages) alongside Oracle's alert log.

-- Identify the affected file using the file# from the error message
SELECT file#, name, status, bytes/1024/1024 AS size_mb
FROM v$datafile
WHERE file# = <file_number_from_error>;

-- Check for physically corrupted blocks
SELECT * FROM v$database_block_corruption;

-- Validate the datafile integrity via RMAN
-- Run in an RMAN session:
-- RMAN> VALIDATE DATAFILE '/oradata/ORCL/users01.dbf';
-- RMAN> RECOVER DATAFILE '/oradata/ORCL/users01.dbf';

-- If restore is needed:
-- RMAN> RESTORE DATAFILE '/oradata/ORCL/users01.dbf';
-- RMAN> RECOVER DATAFILE '/oradata/ORCL/users01.dbf';
Enter fullscreen mode Exit fullscreen mode

Cause 3: Datafile Offline or Permission Issue

A datafile taken offline manually or a permissions change on the OS level will also trigger ORA-01114.

-- Find offline datafiles
SELECT file#, name, status
FROM v$datafile
WHERE status != 'ONLINE';

-- Bring a datafile back online
ALTER DATABASE DATAFILE '/oradata/ORCL/users01.dbf' ONLINE;

-- Or bring the entire tablespace online
ALTER TABLESPACE USERS ONLINE;

-- Confirm the fix
SELECT file#, name, status FROM v$datafile ORDER BY file#;
Enter fullscreen mode Exit fullscreen mode

OS-level fix (run as root or oracle OS user):

chown oracle:oinstall /oradata/ORCL/users01.dbf
chmod 640 /oradata/ORCL/users01.dbf

Quick Fix Checklist

  1. Check Oracle alert log for the exact file# and accompanying ORA-27072 message.
  2. Verify filesystem free space with df -h on Linux/Unix.
  3. Confirm datafile status using v$datafile.
  4. Check OS logs for hardware errors.
  5. Use RMAN to validate and recover corrupted datafiles.

Prevention Tips

  • Automate capacity monitoring: Schedule a script or use Oracle Enterprise Manager (OEM) to alert when any tablespace exceeds 80% usage. Enable AUTOEXTEND with a reasonable MAXSIZE as a safety net. 
-- Enable autoextend on existing datafiles
ALTER DATABASE DATAFILE '/oradata/ORCL/users01.dbf'
AUTOEXTEND ON NEXT 256M MAXSIZE 30G;
Enter fullscreen mode Exit fullscreen mode
  • Validate RMAN backups regularly: A verified backup is your only recovery option when datafile corruption occurs. Run BACKUP VALIDATE CHECK LOGICAL DATABASE; at least once a month to proactively detect block corruption before it causes an outage.

Related Errors

Error Code Description
ORA-01113 Datafile needs media recovery
ORA-01115 IO error reading block from file
ORA-01116 Error opening datafile
ORA-27072 OS-level file I/O error detail
ORA-19502 Write error on file during RMAN backup

📖 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)