An Oracle backup is a copy of data that can be used to restore the original data if it is lost. The backup may include the control file, Archive logs and datafiles. Without a proper backup strategy, recovery of the database due to failure may not be possible. You should tailor your backup strategy to the business requirements of the data. For example, if you are charged with developing a backup strategy for a development database that is refreshed often from a consistent set of test data, you may not need to perform frequent backups. On the other hand if your database is a 24 x 7 catalog sales database you would want to perform frequent backups to minimize down time.

This topic addresses:

Types of Failures

Every database is subject to failure. The following are the different types of failures that may occur:

Statement failure
Statement failure happens when a SQL statement fails. This can be caused by incorrect syntax or incompatible middleware between the client and server database. Oracle automatically handles statement failures and returns control back to the user with an error message. There is no need for recovery when a statement fails.
Process failure
Process failure occurs when a user process connected to Oracle abnormally ends. This can happen because of a network failure, client side power failure or simply the user killing the client process on their PC, causing an abnormal end. Process failures are recovered automatically by Oracle's PMON Process. PMON wakes up periodically and cleans up the cache and frees resources that a failed user process was using.
Instance failure
Instance failure occurs when the Oracle instance fails. This can happen when there is a power outage, operating system crash or one or more of the Oracle processes fail, causing the instance to stop. When an instance fails, buffers in the system global area (SGA) are not written to disk. Oracle automatically recovers from instance failure when the instance is restarted. The SMON process is responsible for instance recovery at startup. SMON uses the online redo logfiles for instance recovery. In a Parallel Server environment, the SMON of one instance can also recover other instances that have failed.
User or application error
User or application errors occur when a user makes a mistake. This can happen if a user deletes data from a table in error or a user drops a table that is still needed. To recover from this type of failure, Oracle provides an exact point-in-time recovery. Point-in-time recovery can recover the database to the point in time just before the error occurred.
Media or disk failure
Media failure occurs when files needed by the database can no longer be accessed. This normally happens when a disk drive fails. Oracle provides several ways to recover from media failure, depending on the situation and business needs of the data. Which backup strategy is chosen determines the media recovery options.

What to Backup and When

This section addresses when to perform database backups and what parts of a database need to be backed up. Before you create any database, you should design a backup and recovery plan based on the business need for the data. If this step is not completed, there is no guarantee the data can be recovered as required.

The following are the physical database structures of an Oracle database that are included in a backup:

The Control Files
The control files contain information on all physical database files (the database physical structure) and their current transaction states. Control files are read to mount and open the database, and transaction numbers are recorded for each datafile. If the control files and datafiles are out of sync, then the database will not startup and will report either that recovery is needed or that the datafiles are out of sync with the control files. Control files are required for both database startup and recovery. The database is required to have one control file to startup. Due to the importance of the control file, it is recommended to let Oracle write mirror copies of the control file to several locations on different disk drives. In addition to the normal backup schedule, you should back up the control file any time there are structural changes to the database!
The Online Redo Log Files
Redo logfiles are set up at instance creation. A minimum of two one-member groups in a single thread is required for instance startup. The redo logs hold records used for recovery purposes. The redo logs contain information on all data-modifying transactions that occur in the database, unless these transactions have been run as non-recoverable. The LGWR process writes data on changes from the redo log buffers to the redo logs. A COMMIT is not considered complete until the LGWR signals that all redo log entries have been written to disk. Due to the importance of the redo logfiles, it is recommended to let Oracle write mirror copies of the redo logfiles to several locations on different disk drives.

There is no need to backup the online redo log files as part of a normal backup strategy. If the database crashes and all copies of the online redo logfiles are lost, recovery depends on whether your database is in ARCHIVELOG mode or not.

  • In ARCHIVELOG mode, you should recover the database to the last archive log. Once recovery is complete, open the database with the RESETLOGS option.
  • In NOARCHIVELOG mode, the last consistent whole backup is restored and the database is opened with the RESETLOGS options. No recovery is done because the database is at a consistent state. All changes since the last backup are lost.

When performing disk maintenance or moving a database from one computer to another, you can backup the redo logfiles and use them so the database does not have to be opened with the RESETLOGS option. This allows the backup taken before the move to be used for recovery and saves the time of doing another whole backup of the database.

The Database Datafiles
The Oracle system uses logical and physical storage. Logical storage uses the concept of tablespaces. A tablespace is physically implemented through one or more datafiles. Datafiles are subdivided into segments, which are subdivided into extents, which may be of several type depending on their use:
  • Table segment
  • Index segment
  • Rollback segment
  • Temporary segment

A single segment extent cannot span multiple datafiles and must be contiguous. Datafiles should be included in every backup strategy.

The Rollback Segments
Rollback segments contain records of changes for multiple transactions. Each transaction gets assigned to a single rollback segment extent. Rollback segments are used to provide read consistency and rollback transactions, and put the database in a transaction-consistent state as part of recovery. During recovery, the rollback segments are used to undo any uncommitted changes applied from the redo logs to the datafiles. Rollback segments are included in the backup of datafiles.
The Archive Logfiles
Archive logfiles are redo logfiles that have been filled with redo information and copied to a backup location as an archive file. Archive logs are only created when the database is in ARCHIVELOG mode. In NOARCHIVELOG mode, redo files are just overwritten without being archived.

Archive logfiles, if kept on disk, should be backed up up to tape before deleting them to make room for more archive logfiles. You should keep archive logfiles until they are older than the latest database backup you may want to use for recovery. For example, if you backup your tablespaces once a week and keep four weeks' worth of tablespace backups, archive logfiles older than four weeks are no longer needed.

Frequency of Backups

Whole database and tablespace backups are part of all backup strategies. The frequency of performing backups should be based on the amount of changes made to the data and the business needs of the data. If the data changes a lot then the frequency of backups should also be high. On the other hand, if downtime is not an issue and loss of some data is acceptable, you can perform backups less frequently. If the database is read-only and refreshed on a monthly basis, you need to perform backups only once a month after the scheduled refresh.

When structural changes are made to your database a backup should be done both before and after the change. Structural changes include:

  • Add a datafile to an existing tablespace.
  • Rename an existing datafile or move it to a new location.
  • Add or drop an online redo log group or member.
  • Rename an existing redo log group or move it to a new location.
  • Create or Drop a tablespace.

If the database is in ARCHIVELOG mode, you need to back up only the control file with an ALTER DATABASE BACKUP CONTROLFILE command. If the database is in NOARCHIVELOG mode, you need to perform a consistent whole database backup before and after the structural changes.

If your database is operated in ARCHIVELOG mode, you can back up tablespaces individually by backing up the datafiles associated with them. This can be very useful if parts of your database change often and other parts do not. This allows you to have a more frequent backup schedule for the more active parts of your database and a less frequent backup schedule for the less active parts. This type of strategy can reduce both recovery and back up time.

When database operations are performed using the UNRECOVERABLE option, backups should be taken after the objects are created. When the UNRECOVERABLE option is used to create tables or indexes, no redo information is recorded, and the object cannot be recovered until the tablespaces they are in are backed up. The same holds true for direct path loads with SQL*Loader.

Read-only tablespaces should be backed up immediately after making the tablespace read-only. As long as the tablespace is read-only, it does not need to be backed up again. When backing up a read-only tablespace, it is not necessary to use the BEGIN and END BACKUP commands; in fact these commands will cause an error if used on a read-only tablespace. Once the tablespace is made read-write again, you should resume backups of the tablespace.


How long database backups are kept depends on the needs of the data. If there is a possibility that the database will need to be recovered to a previous point in time, a backup from before that point in time will be used in the recovery. In NOARCHIVELOG mode, this means keeping consistent whole database backups. In ARCHIVELOG mode, you will need a whole database backup, with the control file from the point in time the recovery is needed, and all archive logs required.

Archive Log Modes

When the database is operated in NOARCHIVELOG mode, redo logfiles are not copied and saved as archive log files. This means the only recovery option for media failure is to restore the last consistent whole database backup. Consistent whole database backups are taken while the database is shut down. In this mode, you should perform the backups on a schedule according to the amount of data it is acceptable to lose. If losing one day's data is acceptable, shutdown the database nightly and perform a full backup. If losing five day's data is acceptable, schedule the database to be shutdown once every 5 days and back it up.

Important Note: Whenever the physical structure of the database is changed in NOARCHIVELOG mode the database should be shutdown and backed up immediately before and after the change is made.

When the database is operated in ARCHIVELOG mode, redo logfiles are copied and saved as archive log files. This means that the archive log, redo logs, and datafile backups can be used for complete recovery to the point of failure or any previous point in time.

Once a database is created, put it in ARCHIVELOG mode, then shut it down and perform a complete backup. The database can then be restarted and run continuously without doing a consistent whole backup again. From this point, you can perform open or closed tablespace backups to keep the backups current and reduce recovery time. Note too, all tablespaces do not have to be backed up on the same frequency. The backup schedule should be determined by the rate at which the data changes and the desired recovery time.

When structural changes are made in ARCHIVELOG mode, the control file should be backed up using the ALTER DATABASE BACKUP CONTROLFILE command. Do not use the operating system to backup the control file unless the database is shutdown.

Standby Database
The standby database feature can be used when recovery time must be kept at an absolute minimum. This feature uses a primary database and a standby database. The primary database operates in ARCHIVELOG mode and the standby database operates on duplicate hardware in a constant state of recovery, applying the archive log files from the primary database. When the primary database goes down, the standby database applies the last archive from the primary database. The DBA can then cancel the recovery and open the standby database as the new primary database. Normally, the standby database would reside on a different computer and possibly even at a different location, although this is not a requirement. When a standby database is used to recover the primary database, any transactions that are not archived are lost.

Multi-Level Backup Schemes

The Oracle Export utility can be used to add flexibility to your backup strategy. It is not a substitute for operating system backups because it is a logical backup and cannot give the same level of recovery as other Oracle backup scenarios. The Export and Import utility can selectively backup and restore specific database objects. This type of backup may be used for a set of tables that never change. If user error causes the data in one of these tables to be lost, the whole tablespace does not need to be taken offline for recovery. The import utility can simply re-import the data for the table, and no other recovery is needed.