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 useless.

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 repeated.

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 copied.

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 any backup.

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 protect duration.

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 tablespaceend 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 range.

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 red.

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 directory.

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 system.

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 catalog.

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 databases.

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 otherwise required.

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.

SUMMARY

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.

BIO

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 His fax number is 510-829-7496.