ORA-01157: cannot identify/lock data file string – see DBWR trace file

ORA-01157 is raised when Database Writer (DBWR) is unable to find and lock a Datafile. This may be due to various reasons like ,

• Datafile is deleted or corrupt.
• Datafile is renamed or moved.
• Mount point is incorrect
• Issues with Read/write permission on Datafile

Solution :

1. If datafile is renamed or moved then we need to get it in its original position.
2. If datafile is deleted or corrupt and do not contain important segments, that can be dropped offline.
3. If datafile is deleted or corrupt , we can recovered it by using a valid backup.
4. If Mount point is incorrect, simply recreate the mount point.
5. If it is due to permission then we need to grant the permission at OS level.

SQL> select NAME from v$datafile where file#=16;
NAME
--------------------------------------------------------------------------------
E:\ORACLE\APP\ORACLE\ORADATA\PRIM\USER1.DBF

SQL> alter database datafile 16 OFFLINE DROP;
Database altered.

SQL> alter database open;
Database altered.