a winner, flashback query; AS OF TIMESTAMP

  • Published on Sep 27 2010
  • # 3,907
  • oracle

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
  3  SELECT * FROM BAGR.********    AS OF TIMESTAMP 
  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

Helpful? - leave your note below so I can brag

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>