Tech Availability: Flash Back to a Better Time
It's New Year's Eve. John, the DBA at Acme Bank, is reveling with his friends, counting down the arrival of the new year. Just when the clock strikes midnight and the crowd roars, his pager goes off. At the bank's data center, during the end-of-the-year bookkeeping process in the interest accumulation batch run, something has gone wrong and all the interest calculations are incorrect. The good news is that the development team has identified the bug and developed an emergency fix, but the fix can't undo the damage already done. The operations manager asks John if he can somehow turn back the hands of time and place the database back to its state before the batch process started, which was around 11:00 p.m.
Does that sound familiar to you DBAs? What are John's options?
Prior to Oracle Database 10g, John could have done a point-in-time recovery to reinstate the database to the desired point. Unfortunately, the bank's regular daily backup starts around that time, meaning he would have to apply almost 24 hours' worth of archived logs to the restored database.
Another option available in Oracle9i Database is to use the flashback query feature to reconstruct the table rows as of 11:00 p.m. and create a different set of tables manually. This approach, although possible, becomes impractical if the number of tables is high.
Fortunately, John's current database is Oracle Database 10g, so he has more options.
If you have worked with databases long enough chances are you have screwed up a massive amount of data by accident. I myself have done it on two seperate occasions.
I once updated every address in Japan to 'Good,' no matter if they are valid or not. Unfortunately this was at a time when I was using SQL Server, and I didn't know all that much about SQL, just enough to get in trouble. Luckily, I had exported a file of all Japanese addresses before I updated them, so it wasn't that big a deal.
The second time I messed up was when tasked with updating all accounts with null passwords to a random password. I created a Cursor that did all the work for me, but I neglected to do two things correctly: Firstly I didn't make my variable in the cursor large enough to hold the entire generated password, actually I made it just one character for some reason. Secondly I forgot to include a WHERE clause, so my flawed cursor updated every record to have a password of 'A.'
Not good. I have since learned to use the TRANSACTION statement so that I can rollback my changes (SQL Server ships with AUTO-COMMIT set on as the default, something which Oracle does not).
Anyway, I am excited about this Flashback query feature, and I think it is very cool. At the moment we are stuck using Oracle 8i, but one of my goals of this year at work is to update our database to 9i and use all of these whiz bang new features.
Recent Comments