Re: Help needed with deleted data file (fwd)

Mike & Amir,

SQLDBA> shutdown force               # force database down NOW in case it is up
SQLDBA> startup nomount              # ignore files besides control file
SQLDBA> connect internal             # connect to kernel
SQLDBA> alter database datafile '/data/file/name' offline;
.                                    # force filename to be offline
SQLDBA> alter database mount;        # mount the other files
SQLDBA> alter database open;         # make sure DB comes up, recovers 'force'
SQLDBA> shutdown                     # shutdown normal to snapshot logfiles
SQLDBA> startup                      # startup normal

>The following is being posted for an Oracle user in Australia. If anyone
>has some suggestions I missed, please add them!
 

>-----
>From: amir_arshad.admin_po_at_ms-gw.uow.edu.au (Amir Arshad)
>To: nolan_at_helios.unl.edu
>Organization: University Of Wollongong
 

>Michael,
 

>I just encountered a chaotic situation in ORACLE. Someone deleted a
>tablespace physical file 'concept' which cannot be recovered from backups.
>When we go starting ORACLE server it popups the error:
 

>ORA-01116: error in opening database file
>'/home/other/tony/concept_db/concept'
>ORA-07360: sfifi: stat error, unable to obtain information about file.
>SunOS Error: 2: No such file or directory
 

>The concept database is no longer required but we have worth six months of
>punching effort flushing down the gurgler. I would want to know urgently a
>way to make ORACLE start without looking for this particular file. I would
>appreciate if you could help or pass this to some other knowledgable
>person.
 

>Many Thanks.
 

>-
>Regards
>Arshad Amir A.Arshad_at_Uow.Edu.Au
>QAP-ISM Programmer
>University of Wollongong
>NSW Australia
>-------
>To: Amir Arshad
>From: nolan_at_helios.unl.edu on Tue, 7 Dec 1993 15:00
 

>Well, let me make a couple of suggestions you might have already
>thought of:
 

>1. Call Oracle support!!
>2. Post the full question to comp.databases.oracle. (If you don't have
> posting access, I'd be willing to repost your message.)
>3. Check with the nearest Oracle user group. (I can send you the list of
> contacts for Australia if you need them.)
>4. If you have Compuserve access, that's where a lot of Oracle wizards
> hang out. (I'm not among them, because I don't have CIS access.)
>5 Have you tried starting the database up as a closed database? You might
> be able to open the unaffected tablespaces manually.
 

>Here's a couple of my own totally off-the-wall ideas:
 

>Create a dummy file with the appropriate name. You may have to fiddle with
>the timestamp (use touch) to get it to match the others in the
>database.
 

>I haven't tried this, and doubt it will work. However, it might enable you
>to figure out something else that will work.
 

>I assume you don't have a full export or other backup with that datafile.
>(If you do, you might try renaming all the live database files to save them,
>do a restore, then try changing the name/timestamp on the missing datafile
>as in my earlier idea.)
 

>-----
>From: "Amir Arshad" <a.arshad_at_uow.edu.au>
>Subject: RE: ORACLE CHAOS
>To: nolan_at_helios.unl.edu
 

>Here's what I have tried:
 

>o I created a dummy file but it failed because it couldn't read the header.
>o I tried to change the control file to make it ignore that tablespace
> but it resulted in an error which looks less chronic - >
 

>ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
>Attempting to dismount database........Database dismounted.
>Attempting to shutdown instance........ORACLE instance shut down.
 

>I tried to specify this option at startup but didn't work.

>I would appreciate if you forward my two messages to the appropriate channels.
 

>Many Thanks
 

>-
>Regards
>A Amir
 

>---
>Michael Nolan, Sysop for the DBMS RoundTable on GEnie
>nolan_at_notes.tssi.com, dbms_at_genie.geis.com
>(posted from nolan_at_helios.unl.edu)

-- 
Andrew Finkenstadt  |  Systems Analyst, Homes & Land Publishing Corporation
+1 904-575-0189     |  GEnie Sysop             ,,,
andy_at_genie.geis.com |                         (. .)     Peek-a-boo!
andy_at_homes.com      +----------------------o00-(_)-00o---------------------