We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Can't open file - Ora-01157

rfm
rfm asked
on
Medium Priority
984 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
Comment
Watch Question

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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
jtriftsMI and Automation
CERTIFIED EXPERT

Commented:
...and what have you found on the trace file?
jtriftsMI and Automation
CERTIFIED EXPERT

Commented:
...and what have you found on the trace file?
rfm

Author

Commented:
Adjusted points to 100
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.