Tech Availability-Flash Back to a Better Time

Tech Availability-Flash Back to a Better Time:
"TECHNOLOGY: Availability

Flash Back to a Better Time
By Arup Nanda

Restore a table or an entire database to a point in the past, using a simple SQL statement.

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.

"