OraFAQ Forum: Backup & Recovery » Recover drop table

Home » RDBMS Server » Backup & Recovery » Recover drop table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Recover drop table [message #537495]

Thu, 29 December 2011 00:30

ashishs
Messages: 40
Registered: May 2011

Member

Sir,

How i can recover my drop table..i use user managed backup concept after dropping table i cant recover my table..how i use step that my dropped table recover.

Report message to a moderator

Re: Recover drop table [message #537496 is a reply to message #537495]

Thu, 29 December 2011 00:31

Kamran Agayev
Messages: 145
Registered: February 2009
Location: Azerbaijan, Baku

Senior Member

Use flashback table feature

flashback table table_name to before drop

Report message to a moderator

Re: Recover drop table [message #537497 is a reply to message #537496]

Thu, 29 December 2011 00:38

ashishs
Messages: 40
Registered: May 2011

Member

no, i do not want to use flashback concept. flashback,rman,user managed these three are different concept to recover drop table..but i want do my task through user managed.

Report message to a moderator

Re: Recover drop table [message #537498 is a reply to message #537497]

Thu, 29 December 2011 00:39

Kamran Agayev
Messages: 145
Registered: February 2009
Location: Azerbaijan, Baku

Senior Member

There's NO way to recover the dropped table using user-managed way, except restoring the backup and performing an incomplete recovery to the before time when the table was dropped. For this you need to have backup files and ALL archived redo log files that were generated after backup has been taken

Report message to a moderator

Re: Recover drop table [message #537499 is a reply to message #537497]

Thu, 29 December 2011 00:48

ashishs
Messages: 40
Registered: May 2011

Member

create table t1(code number)tablespace test;
insert into t1 values(123456);
commit;
alter system switch logfile;
alter tablespace test begin backup;
cp '/home/oracle/oracle/product/10.2.0/db_1/oradata/test' \
'/home/oracle/oracle/product/10.2.0/db_1/oradata/test.bkp';
alter tablespace test end backup;
shutdown immediate;
rm '/home/oracle/oracle/product/10.2.0/db_1/oradata/test';
startup mount;
drop table t1;
alter database rename file '/home/oracle/oracle/product/10.2.0/db_1/oradata/test' 
to '/home/oracle/oracle/product/10.2.0/db_1/oradata/test.bkp';
recover datafile '/home/oracle/oracle/product/10.2.0/db_1/oradata/test.bkp';
alter database open;

This is my step i performed to get my dropped table...but it cant recover why?..

[Updated on: Tue, 03 January 2012 01:13] by Moderator

Report message to a moderator

Re: Recover drop table [message #537503 is a reply to message #537499]

Thu, 29 December 2011 01:16

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

Ashishs, you are not telling the truth, This is impossible:

startup mount;
drop table t1;

Usually it is users who tell lies. A DBA should know better.

Report message to a moderator

Re: Recover drop table [message #537509 is a reply to message #537499]

Thu, 29 December 2011 01:42

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Use SQL*Plus and copy and paste your session, the WHOLE session.

Regards
Michel

Report message to a moderator

Re: Recover drop table [message #537516 is a reply to message #537509]

Thu, 29 December 2011 02:28

ashishs
Messages: 40
Registered: May 2011

Member

ok,i'm telling lie but you know how to do it,can you correct my step.

Report message to a moderator

Re: Recover drop table [message #537522 is a reply to message #537516]

Thu, 29 December 2011 02:48

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

Quote:

can you correct my step.

c:\users\john\home>rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Dec 29 08:44:49 2011

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1295656401)

RMAN> recover dropped tables all;

Starting table recovery at 29-DEC-11
using target database control file instead of recovery catalog
5 tables recovered

RMAN> exit


Recovery Manager complete.

c:\users\john\home>

Report message to a moderator

Re: Recover drop table [message #537523 is a reply to message #537522]

Thu, 29 December 2011 02:59

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Report message to a moderator

Re: Recover drop table [message #537532 is a reply to message #537522]

Thu, 29 December 2011 03:32

ashishs
Messages: 40
Registered: May 2011

Member

John Watson wrote on Thu, 29 December 2011 03:48

Quote:

can you correct my step.

c:\users\john\home>rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Dec 29 08:44:49 2011

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1295656401)

RMAN> recover dropped tables all;

Starting table recovery at 29-DEC-11
using target database control file instead of recovery catalog
5 tables recovered

RMAN> exit


Recovery Manager complete.

c:\users\john\home>

----------------
Jhon you are using RMAN concept..i want user managed..

Report message to a moderator

Re: Recover drop table [message #537533 is a reply to message #537532]

Thu, 29 December 2011 03:40

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

c:\users\john\home>dos2sql -c 'recover dropped tables' -o 'autolocate backups'
5 tables recovered.

c:\users\john\home>

Report message to a moderator

Re: Recover drop table [message #537537 is a reply to message #537533]

Thu, 29 December 2011 03:49

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Report message to a moderator

Previous Topic:	RMAN Error
Next Topic:	Recovery Catalog

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Feb 09 11:02:14 CST 2025