OraFAQ Forum: Backup & Recovery » how to recover truncated table

Home » RDBMS Server » Backup & Recovery » how to recover truncated table (Oracle 10g, Windows XP)

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

how to recover truncated table [message #503909]

Wed, 20 April 2011 05:32

reym21
Messages: 241
Registered: February 2010
Location: Philippines

Senior Member

Good day...

One of our programmer/user accidentally truncated 2 tables in production. The dB is in NO ARCHIVELOG MODE.

How can we restore records of the 2 tables?

Please help.

Thank you.

Report message to a moderator

Re: how to recover truncated table [message #503915 is a reply to message #503909]

Wed, 20 April 2011 06:24

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

Senior Member
Account Moderator

You can't.

Regards
Michel

Report message to a moderator

Re: how to recover truncated table [message #503916 is a reply to message #503915]

Wed, 20 April 2011 07:00

ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

If you have a cold backup of the database you could restore that backup to another location and get the data at the point of that backup from that new database.

Otherwise it's definitely gone. And since truncate is DDL not DML it doesn't write redo entries, so it wouldn't have helped to be in archivelog mode either.

Report message to a moderator

Re: how to recover truncated table [message #503917 is a reply to message #503916]

Wed, 20 April 2011 07:21

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

Senior Member
Account Moderator

In archivelog mode, you could restore the database (in an auxiliary location) up to the point before the truncate then export the table and import it in the original db.

Regards
Michel

Report message to a moderator

Re: how to recover truncated table [message #503918 is a reply to message #503917]

Wed, 20 April 2011 07:24

ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

That's true. Without archivelog you can only restore the point of the last cold backup (if you have one) to the other location and import from that.

Report message to a moderator

Re: how to recover truncated table [message #503924 is a reply to message #503909]

Wed, 20 April 2011 08:35

reym21
Messages: 241
Registered: February 2010
Location: Philippines

Senior Member

Guys,

Thanks for your responses.

Now I know what's our db status currently.
Our last resort is the restoration of backup
last April 15.

Again many thanks.

Report message to a moderator

Re: how to recover truncated table [message #503942 is a reply to message #503916]

Wed, 20 April 2011 10:09

reym21
Messages: 241
Registered: February 2010
Location: Philippines

Senior Member

Btw Sir, why is it that the execution of the truncate table commnand didn't registered in the alert.log?

Thanks again.

Report message to a moderator

Re: how to recover truncated table [message #503947 is a reply to message #503942]

Wed, 20 April 2011 10:19

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

Senior Member
Account Moderator

There is no reason to register it.

Regards
Michel

Report message to a moderator

Re: how to recover truncated table [message #504341 is a reply to message #503947]

Sat, 23 April 2011 18:18

cparisienreveregroupcom
Messages: 3
Registered: April 2011

Junior Member

correct you'll have to restore from a cold backup. if you have one. there's no way to recover the data beyond that.

[Updated on: Sat, 21 January 2012 01:07] by Moderator

Report message to a moderator

Re: how to recover truncated table [message #504356 is a reply to message #504341]

Sun, 24 April 2011 01:23

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

Senior Member
Account Moderator

Thanks to confirm what we posted.

Regards
Michel

Report message to a moderator

Previous Topic:	Managing partition table please help
Next Topic:	RESTORE RMAN

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 20 02:49:12 CDT 2024