ORA-01157: cannot identify/lock data file and ORA-01110

 ORA-01157 error along with ORA-01110 in alert log file means that a datafile that DBWR is trying to access (to read or write) is no longer available. One reason coule be that this file was accidentally removed, or file permissions were accidentally changed, thus hindering oracle processes to read/write this datafile. Another reason could be the file system - where this dataifle existed - is no longer mounted or available.

Application will also not able to work properly if it needs data from this datafile and errors would be returned. If this datafile belongs to SYSTEM or current UNDO tablespace, database may also go down and would not come up until the file is made available.

For a cluster database if you accidentally add a datafile on the local file system of a RAC node, you would still see this error message in alert log file.

Alert log would show entries similar to the following.

Thu May 03 03:52:58 2018 LNS: Standby redo logfile selected for thread 2 sequence 43145 for destination LOG_ARCHIVE_DEST_2 Thu May 03 03:53:04 2018 Archived Log entry 232402 added for thread 2 sequence 43144 ID 0x930f2233 dest 1: Thu May 03 03:58:53 2018 Errors in file /u01/app/oracle/base/diag/rdbms/MYDB/MYDB2/trace/MYDB2_m000_71472.trc: ORA-01157: cannot identify/lock data file 207 - see DBWR trace file ORA-01110: data file 15: '/u05/oracle/oradata/mydata01.dbf' Thu May 03 04:07:50 2018 Thread 2 advanced to log sequence 43146 (LGWR switch)   Current log# 16 seq# 43146 mem# 0: /u03/MYDB/onlinelog/o1_mf_16__1488204883019347_.log   Current log# 16 seq# 43146 mem# 1: /u03/MYDB/onlinelog/o1_mf_16__1488204888783016_.log

In order to resolve ths issue, make the datafile available. After making datafile available, it may require a recovery. If datafile was recently gone unavailable, and then made available (without any corruption), you may just simply execute “recover datafile” command to have data in redo logs applied to it and then bring datafile online. In case datafile was missing for a long period of time, issuing recovery command may show you archived redo logs sequence numbers needed to recover this datafile. You may restore required archived logs from the backup and issue RECOVER command to perform datafile recovery.

If datafile was temporarily unavailable and then becomes available, take it offline, perform recovery, and bring it online.

SQL> alter database datafile '/u05/oracle/oradata/mydata01.dbf’ offline;   SQL> recover datafile '/u05/oracle/oradata/mydata01.dbf’;   SQL> alter database datafile '/u05/oracle/oradata/mydata01.dbf’ on;

Remember that if this datafile belongs to SYSTEM or UNDO tablespace, database instance may crash. You would need to bring database to mount and perform the recovery (datafile would not need to be taken offline in this case, just RECOVER command should suffice during mount mode).

If this datafile was accidentally deleted or is corrupt, you would need to perform restore and recovery of this datafile form a valid backup.

RMAN> RESTORE DATAFILE '/u05/oracle/oradata/mydata01.dbf’; RMAN> RECOVER DATAFILE '/u05/oracle/oradata/mydata01.dbf’;