How to recover previous rows after update

[Anupam Kumar]

Hi,
One user has accidentally upadated and commited the transaction. Now
my job is to restore the previous data for that table. Is there any
way to do it. Its already been 24 hr past when It happen.. I'm not
sure wether I can use Flashback or not.

Pease help.

Thanks in advance,
Anupam

[Mladen Gogala]

1) What is the version of the database?
2) Is the flashback on? What is the retention target, if the flashback is
on? The default flashback retention is 24 hours. Even if the flashback
is on, if the retention target is not set to more than 24 hours, you
will not be able to get the data back. Skip this question if the
answer to the first question starts with number 9.
3) Is there a chance that the data may still reside in UNDO segments?
AS OF TIME queries query UNDO segments. If the data is there, it can
be restored. If the database is active, chances are very slim.
4) Do you have a backup? Are you sure?
5) Is your resume up to date? This maybe the right time to update your
resume. You should leave this episode out of your resume.




--
http://mgogala.byethost5.com

[joel garry]

Besides all the new features and trick stuff like PITR, logical
backups are very useful for this type of thing (viz., user error). I
personally have found daily exports to be quite useful, especially
being redundant with proper backups.

PITR means Point In Time Recovery, and there are examples in the docs,
on MOS and on the web. In general, you restore the database (or
tablespace if it contains all the relevant data) on to another
database, stopping before the problematic transaction took place.
Then, either update from the proper table or do a table level
destructive export/import.

It does help to post the exact Oracle version/patch level and the same
for the OS and platform, database options and edition, as well as any
space limitations you have.

jg
--
@home.com is bogus.
Dang, I would've bought one of the originals if they had just been
selling them when I bought a car:
http://www.signonsandiego.com/news/2011/dec/02/carmaker-closes-without-green-energy-aid/

[mhoys]

Did you try to query the flashback data for the specific table to see
if it returns any rows?

select * from <table>
as of timestamp to_timestamp('2011-12-01 11:00:00','YYYY-MM-DD
HH:MI:SS')

Matthias

[onedbguru]

First, fire the user, then Restore the backup to another system and
recover the missing row. or if you have an export, restore the table
to a new name (see impdp remap_schema, remap_table and recover it that
way.