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

[tamil_arasan]

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

[former_member182657 · Accepted solution]

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

[tamil_arasan · Accepted solution]

Hi Gaurav,

Thanks!,

yes it's production system. Do i need to create datafile with 10GB? the system is not being used for storing data as it is PI system. we are only using to submit the files to third party system. can i reduce it do 1 GB?

Kindly advise.

Thanks a lot!,

Tamil

[former_member182657 · Accepted solution]

Hi Pradeep,

If space is an issue on the system you can go with 2GB & perform the required SQL queries.

yes it's production system

Earlier i said make sure you're with proper backup if you're performing these activities on Production env. ( For safer end ).

Hope this will clear your queries .

Regards,

Gaurav

[former_member188883 · Accepted solution]

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

[tamil_arasan]

Thanks Gaurav and Deepak!!! The issue was resolved

[former_member182657]

Great !! Nice to hear issue has been resolved.

Regards,

Gaurav Rana

[former_member182657]

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

[tamil_arasan]

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