How many times have database recoveries been performed because of incorrect changes made to database data? A user deletes or updates “one too many rows” by mistake and the result is a time-consuming and error prone process to restore and recover the database to a point-in-time before the error occurred.
Oracle Education states that point-in-time recoveries are responsible for the majority of DBA errors and the resulting unrecoverable databases. When I was teaching Oracle as an Oracle-certified instructor, I ran my backup and recovery classes like a boot camp (one of the high-points of my career was when I won one of Oracle's Top Instructor Titles). Some of my students referred to my classes as “Foot Camp” because of my last name. When my students were done; they may not have liked me but they certainly knew how to back up and recover a database. I wasn’t going to lay awake nights thinking I was sending them on their way to future Oracle stardom without having all of the information they needed to safeguard the data stores they were charged with protecting.
A common question in the Oracle DBA backup and recovery class was “why can’t I just roll the database back to remove unwanted changes instead of restoring the database from a backup and applying the redo logs to roll forward to a point-in-time before the error occurred?” The question was invariably followed by “don’t you think that would be easier?” The answer was always the same “yes, it would be easier, but the Oracle database doesn’t have that capability.”
Until Oracle10G that is… Oracle10G’s Flashback Database feature provides a new tool in the DBA’s recovery toolbox. Flashback Database allows the DBA to “roll back” a table, set of tables or the entire database to a previous point-in-time.
A flashback log is used to capture old versions of changed data blocks. During Flashback execution, the Oracle database restores the old versions of the data blocks to their original locations which allows the database to be rolled back to a previous point-in-time. Oracle provides the following example in its sales collateral to highlight how simple this new Flashback Database Feature is:
SQL> flashback database to ‘2:05 PM’;
Compare that to previous point-in-time recovery procedures, which required the entire database to be restored to a previous backup and then rolled forward using archived and online redo log files to replay the changes to a point-in-time before the error occurred.
Oracle10G also uses the flashback logs to provide the mechanism for its Flashback Backup feature. Flashback Backup allows administrators to take a base level backup and then perform nightly incremental backups to roll the backup database forward to the current point-in-time. Since the changed blocks are recorded by the database, a full scan of all of the database blocks is no longer required to keep the backup database synchronized with its production counterpart.
OK, that was a LOT of
information. If you have any
questions on any of these features,
please feel free to comment. I
would also like to encourage readers
to respond with any hints, tips,
tricks and techniques on
administering these Oracle10G
features. That’s the great benefit
of a blog! I have more information
on Oracle10G in other blogs on this