ORA-01157 cannot identify/lock data file
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: ORA-01157 cannot identify/lock data file

  1. #1
    Join Date
    Jun 2003
    Location
    India
    Posts
    118

    ORA-01157 cannot identify/lock data file

    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.
    vishal sood
    OCP 8

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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 ...;
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    .
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Jun 2003
    Location
    India
    Posts
    118
    When I tried as advised by you, I got following error.

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

    Kindly guide
    vishal sood
    OCP 8

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Please! You being OCP, knowing where to get manuals, where and what to look in them, should certanly not need any guidance.....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width