🧩 ORA-01116: Error in Opening Database File

🧩 Introduction

The error ORA-01116: error in opening database file occurs when Oracle fails to access or open a specific datafile. This usually happens during database startup, recovery operations, or when querying objects stored in the affected datafile. If not resolved, it can prevent the database or specific tablespaces from functioning properly.

Common Causes

1. Missing Datafile

The file was deleted accidentally from the filesystem.

2. Incorrect File Path

The datafile path in the controlfile does not match the actual location on disk.

3. Permission Issues

Oracle user does not have read/write access to the datafile.

4. Corrupted Filesystem or Disk

Storage-related corruption prevents access.

5. Mount or ASM Problems

Filesystem not mounted or ASM diskgroup unavailable.

Step-by-Step Solution

1. Identify the Problematic File

Check the alert log for details about which file is failing.

SELECT name FROM v$datafile WHERE file# = <file_number>;

2. Verify File Existence

Check if the file exists on the OS:

ls -l <datafile_path>

3. Correct the Path (if moved)

If the file has been moved, update the controlfile:

ALTER DATABASE RENAME FILE '/old_path/filename.dbf' TO '/new_path/filename.dbf';

4. Restore Missing Datafile from Backup

If the file is missing, restore it:

RESTORE DATAFILE <file_number>;

RECOVER DATAFILE <file_number>;

5. Check Permissions

Ensure Oracle user can access the file:

chmod 660 <datafile_path>

chown oracle:dba <datafile_path>

6. Verify ASM or Filesystem Availability

If using ASM, check the diskgroup status:

SELECT name, state FROM v$asm_diskgroup;

Example Scenario

During startup, the database throws ORA-01116 indicating that file #5 could not be opened. Investigation shows the file was moved to a different mount point after storage migration. The DBA corrected the path in the controlfile using ALTER DATABASE RENAME FILE, and the database opened successfully.

Preventive Measures

Always use RMAN to move or rename datafiles.

Monitor storage changes and mount points before restarting the database.

Keep frequent backups to restore missing or corrupted files.

Use ASM for better file management and redundancy.

Conclusion

The error ORA-01116: error in opening database file often arises from missing or misplaced datafiles, permission issues, or storage problems. By carefully checking file paths, restoring missing files, and ensuring proper storage availability, DBAs can resolve this issue and prevent downtime.