Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









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.


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

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


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)


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)

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

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



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational