Flashback Session … that was cool! Thanks mate!

Today my ass was saved by my good friend and colleague, Lars Johan Ulveseth. Thanks mate!
Yes I messed up … just a little … in a test environment. But it could have made a lot of mess.

We are getting close to a new release, and as usually it gets a little heated getting close to code freeze. A lot of system testing is going on in the systemtest database, and the testers really don´t need any disturbance. I was just to test a implementation and a rollback script in the same database. All code in the script was just new stuff, not effecting anything of the testing activity going on at the moment. At least – I thought. But having done some cut & paste, one of the scripts did a “drop user myuser cascade” on a user being used at the moment. Uppss …

My dear friend Lars Johan heard my “upppsss” and noticed the upset look on my face. He followed up by a “hang on”, and after 2-3 minutes he said: “Ok. Now everything is back!”.
Silent and impressed I had to ask: “How in the heck did you do that?”. His answer was “Session flashback”. Well … Flashback has been known to me for quite some time now, but not this session flashback feature. This was definetly new and cool! At my surprise it seems like this functionality has been available since 9i. At least the dbms_flashback.enable_at_time was introduced in this version. But it definitly worked in version 11g R2. Here is what he did:

First he ran the following in his Toad session:

exec dbms_flashback.enable_at_time(to_date('2012-05-25 13:00:00', 'YYYY-MM-DD HH24:MI:SS'));

Then he just opened a database browser in the same toad session, and found the user and objects I just deleted (the information was still in the undo segments) and generated the create scripts.
After running the (re)create script, and recovering my mess, he turned of the flashback by running:

exec dbms_flashback.disable;

After he had completed, he told me that this was the method he also used last week when there was someone else deleting a little to much in a production database.
Just another nice and handy feature to know about, and that you sooner or later will need!

Thanks mate!

Post a Comment

Your email is never published nor shared. Required fields are marked *