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 of:

1. Normal backups using system backups, Oracle Backup Manager, Recovery Manager or a third party, tested against Oracle tool.

2. Exports and imports.

3. Archive logging of redo logs.

Let?s look at each of these and how they are used.

Backups

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.

Cold Backups

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:

1. Using the shutdown script(s) provided, shutdown the Oracle instance(s) to be backed up.

2. Ensure that there is enough backup media to back up the entire database.

3. Mount the first volume of the backup media (9 track, WORM, 4mm, 8mm, etc.) using the proper operating system mount command:

For example on OpenVMS:

    $ mount/foreign dev: volume_name

    On UNIX:

    $ umount ?orw /dev/rmt0 /tape1

4. For example on OpenVMS:

    $ backup/log=ora_<date>.log -
       $ ora_diskx:[oracle...]*.*,-
    $ ora_diskx+1:[oracle...]*.* -
       $ ora_diskx+2:[oracle...]*.*
    .
       .
.
    $ ora_diskx+n:oracle...] dev:ora_<date>.sav/save
   
Where:

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.

    <date> represents the date for the backup.

    dev: 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.

    On UNIX:

    $ tar ?cvf /tape1 /ud*/oracle*/ortest1/*  (for all Oracle data, log and trace files assuming

    an OFA installation)

    Where:

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)

5. 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:

    $ DISMOUNT dev:

    On UNIX:

    $ umount /tape1

6. Restart the Oracle instances using the appropriate startup script(s).

Hot Backups

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:

1. The tablespace data files are backed up.

2. The archived redo logs are backed up.

3. 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 reference product).

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