RE: recover table after drop

you can recover the table from a hot backup *if* you have the space on a different server to play (or the ability to shutdown your system for a while)....

restore (at minimum) the system, rollback, temp tablespaces as well as the tablespace that contained the table. If you are doing it to your production system, shutdown the database first and take a FULL backup before starting this!

Once you have restored those tablespaces, do a startup mount, alter the other tablespaces offline drop and open the database. Apply whatever archive logs you have, you can also apply the redo logs if you know EXACTLY when the table was dropped -- do a recover until <time> where the time is just before the drop.

EXPORT the table. Shutdown the restored database, delete the files, if you did this on your production system, restore that backup. Now import the table.

These steps are the general steps to take. They are NOT the exact process..... I leave that as an exercise (and possibly punishment?) for the person who has to do this.

And in the future consider taking an export, at least of critical tables, on a regular basis, in addition to your files-level backups. Logical backups are as important as physical.

Rachel

>From: ddorr.cs_at_clearstream.com
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: RE: recover table after drop
>Date: Mon, 25 Sep 2000 02:00:24 -0800
>
>This is really a nice demo, but :
>
>1) The user didn't drop a datafile, but a table, which is - AFAIK - NOT
>recoverable,
>2) What you did only worked because, even you've removed the file on unix,
>the file (and data it contains) was still here as Oracle kept it open.
>Try to shutdown oracle / rm your datafile on unix / startup oracle / do
>your
>recover and come back with the results ...
>
>
> > ----------
> > From: Oliver Artelt[SMTP:oli_at_md.transnet.de]
> > Reply To: ORACLE-L_at_fatcity.com
> > Sent: Saturday, September 23, 2000 22:55
> > To: Multiple recipients of list ORACLE-L
> > Subject: Re: recover table after drop
> >
> >
> > Hi,
> > I am also fully DISagree with you. OK, archived logs only aint a
>solution
> > because the MTTR is to high, but we can help this guy:
> >
> > create tablespace test01 datafile '/ora/u04/oradata/stock1/test01.dbf'
> > size
> > 20 m;
> >
> > create user test identified by blabla default tablespace test01
> > quota unlimited on test01;
> >
> > grant resource,connect to test;
> >
> > connect test
> >
> > create table test(id number);
> >
> > insert into test values(1);
> >
> > /
> >
> > /
> >
> > /
> >
> > !rm /ora/u04/oradata/stock1/test01.dbf
> >
> > connect internal
> >
> > shutdown
> > ORA-01116: error in opening database file 10
> > ORA-01110: data file 10: '/ora/u04/oradata/stock1/test01.dbf'
> > ORA-27041: unable to open file
> >
> > alter tablespace temp01 offline;
> >
> > alter database datafile '/ora/u04/oradata/stock1/test01.dbf' offline;
> >
> > alter database create datafile '/ora/u04/oradata/stock1/test01.dbf';
> >
> > recover datafile '/ora/u04/oradata/stock1/test01.dbf';
> >
> > alter tablespace test01 online;
> >
> > select * from test.test;
> >
> > ID
> > ----------
> > 1
> > 1
> > 1
> > 1
> >
> > qed.
> >
> > oli.
> >
> >
> > On Sat, 23 Sep 2000, Ajay Kothari wrote:
> > > I am also fully agree with you that Archived Logs doesn't give instant
> > > solution for this kind of problem.
> > >
> > > Ajay
> > >
>_______________________________________________________________________
> > > Keep smiling !!! Visit us at www.lipidata.com
> > > ----- Original Message -----
> > > To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > > Sent: Thursday, September 21, 2000 05:10 PM
> > >
> > >
> > >
> > > Archived logs are useless if you don't have a good old backup to start
> > > from. I'm afraid you're stuck ... :-(
> > >
> > > > ----------
> > > > From: Alexander Ordonez[SMTP:aordonez_at_ccss.sa.cr]
> > > > Reply To: ORACLE-L_at_fatcity.com
> > > > Sent: Thursday, September 21, 2000 01:16
> > > > To: Multiple recipients of list ORACLE-L
> > > > Subject: recover table after drop
> > > >
> > > > > Hi DBA's
> > > > > I need help for recovery table after drop, i don't have backup,
> > exists
> > > >
> > > > any
> > > >
> > > > > forms????
> > > > > only have a archivelog....
> > > > >
> > > > > any idea?????????????
> > > >
> > > >
> >
>-------------------------------------------------------------------------
> > > >
> > > > > Lic. Alexander Ordóñez Arroyo
> > > > > Administrador Tru64Unix
> > > > > Caja Costarricense del Seguro Social
> > > > > División de Informática Soporte Técnico
> > > > > Telefono: 295-2004, San José, Costa Rica
> > > > > Aordonez_at_ccss.sa.cr
> > > >
> > > >
> >
>-------------------------------------------------------------------------
> > > > --
> > > > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > > --
> > > > Author: Alexander Ordonez
> > > > INET: aordonez_at_ccss.sa.cr
> > > >
> > > > 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).
> >
> > --
> > ---
> >
> > Oliver Artelt, System- und Datenbankadministration
> > ---------------------------------------------------------------
> > cubeoffice GmbH & Co.KG # jordanstrasse 7 # 39112 magdeburg
> > telefon: +49 (0)391 6 11 28 10 # telefax: +49 (0)391 6 11 28 19
> > email: oli@cubeoffice.de # web: http://www.cubeoffice.de
> > ---------------------------------------------------------------
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Oliver Artelt
> > INET: oli_at_md.transnet.de
> >
> > 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).
> >
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author:
> INET: ddorr.cs_at_clearstream.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).