Solved

Can't open file - Ora-01157

Posted on 1997-12-03
4
831 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.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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…
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

760 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now