The purpose of a backup and recovery is to protect the database against data loss and reconstruct the database after data loss.

Before starting oracle database backup and recovery process you should know some basic concepts.

  1. Error and failures can occur in Oracle database.
  2. Oracle Backup
    1. What is Oracle Backup and its different types
    2. Oracle backup and recovery methods.
    3. Oracle backups be made online or offline?
    4. Oracle Backup and Recovery Files and Key data structures
  3. Oracle Recovery
    1. How does recovery work
    2. What are the types of recovery.
    3. Which recovery method should be used?
  4. Backup and recovery with RMAN

Error and failures can occur in oracle database.

Several problems can halt the normal operation of an Oracle database or affect database I/O operations such as: media failure, user errors, and application errors.

Statement failure (No Recovery Required)

Logical failure in the handling statement in a program. For example, a user issues a statement that is not a valid SQL construction. When occurs, Oracle automatically undoes any effects of the statement and returns control to the user.

Process failure (No Recovery Required)

Failure in a user process accessing Oracle, i.e., an abnormal disconnection or process termination. If the user process fails while modifying the database, Oracle background processes undo the effects of uncommitted transactions.

Instance failure (No Recovery Required)

Problem that prevents an Oracle instance, i.e., the SGA and background processes, from continuing to function. Instance failure can result from a hardware problem such as a power outage, or a software problem such as an operating system crash. Recover automatically at next startup.

Media failure (Recovery Required)

A physical problem that arises when Oracle tries to write or read a file that is required to operate the database. A common example is a disk head crash that causes the loss of all data on a disk drive.

Application Errors (Recovery Required)

Software malfunction can corrupt data blocks which is also called a media corruption. The database does not recognize the block at all: the checksum is invalid, the block contains all zeros, or the header and footer of the block do not match.

User Errors (Recovery Required)

User errors occur when, either due to an error in application logic or a manual mistake, data in a database is changed or deleted incorrectly. An example of such error is deleting the wrong row from the employees table. To allow recovery from user error Oracle provides Flashback Technology.

What is Oracle Backup and its different types

A database backup is a copy of data that can be used to reconstruct the data. Oracle backup types can be divided into two categories.

Physical backups

Physical backups are copies of the physical files. These files include datafiles, control files, and archived redo logs. Every physical backup is a copy of files that store database information to another location, whether on disk or on offline storage media such as tape.

Logical backups

Logical backups contain logical data such as tables and stored procedures. Logical backups are a useful supplement to physical backups in many circumstances but are not sufficient protection against data loss without physical backups.

Physical backups are the foundation of any sound backup and recovery strategy. The term "backup" as used in the oracle backup and recovery refers to physical backups, and to back up part or all of your database is to take some kind of physcial backup. Read More.......

Oracle Backup and Recovery Methods.

Oracle provides users a choice of several basic methods for making backups. The methods include:

Recovery Manager (know as RMAN Backup)

Recovery manager (RMAN) is used to perform backup and recovery operations. RMAN provide greater ease of management and administration. RMAN is fully integrated with oracle database so RMAN can be access through the command line utility or enterprise manager.. RMAN offers a number of backup techniques not available through user-managed methods.

User Managed backup and recovery

The database is backed up manually by executing commands specific to the user's operating system. In this method backup and recovery performed with a mixture of host operating system commands and SQL*Plus recovery commands. Backup operations can also be automated by writing scripts. The user can make a backup of the whole database at once or back up individual tablespaces, datafiles, control files, or archived logs. O/S commands can also be used to perform these backups if the database is down.

Oracle Backups be made online or offline?

Online backups or offline backups can be made using either Recovery Manager or O/S commands. An online backup, also known as an open backup, is a backup of one or more database files that is made while the database is open.

An offline backup, also known as a closed backup, is a backup of one or more database files that made after the database has been closed cleanly. If the database must be open and available all the time, then online backups are the only option. If there is a time of little or no activity on the database, then the user may decide to take periodic offline backups of the whole database.

Oracle Backup and Recovery Files and Key data structures

The files and other structures that make up an Oracle database store data and safeguard it against possible failures. Followings are the key data structures used in oracle database.

  1. Data files
  2. Control files
  3. Online redo log files
  4. Circular Use of Redo Log Files
  5. Archive redo log files
  6. Automatic Managed Undo

Oracle Recovery

Basic recovery involves two parts: restoring a physical backup and then updating it with the changes made to the database since the last backup. The most important aspect of recovery is making sure all data files are consistent with respect to the same point in time. Oracle has integrity checks that prevent the user from opening the database until all data files are consistent with one another. When preparing a recovery strategy, it is critical to understand the answers to these questions:

  • How does recovery work?
  • What are the types of recovery?
  • Which recovery method should be used?
  • If a disk failed and destroyed some of the database files, such as datafiles, control files, and online redo logs, how would you recover the lost files?
    Media Failure: Restore and Media Recovery techniques available to you
  • If a logic error in an application or a user error caused the loss of important data from one or several tables or tablespaces, how could you recover that data, and what would happen to database updates since the error?
    Point-in-Time Recovery, Flashback Features, Logical Import and Exports techniques available to you
  • If the instance alert log indicates that one or more tables contain corrupt blocks, how can you repair the corruption?
    Block Media Recovery, the RMAN BLOCKRECOVER command can help you in this situation. Also, troubleshoot recovery with the SQL*Plus RECOVER ... TEST command.
  • If the entire data center is destroyed, can you perform disaster recovery?  Assume that all you have is an archive tape containing backups. How would you recover the database? How long would that recovery take?
  • If you were not available to recover your database, could someone else recover it in your absence? Are your recovery procedures sufficiently automated and documented?

With these needs in mind, decide how you can take advantage of features related to backup and recovery.

  • Recovery Manager,
  • Flashback Database,
  • Block media recovery may be better than datafile media recovery if availability is critical.
  • While block media recovery is possible even if you do not base your backup and recovery strategy on RMAN,
  • RMAN-based block media recovery can be performed more quickly and with less effort.

Once you decide which features to use in your recovery strategy, you can plan your backup strategy,

  • How and where will you store your recovery-related files?
  • Will you use a flash recovery area?
  • Will you use an ASM disk group to provide redundancy?
  • Will you store backups on tape or other offline storage, or only on disk?
  • At what intervals will you take scheduled backups?
  • And what form of physical backups will you take in each situation?
  • What situations require you to take a database backup outside of the regular schedule?
  • How can you validate your backups, to ensure that you can recover your database when necessary?
  • How do you manage records of your backups? Do you use RMAN with a recovery catalog?
  • Do you have detailed recovery plans that cover each type of failure?
  • How do your DBAs can execute these plans in a crisis?
  • Can scripts be written to automate execution of these plans in a crisis?
  • Can you apply Oracle database availability technology.

How does recovery work?

Recovery typically involes two phases.

  1. Reteriving a copy of datafile from backup.
  2. Reapplying changes to the files since the backup from the archived and online redo logs.

In every type of recovery, Oracle sequentially applies redo data to data blocks. Oracle uses information in the control file and datafile headers to ascertain whether recovery is necessary. Recovery has two parts:

Rolling forward.
When Oracle rolls forward, it applies redo records to the corresponding data blocks. Oracle systematically goes through the redo log to determine which changes it needs to apply to which blocks, and then changes the blocks. For example, if a user adds a row to a table, but the server crashes before it can save the change to disk, Oracle can use the redo record for this transaction to update the data block to reflect the new row.

Rolling back
Once Oracle has completed the rolling forward stage, the Oracle database can be opened. The rollback phase begins after the database is open. The rollback information is stored in transaction tables. Oracle searches through the table for uncommitted transactions, undoing any that it finds. For example, if the user never committed the SQL statement that added the row, then Oracle will discover this fact in a transaction table and undo the change.

What are the types of recovery?

There are three basic types of recovery:

  1. instance recovery
  2. Crash recovery
  3. Media recovery : Restore Datafiles, Apply Redo
    1. Complete,
    2. Incomplete
    3. Point-In-Time Recovery

Oracle performs the first two types of recovery automatically at instance startup. Only media recovery requires the user to issue commands.

An instance recovery, which is only possible in an Oracle Real Applications Cluster configuration, occurs in an open database when one instance discovers that another instance has crashed. A surviving instance automatically uses the redo log to recover the committed data in the database buffers that was lost when the instance failed. Oracle also undoes any transactions that were in progress on the failed instance when it crashed, then clears any locks held by the crashed instance after recovery is complete.

A crash recovery occurs when either a single-instance database crashes or all instances of a multi-instance database crash. In crash recovery, an instance must first open the database and then execute recovery operations. In general, the first instance to open the database after a crash or SHUTDOWN ABORT automatically performs crash recovery.

  • Happen when DB is started after a crash ( or shutdown abort). Preserve all committed data when the instance failed. Performed automatically.
  • Crash recovery uses online redo log files and current online data files

A media recovery is executed on the user's command, usually in response to media failure. In media recovery, online or archived redo logs can be used to make a restored backup current or to update it to a specific point in time.

Media recovery can restore the whole database, a tablespace or a datafile and recover them to a specified time. Whenever redo logs are used or a database is recovered to some non-current time, media recovery is being performed. A restored backup can always be used to perform the recovery.

  • Can be used to recover from lost or damage current data-files, SPFILE or control file
  • The first step is to manually restore the datafile by copying it from a backup.
  • When data-file is restored, DB automatically detect that this datafile is out of date and must undergo media recovery.
  • Media recovery is required if a datafile I taken offline without  the offline normal.
  • The DB the data-file belongs to must not be open or must be offline if DB is open.
  • A data-file can not be open until media recovery has been completed.
  • A DB can not be opened if any of the online data-file needs media recovery.

Complete recovery

Recovering a DB to the most recent point-in-time without the loss of any committed transaction. involves using redo data combined with a backup of a database, tablespace, or datafile to update it to the most current point in time. It is called complete because Oracle applies all of the redo changes to the backup

Incomplete recovery

Also known as point-in-time recovery, restore the DB to it states at some previous targe SCN or Time

Point-in time recovery

Is only option if your have to perform recovery and discover that that you are missing an archive log which is required for recovery (OPEN RESETLOGS)

Recovery Options
If the user does not completely recover the database to the most current time, Oracle must be instructed how far to recover. The user can perform:

  • Tablespace point-in-time recovery (TSPITR), which enables users to recover one or more tablespaces to a point-in-time that is different from the rest of the database.
  • Time-based recovery, also called point-in-time recovery (PITR), which recovers the data up to a specified point in time.
  • Cancel-based recovery, which recovers until the CANCEL command is issued.
  • Change-based recovery or log sequence recovery. If O/S commands are used, change-based recovery recovers up to a specified SCN in the redo record.

Flashback from human error

If Recovery Manager is used, log sequence recovery recovers up to a specified log sequence number. When performing an incomplete recovery, the user must reset the online redo logs when opening the database. The new version of the reset database is called a new incarnation. Opening the database with the RESETLOGS option tells Oracle to discard some redo. In Oracle Database 10g and following releases, the control file added new structures that provides the ability to recover through a RESETLOGS operation using backups from a previous incarnation.

Recovering From Human Errors
Flashback Technology provides a set of new features to view and rewind data back and forth in time. The Flashback features offer the capability to query past versions of schema objects, query historical data, perform change analysis or perform self-service repair to recover from logical corruptions while the database is online.

Which recovery method should be used?

Users have a choice between two basic methods for recovering physical files. They can:

  • Use Recovery Manager to automate recovery.
  • Execute SQL commands.

Recovering with Recovery Manager
The basic RMAN commands are RESTORE and RECOVER. RMAN can be used to restore datafiles from backup sets or image copes, either to their current location or to a new location. If any archived redo logs are required to complete the recovery operation, RMAN automatically restores and applies them. In a recovery catalog, RMAN keeps a record containing all the essential information concerning every backup ever taken. If a recovery catalog is not used, RMAN uses the control file for necessary information. The RM AN RECOVER command can be used to perform complete media recovery and apply incremental backups, and to perform incomplete media recovery.

Recovering with SQL*Plus
Administrators can use the SQL*Plus utility at the command line to restore and perform media recovery on your files. Users can recover a database, tablespace, or datafile. Before performing recovery, users need to:

  • Determine which files to recover. Often the table V$RECOVER_FILE can be used.
  • Restore backups of files permanently damaged by media failure. If the user does not have a backup, recovery can still be performed if the user has the necessary redo log files and the control file contains the name of the damaged file.
  • If a file cannot be restored to its original location, then the user must relocate the restored file and inform the control file of the new location.
  • Restore necessary archived redo log files.

Before performing recovery, users need to:

  • Determine which files to recover. Often the table V$RECOVER_FILE can be used.
  • Restore backups of files permanently damaged by media failure. If the user does not have a backup, recovery can still be performed if the user has the necessary redo log files and the control file contains the name of the damaged file.
  • If a file cannot be restored to its original location, then the user must relocate the restored file and inform the control file of the new location.
  • Restore necessary archived redo log files.

Backup and Recovery with RMAN

RMAN gives you access to several data backup and recovery techniques and features not available at all with user-managed backup and recovery. There are a number of significant benefits/Features to using RMAN.

Automation of backup and recovery

RMAN automates oracle backup and recovery by querying information in the recovery catalog, the database’s control file, and any datafiles affected by the operations requested. RMAN decides the most efficient method of executing the requested backup, restore, or recovery operation and then issues these steps to the oracle server.

Incremental backups

An incremental backup stores only blocks changed since a previous backup. Incremental backups provide more compact backups and faster recovery. BACKUP INCREMENTAL command is used to perform incremental backups. In traditional backup methods, all the data-blocks ever used in a data-file must be backed up. RMAN has the ability to recover through unrecoverable operations, when using incremental backups

Block media recovery

During oracle recovery operation, RMAN supports block-level recovery for recovery operations that only need to restore or repair a small number of blocks identified as being corrupt during the backup operation. The rest of the tablespace and the objects within the tablespace can remain online while RMAN repairs the damaged blocks. RECOVER command is used to perform block media recovery.

Skip Unused blocks /compression

RMAN Backup skip data blocks that have never been used and, in some cases, used blocks that are currently unused. Such as blocks above the high water mark (HWM) in a table, are not backed up by RMAN when the backup is an RMAN backupset. Traditional backup methods have no way to know which blocks have been used.

Binary compression

RMAN Backup uses binary compression to reduce the size of backups. There is a slight increase in CPU time during an RMAN compressed backup or recovery operation, the amount of media used for backup may be significantly reduced, as well as network bandwidth if the backup is performed over the network.

Multiple CPUs can be configured for an RMAN backup to help alleviate the compression overhead.

Encrypted backups

RMAN Backup uses backup encryption to store backup sets in an encrypted format. The database must use the Advanced Security Option. To create encrypted backups directly on tape, RMAN must use the Oracle Secure Backup SBT interface, but does not require the Advanced Security Option

Corrupt block detection

The integrity checks that are normally performed when reading a datafile to satisfy a SQL operation are also performed when creating or restoring from a backup. Many types of corruption are detected and noted by the RMAN while the backup is in progress.

Scripting capabilities

RMAN Backup scripts can be saved in a recovery catalog for retrieval during a backup session.

Platform independent scripting language

Backups written with RMAN commands will be syntactically identical regardless of the hardware or software platform used, with the only difference being the media management channel configuration. On the other hand, a Unix script with lots of cp commands will not run very well if the backup script is migrated to a Windows platform!

Cataloging backups

A record of all RMAN backups is recorded in the target database control file, and optionally in a recovery catalog stored in a different database.

Old operating system backups can also be catalog.

Support for oracle parallel server (RAC)

RMAN is able to distribute backups, restores and recoveries over multiple instances in an oracle parallel server configuration. to do this, the DBA specifies multiple connect strings in the RMAN backup or restore commands server processes on the instances specified will then be started to perform the actions specified.

Tape manager support

All major enterprise backup systems are supported within RMAN by a third-party media management driver provided by a tape backup vendor.

Multiple I/O channels

During a backup or recovery operation, RMAN can utilize many I/O channels, via separate operating system processes, to perform concurrent I/O. Traditional backup methods, are typically single-threaded operations.

The Flash recovery area (Automatic Disk-Based Backup and Recovery)

With Automatic Disk-Based Backup and Recovery, you can create a flash recovery area, which automates management of backup-related files. Choose a location on disk and an upper bound for storage space, and set a retention policy that governs how long backup files are needed for recovery, and the database manages the storage used for backups, archived redo logs, and other recovery-related files for your database within that space. Files no longer needed are eligible for deletion when RMAN needs to reclaim space for new files.

If you do not use a flash recovery area, you must manually manage disk space for your backup-related files and balance the use of space among the different types of files. Oracle Corporation recommends that you enable a flash recovery area to simplify your backup management.

  • Full database backups or backups of individual files / logical units such as: datafiles, control files, archivelog files or tablespaces
  • open or closed backups
  • Point-in-time tablespaces recovery
  • Oracle enterprise manager (GUI interface) can also be use for backup and recovery
  • no extra redo is generated during open database backup


Go to top
JSN Boot template designed by