ORA-01157: Cannot Identify/Lock Data File – Causes, Fixes & Prevention
ORA-01157 is a critical Oracle database error that occurs when the Database Writer (DBWR) background process cannot identify or acquire a lock on a data file. This error typically surfaces during database startup or normal operations when a data file becomes inaccessible at the OS level. It almost always appears alongside ORA-01110, which identifies the specific file causing the issue.
Top 3 Causes
1. Physical Loss or Deletion of Data File
The most common cause is when a data file has been accidentally deleted or is physically unavailable due to storage failure. The control file still holds a reference to the file path, but the actual file no longer exists on disk.
-- Check data file status to identify the missing file
SELECT file#, status, name
FROM v$datafile
WHERE status != 'ONLINE';
-- Mount the database and take the file offline
STARTUP MOUNT;
ALTER DATABASE DATAFILE '/u01/oradata/ORCL/users01.dbf' OFFLINE;
ALTER DATABASE OPEN;
-- Restore and recover using RMAN
-- Run in RMAN session:
-- RESTORE DATAFILE '/u01/oradata/ORCL/users01.dbf';
-- RECOVER DATAFILE '/u01/oradata/ORCL/users01.dbf';
-- Bring the file back online after recovery
ALTER DATABASE DATAFILE '/u01/oradata/ORCL/users01.dbf' ONLINE;
2. File Permission or Ownership Issues
Even if the data file exists, ORA-01157 will occur if the Oracle OS user (oracle) lacks read/write permissions on the file. This often happens after OS patching, storage migration, or NFS remounting changes file ownership.
-- Identify the file path needing permission fix
SELECT file#, name FROM v$datafile;
# Fix ownership and permissions at OS level
chown oracle:oinstall /u01/oradata/ORCL/users01.dbf
chmod 640 /u01/oradata/ORCL/users01.dbf
-- Restart the database after fixing permissions
SHUTDOWN ABORT;
STARTUP;
3. File Path Mismatch After Storage Reorganization
When a data file is moved to a new location but the control file still holds the old path, Oracle cannot find the file. This commonly happens after ASM migrations, filesystem restructuring, or NFS mount point changes.
-- Update the control file with the new file path
STARTUP MOUNT;
ALTER DATABASE RENAME FILE '/old/path/users01.dbf'
TO '/new/path/users01.dbf';
-- Verify the change
SELECT file#, name FROM v$datafile;
-- Open the database
ALTER DATABASE OPEN;
Quick Fix Summary
| Cause | Quick Fix |
|---|---|
| File deleted | OFFLINE the file, restore from RMAN backup |
| Permission issue | Fix OS-level permissions, restart DB |
| Wrong path | Use ALTER DATABASE RENAME FILE in MOUNT mode |
Prevention Tips
1. Proactive Monitoring with Automated Alerts
Schedule a monitoring job to regularly check data file status and alert the DBA team before a minor issue escalates into an outage.
-- Schedule this query via DBMS_SCHEDULER or cron
SELECT file#, name, status
FROM v$datafile
WHERE status NOT IN ('ONLINE', 'SYSTEM');
2. Maintain a Robust RMAN Backup Strategy
Implement a full + incremental RMAN backup policy and validate backups regularly. Without a valid backup, recovery from ORA-01157 caused by file deletion can be catastrophic.
-- Check recent RMAN backup status
SELECT bs.status, bs.start_time, bs.completion_time
FROM v$backup_set bs
ORDER BY bs.start_time DESC
FETCH FIRST 5 ROWS ONLY;
Related Errors
- ORA-01110 – Always accompanies ORA-01157; specifies the exact file number and path.
- ORA-01115 – I/O error reading a data file; similar root cause related to storage issues.
- ORA-27037 – Unable to obtain file status at the OS level; often seen alongside ORA-01157 during NFS or storage failures.
📖 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)