About Me

My photo
Bangalore, India
I am an Oracle Certified Professional working in SAP Labs. Everything written on my blog has been tested on my local environment, Please test before implementing or running in production. You can contact me at amit.rath0708@gmail.com.

Tuesday, February 26, 2013

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

8 comments:

  1. Great but it not works for me
    log alert file\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
    ORACLE_BASE from environment = C:\oracle\Administrator
    Spfile C:\ORACLE\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILECASTHOUSE.ORA is in old pre-11 format and compatible >= 11.0.0; converting to new H.A.R.D. compliant format.
    Sun Dec 27 19:01:06 2015
    ALTER DATABASE MOUNT
    Successful mount of redo thread 1, with mount id 278793138
    Database mounted in Exclusive Mode
    Lost write protection disabled
    Sun Dec 27 19:01:16 2015
    Completed: ALTER DATABASE MOUNT
    Sun Dec 27 19:01:20 2015
    ALTER DATABASE OPEN
    Sun Dec 27 19:01:20 2015
    Errors in file c:\oracle\administrator\diag\rdbms\casthouse\casthouse\trace\casthouse_dbw0_6424.trc:
    ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
    ORA-01110: data file 1: 'C:\APP\ROOT\ORADATA\CASTHOUSE\SYSTEM01.DBF'
    ORA-27041: unable to open file
    OSD-04002: unable to open file
    O/S-Error: (OS 3) The system cannot find the path specified.
    Errors in file c:\oracle\administrator\diag\rdbms\casthouse\casthouse\trace\casthouse_dbw0_6424.trc:
    ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
    ORA-01110: data file 2: 'C:\APP\ROOT\ORADATA\CASTHOUSE\SYSAUX01.DBF'
    ORA-27041: unable to open file
    OSD-04002: unable to open file
    O/S-Error: (OS 3) The system cannot find the path specified.
    Errors in file c:\oracle\administrator\diag\rdbms\casthouse\casthouse\trace\casthouse_dbw0_6424.trc:
    ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
    ORA-01110: data file 3: 'C:\APP\ROOT\ORADATA\CASTHOUSE\UNDOTBS01.DBF'
    ORA-27041: unable to open file
    OSD-04002: unable to open file
    O/S-Error: (OS 3) The system cannot find the path specified.
    Errors in file c:\oracle\administrator\diag\rdbms\casthouse\casthouse\trace\casthouse_dbw0_6424.trc:
    ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
    ORA-01110: data file 4: 'C:\APP\ROOT\ORADATA\CASTHOUSE\USERS01.DBF'
    ORA-27041: unable to open file
    OSD-04002: unable to open file
    O/S-Error: (OS 3) The system cannot find the path specified.
    Errors in file c:\oracle\administrator\diag\rdbms\casthouse\casthouse\trace\casthouse_dbw0_6424.trc:
    ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
    ORA-01110: data file 5: 'C:\APP\ROOT\ORADATA\CASTHOUSE\EXAMPLE01.DBF'
    ORA-27041: unable to open file
    OSD-04002: unable to open file
    O/S-Error: (OS 3) The system cannot find the path specified.
    //////////////////////////////////////////////////////////////
    sqlplus
    ORACLE instance started.

    Total System Global Area 431038464 bytes
    Fixed Size 1375088 bytes
    Variable Size 327156880 bytes
    Database Buffers 96468992 bytes
    Redo Buffers 6037504 bytes
    Database mounted.
    ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
    ORA-01110: data file 1: 'C:\APP\ROOT\ORADATA\CASTHOUSE\SYSTEM01.DBF'

    ReplyDelete
    Replies
    1. Hi ,

      Seems a compatible parameter issue by looking at the error details, please add compatible parameter to your pfile/spfile and then try to open. Is this database is upgraded recently? or migrated from different platform?

      Thanks
      AMit Rath

      Delete
  2. \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
    RMAN
    RMAN> restore datafile 1;

    Starting restore at 27-DEC-15
    using channel ORA_DISK_1

    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 C:\APP\ROOT\ORADATA\CASTHOUSE\SY
    STEM01.DBF
    channel ORA_DISK_1: reading from backup piece C:\APP\ROOT\FLASH_RECOVRY\CASTHOUS
    E\BACKUPSET\2015_12_27\O1_MF_NNNDF_TAG20151227T174131_C7ZOCTZ2_.BKP
    channel ORA_DISK_1: ORA-19870: error while restoring backup piece C:\APP\ROOT\FL
    ASH_RECOVRY\CASTHOUSE\BACKUPSET\2015_12_27\O1_MF_NNNDF_TAG20151227T174131_C7ZOCT
    Z2_.BKP
    ORA-19505: failed to identify file "C:\APP\ROOT\FLASH_RECOVRY\CASTHOUSE\BACKUPSE
    T\2015_12_27\O1_MF_NNNDF_TAG20151227T174131_C7ZOCTZ2_.BKP"
    ORA-27041: unable to open file
    OSD-04002: unable to open file
    O/S-Error: (OS 3) The system cannot find the path specified.

    failover to previous backup

    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 C:\APP\ROOT\ORADATA\CASTHOUSE\SY
    STEM01.DBF
    channel ORA_DISK_1: reading from backup piece C:\APP\ROOT\FLASH_RECOVRY\CASTHOUS
    E\BACKUPSET\2015_12_27\O1_MF_NNNDF_TAG20151227T172208_C7ZN7O8N_.BKP
    channel ORA_DISK_1: ORA-19870: error while restoring backup piece C:\APP\ROOT\FL
    ASH_RECOVRY\CASTHOUSE\BACKUPSET\2015_12_27\O1_MF_NNNDF_TAG20151227T172208_C7ZN7O
    8N_.BKP
    ORA-19505: failed to identify file "C:\APP\ROOT\FLASH_RECOVRY\CASTHOUSE\BACKUPSE
    T\2015_12_27\O1_MF_NNNDF_TAG20151227T172208_C7ZN7O8N_.BKP"
    ORA-27041: unable to open file
    OSD-04002: unable to open file
    O/S-Error: (OS 3) The system cannot find the path specified.

    failover to previous backup

    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of restore command at 12/27/2015 23:04:28
    RMAN-06026: some targets not found - aborting restore
    RMAN-06023: no backup or copy of datafile 1 found to restore

    ReplyDelete
  3. thanks, solution 1 did the trick

    ReplyDelete
  4. Great! Its really very helpful for me. Thanks for your valuable support.

    ReplyDelete