Re: Dropped Datafile

I don't know how many times we mentioned in this list..

ALTER DATABASE DATAFILE '...' OFFLINE DROP is not a command to drop a datafile from the tablespace!!

The file is be in the v$datafile and controlfile FOREVER even you issue the offline drop command.

That command is only for you to put that datafile in the RECOVERY mode and tell Oracle that you will recovery that file later.

You will HAVE TO DROP THAT TABLESPACE if you cannot recover that file.

If you have archivelog mode on, restore that particular file (only that file alone!) to its original location and use the archive log to rollforward to recover the database.

Or if you don't, and don't want to go back to any cold backup, you will need to use your export file to rebuild all the object of that tbalespaces after you dropped it.

Winnie

"Subramaniam, Lakshmi (L.)" <lsubrama_at_ford.com> on 05/19/2000 07:28:07 AM

Please respond to ORACLE-L_at_fatcity.com

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc: (bcc: Winnie Liu/HQ/ISC)

Subject: Dropped Datafile

A trainee inadvertantly deleted a datafile from the OS 'immediately' after creating it. Everything seems okay for now except once in a while the following error shows up while creating schema objects:

     ORA-00376: file 13 cannot be read at this time
     ORA-01110: data file 13: '<OS datafile path>'

Query on dba_data_files shows the datafile to exist even after issuing the 'ALTER database datafile ... offline DROP' command.

Full database export works fine. Logical backups every day - Development server.

Any suggestions to clean up the database apart from cold backup - dropping tablespace - dropping datafiles at OS level - recreating tablespace and doing a table level import for the objects that earlier existed in that tablespace.

Thanks in advance

--
Author: Subramaniam, Lakshmi (L.)
  INET: lsubrama_at_ford.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).