ORA-01157 cannot identify/lock data file

[vishal sood]

In my test database where I have installed my backup of primary database for testing, there I am getting following error whenever the database gets started and when I try to take full export.

ORA-01157 cannot identify/lock data file string - see DBWR trace file
ORA-01110 can open file 201 E:\oracle\ora90\bin\temp01.dbf
ORA-27041 unable to open file
OSD-04002 unable to open file

At the time of starting database the message appeares in alert.log file. After giving the error message the database gets open and every thing is working fine.

On checking the view V$datafile or dba_data_file it is not showing the temp01.dbf.

When I try to add temp01.dbf to temp tablespace it gives error message file already exist.

I have also issued the commend alter system check datafiles. But still got no effects of it.

1. what is wrong ?
2. How to solve it ?
3. Which view I should check after issuing command
alter system check datafiles.

[jmodic]

You said you created your test db from a backup of your production. How was that backup made? I pressume you made id manualy, meaning that you decided which datafiles to include in a backup. And you included also the temporary datafiles, which is wrong. In 9i all datafiles that belong to localy managed temporary tablespace are of type TEMPORARY, and this files must not be included in a backup, because they are not "transportable". That's also why they are not included in views DBA_DATA_FILES and V$DATAFILE. After you restore such a backup, you should manualy create the missing temporary files.

Now in your case you must first drop those tempfile references from your control file by isuing:

ALTER DATABASE TEMPFILE DROP 'E:\oracle\ora90\bin\temp01.dbf' INCLUDING DATAFILES;

Then you must manualy create the new tempfile with:

ALTER TABLESPACE my_temp_tablespace
ADD TEMPFILE 'E:\oracle\ora90\bin\temp01.dbf' SIZE ...;

[jmodic]

.

[vishal sood]

When I tried as advised by you, I got following error.

ORA-01516 nonexistent log file, datafile or tempfile 'string'

Kindly guide

[jmodic]

Please! You being OCP, knowing where to get manuals, where and what to look in them, should certanly not need any guidance.....