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

Few days ago when I was trying to start my database , I faced this error. PFB details:-

SQL> startup
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             209718148 bytes
Database Buffers          394264576 bytes
Redo Buffers                7135232 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\AMIT01.DBF'

Cause :-

I checked the following location( C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT) and found out that the file AMIT01.dbf was not present.

Solution :-

There are two solutions to the above problem.
1. If data in this file is not that much important that remove this file information from database and open your database.

2. If data in this file is important and you have backup available for this file and if your database is in archivelog mode than you can recover the data in this file upto the last commit.But if database is in NOARCHIVELOG mode then restoration of data possible only upto the last backup.

Solution 1 :- Make the file Offline and then open your database after that drop that tablespace.


SQL> set lin 400
SQL> col name for a55
SQL> select a.file#,a.name as FILE_NAME,b.name as TABLESPACE_NAME ,status from v$datafile a ,v$tablespace b where a.ts#=b.ts#;

     FILE# FILE_NAME                                                                                             TABLESPACE_NAME                        STATUS

---------- -----------------------------------------------------------------------------------             -----------------------------------------         ------------------

         1 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\SYSTEM01.DBF         SYSTEM                                            SYSTEM

         2 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\UNDOTBS01.DBF     UNDOTBS1                                        ONLINE

         3 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\SYSAUX01.DBF        SYSAUX                                             ONLINE

         4 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\USERS01.DBF         USERS                                               ONLINE

         5 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\AMIT01.DBF            AMIT                                                   ONLINE

SQL> drop tablespace AMIT including contents and datafiles;

drop tablespace AMIT including contents and datafiles

*

ERROR at line 1:

ORA-01109: database not open

SQL> alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\AMIT01.DBF' offline drop;

Database altered.

SQL> alter database open;

Database altered.

SQL> select a.file#,a.name as FILE_NAME,b.name as TABLESPACE_NAME ,status from v$datafile a ,v$tablespace b where a.ts#=b.ts#;

     FILE# FILE_NAME                                                                                             TABLESPACE_NAME                        STATUS

---------- -----------------------------------------------------------------------------------             -----------------------------------------         ------------------

         1 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\SYSTEM01.DBF         SYSTEM                                            SYSTEM

         2 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\UNDOTBS01.DBF     UNDOTBS1                                        ONLINE

         3 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\SYSAUX01.DBF        SYSAUX                                             ONLINE

         4 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\USERS01.DBF         USERS                                               ONLINE

         5 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\AMIT01.DBF            AMIT                                                   OFFLINE

SQL> drop tablespace AMIT including contents and datafiles;

Tablespace dropped.

SQL>  select open_mode from v$database;

OPEN_MODE

----------

READ WRITE

Solution 2 :- Restore and recover the lost file through RMAN

C:\Documents and Settings\amit.rath>rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Feb 26 12:43:18 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: AMIT (DBID=2171747492, not open)

RMAN> list backup of datafile 5;

using target database control file instead of recovery catalog

List of Backup Sets

===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

19      Full    152.00K    DISK        00:00:23     26-FEB-13

        BP Key: 19   Status: AVAILABLE  Compressed: YES  Tag: TAG20130226T123606

        Piece Name: C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\AMIT\BACKUPSET\2013_02_26\O1_MF_NNNDF_TAG20130226T123606_8LRQPZKC_.BKP

  List of Datafiles in backup set 19

  File LV Type Ckp SCN    Ckp Time  Name

  ---- -- ---- ---------- --------- ----

  5       Full 1028343    26-FEB-13 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\AMIT01.DBF

RMAN> restore datafile 5 preview;

Starting restore at 26-FEB-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

List of Backup Sets

===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

19      Full    152.00K    DISK        00:00:23     26-FEB-13

        BP Key: 19   Status: AVAILABLE  Compressed: YES  Tag: TAG20130226T123606

        Piece Name: C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\AMIT\BACKUPSET\2013_02_26\O1_MF_NNNDF_TAG20130226T123606_8LRQPZKC_.BKP

  List of Datafiles in backup set 19

  File LV Type Ckp SCN    Ckp Time  Name

  ---- -- ---- ---------- --------- ----

  5       Full 1028343    26-FEB-13 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\AMIT01.DBF

using channel ORA_DISK_1

List of Backup Sets

===================

BS Key  Size       Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

26      2.50K      DISK        00:00:01     26-FEB-13

        BP Key: 26   Status: AVAILABLE  Compressed: YES  Tag: TAG20130226T123711

        Piece Name: C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\AMIT\BACKUPSET\2013_02_26\O1_MF_ANNNN_TAG20130226T123711_8LRQS0JY_.BKP

  List of Archived Logs in backup set 26

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

  ---- ------- ---------- --------- ---------- ---------

  1    20      1028338    26-FEB-13 1028364    26-FEB-13

Media recovery start SCN is 1028343

Recovery must be done beyond SCN 1028343 to clear data files fuzziness

Finished restore at 26-FEB-13

RMAN> restore datafile 5;

Starting restore at 26-FEB-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00005 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\AMIT01.DBF

channel ORA_DISK_1: reading from backup piece C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\AMIT\BACKUPSET\2013_02_26\O1_MF_NNNDF_TAG20130226T123606_8LRQPZKC_.BKP

channel ORA_DISK_1: restored backup piece 1

piece handle=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\AMIT\BACKUPSET\2013_02_26\O1_MF_NNNDF_TAG20130226T123606_8LRQPZKC_.BKP tag=TAG20130226T123606

channel ORA_DISK_1: restore complete, elapsed time: 00:00:27

Finished restore at 26-FEB-13

RMAN> recover datafile 5 ;

Starting recover at 26-FEB-13

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:01:02

Finished recover at 26-FEB-13

RMAN> exit

Recovery Manager complete.

C:\Documents and Settings\amit.rath>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Feb 26 12:46:12 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter user-name: /as sysdba

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> select open_mode from v$database;

OPEN_MODE

----------

MOUNTED

SQL> alter database open;

Database altered.

SQL> set lin 400

SQL> col name for a55

SQL> select a.file#,a.name as FILE_NAME,b.name as TABLESPACE_NAME ,status from v$datafile a ,v$tablespace b where a.ts#=b.ts#;

     FILE# FILE_NAME                                                                                             TABLESPACE_NAME                        STATUS

---------- -----------------------------------------------------------------------------------             -----------------------------------------         ------------------

         1 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\SYSTEM01.DBF         SYSTEM                                            SYSTEM

         2 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\UNDOTBS01.DBF     UNDOTBS1                                        ONLINE

         3 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\SYSAUX01.DBF        SYSAUX                                             ONLINE

         4 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\USERS01.DBF         USERS                                               ONLINE

         5 C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\AMIT01.DBF            AMIT                                                   ONLINE

NOTE :- If database is in NOARCHIVELOG mode and you want to restore and recover your lost file, then after restoration of file from backup if recovery is possible through the redo logs currently available then you can open your database but if while in the process of recovery it asks for archive logs then you cannot open your database, you have to restore your full database from the backup to open your database. For Recovery upto last commit keep your database in ARCHIVELOG mode.

I hope this article helped you.

Regards,
Amit Rath