Flashback Query Usage Example

Flashback Query

In my earlier blog, I explained about Flashback Database Feature usage. In this blog I wanted to give an example of a Flashback Query usage.

I received a call one Friday afternoon from one my client DBAs, just before I was getting ready to leave my office. He was extremely nervous on the phone and could not speak clearly. I said "oh no.. don't worry" and then asked him "What happened?".

He just realized that he had just dropped bunch of rows from a critical table in production. He wanted to know how long it will take to restore the database. I asked him if he knew when he performed this operation. He said he didn't know just not in the mindset to guess either. I asked him if the previous RMAN backups were good, he said yes. But he deleted bunch of archivelog files this morning. So point in time recovery just before the delete operation was out of question.

I asked him if the database had undo_retention set. He said yes with 24 hour retention. I asked him the name of the table and told him not to call me or IM me next 10 min.

Here are the steps I followed. I just estimated 3 hours and started querying from the table from last three hours. First to see how many rows were there around 3:00 P.M on Feb 1st.

SELECT count(*) FROM schema_name.card_accounts AS OF TIMESTAMP TO_TIMESTAMP('01-FEB-10 03:00:00 PM','DD-MON-YY HH:MI:SS PM')
/
20004

Ok there were 20004 records at 3:00 P.M. It only had 2 records now at 4:30 P.M. I have used dummy schema_name so you won't guess my client.

Clearly the rows were deleted between 4:00 P.M and 4:30 P.M. But it is a wide window.

SO let's query again

SELECT count(*) FROM schema_name.card_accounts AS OF TIMESTAMP TO_TIMESTAMP('01-FEB-10 03:30:00 PM','DD-MON-YY HH:MI:SS PM')
/
20004

SELECT count(*) FROM schema_name.card_accounts AS OF TIMESTAMP TO_TIMESTAMP('01-FEB-10 04:00:00 PM','DD-MON-YY HH:MI:SS PM')
/
20004

SELECT count(*) FROM schma_name.card_accounts AS OF TIMESTAMP TO_TIMESTAMP('01-FEB-10 04:30:00 PM','DD-MON-YY HH:MI:SS PM')
/
2

Alright, so the rows got deleted between 4:00 P.M and 4:30 P.M. There my phone rings again. I picked up and asked him "Hey Mike, does 20004 sounds good?". "Yeah that is what I had before" Mike replied.

I inserted those rows to a temporary table.

insert into card_accounts_tmp2 select * from schema_name.card_accounts AS OF TIMESTAMP TO_TIMESTAMP('01-FEB-10 03:00:00 PM','DD-MON-YY HH:MI:SS PM')
/
20004 rows created.

Ok go ahead and check that temporary table to see if you have everything you need! He said "No". OH, yeah I forgot to "commit".


I issued commit.

SQL> commit;

Commit complete

Ok, "Now?" I asked. He typed carelessly and said "Wow! yes they are all there! You are my hero!" , slowly raising his voice.

Now I queried the table again and found 8 rows. oh yeah, the users are in and inserting rows into that table.

Mike said that was a easy fix, he can just merge those two tables and insert back.

Ok, Problem Solved and Over! So was my Friday evening! :-(