?
Solved

Can't open file - Ora-01157

Posted on 1997-12-03
4
Medium Priority
?
896 Views
Last Modified: 2010-05-18
we have the following problem:

>alter database ammain mount exclusive
>Mon Dec 01 11:31:24 1997
>SNP0 started
>LGWR started
>Mon Dec 01 11:31:24 1997
>DBWR started
>Mon Dec 01 11:31:25 1997
>Successful mount of redo thread 1.
>Mon Dec 01 11:31:25 1997
>Completed: alter database ammain mount exclusive
>Mon Dec 01 11:31:25 1997
>alter database ammain open
>SNP1 started
>Mon Dec 01 11:31:26 1997
>Errors in file W:\ORANT\RDBMS73\trace\amdbDBWR.TRC:
>ORA-01157: cannot identify data file 2 - file not found
>ORA-01110: data file 2: 'RBS1AMDB.ORA'
>ORA-09202: sfifi: error identifying file
>OSD-04002: Die Datei kann nicht geöffnet werden. (OS 2)
>ORA-1157 signalled during: alter database ammain open...

As you can read, we can't open the database.
On the server we have two instance of oracle.
The first instance work without any problem.
But the second...

We have tried the following:

" alter database datafile 'full pathname' offline drop;"

But we receive the following error:
ORA-01516: not existing databasefile "full pathname".

We have doublechecked that the path and filename is correct.

Need help to solve this problem!


Best Regards

Raymond Michalek

P.S. Oracle is installed on a NT Server 4.0
0
Comment
Question by:rfm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 3

Accepted Solution

by:
poncejua earned 400 total points
ID: 1081010
Hi,

There are two types of solutions for an ORA-1157:  
 
 
I. THE DATAFILE IS LOST OR TOO DAMAGED  
-----------------------------------------------------------------------------  
 
In this case, the solution depends on the tablespace to which the datafile belongs.  Look for a Solution Reference matching the type of datafile lost.  
 
 
II. THE DATAFILE HAS SIMPLY BEEN MOVED OR RENAMED
-----------------------------------------------------------------------------------------------------    
If you originally wanted to change the name/location of the file, look for the Solution Reference on how to rename datafiles inside Oracle.  If the file has been moved or renamed by mistake, simply restore it to its original name/location and startup the database.

Nevertheless, you doublechecked that the path and filename. Do you check the attributes of the file ? Could someone changed it to read only ?

Also, it seems that datafile contains rollback segments. Check the status of the tablespaces contained in that datafile (use the dba_tablespaces table). If the status is missing and, as I'm guessing, contains rollback segments try to drop that segments, then the tablespace and recreate the datafile, tablespaces and segments.

If you have any comments or inquiries please don't hesitate to contact me directly at jmponcemartinez@hotmail.com.

Regards,

Juan Manuel Ponce Martinez
DBA - Bridas S.A.P.I.C.
Buenos Aires - Argentina
0
 
LVL 4

Expert Comment

by:jtrifts
ID: 1081011
...and what have you found on the trace file?
0
 
LVL 4

Expert Comment

by:jtrifts
ID: 1081012
...and what have you found on the trace file?
0
 

Author Comment

by:rfm
ID: 1081013
Adjusted points to 100
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to recover a database from a user managed backup
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question