Can't open file - Ora-01157

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
rfmAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

poncejuaCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jtriftsMI and AutomationCommented:
...and what have you found on the trace file?
0
jtriftsMI and AutomationCommented:
...and what have you found on the trace file?
0
rfmAuthor Commented:
Adjusted points to 100
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.