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

Cause: Oracle can't find your data file in your physical location cause of delete or lost your data file.

Solution : In this situation if you have RMAN backup you can use this solution and If you have not RMAN Backup then you can drop your data file information from control file using this (http://rajiboracle.blogspot.com/2014/03/ora-01157-cannot-identifylock-data-file.html) way. Drop data file will lost your all of data inside the data file. Drop data file is last option for recover your remaining data from database.


C:\Users\rajib.pradhan>SET ORACLE_SID=DBRMAN

C:\Users\rajib.pradhan>RMAN TARGET/

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Sep 16 11:09:55 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DBRMAN (DBID=3796675194, not open)

RMAN> ALTER DATABASE OPEN;

using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 09/16/2014 11:12:12
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: 'D:\APP\ORADATA\DBRMAN\USERS01.DBF'

RMAN> SHUTDOWN ABORT;

Oracle instance shut down

RMAN> STARTUP FORCE MOUNT;

Oracle instance started
database mounted

Total System Global Area     535662592 bytes

Fixed Size                     1375792 bytes
Variable Size                406847952 bytes
Database Buffers             121634816 bytes
Redo Buffers                   5804032 bytes

RMAN> RESTORE DATABASE;

Starting restore at 16-SEP-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=129 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to D:\APP\ORADATA\DBRMAN\SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00002 to D:\APP\ORADATA\DBRMAN\SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00003 to D:\APP\ORADATA\DBRMAN\UNDOTBS01.DBF
channel ORA_DISK_1: restoring datafile 00004 to D:\APP\ORADATA\DBRMAN\USERS01.DBF
channel ORA_DISK_1: restoring datafile 00005 to D:\APP\ORADATA\DBRMAN\CATALOG_SPC01.DBF
channel ORA_DISK_1: restoring datafile 00006 to D:\APP\ORADATA\DBRMAN\TBS_RAMN_CAT_01.DBF
channel ORA_DISK_1: restoring datafile 00010 to D:\APP\ORADATA\DBRMAN\EXAMPLE01.DBF
channel ORA_DISK_1: reading from backup piece D:\APP\ORADATA\DBRMAN_BACKUP\BKP_DFDBRMAN_858423127_94_1_2UPIKVQN
channel ORA_DISK_1: piece handle=D:\APP\ORADATA\DBRMAN_BACKUP\BKP_DFDBRMAN_858423127_94_1_2UPIKVQN tag=TAG20140916T105207
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:46
Finished restore at 16-SEP-14

RMAN> RECOVER DATABASE;

Starting recover at 16-SEP-14
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 169 is already on disk as file D:\APP\ORADATA\DBRMAN_ARCHIVE\ARC0000000169_0850433149.0001
archived log for thread 1 with sequence 170 is already on disk as file D:\APP\ORADATA\DBRMAN_ARCHIVE\ARC0000000170_0850433149.0001
archived log for thread 1 with sequence 171 is already on disk as file D:\APP\ORADATA\DBRMAN_ARCHIVE\ARC0000000171_0850433149.0001
archived log for thread 1 with sequence 172 is already on disk as file D:\APP\ORADATA\DBRMAN_ARCHIVE\ARC0000000172_0850433149.0001
archived log for thread 1 with sequence 173 is already on disk as file D:\APP\ORADATA\DBRMAN_ARCHIVE\ARC0000000173_0850433149.0001
archived log file name=D:\APP\ORADATA\DBRMAN_ARCHIVE\ARC0000000169_0850433149.0001 thread=1 sequence=169
archived log file name=D:\APP\ORADATA\DBRMAN_ARCHIVE\ARC0000000170_0850433149.0001 thread=1 sequence=170
archived log file name=D:\APP\ORADATA\DBRMAN_ARCHIVE\ARC0000000171_0850433149.0001 thread=1 sequence=171
media recovery complete, elapsed time: 00:00:27
Finished recover at 16-SEP-14

RMAN> ALTER DATABASE OPEN;

database opened

RMAN>