DATABASE BACKUP AND RECOVERY
USING ORACLE ENTERPRISE BACKUP UTILITY
One of the most critical and challenging duties of an Oracle DBA
is implementation of database backup and recovery procedures.
Providing this protection to multiple databases throughout an
enterprise is a formidable challenge. Oracle provides a solution
to the challenge of database backup and recovery via Oracle
Enterprise Backup Utility.
THE BACKUP AND RECOVERY CHALLENGE
An Oracle DBA is responsible for ensuring reliable database
backups. Oftentimes this means writing numerous, complex scripts
to protect a variety of databases throughout an enterprise. Some
scripts will cause databases to be shutdown for off-line backups,
while other scripts will implement on-line, or "hot" backups. In
each case, the script accuracy is crucial; mistakes in backup
scripts often produce useless backups. More than one DBA has
been embarrassed to discover that the "backups" are completely
For businesses that deploy only a few, stable databases, the
process of manual script-generation and testing is achievable. In
many companies, however, the quantity and diversity of databases
complicate the backup process. For instance, on every server
housing an Oracle database, all backup scripts need to be
generated, tested, scheduled, and monitored. When database
configurations change, the entire process may need to be
ORACLE ENTERPRISE BACKUP UTILITY (EBU)
Oracle Enterprise Backup Utility (EBU), used in conjunction with
a media manager such as HP OMNIBAK II, provides a simplified
method for managing database backup and recovery throughout an
organization. Instead of tedious writing and testing of backup
scripts, the DBA simply chooses the backup method desired (hot or
cold), and then schedules a backup for periodic execution.
EBU works "behind the scenes," communicating with Oracle to
direct database backup operations. Using EBU, the DBA need not
worry about the exact UNIX or SQL commands to issue in order to
perform hot or cold backups. The backup system issues all
commands to prepare tablespaces and copy data files, archive
logs, and control files. Should recovery be needed, the
EBU/OMNIBAK system locates all necessary files, and then restores
each file to the desired directory.
In order to manage database backups, a database is "registered"
in the EBU "catalog." The registration process captures all
references to parameter files, database files, and control files.
The EBU catalog, which consists of 20 Oracle tables, is stored in
any convenient Oracle7 database, on any server (local or remote).
During the backup or restore process, the EBU catalog is
automatically consulted to ensure that all pertinent files are
At McDonnell Douglas in Long Beach, California, EBU has been
implemented to protect both development and production databases.
A total of twenty-five Oracle databases are registered in the
EBU/OMNIBAK system. The servers are divided into production and
development "cells." A separate EBU backup catalog is maintained
for each cell.
The results from a typical EBU registration session are shown in
Figure 1. Note that the full path name of each data file is
captured into the EBU catalog.
THE BACKUP PARTNER: HP OMNIBAK II
While EBU transmits the database commands needed to backup or
restore an Oracle database, EBU is not designed to work alone.
Rather, EBU works in conjunction with "media management"
software, such as OMNIBAK II, by Hewlett Packard. Whereas EBU
manages the database, OMNIBAK manages all physical media,
typically robotic tape systems. All file transfers to and from
the backup system are under OMNIBAK control.
OMNIBAK allows for media drives to be grouped into media "pools."
In this way, certain drives can be reserved for critical
activities. For instance, tape drive 1 can be reserved for
regular daily backups of Oracle databases, while drive 2 can be
reserved for System Administration use. A backup for a given
database may be assigned to any media pool.
At McDonnell Douglas in Long Beach, the tape drives for each cell
are divided into two media pools. One pool is used for daily
backups; backups assigned to this pool are protected against
erasure for two weeks, and then recycled. The second pool is
reserved for weekend backups. Tapes from this pool are stored
off-site, and protected for five weeks.
OMNIBAK reduces database backup time by using parallel backup
"streams." These backup streams employ "Disk agents," which
manage file reads from disk. Similarly, "Media Agents" direct
file writes to tape. The degree of parallelism is selectable for
In addition to controlling the tape drives, OMNIBAK also provides
the GUI user interface. All backup and restore sessions are
initiated through OMNIBAK, using an X-windows interface. OMNIBAK,
in turn, communicates with EBU, relaying commands as needed.
DATABASE BACKUPS USING THE EBU/OMNIBAK SYSTEM
In order to prepare a new database for backup, three steps are
required: (1) Register the database; (2) Create a backup
"Barlist"; and (3) Specify a desired backup schedule.
STEP 1: REGISTER THE DATABASE
Database backup activities begin by typing XOMNI to launch
OMNIBAK. The user will see a list of options as shown in Figure
2. The first option, Backup, is used to Configure (register) a
database, as well as to define the type of backup desired.
Figure 3 illustrates a database being configured (registered)
with OMNIBAK. Information such as Hostname, Oracle Home, and
Oracle SID are entered into OMNIBAK. The name of a database
shutdown script and a startup script may also be entered. These
entries are applicable only to backups defined as off-line.
Upon completion of the registration window, the user selects
“Start Configuration.” OMNIBAK then activates EBU and transfers
all information to the EBU catalog.
STEP 2: CREATE A "BARLIST"
Once a database has been registered, the Backup option is again
selected, in order to create a backup "barlist." This window, as
shown in Figure 4, defines the Backup Devices (tape drives) to be
used, as well as backup options such as backup type and write
Upon completion of all backup entries, the Barlist is saved under
a user-defined name. The database is now available for backup.
STEP 3: SCHEDULE THE BACKUP
Once a database barlist is defined, the OMNIBAK option Change
Schedule is used to define the backup schedule. Any type of
periodic (or one-time) backup frequency may be selected, such as
every Sunday at 2:00 A.M., or daily at 10:00 P.M. The schedule
may be altered at any time. Figure 5 shows a typical backup
schedule, using a Monday-Wednesday-Friday backup schedule.
THE BACKUP PROCESS
When the backup time arrives, OMNIBAK/EBU reads the backup list
and automatically activates the backup session. For hot backups,
EBU issues the alter tablespace begin backup and alter tablespace
end backup commands. For cold backups, the database is first
shutdown, using the shutdown script name pre-defined in the
database registration window.
Once the database is prepared for backup, OMNIBAK readies the
specified tape drive and consults the EBU catalog to determine
which files should be copied. Every file identified with the
database is then queued for tape backup. Compression is performed
on each file to reduce storage requirements.
In addition to the data files, all archive logs (if applicable),
plus one copy of the control file are also copied to tape. The
archive logs may optionally be deleted from disk after copying.
For hot backups, each tablespace is removed from backup mode once
all files for that tablespace have been copied. For cold
backups, the startup script is activated following all file
copying, in order to return the database to normal operation.
At McDonnell Douglas in Long Beach, backup time for an entire
database ranges from 10 minutes for small databases to 40 minutes
for 14 gigabit databases. File restore time spans a similar time
REVIEWING PAST BACKUP SESSIONS
The Monitor OMNIBAK option is used to review past backup
sessions, or to monitor a backup in progress. Any backup session
may be reviewed by double-clicking over the session entry. As
shown in Figure 6, backups that have completed normally are
shaded green, while backups that encountered problems are shaded
Figure 7 illustrates a backup session in progress. The lower
pane shows the tablespaces being set to backup mode. The top
panes show backup statistics such as media label, write errors
(if any) and total storage used.
RESTORING A DATABASE
The OMNIBAK Restore screen, shown in Figure 8, is equally simple
to use. The user simply provides information such as Hostname,
Dbname, Oracle SID, and date from which a backup is needed. This
date is called "State On." If no date is entered, the latest
backup is used.
The user may request that the entire set of database files be
restored ("Restore All"), or only those files for certain
tablespaces. The files can be restored to their original
location (the default), or they can be "remapped" to any other
Regardless of the database restore desired, the user is not
required to supply tape ID, drive number, names of files, etc.
Instead, files are automatically retrieved using backup log
information from the EBU catalog.
Our experience at McDonnell Douglas with EBU/OMNIBAK has surfaced
some "tricks" and "traps" that DBAs should consider when
implementing EBU/OMNIBAK. These Tips and Techniques should
reduce the "learning curve" associated with implementing the
EBU/OMNIBAK TIPS AND TECHNIQUES
Remember that EBU/OMNIBAK is designed to restore files, not to
perform subsequent Oracle database recovery. Once files are
restored, the DBA must still initiate database startup.
The time to copy all files of a database is a function of the
number of files, as well as the file size. The quantity of
files is usually not an issue, except for databases having
hundreds of archive logs. To reduce backup time for databases in
archive mode, use larger, rather than smaller redo log sizes.
The server containing the EBU catalog should be a very reliable
platform, as the entire backup/restore process depends on the EBU
For backups of Oracle 7.3 databases, EBU version 2.0.12 (or
later) must be used. Earlier versions of EBU cannot backup 7.3
When EBU 2.0.12 is installed, a new environment variable,
OBK_DB_LOGIN must be added to the OMNIBAK template script,
obackup.sh.temp. This new variable provides the connect string
(e.g., internal) to the database being registered.
During EBU 2.0.12 install, some symbolic links from the "staging
area" to the CD-ROM may be missing, causing an install error. If
this occurs, simply create the missing links, and choose the
Retry option. Also, be sure to specify the relink executables
option when installing EBU 2.0.12, even though relinking is not
If the ORACLE_HOME for a registered database is changed, the next
registration of the database may fail. If this occurs, update
the EBU catalog table obk.obk_inst_conf to reflect the new
ORACLE_HOME. Similarly, if a database is re-created with a new
block size, update obk.obk_db_conf to show the new block size.
It is critical that a database be re-registered in the EBU
catalog whenever the number or location of database files
changes. Without re-registration, EBU will not “know” about the
new files. Re-registration is easily implemented through the
UNIX CRON facility.
Database recovery from a "cold" OMNIBAK backup will require use
of the resetlogs option. This is necessary because EBU does NOT
copy the on-line redo logs. This is not an omission, as closed
on-line redo logs do not contain “roll forward” information
required for a database recovery.
Once EBU "knows" (via re-registration) that a database has been
opened with "resetlogs," all prior backups will be unusable, as
EBU protects against merging incompatible database files. Thus,
always activate a fresh backup whenever the resetlogs option has
been used to open a database.
For databases set to archive mode, be sure to put the archive
logs in a directory separate from any other files. This is
imperative because all files located in the archive directory are
marked for archival and deletion.
Regardless of the backup system used, a good DBA will always
understand the principles of database backup and recovery. Due
to the complexities of managing multiple database instances,
however, an integrated backup/recovery solution is often needed.
Oracle Enterprise Backup and HP OMNIBAK II provide a valuable
solution to a difficult and time consuming problem.
Chris Lawson is a Senior DBA Consultant with MAXIM Group of San
Jose, where he specializes in database performance tuning. He is
a certified Oracle7 DBA, and member of the Northern California
and International Oracle User Groups. He may be reached at
email@example.com His fax number is 510-829-7496.