Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Fwd: Re: recover table after drop

Fwd: Re: recover table after drop

From: Oliver Artelt <oli_at_md.transnet.de>
Date: Mon, 25 Sep 2000 17:43:04 +0200
Message-Id: <10630.117753@fatcity.com>


Whoop!

There's an important thing that I've forgot to say: The system-ts cannot be restored without a backup:

You cannot re-create any of the datafiles for the SYSTEM tablespace by using the CREATE DATAFILE clause

             of the ALTER DATABASE statement, since the necessary redo data is not available.

It means you can do TSPITR or recover without a backup -but not TSPITR without a backup!

oli.

On Mon, 25 Sep 2000, ddorr.cs_at_clearstream.com wrote:
> 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-011
> >
> > 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).

--
---

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
---------------------------------------------------------------

-------------------------------------------------------

-- 
---

Oliver Artelt, System- und Datenbankadministration
---------------------------------------------------------------
  cubeoffice GmbH & Co.KG # jordanstrasse 7 # 39112 magdeburg
Received on Mon Sep 25 2000 - 10:43:04 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US