Introduction Oracle ORA-01114 `IO error writing block to file` occurs when the database cannot write a data block to disk. This is typically caused by storage subsystem failures, filesystem issues, or permission problems. Unlike logical corruption, I/O errors indicate a physical storage problem that must be resolved before Oracle can operate normally.

Symptoms - `ORA-01114: IO error writing block to file N (block # M)` - `ORA-27072: File I/O error` or `ORA-01110: data file N: 'path'` - Checkpoint processes (CKPT, DBWn) reporting write errors - Tablespace going offline automatically - Alert log showing repeated I/O errors for specific datafiles

Common Causes - Disk failure or bad sectors on the storage device - NFS mount disconnected or read-only - Filesystem full preventing block writes - Permission change on the datafile directory - Storage array controller failover causing temporary I/O errors

Step-by-Step Fix 1. **Identify the affected datafile": ```sql SELECT file#, name, status, bytes / 1024 / 1024 AS size_mb, enabled FROM v$datafile WHERE file# = <file_number_from_error>; ```

  1. 1.**Check the storage and filesystem":
  2. 2.```bash
  3. 3.# Check disk space
  4. 4.df -h /u01/app/oracle/oradata/

# Check filesystem health ls -la /u01/app/oracle/oradata/ORCL/users01.dbf

# Check for I/O errors in system logs dmesg | grep -i "error|fail|scsi"

# For NFS, check mount status mount | grep oracle showmount -e nfs-server ```

  1. 1.**Attempt to bring the tablespace online":
  2. 2.```sql
  3. 3.-- If the tablespace went offline
  4. 4.ALTER TABLESPACE users ONLINE;

-- If the datafile needs recovery ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ORCL/users01.dbf' OFFLINE;

-- Recover the datafile RECOVER DATAFILE '/u01/app/oracle/oradata/ORCL/users01.dbf';

-- Bring it back online ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ORCL/users01.dbf' ONLINE; ```

  1. 1.**If block media recovery is needed":
  2. 2.```sql
  3. 3.-- Check for corrupted blocks
  4. 4.SELECT * FROM v$database_block_corruption;

-- Perform block media recovery (requires RMAN) RMAN> BLOCKRECOVER DATAFILE 4 BLOCK 1234;

-- Or recover all corrupted blocks RMAN> BLOCKRECOVER CORRUPTION LIST; ```

  1. 1.**If the datafile is permanently lost, restore from backup":
  2. 2.```sql
  3. 3.-- In RMAN
  4. 4.RMAN> RUN {
  5. 5.SQL 'ALTER DATABASE DATAFILE 4 OFFLINE';
  6. 6.RESTORE DATAFILE 4;
  7. 7.RECOVER DATAFILE 4;
  8. 8.SQL 'ALTER DATABASE DATAFILE 4 ONLINE';
  9. 9.};
  10. 10.`

Prevention - Use redundant storage (RAID, ASM with mirroring) - Monitor disk health with SMART and storage array alerts - Set up I/O latency monitoring on Oracle datafile mounts - Use ASM (Automatic Storage Management) for automatic block mirroring - Implement RMAN block change tracking for faster block recovery - Test storage failover procedures regularly - Monitor `v$database_block_corruption` with automated RMAN checks