OraFAQ Forum: Backup & Recovery

Home » RDBMS Server » Backup & Recovery » table recovery (Oracle 10g/Windows )

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

table recovery [message #514921]

Thu, 07 July 2011 05:02

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

Report message to a moderator

Re: table recovery [message #514924 is a reply to message #514921]

Thu, 07 July 2011 05:09

Michel Cadot
Messages: 54672
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

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.

Report message to a moderator

Re: table recovery [message #514928 is a reply to message #514927]

Thu, 07 July 2011 05:15

gxeon
Messages: 53
Registered: January 2011
Location: Mumbai

Member

we are also not using recovery cataloge, it is set to control file.

Report message to a moderator

Re: table recovery [message #514930 is a reply to message #514928]

Thu, 07 July 2011 05:33

Michel Cadot
Messages: 54672
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

It does not change the answer.

Regards
Michel

Report message to a moderator

Re: table recovery [message #514931 is a reply to message #514930]

Thu, 07 July 2011 05:38

Michel Cadot
Messages: 54672
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

Report message to a moderator

Re: table recovery [message #514933 is a reply to message #514931]

Thu, 07 July 2011 05:49

John Watson
Messages: 3174
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.

Report message to a moderator

Re: table recovery [message #515118 is a reply to message #514933]

Fri, 08 July 2011 02:39

gxeon
Messages: 53
Registered: January 2011
Location: Mumbai

Member

Thanks Michal the issue is solved Smile

Report message to a moderator

Re: table recovery [message #516459 is a reply to message #514927]

Mon, 18 July 2011 04:56

x-oracle
Messages: 300
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';

Report message to a moderator

Previous Topic:	RMAN Incremental Backup
Next Topic:	Duplexing RMAN backup

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jun 18 15:55:49 CDT 2013

Total time taken to generate the page: 0.15269 seconds