a winner, flashback query; AS OF TIMESTAMP

Recovering data from a malicious SQL update carried out on a production database. Instead of restoring the whole database to recover some lost data I gave flashback query a try and even thougg the ‘undo_retention’ parameter was set to only 5 minutes oracle recovered the data from the table from about 18 hours before.

SQL Statements below recover the data from a timestamp just before the offending SQL update was carried out. I saved the data to a _BAK table.

From: Visser, Remi
Sent: Wednesday, September 22, 2010 3:37 PM
To: ********
Subject: recoverd *********** table using oracle flashback query

Importance: High

Hi ****

I was able to recover the table using flashback query. See below.

Please check the data in the table ********_BAK it contains the data in ******** as of 2010-09-22 09:00:00. The offensing update was carried out at 9:10 hours.

When the data is OK I can merge the data from the _BAK table to the original production table.

SQL> create table ********_BAK
  2  as
  4     TO_TIMESTAMP('2010-09-22 09:00:00', 'YYYY-MM-DD HH:MI:SS')
  5  ;
Table created.

SQL> create public synonym ********_BAK for ********.********_BAK;
Synonym created.

SQL> grant select on ********.********_BAK to public;
Grant succeeded.

Dutch people, notice the schema owner name ‘BAGR.********’, Hello my name is ‘BAGR’, how nice…

Leave a Reply

Your email address will not be published. Required fields are marked *