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.
Flashback Backup
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
site.
