DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01116 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01116: Error in Opening Database File — Causes, Fixes & Prevention

ORA-01116 occurs when Oracle is unable to open a database datafile during startup, recovery, or normal operation. This error typically surfaces alongside ORA-01110 (which identifies the specific file) and ORA-27041 (an OS-level open failure). Immediate action is required, as this error can cause partial or complete database unavailability.

Top 3 Causes

1. Missing or Relocated Datafile

The most common cause — the physical file has been deleted, moved, or is inaccessible at the path recorded in the control file.

-- Check datafile status and paths
SELECT file#, status, name
FROM v$datafile
WHERE status != 'ONLINE';

-- If file was moved, rename in control file (run in MOUNT mode)
STARTUP MOUNT;

ALTER DATABASE RENAME FILE
  '/old/path/datafile01.dbf'
TO '/new/path/datafile01.dbf';

ALTER DATABASE OPEN;
Enter fullscreen mode Exit fullscreen mode

2. OS-Level Permission or Mount Point Failure

Oracle processes lack read/write permission on the file, or an NFS mount point / ASM disk group has become unavailable.

-- Identify files needing recovery
SELECT d.file#, d.name, d.status, t.name AS tablespace_name
FROM v$datafile d
JOIN v$tablespace t ON d.ts# = t.ts#
WHERE d.status IN ('RECOVER', 'OFFLINE');

-- After fixing OS permissions, bring file online
ALTER DATABASE DATAFILE '/path/to/datafile01.dbf' ONLINE;
Enter fullscreen mode Exit fullscreen mode 
# Fix OS permissions (Linux/Unix)
# chown oracle:dba /path/to/datafile01.dbf
# chmod 640 /path/to/datafile01.dbf
Enter fullscreen mode Exit fullscreen mode

3. Datafile Header Corruption

Hardware failure or abnormal shutdown can corrupt the file header, making it unreadable even though the file physically exists.

-- Offline the corrupted file and open DB
ALTER DATABASE DATAFILE '/path/to/datafile01.dbf' OFFLINE;
ALTER DATABASE OPEN;

-- Restore and recover using RMAN
RMAN> RESTORE DATAFILE 5;
RMAN> RECOVER DATAFILE 5;

-- Bring the file back online
ALTER DATABASE DATAFILE 5 ONLINE;

-- Verify final status
SELECT file#, name, status FROM v$datafile;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Summary 

-- Step 1: Mount the database if it won't open
STARTUP MOUNT;

-- Step 2: Identify the problem file
SELECT file#, name, status FROM v$datafile;

-- Step 3a: Rename if file was moved
ALTER DATABASE RENAME FILE '/old/path/file.dbf' TO '/new/path/file.dbf';
ALTER DATABASE OPEN;

-- Step 3b: Full DB restore if file is lost
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN RESETLOGS;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Schedule regular RMAN backups and validate them:

-- Validate all datafiles for physical corruption
RMAN> BACKUP VALIDATE DATABASE;

-- List available backups
RMAN> LIST BACKUP OF DATABASE SUMMARY;
Enter fullscreen mode Exit fullscreen mode

2. Automate datafile health monitoring:

-- Run this query regularly via a scheduled job or OEM alert
SELECT file#, name, status,
       bytes/1024/1024 AS size_mb
FROM v$datafile
WHERE status NOT IN ('ONLINE', 'SYSTEM')
ORDER BY file#;
Enter fullscreen mode Exit fullscreen mode

Related Errors

Error Code Description
ORA-01110 Identifies the specific file number and name involved
ORA-27041 OS-level failure to open the file
ORA-01157 Cannot identify/lock datafile — similar cause
ORA-00376 File cannot be read in its current state
ORA-01578 Physical block corruption within the datafile

Key Takeaway: ORA-01116 is almost always recoverable — provided you have a valid RMAN backup. Always verify your backups regularly, and never ignore datafile status alerts.

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