This morning, a coworker came to my office with an awkward smile on his face. “Oops”, he started, “I ran a bad query and I updated a bunch of records incorrectly.” He thought he had messed up big time, but actually, I was able to recover data for him quite easily, utilizing our Oracle 11g database’s flashback feature (note: this method applies to Oracle 10g as well).
I asked him two questions: What was the update SQL statement that he ran, and when did he run it? For our example, let’s say the SQL statement was as follows, and he ran it at 10am.
-- Bad SQL statement executed by user update myTable set field1 = field2, field2 = null where field1 is null;
As you can see, since he is moving field2 into field1, and previously other records had already gone through this process, he could no longer tell which ones were modified by him at 10am and which ones were done previously. Since it only had been a few minutes since he committed the bad update statement, I knew I had plenty of time to recover the data. Actually, I did not need much time at all. This is what I did to identify the records he modified:
create table myTable_comparison
as
select before.pkey_field, before.field1, after.field1
from myTable after,
myTable as of timestamp to_date('6/1/2009 09:59:00','mm/dd/yyyy hh24:mi:ss') before
where before.pkey_field=after.pkey_field
and before.field1 <> after.field1;
Note in the above example, I used the additional clause … as of timestamp [insert a date value here] to pull out a view of what the data looked like today at 9:59am, a moment before my coworker updated the data. This creates the table myTable_comparison that contains all rows which had a different before and after field1 value, using myTable’s primary key column as the unique identifier to help my coworker review what had been changed. He confirmed the records in this table are exactly what he wanted to revert, so I rewrote the select statement into an update statement to revert the data back to the previous state.
In this case, my coworker mistakenly updated values. What if he had mistakenly deleted records instead? To recover, we just need to find the records that was present previously, but no longer present anymore by using an outer join between the before and after tables.
select before.pkey_field, before.field1, after.field1
from myTable after,
myTable as of timestamp to_date('6/1/2009 09:59:00','mm/dd/yyyy hh24:mi:ss') before
where before.pkey_field=after.pkey_field
and before.pkey_field=after.pkey_field(+) and after.pkey_field is null;