Skip to Content

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

Dear Experts,

I have restored online backup and resetlogs to open the DB..everything went well however when I try to schedule offline backup I am getting below error. could you please let me know whet needs to be done? thanks in advance!!!

BR0051I BRBACKUP 7.00 (40)

BR0055I Start of database backup: bentnldh.afd 2014-05-02 15.05.45

BR0484I BRBACKUP log file: /oracle/<SID>/sapbackup/bentnldh.afd

BR0477I Oracle pfile /oracle/<SID>/112_64/dbs/init<SID>.ora created from spfile /oracle/<SID>/112_64/dbs/spfile<SID>.ora

BR0280I BRBACKUP time stamp: 2014-05-02 15.05.46

BR0301E SQL error -1157 at location BrDbfInfoGet-1, SQL statement:

'OPEN curs_8 CURSOR FOR'

'SELECT TS.TABLESPACE_NAME, TS.STATUS, TS.BLOCK_SIZE, TS.CONTENTS, TS.EXTENT_MANAGEMENT, DF.FILE_NAME, DF.FILE_ID, NVL(DF.STATUS, ' '), NVL(DF.BYTES, 0), NVL(DF.AUTOEXTENSIBLE,

'NO'), NVL(DF.MAXBYTES, 0), NVL(DF.INCREMENT_BY, 0) FROM DBA_TABLESPACES TS, DBA_DATA_FILES DF WHERE TS.TABLESPACE_NAME = DF.TABLESPACE_NAME UNION ALL SELECT TS.TABLESPACE_NAME,

TS.STATUS, TS.BLOCK_SIZE, TS.CONTENTS, TS.EXTENT_MANAGEMENT, TF.FILE_NAME, TF.FILE_ID * -1, NVL(TF.STATUS, ' '), NVL(TF.BYTES, 0), NVL(TF.AUTOEXTENSIBLE, 'NO'), NVL(TF.MAXBYTES

, 0), NVL(TF.INCREMENT_BY, 0) FROM DBA_TABLESPACES TS, DBA_TEMP_FILES TF WHERE TS.TABLESPACE_NAME = TF.TABLESPACE_NAME ORDER BY 1, 6'

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

ORA-01110: data file 201: '/oracle/<SID>/sapdata1/temp_1/temp.data1'

BR0314E Collection of information on database files failed

BR0056I End of database backup: bentnldh.afd 2014-05-02 15.05.46

BR0280I BRBACKUP time stamp: 2014-05-02 15.05.46

BR0054I BRBACKUP terminated with errors

Kind regards,

Pradeep

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Best Answer
    Posted on May 02, 2014 at 05:30 PM

    No folder exits like temp_1 in the system..

    First you need to create the dir temp_1 with ora<sid>:dba ownership ,after that perform below queries

    SQL>alter database tempfile '/oracle/<SID>/sapdata1/temp_1/temp.data1.dbf' drop .


    After drop just add tempfile with query

    SQL>alter tablespace PSAPTEMP add tempfile '/oracle/<SID>/sapdata1/temp_1/temp.data1.dbf' size 10G;

    Make sure you're with proper backup if you're performing these activities on Production env.

    Also please consider SCN thread https://scn.sap.com/thread/3382583

    Regards,

    Gaurav

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Pradeep,

      As I see you have Oracle 11g running with 7.00 brtools patch

      BR0051I BRBACKUP 7.00 (40)

      Firstly update your brtools with 720 patch.

      Post this if the error still persists following the instructions for dropping and re-creating the temp file again.

      New datafile can be of 2 GB .

      Hope this helps.

      Regards,

      Deepak Kori

  • Posted on May 02, 2014 at 04:36 PM

    Hi Pradeep,

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

    ORA-01110: data file 201: '/oracle/<SID>/sapdata1/temp_1/temp.data1'

    Please share results by using below SQL queries

    1. SQL> select * from v$tempfile;

    2. select file#,status,name from v$tempfile;

    3.Is the file temp.data1 exists at location specified /oracle/<SID>/sapdata1/temp_1. Please confirm.

    In your issue we may have to drop temp file temp.data1 & need to add it again.Before this please share the results for above.

    Regards,

    Gaurav

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Gaurav,

      Many Thanks!!

      here is the results of the SQL queries,

      SQL> select * from v$tempfile;


      FILE# CREATION_CHANGE# CREATION_TIME TS# RFILE# STATUS
      ---------- ---------------- --------------- ---------- ---------- -------
      ENABLED BYTES BLOCKS CREATE_BYTES BLOCK_SIZE
      ---------- ---------- ---------- ------------ ----------
      NAME
      --------------------------------------------------------------------------------
      1 3828 16-MAR-12 3 1 ONLINE
      READ WRITE 0 0 314572800 8192
      /oracle/<SID>/sapdata1/temp_1/temp.data1

      SQL>select file#,status,name from v$tempfile;


      FILE# STATUS
      ---------- -------
      NAME
      --------------------------------------------------------------------------------
      1 ONLINE
      /oracle/<SID>/sapdata1/temp_1/temp.data1

      No folder exits like temp_1 in the system..

      Please let me know what to do..

      Thanks a lot!!,

      Pradeep

  • Posted on Jun 12, 2014 at 03:51 PM

    Thanks Gaurav and Deepak!!! The issue was resolved 😊

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.