Datafile removed from database

[parthaC]

We have a Oracle 9i installed consisting of a number of tablespaces. The platform is SUn solaris.Accidentally one of the datafile in a particula tablespace has been removed. There is an export backup of the user who is a default user of the tablespace. the database has been shut down and is not getting up. Can someone suggest answer to the following questions
1) How to start up the database.
2) How to recover the tablespace from the export backup
3) And if 2 is not possible how to drop the tablespace.
Thanks in advance.

 

[SantaMufasa]

Partha, Namaste. Op kaise hai?

If you have recent contents of your tablespace backed up to an exported dumpfile, then you should be in good shape. Here is the process to get back on-line:

1) Create a text copy of your Control file -- &quot;ALTER DATABASE BACKUP CONTROLFILE TO TRACE;&quot; Oracle writes a text copy of your control file to $ORACLE_HOME/udump/<SID_ora_nnn.trc.
2) Modify the text control file -- Review the contents of the text control file. (Depending upon your Oracle version, the text control file contains two versions of the &quot;CREATE CONTROLFILE...&quot; statement: one with NORESETLOGS and one with RESETLOGS. Remark out the version you will not be using...I try the NORESETLOGS version first.) Using your text editor of choice, remove the reference to the missing data file.
3) Execute the text control file as a SQL script -- Since the script starts up the database to NOMOUNT, execute the script with the instance completely shut down.
4) Drop the &quot;bad&quot; tablespace -- Once the script completes, if everything was successful, the database should be OPEN, but the tablespace of the missing file will be OFFLINE. Get rid of the &quot;bad&quot; tablespace with the command: &quot;drop tablespace name_of_bad_tablespace including contents;&quot;
5) Re-create just-dropped tablespace -- Following is the syntax I use to create tablespaces:

&quot;CREATE TABLESPACE tablespace_name DATAFILE 'file_name' SIZE xxM REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 2000M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;&quot;

6) Re-populate your tablespace -- Import your dumpfile(s) to restore the objects that resided in the old tablespace.

Let us know how this worked for you.

Mufasa
(aka Dave of Sandy, Utah, USA @ 22:03 (01Dec03) GMT, 15:03 (01Dec03) Mountain Time)