RE: recover table after drop

That's great idea !!!!

-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Rachel Carmichael
Sent: Monday, September 25, 2000 6:10 PM To: Multiple recipients of list ORACLE-L Subject: 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).


________________________________________________________________________
_
Get Your Private, Free E-mail from MSN Hotmail at
http://www.hotmail.com.

Share information about yourself, create your own public profile at 
http://profiles.msn.com.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rachel Carmichael
  INET: carmichr_at_hotmail.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