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

ORA-01157: cannot identify/lock data file string - see DBWR trace file
Cause: The background process was either unable to find one of the data files or failed to lock it because the file was already in use. The database will prohibit access to this file but other files will be unaffected. However the first instance to open the database will need to access all online data files. Accompanying error from the operating system describes why the file could not be identified.
Action: Have operating system make file available to database. Then either open the database or do ALTER SYSTEM CHECK DATAFILES.

Reference: http://docs.oracle.com/cd/B28359_01/server.111/b28278/e900.htm#ORA-01157

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

To solve ORA-01157 we can use one of the following steps:
- If datafile is deleted or corrupt and is not of TEMP or UNDO tablespace then we need to recoved it by using a valid backup.
- If datafile is deleted or corrupt and is not of TEMP or UNDO tablespace but that tablespace do not containt important segments, that can be dropped offline
- If datafile is renamed or moved then we need to get it in its original position
- If Mount point is incorrect, simply recreate the mount point
- if it is due to permission then we need to grant the permission at OS level

I faced ORA-01157 at my local environment, when I was restoring a cold backup. Fortunatially it was with a tablespace which was added for testing purpose and was not critical. So I simply drop the datafile using "OFFLINE DROP" clause and opened the database.

C:\>sqlplus sys/sys as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 30 19:07:16 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup
ORACLE instance started.
Total System Global Area  778387456 bytes
Fixed Size                  1384856 bytes
Variable Size             520097384 bytes
Database Buffers          251658240 bytes
Redo Buffers                5246976 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 11 – see DBWR trace file
ORA-01110: data file 16: 'E:\oracle\app\nimish.garg\oradata\orcl\test_ts.dbf'

SQL> select NAME from v$datafile where file#=16;
NAME
--------------------------------------------------------------------------------
E:\ORACLE\APP\NIMISH.GARG\ORADATA\ORCL\TEST_TS.DBF

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

SQL> alter database open;
Database altered.

I hope this solution is helpful in solving your problem. Your comments are well appreciated.

Related Posts:
- ORA-00849: SGA_TARGET cannot be set to more than MEMORY_MAX_TARGET
- ORA-27101: shared memory realm does not exist
- ORA-00604: error occurred at recursive SQL level string
- ORA-00600: internal error code
- ORA-00257: archiver error. Connect internal only, until freed
- ORA-16000: database open for read-only access

56 comments:

  1. Thank you for very useful post. I believe this is exactly the real world problem analysis and solution that makes Oracle DBA community so professional. Likely this post will be found when some non-DBA needs solution to ORA-01157 very clear and in two-stepss. Well done.

    ReplyDelete
  2. This worked like charm, gr8 buddy

    ReplyDelete
  3. thanks for the solution. It worked for me.

    ReplyDelete
  4. Thank you, this helped me get my Oracle test server back up and running after I deleted several .DBF files from tablespaces I had already dropped. Oracle apparently still considered the data files to be in use.

    ReplyDelete
  5. Thank you! Great post helped me quickly get my test server back up.

    ReplyDelete
  6. Excellent! It worked.

    ReplyDelete
  7. Thanks for the clearcut solution. With all the grabage floating all over the internet, this piece of work is quite admirable. Thanks, it saved a lot of time which could have been wasted going to those overrated sites and looking at all those troll posts.

    ReplyDelete
  8. That was very helpful thanksπŸ˜„πŸ˜„πŸ˜„πŸ˜„

    ReplyDelete

  9. SQL> alter database open;
    alter database open
    *
    ERROR at line 1:
    ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

    ReplyDelete
    Replies
    1. same error i am getting.
      how you solve your issue.

      Delete
  10. Great article, it really helped me out!

    ReplyDelete
  11. Excellent, saved me lot of time. Thank you!

    ReplyDelete
  12. thanks for the solution. It worked for me.

    ReplyDelete
  13. Saved me a lot of time. Thank you!

    ReplyDelete
  14. Thanks my friend!!! You save my job!!

    ReplyDelete
  15. What should we do when same error occurs in system datafile?

    ReplyDelete
    Replies
    1. I Also have the same error on system datafile. Anyone with solution?

      Delete
  16. Thanks it's work

    ReplyDelete
  17. So my query was: Does it really matter?

    ReplyDelete
  18. Hello, I read your blog like every week. Your writing style is
    witty, keep up the good work!

    ReplyDelete
  19. Excellent site. Lots of useful info here. I'm sending it to some pals
    ans also sharing in delicious. And naturally, thank you on your
    sweat!

    ReplyDelete
  20. Same issue for system01 datafile.
    So can't follow to drop this datafile and open database.
    Please help.

    ReplyDelete
  21. What's up to every one, the contents present at this web site
    are really amazing for people experience, well,
    keep up the good work fellows.

    ReplyDelete
  22. It is all in the presentation and digital
    technology in the boardroom is vital to the success of any business.

    ReplyDelete
  23. THAAAAAAAANKSSSSSSSSSSSSSSSSSSSSSS YOUUUUUUUUUUUUUUUUUUUUUUUU, I LOVE UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUU ,,,,,,,,, THANKS SO MUCHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH, I LOVE U

    ReplyDelete
  24. Microsoft Windows [Version 6.3.9600]
    (c) 2013 Microsoft Corporation. All rights reserved.

    C:\Users\nx231a>set oracle_sid=RMEPSMES

    C:\Users\nx231a>sqlplus sys as sysdba

    SQL*Plus: Release 12.1.0.2.0 Production on Wed Feb 14 19:50:23 2018

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

    Enter password:
    Connected to an idle instance.

    SQL> startup
    ORACLE instance started.

    Total System Global Area 1.3724E+10 bytes
    Fixed Size 3061240 bytes
    Variable Size 6979324424 bytes
    Database Buffers 6710886400 bytes
    Redo Buffers 30490624 bytes
    Database mounted.
    ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
    ORA-01110: data file 1: 'G:\ORACLE\RMEPSMES\ORADATA\DATA\SYSTEM01.DBF'


    SQL>
    SQL> select NAME from v$datafile where file#=1;

    NAME
    --------------------------------------------------------------------------------

    G:\ORACLE\RMEPSMES\ORADATA\DATA\SYSTEM01.DBF

    SQL>

    ReplyDelete
  25. Thanks it's working

    ReplyDelete
  26. Thanks and Appreciation for the crystal clear approach

    ReplyDelete
  27. Oh man. I'm bound to utilize this.

    ReplyDelete
  28. This is very good. Jogs my storage of this version.
    Wall Tiles

    ReplyDelete
  29. very good concept
    the way you writen

    ReplyDelete
  30. THANKS IT IS WORKING FOR ME....
    NICE EXPLANATION...

    ReplyDelete
  31. Hello,

    I am trying to build a data guard on the same pc and when i run the script i keep getting this error. Please can someone help me out with the fix for this ?

    Starting recover at 03-FEB-19

    starting media recovery
    media recovery failed
    released channel: prmy4
    released channel: stby
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of Duplicate Db command at 02/03/2019 00:39:30
    RMAN-05501: aborting duplication of target database
    RMAN-03015: error occurred in stored script Memory Script
    ORA-00283: recovery session canceled due to errors
    RMAN-11003: failure during parse/execution of SQL statement: alter database recover
    if needed standby start until change 11245694
    ORA-00283: recovery session canceled due to errors
    ORA-01110: data file 1: '/media/sf_software/data/oracledb/system01.dbf'
    ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
    ORA-01110: data file 1: '/media/sf_software/data/oracledb/system01.dbf'

    ReplyDelete
  32. ORA-12919: Can not drop the default permanent tablespace

    ReplyDelete
    Replies
    1. Create a new tablespace, and alter database default tablespace ,
      then you should be able to drop the old one

      Delete


  33. We can recover the DB or any action need to perform please provide the solution.

    DB backup not available its Noarchive log mode.

    SQL> RECOVER DATABASE using BACKUP CONTROLFILE;
    ORA-00279: change 3762878450 generated at 05/08/2019 14:41:57 needed for thread
    1
    ORA-00289: suggestion : /oraclet/1220/dbs/arch1_1123_1000157358.dbf
    ORA-00280: change 3762878450 for thread 1 is in sequence #1123


    Specify log: {=suggested | filename | AUTO | CANCEL}
    CANCEL
    Media recovery cancelled.

    SQL> alter database open resetlogs;
    alter database open resetlogs
    *
    ERROR at line 1:
    ORA-01113: file 1 needs media recovery
    ORA-01110: data file 1: '/orat_data/KMIMESTO/system01.dbf'



    SQL> select FILE#,TS#,STATUS from v$datafile where FILE#=1;

    FILE# TS# STATUS
    ---------- ---------- --------------
    1 0 SYSTEM

    SQL> create tablespace SYSTEM01 datafile '/orat_data/KMIMESTO/system02.dbf' size 10g;
    create tablespace SYSTEM01 datafile '/orat_data/KMIMESTO/system02.dbf' size 10g
    *
    ERROR at line 1:
    ORA-01109: database not open

    We can recover the DB or any action need to perform please provide the solution.

    DB backup not available its Noarchive log mode.

    SQL> RECOVER DATABASE using BACKUP CONTROLFILE;
    ORA-00279: change 3762878450 generated at 05/08/2019 14:41:57 needed for thread
    1
    ORA-00289: suggestion : /oraclet/1220/dbs/arch1_1123_1000157358.dbf
    ORA-00280: change 3762878450 for thread 1 is in sequence #1123


    Specify log: {=suggested | filename | AUTO | CANCEL}
    CANCEL
    Media recovery cancelled.

    SQL> alter database open resetlogs;
    alter database open resetlogs
    *
    ERROR at line 1:
    ORA-01113: file 1 needs media recovery
    ORA-01110: data file 1: '/orat_data/KMIMESTO/system01.dbf'

    ReplyDelete
  34. How to recover the DB and its NoArchivelog mode and No backup avalible. please help out to fix the issue

    ReplyDelete
  35. By mistake I have deleted data file physically from oradata location and I am using Oracle 12c on windows 10.so because of this database not open error occurs and I am unable to connect to database.
    I have that file in recycle bin and I already restored to the previous location but I still not able to connect to DB.Its giving error "system tablespace file 13 is offline.
    Please give me solution on this, means it's possible to connect to server or not??

    ReplyDelete
  36. for 12c the alter database datafile 16 offline drop didn't work - one has to put the datafile filepath instead of number for it to work :)

    ReplyDelete
  37. If you have any any mpeg or avi file then you can convert in other formats like these;

    Converter Files

    ReplyDelete
  38. i am browsing this website dailly , and get nice facts from here all the time .

    ReplyDelete