I won't harp on the obvious: The ability to access recent,
consistent backup files and archived redo logs is the key to recovering from
and surviving a disaster. Of course, I am assuming your production database
is running in ARCHIVELOG mode for maximum flexibility for recovery. Moreover,
if the database is running in ARCHIVELOG mode, I am assuming that Recovery
Manager (RMAN) is being used for creating backups and recovery.
As in most
shops, we have designed our production backup scheme to run overnight during
off-peak hours. We have the luxury of a relatively small production database
(330GB) at about 20% utilization, so nightly Incremental Level 0 RMAN backups
only consume about 45-50 GB of disk space, and they are completed in
approximately 4 hours. However, this gives me extreme flexibility in rolling
forward from a potential disaster, including point-in-time incomplete recovery
to RMAN backups.
My Oracle University teacher repeated this in class over and over again: "RMAN
is the best way to back up a database - but it's not the only way." I thank him
every day for the reminder! Even though our RMAN backups are created nightly,
as a second line of defense against data loss I create a full set of exports
every night. If I should need to recover just one table, or a portion of the
table, it is a lot easier to recover it from an export than from a full tablespace
backup. In addition, if a disaster does arise, and my backups are damaged as
well, I have a chance of recovering at least some of the data from an export.
media storage of backup files.
While writing to disk media is probably the speediest and
easiest mechanism for backup files retention, in many cases the disk space
required is a luxury. Even though I do have the advantage of sufficient disk
space, however, I have worked out a scheme of alternate media backups (tape) as
a third line of defense against loss of the database server. In an absolute worst-case
scenario - complete loss of the physical hardware - I still have a guaranteed
method to recover a significant portion of my production database, albeit
limited by the most recent available set of archived redo logs on tape.
word about alternate media backups: Offsite storage is strongly recommended for
at least some of the backup tapes. We currently send a complete set of backups
off to a remote site once a week for vaulted archival with guaranteed
turnaround of one hour for any particular tape (for a small fee, of course).
having a hard time imagining why you'd ever need offsite storage for backups,
here's a classic Oracle "urban legend" I heard at a recent seminar. A panicked
DBA called Oracle for help because his production server had been destroyed
when a truck backed up through his company's loading dock, which was on the
other side of the server room. Part of the collapsed wall crashed down directly
on top of the production server, destroying it. The DBA had an alternate server
available, and had been backing up his database to tape.
the backup tapes were stored - you guessed it - on top of the production
the disaster recovery plan.
Once all the disaster recovery pieces I have discussed
previously are in place, I have found it is important to determine if the
disaster recovery plan will work by actually simulating at least the most
critical disaster scenarios.
experiences a few Saturdays ago, I reviewed all the media failure
possibilities, including the loss of one or more datafiles containing SYSTEM,
UNDO/rollback, index, and data segments. Then I constructed scenarios under
which they might fail, and my expected course of action. Finally, I constructed
methods to simulate the failure.
media failures of the various segment types, for example, I configured a RAID-0
drive on one of our development servers and then restored copies of a test
database so that the appropriate datafiles were installed on that drive. While
our QA manager simulated activity against that datafile by running application
code that accessed that datafile's tablespace, I simply pulled that drive out
of the disk array. I compared the expected results from the simulated failure
against my expectations, and then attempted to restore and recover the damaged datafile
using appropriate RMAN scripts.
I ran into
some unexpected challenges with my initial attempts at RMAN recovery scripts,
since some of the commands to rename and switch datafiles during restoration
are slightly different from those used when restoring from "hot" or "cold"
backups of datafiles and tablespaces. However, I have considered the lessons I
learned during the evaluations of these scenarios to be invaluable, since I now
have working examples of RMAN scripts for each specific scenario.
I am now fully confident that in the worst-case scenarios of a partial or
complete media failure of my production databases, I can easily restore and
recover the appropriate datafiles from an RMAN backup set - something I do not
ever want to have to do under the gun with one hand on the manual and one hand
on the keyboard!
is an Oracle DBA for a telecommunications company in Schaumburg, IL. He can be
contacted at email@example.com.