Home » RDBMS Server » Backup & Recovery » Recover drop table
Recover drop table [message #537495] Thu, 29 December 2011 00:30 Go to next message
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.
Re: Recover drop table [message #537496 is a reply to message #537495] Thu, 29 December 2011 00:31 Go to previous messageGo to next message
Kamran Agayev
Messages: 145
Registered: February 2009
Location: Azerbaijan, Baku
Senior Member

Use flashback table feature

flashback table table_name to before drop
Re: Recover drop table [message #537497 is a reply to message #537496] Thu, 29 December 2011 00:38 Go to previous messageGo to next message
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.
Re: Recover drop table [message #537498 is a reply to message #537497] Thu, 29 December 2011 00:39 Go to previous messageGo to next message
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
Re: Recover drop table [message #537499 is a reply to message #537497] Thu, 29 December 2011 00:48 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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.

Re: Recover drop table [message #537509 is a reply to message #537499] Thu, 29 December 2011 01:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste your session, the WHOLE session.

Regards
Michel
Re: Recover drop table [message #537516 is a reply to message #537509] Thu, 29 December 2011 02:28 Go to previous messageGo to next message
ashishs
Messages: 40
Registered: May 2011
Member

ok,i'm telling lie but you know how to do it,can you correct my step.
Re: Recover drop table [message #537522 is a reply to message #537516] Thu, 29 December 2011 02:48 Go to previous messageGo to next message
John Watson
Messages: 8922
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>
Re: Recover drop table [message #537523 is a reply to message #537522] Thu, 29 December 2011 02:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

/forum/fa/1587/0/

Re: Recover drop table [message #537532 is a reply to message #537522] Thu, 29 December 2011 03:32 Go to previous messageGo to next message
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..
Re: Recover drop table [message #537533 is a reply to message #537532] Thu, 29 December 2011 03:40 Go to previous messageGo to next message
John Watson
Messages: 8922
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>
Re: Recover drop table [message #537537 is a reply to message #537533] Thu, 29 December 2011 03:49 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

/forum/fa/449/0/

Previous Topic: RMAN Error
Next Topic: Recovery Catalog
Goto Forum:
  


Current Time: Fri Mar 29 00:49:22 CDT 2024