Home » RDBMS Server » Backup & Recovery » table recovery (Oracle 10g/Windows )
table recovery [message #514921] Thu, 07 July 2011 05:02 Go to next message
gxeon
Messages: 53
Registered: January 2011
Location: Mumbai
Member
Hello Everyone


I am new to Oracle DBA

i need to restore one droped table which i mistakenly deleted from my UAT Server.My flashback was off also. please suggest me how to recover the lost table by RMAN.


regards
GG
Re: table recovery [message #514924 is a reply to message #514921] Thu, 07 July 2011 05:09 Go to previous messageGo to next message
Michel Cadot
Messages: 58605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Restore backup in another place (only SYSTEM, SYSAUX, undo and this table tablespaces), export the table, import into the originale database.

Regards
Michel

[Updated on: Thu, 07 July 2011 05:11]

Report message to a moderator

Re: table recovery [message #514927 is a reply to message #514924] Thu, 07 July 2011 05:13 Go to previous messageGo to next message
gxeon
Messages: 53
Registered: January 2011
Location: Mumbai
Member
Sir

can u please tell me how to recover the droped table from RMAN. it was belonging to users tablespace.
Re: table recovery [message #514928 is a reply to message #514927] Thu, 07 July 2011 05:15 Go to previous messageGo to next message
gxeon
Messages: 53
Registered: January 2011
Location: Mumbai
Member
we are also not using recovery cataloge, it is set to control file.
Re: table recovery [message #514930 is a reply to message #514928] Thu, 07 July 2011 05:33 Go to previous messageGo to next message
Michel Cadot
Messages: 58605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It does not change the answer.

Regards
Michel
Re: table recovery [message #514931 is a reply to message #514930] Thu, 07 July 2011 05:38 Go to previous messageGo to next message
Michel Cadot
Messages: 58605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database Backup and Recovery Advanced User's Guide
Chapter 19 Advanced User-Managed Recovery Scenarios
Section Recovering from a Dropped Table: Scenario

Regards
Michel

Re: table recovery [message #514933 is a reply to message #514931] Thu, 07 July 2011 05:49 Go to previous messageGo to next message
John Watson
Messages: 4400
Registered: January 2010
Location: Global Village
Senior Member
Quote:
My flashback was off also

Do you mean that flashback database was not enabled, or that the recyclebin was not enabled?
orcl> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

orcl> sho parameters recyclebin

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------
recyclebin                           string      on
orcl>

If your recyclebin is on, and you did not drop the table with the PURGE option, recover the table and all dependent objects with
flashback table [tablename] to before drop;

You'll have to be quick, though - its extents may be overwritten if the tablespace is filling up.
Otherwise, Michel has given you the only option.
Re: table recovery [message #515118 is a reply to message #514933] Fri, 08 July 2011 02:39 Go to previous messageGo to next message
gxeon
Messages: 53
Registered: January 2011
Location: Mumbai
Member
Thanks Michal the issue is solved Smile
Re: table recovery [message #516459 is a reply to message #514927] Mon, 18 July 2011 04:56 Go to previous message
x-oracle
Messages: 326
Registered: April 2011
Location: gujarat
Senior Member
you can recover your users tablespace by tspir mathod

RMAN> CONNECT TARGET
RMAN> RECOVER TABLESPACE users, example
> UNTIL TIME '2007-06-29:08:00:00'
> AUXILIARY DESTINATION
> '/u01/app/oracle/oradata/aux';
Previous Topic: RMAN Incremental Backup
Next Topic: Duplexing RMAN backup
Goto Forum:
  


Current Time: Mon Jul 28 22:44:48 CDT 2014

Total time taken to generate the page: 0.05791 seconds