Flashback!Posted: August 8, 2007
Many of our application owners arrive from Microsoft SQL Server backgrounds, where they learned some very bad habits. For example, when using SQL Server’s query analyzer, every time you run a query it will automatically commit. When we started using Oracle, they were confused at first, but quickly learned to configure their Oracle tools (AquaStudio is rather popular for some reason) to emulate the same behavior. Usually it doesn’t really matter, until someone makes a mistake.
“Chen, how do I roll back a transaction after it was committed?” is not the most frequent question I hear, but it is somewhere in the top 10.
We keep our undo for about 30 minutes, so if they caught me fast enough, I’m able to save the day using the magical flashback query:
SELECT * FROM mytable AS OF TIMESTAMP TO_TIMESTAMP ('06-aug-07 08:30:00','DD-MON-YY HH24: MI: SS')
Once the user verifies that I managed to get a good copy of his table, I usually save it as another table, so I won’t lose it because too much time passed.
create table mytable_old as SELECT * FROM mytable AS OF TIMESTAMP TO_TIMESTAMP ('06-aug-07 08:30:00','DD-MON-YY HH24: MI: SS')
And then I have to figure out how to use the old data to undo what the user did. Do I insert missing rows? Reverse a bad update? It all depends on the exact mistake, but if I have good data from flashback, I know that we can fix the damage. Now the user can go to her white board and write 50 times: “I will not use auto commit”.
I heard that in 11g flashback will use the redo logs and not just the undo, so theoretically it means that there won’t be any time constraints on resolving the issue. I’m looking forward to that.
Sometimes, DBAs make mistakes too. And our mistakes are even more painful because DROP and TRUNCATE can’t be rolled back. Oracle now has an option of keeping dropped tables for a while longer in a recycle bin before really dropping them, allowing the DBA to fix his mistakes. We don’t use the recycle bin for a silly reason – when we first started using 10g, this feature caught us by surprise and caused us to run out of space when least expected, so we turned it off and planned to deal with this later. Later didn’t happen yet. So when we drop an important table by mistake we need to restore our backups on a standby machine, roll the logs until the point just before the mistake and get the data from there. That can easily take two hours, while using flashback takes few minutes.
Maybe its time to revisit our recycle bin policy.