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//sapbackup/bentnldh.afd

BR0477I Oracle pfile /oracle//112_64/dbs/init.ora created from spfile /oracle//112_64/dbs/spfile.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//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

正确答案 按照 Gaurav Rana 打开 May 2, 2014 7:30 PM

No folder exits like temp_1 in the system..

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

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

After drop just add tempfile with query

SQL>alter tablespace PSAPTEMP add tempfile '/oracle//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

查看上下文中的回答

有用的答案 按照 Gaurav Rana, Gaurav Rana, Deepak Kori

23610 浏览次数
Products: sap_netweaver Tags: application, dba

平均用户评级

(0 评级)

有用答案 Re: ORA-01157: cannot identify/lock data file 201 - see DBWR trace file

Gaurav Rana 2014-5-2 下午6:36 （回复 Pradeep Kumar）

Hi Pradeep,

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

ORA-01110: data file 201: '/oracle//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//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
- Alert Moderator
- 喜爱 (0)
- Re: ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
  
  Pradeep Kumar 2014-5-2 下午6:56 （回复 Gaurav Rana）
  
  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//sapdata1/temp_1/temp.data1
  
  SQL>select file#,status,name from v$tempfile;
  
       FILE# STATUS
  ---------- -------
  NAME
  --------------------------------------------------------------------------------
           1 ONLINE
  /oracle//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
  
  Alert Moderator
  
  喜爱 (0)
正确答案 Re: ORA-01157: cannot identify/lock data file 201 - see DBWR trace file

Gaurav Rana 2014-5-2 下午7:30 （回复 Pradeep Kumar）

No folder exits like temp_1 in the system..

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

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

After drop just add tempfile with query

SQL>alter tablespace PSAPTEMP add tempfile '/oracle//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
- Alert Moderator
- 喜爱 (1)
- Re: ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
  
  Pradeep Kumar 2014-5-2 下午7:47 （回复 Gaurav Rana）
  
  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
  
  Alert Moderator
  
  喜爱 (0)
  - 有用答案 Re: ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
    
    Gaurav Rana 2014-5-3 上午6:22 （回复 Pradeep Kumar）
    
    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
    
    Alert Moderator
    
    喜爱 (0)
  - 有用答案 Re: ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
    
    Deepak Kori 2014-5-4 下午8:31 （回复 Pradeep Kumar）
    
    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
    
    Alert Moderator
    
    喜爱 (0)
Re: ORA-01157: cannot identify/lock data file 201 - see DBWR trace file

Pradeep Kumar 2014-6-12 下午5:51 （回复 Pradeep Kumar）

Thanks Gaurav and Deepak!!! The issue was resolved
- Alert Moderator
- 喜爱 (0)
- Re: ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
  
  Gaurav Rana 2014-6-12 下午7:16 （回复 Pradeep Kumar）
  
  Great !! Nice to hear issue has been resolved.
  
  Regards,
  
  Gaurav Rana
  
  http://scn.sap.com/thread/3547503