Backing up the Oracle Enterprise - Technical Sidebar

Understanding Oracle Backup Techniques.

By Derek Ashmore

---

DBMS, May, 1998

Oracle backups can be classified as logical or physical. Logical backups are used to take a �picture� of the data and structures within a database at a specific point in time. Logical backups export all data and structure definitions from the database via the export utility. They can contain structure definitions (tables, indexes, sequences, procedures, and so on) as well as table data. They can cover the full database or only part of it (for example, specific tables or objects belonging to specific schemas). Output of this utility is a proprietary format often called an �export� or �dump� file.

All other types of backups detailed in this article are physical backups. Physical backups copy underlying datafiles at an operating system level. A list of database-related files and volumes can be found in Table 1. A summary of different ways to backup Oracle databases can be found in Table 2.

In Oracle8, a �DIRECT� parameter has been added that allows the export utility to read data blocks directly and not use the SQL parsing mechanisms within Oracle. The same concept has existed in the loader utility (�direct path load�) for years. However, Oracle did not add this same mechanism to Oracle8�s import utility. Furthermore, direct path exports are more restrictive as they don�t currently support many of the new data-types present in Oracle8 (that is, LOBs, VARRAYS, and so forth).

The export and import utilities included in Oracle8 can recover tablespaces to a specific point in time with the POINT_IN_TIME_RECOVERY and RECOVERY_TABLESPACES parameters. This is significant; physical recovery methods require that all datafiles be recovered to the same point in time. In cases where a database contains data for many applications, it is difficult to use Oracle-provided utilities to recover from an application processing error. For example, with the new point-in-time features of the export and import utilities it will be easier to recover from application-specific programming errors, such as programs which inadvertently cause incorrect data to be in a database. A bank, for example, could have a programming bug that miscalculates interest. The new features in Oracle8�s import utility could be used to restore a portion of the database to its original state. It should be noted that physical backups will still be required for full recovery in the event of a system crash.

Logical backups are restored by recreating all or part of a database from the ground up via the �import� utility. Logical backups are frequently used in development environments or while moving data from one database to another. Logical backups are not considered production grade because they do not provide full recovery in the event of a system crash. Recovery from a logical backup after a system crash would lack all transactions occurring between the time of export and the crash itself and the database would only be recovered to the point in time when the backup was taken.. They are, however, often used for development and test environments.

Physical backups are copies of the underlying physical volumes and files that make up a database. I differentiate physical backups of Oracle databases in the following ways: user-directed cold, user-directed hot, package-directed cold, package-directed hot, backup sets, and image copies. �Cold� backups are backups that occur while the instance is down. �Hot� backups occur while the instance is up and the database is available. �User-directed� backups are managed by scripts or programs that have been developed in house. �Package-directed� backups are managed by a purchased storage-management package, such as IBM�s ADSM or Legato�s Networker. Backup sets and image copies are new backup methods provided with Oracle8.

Cold backups are a physical copy of the database as it existed at a specific point in time. Cold backups require that the database be shut down during the backup and that all physical files or volumes associated with a database be included in the backup. If a cold backup is user-directed, underlying database-related files and volumes are copied via operating system utilities (for example, cp, dd, cpio, tar, backup, copy, etc.).

If a cold backup is package-directed, a storage management package works in conjunction with Oracle�s Enterprise Backup Utility server instance is down during the backup, all files or volumes are in a consistent state. It is common to have periodic cold backups in addition to hot backups, because it is less complicated to restore a cold backup than a hot backup.

The Enterprise Backup Utility (EBU) is a companion product provided by Oracle that can work with a storage-management product to back up Oracle databases. For Oracle8, EBU is functionally replaced by Recovery Manager (rman). It should be noted that Enterprise Manager has been enhanced from earlier releases to provide a GUI interface to Recovery Manager which is called �Backup Manager.� Alternatively, there is a command-line interface to these tools (rman) which provides these capabilities. I suspect that rman will be used mainly for batch or scripted backups. Most people will find Enterprise Manager easier to use for performing interactive backups.

Hot backups allow the database to be fully functional during the backup but require more resources and make recovery more complicated. If a hot backup is user-directed, underlying database files and volumes are copied at an operating system level in much the same way as with cold backups. If a hot backup is package directed, EBU or rman will physically perform the copy.

Performing a hot backup requires enabled archiving. As logging during a hot backup is exponentially inflated, it is common to schedule hot backups at times when updates, inserts, and deletes are at a minimum. When backing up a tablespace that is available for use, you must inform Oracle when hot backups that underlie datafiles to tablespaces are being backed up via ALTER TABLESPACE statements:

ALTER TABLESPACE data01 BEGIN BACKUP;
ALTER TABLESPACE data01 END BACKUP;

During hot backup, Oracle logs a complete copy of the block before an update, insert, or delete is applied. Recover Manager or EBU will issue these commands when performing hot backups.

Under Oracle8, Recovery Manager supports a new type of backup called a �backup set.� A backup set is stored in a proprietary format (such as an export file). A backup can contain information found in datafiles, control files, or log files. A backup set only contains used blocks and can be full or incremental. An incremental backup contains only the blocks that have been changed since a previous incremental or full backup set was taken. Oracle8�s ability to take incremental backups will, in most circumstances, reduce backup time.

If an incremental backup is restored, archive logs will still be necessary to apply transactions that occurred after the backup. Please note that archive files can be used to roll a database forward in time, but they cannot be used to roll a database backward in time. Also note that archives cannot be used to roll forward databases restored via the import utility.

Oracle8, along with backup sets, introduces the concept of image copies. Image copies are identical to hot backups taken at an operating system level, except for the following:

• Image copies are administered via Recovery Manager.
• All blocks are inspected for corruption.
• Image copy specifics (time taken, etc.) are recorded in the control file.

Information about image copies taken can be queried from several system views. A synopsis of system views under Oracle8 that contain information about backup sets and logs can be found in Table 3.

Archive files consist of data that was once present in a redo log group. When the database is in archive mode, redo log groups are not reused before the data within them has been archived. In order to achieve point-in-time recovery, all archive files must be part of backup.

While archiving does add load to a system, the additional load can be tuned so that it is relatively efficient and does not noticeably impact performance of applications.

Restoration cannot occur while the instance is up. If you want to recover to the point in time of the system crash, you must recover only those files and volumes that were damaged during the crash. You must also recover any archives needed to roll forward the restored volumes to the current state. If you want to recover the whole database to a point in the past (for example, application error), you must recover all data files. If file and volume changes were made to the database between the point in the past and the current time, you must also recover the control files. To roll the database forward to the point you want to recover, it is necessary to have all the archives.

It should be noted that all backup capabilities provided under Oracle7.x are still supported under Oracle8. If you already have written, purchased, or established backup and recovery procedures under Oracle7.x, they should work in Oracle8.

File/Volume Type	Description
Data Files	Files/volumes that are associated with a tablespace .Typically used to physically store tables, indexes, clusters, rollback segments, and temp space.
Control Files	Contain information about all underlying files/volumes associated with datafiles or redo log files.
Redo Log Files	Files/volumes that contain log information about anything that changes in a data file.
Archive Files	Contain information once written to redo log files.

Backup Type	Utility	Oracle Version	Description
Logical	Export/Import	v7.x/v8.x	Logical picture of database structures and table data.
User-directed Cold	O/S utilities	v7.x/v8.x	Copy of all database related files/volumes in Table 1 at an operating system level while database is not operational.
User-directed Hot	O/S utilities	v7.x/v8.x	Copy of all datafiles, control file, and archive files at an operating system level while database is operational.
User-directed Hot	O/S utilities	v7.x/v8.x	Copy of all datafiles, control file, and archive files at an operating system level while database is operational..
Package-directed Hot	EBU for v7.x, rman for v8.x	v7.x/v8.x	Copy of all database related files/volumes in table 1 via Oracle-provided utility while database is not operational. Backup initiated by storage management package.
Package-directed Cold	EBU for V7.x, rman for V8.x	v7.x/v8.x	Copy of all datafiles, control file, and archive via Oracle-provided utility while database is operational. Backup initiated by storage management package.
Backup Set	rman	v8.x	an be full or incremental. Only used blocks represented.
Image Copy	rman	v8.x	Copy of a single datafile, archive files, or control file to disk with an inspection for corrupted blocks.