Oracle Concepts - Backup
and Recovery Concepts
Oracle Tips by Burleson Consulting
Backup & recovery
As should be obvious from the previous lesson,
Oracle is a complex, interrelated set of files and executables. With
Oracle8 and Oracle8i it hasn’t gotten any simpler. The database files
include data segments, redo logs, rollback segments, control files,
bfiles, libraries, and system areas. Each of these files is not a
separate entity but is tightly linked to the others. For instance, the
data files are repositories for all table data; the data file
structure is controlled by the control file, implemented by the system
areas, and maintained by a combination of the executables, redo, and
rollback segments. Data files reference bfiles that are tied to
external procedures stored in libraries that are referenced in
procedures stored in data files. This complexity leads to the
requirement of a threefold backup recovery methodology to ensure that
data recovery can be made.
The threefold recovery methodology consists
Normal backups using system backups, Oracle
Backup Manager, Recovery Manager or a third party, tested against
Exports and imports.
Archive logging of redo logs.
Let’s look at each of these and how they are
Normal system backups, referred to as either
hot or cold backups, are used to protect the system from media
failure. Each can and should be used when required.
A cold backup, that is, one done with the
database in a shutdown state, provides a complete copy of the database
that can be restored exactly. The generalized procedure for using a
cold backup is as follows:
Using the shutdown script(s) provided,
shutdown the Oracle instance(s) to be backed up.
Ensure that there is enough backup media to
back up the entire database.
Mount the first volume of the backup media
(9 track, WORM, 4mm, 8mm, etc.) using the proper operating system
For example on OpenVMS:
mount/foreign dev: volume_name
–orw /dev/rmt0 /tape1
For example on OpenVMS:
$ ora_diskx+1:[oracle...]*.* -
$ ora_diskx+n:oracle...] dev:ora_<date>.sav/save
ora_diskx. . . . ora_diskn represents the system logicals in
accordance with OFA rules, with n being the highest numbered disk and
x being the lowest.
represents the date for the backup.
represents the backup media device name such as mua0:
/log=log_<date>.log names a file to log the results from the backup.
/save this tells
BACKUP to archive the files in save set format; this requires less
room than an image backup.
$ tar –cvf
/tape1 /ud*/oracle*/ortest1/* (for all Oracle data, log and trace
tar – this is short for tape archiver and is
the default backup command on UNIX, raw volumes may require "dd"
-cvf – These arguments tell tar to c: create a
new archive, v: tell us what it is doing, f: use the device
specification which follows (we could have not specified a device and
it would default to the default tape drive)
Once the backup is complete, be sure all
backup volumes are properly labeled and stored, away from the
computer. The final volume is dismounted from the tape drive using
the appropriate operating system DISMOUNT command:
For example on OpenVMS:
Restart the Oracle instances using the
appropriate startup script(s).
A hot backup, or one taken while the database
is active, can only give a read-consistent copy but doesn’t handle
active transactions. You must ensure that all redo logs archived
during the backup process are also backed up. The hot backup differs
from the cold backup in that only sections of the database are backed
up at one time. This is accomplished by using the ALTER command to
modify a tablespace’s status to backup. Be sure that you restore the
status to normal once the database is backed up or else redo log
mismatch and improper archiving/rollbacks can occur.
While it is quite simple (generally speaking)
to do a cold backup by hand, a hot backup can be quite complex and
should be automated. The automated procedure should then be thoroughly
tested on a dummy database for both proper operation and ability to
restore prior to its use on the production database(s).
Limitations on hot or on-line backups:
* The database must be operating in ARCHIVELOG
mode for hot backups to work.
* Hot backups should only be done during off
or low-use periods.
* During the hot backups the entire block
containing a changed record, not just the changed record, is written
to the archive log, requiring more archive space for this period.
The hot backup consists of three processes:
The tablespace data files are backed up.
The archived redo logs are backed up.
The control file is backed up.
The first two parts have to be repeated for
each tablespace in the database. For small databases, this is
relatively easy. For large, complex databases with files spread across
several drives, this can become a nightmare if not properly automated
in operating system specific command scripts.
As you can see, this is a bit more complex
than a full cold backup and requires more monitoring than a cold
backup. Recovery from this type of backup consists of restoring all
tablespaces and logs and then recovering. You only use the backup of
the control file if the current control file was also lost in the
disaster; otherwise, be sure to use the most current copy of the
control file for recovery operations.
TIP: In a number of computer facilities
backups are kept close at hand, sometimes in the same room as the
computer. What would happen if a site disaster destroyed the computer
room? Not only the hardware, but all of the system backups and your
data could be lost. Store backups in another building or even totally
off-site somewhere. This assures that come fire, flood, or typhoon,
you should be able to get backup one way or another.
UNIX of course requires a different scripting
language and command set.
One problem with a canned script for hot
backup is that they don’t automatically reconfigure themselves to
include new tablespaces, or redo logs. The script shown below is an
example of how to let Oracle build its own hot backup script using
dynamic SQL and the data dictionary. This script is excerpted from
Oracle Administrator from RevealNet, Inc.Version 99.2 (an online
This is an excerpt from
the eBook "Oracle
DBA made Simple".
For more details on Oracle
database administration, see the "Easy
Oracle Jumpstart" by Robert Freeman and Steve Karam. It’s
only $19.95 when you buy it directly from the publisher