Solved

Can't open file - Ora-01157

Posted on 1997-12-03
4
861 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
  • 2
4 Comments
 
LVL 3

Accepted Solution

by:
poncejua earned 100 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

820 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