Re: recover table after drop

O.K. second round:
1) It's true that recovering a table can't be done directly. Oracle recommends in such cases
- to do a tablespace point-in-time recovery or - to create an auxilary database, perform a time-based recovery there and export/import the lost table.
If you have no backup but all arcredos you must delete the datafiles (they are too new), replace them with empty files (with scn at original creation time) and perform a 'recover database until time'. That's very expensive but this I've said in my first mail too.

2) Are you really think that cached blocks in the sga survive a tablespace offline, create datafile, recover datafile? BTW. That's not my own stuff. You can read this in the oracle generic docs, backup&recovery->14 Performing Operating System Recovery->Re-Creating Datafiles when Backups Are Unavailable (version 815) O.K. here the requested test:

connect internal

create tablespace test01 datafile '/ora/u04/oradata/stock1/test01.dbf' size 20m;

create user test identified by blabla default tablespace test01 quota unlimited on test01;

grant connect,resource to test;

connect test

create table test(id number);

insert into test values (1);

/

/

/

connect internal                                                                               

shutdown abort

exit

rm /ora/u04/oradata/stock1/test01.dbf

su -

init 6

sqlplus internal

startup
ORA-01110: data file 10: '/ora/u04/oradata/stock1/test01.dbf'

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 database datafile '/ora/u04/oradata/stock1/test01.dbf' online;

startup force

connect test

select * from test

        ID

         1
         1
         1
         1

-
I can also unplug the power cord if you wish. 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