Help needed with deleted data file (fwd)

From: Michael Nolan <nolan_at_helios.unl.edu>
Date: 7 Dec 1993 04:33:02 GMT
Message-ID: <2e111u$cgk_at_crcnis1.unl.edu>


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)
Received on Tue Dec 07 1993 - 05:33:02 CET

Original text of this message