Skip Headers
Oracle® Database Concepts
11g Release 1 (11.1)

Part Number B28318-01
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Go to next page
View PDF

15 Backup and Recovery

Backup and recovery procedures protect your database against data loss and reconstruct the data, should loss occur. This chapter introduces concepts fundamental to designing a backup and recovery strategy.

This chapter contains the following topics:

See Also:

Introduction to Backup and Recovery

A backup is a copy of data. This copy can include important parts of the database such as datafiles, which contain user data, and the server parameter file and control file, which contain configuration information.

The main purpose of a backup is as a safeguard against unexpected data loss and application errors. For example, a disk may fail, causing the loss of datafiles. You can restore a backup of the data and reconstruct the lost data through media recovery. Media recovery refers to the various operations involved in restoring, rolling forward, and rolling back a backup of database files.

You have two ways to perform backup and recovery of an Oracle database: Recovery Manager (RMAN) and user-managed techniques. RMAN is an Oracle Database utility that can back up, restore, and recover database files. It is a feature of Oracle Database and does not require separate installation. You can also use operating system commands for backups and SQL*Plus for media recovery. This technique, also called user-managed backup and recovery, is fully supported by Oracle, although use of RMAN is recommended because it is more robust and simplifies administration.

Oracle Flashback Technology is an alternative to traditional backup and recovery. You can use flashback features to view past states of data, and move data back and forth in time, without restoring data from backups. Instead, you can issue a single command to rewind your entire database, or a single table, to a time in the past. The flashback features of Oracle Database are more efficient and less disruptive than media recovery in most circumstances in which they are applicable.

No matter which backup and recovery tool you use, it is recommended that you configure a flash recovery area to manage your recovery-related files.

Flash Recovery Area

The flash recovery area is an optional Oracle Database-managed directory, file system, or Automatic Storage Management disk group that provides a centralized disk location for backup and recovery files. You can configure the flash recovery area when creating a database with the Database Configuration Assistant or add it later.

Oracle Database can write archived logs to the flash recovery area. RMAN can store backups in the flash recovery are and restore them from the flash recovery area during media recovery. The flash recovery area also acts as a disk cache for tape.

Oracle Database recovery components interact with the flash recovery area to ensure that the database is completely recoverable by using files stored in the recovery area. All files necessary to recover the database following a media failure are part of the flash recovery area.

The following recovery-related files are stored in the flash recovery area:

  • Current control file

  • Online redo logs

  • Archived redo logs

  • Flashback logs

  • Control file autobackups

  • Datafile and control file copies

  • Backup pieces

Oracle Database enables you to define a disk limit, which is the amount of space that the database can use in the flash recovery area. A disk limit enables you to use the remaining disk space for other purposes and not to dedicate a complete disk for the flash recovery area. It does not include any overhead that is not known to Oracle Database. For example, the disk limit does not include the extra size of a file system that is compressed, mirrored, or uses some other redundancy mechanism.

Oracle Database and RMAN create files in the flash recovery area until the space used reaches the recovery area disk limit. When it needs to make room for new files, Oracle Database deletes files from the flash recovery area that are obsolete, redundant, or backed up to tertiary storage. Oracle Database prints a warning when available disk space is less than 15%, but it continues to fill the disk to 100% of the disk limit.

The bigger the flash recovery area, the more useful it becomes. The recommended disk limit is the sum of the database size, the size of incremental backups, and the size of all archive logs that have not been copied to tape.

See Also:

Database Backups

This section describes physical backups. This section includes the following topics:

What Are Database Backups?

Database backups can be either physical or logical. Physical backups, which are the primary concern in a backup and recovery strategy, are copies of physical database files. You can make physical backups with either RMAN or operating system utilities.

In contrast, logical backups contain logical data such as tables and stored procedures. You can extract the logical data with an Oracle Database utility such as Data Pump Export and store it in a binary file. Logical backups can supplement physical backups.

The primary purpose of a database backup is for data protection, but you can also create archival database backups for data preservation. For example, suppose you have a business requirement to preserve customer transaction records for a specified period of time. You can use RMAN to create an archival backup of the database, along with the redo necessary to make it consistent, for offsite storage. You can control how long this database backup is exempt from the RMAN retention policies that govern the deletion of obsolete backups.

Whole Database and Partial Database Backups

A whole database backup is a backup of every datafile in the database, plus the control file. Whole database backups are the most common type of backup.

As shown in Figure 15-1, a whole database backups can be taken in either ARCHIVELOG or NOARCHIVELOG mode and is either a consistent backup or an inconsistent backup. Whether a backup is consistent determines whether you need to apply redo logs after restoring the backup.

Figure 15-1 Whole Database Backup Options

Description of Figure 15-1 follows
Description of "Figure 15-1 Whole Database Backup Options"

A partial backup includes a subset of the database, that is, individual tablespaces or datafiles. A tablespace backup is a backup of the datafiles that make up the tablespace. Tablespace backups, whether online or offline, are valid only if the database is operating in ARCHIVELOG mode. The reason is that redo is required to make the restored tablespace consistent with the other tablespaces in the database.

A datafile backup is a backup of a single datafile. Datafile backups, which are not as common as tablespace backups, are valid in ARCHIVELOG databases.

See Also:

Oracle Database Backup and Recovery Reference and Oracle Database Utilities for information about logical backups

Consistent and Inconsistent Backups

Database backups are either consistent or inconsistent. This section explains the difference between them.

This section includes the following topics:

Overview of Consistent Backups

In a consistent database backup, all read/write datafiles and control files are checkpointed with the same system change number (SCN). The files in the backup are guaranteed to contain all changes up to the same SCN. Unlike an inconsistent backup, a consistent whole database backup does not require recovery after it is restored.

The only way to make a consistent whole database backup is to shut down the database with the NORMAL, IMMEDIATE, or TRANSACTIONAL options and make the backup while the database is closed. If a database is not shut down consistently, for example, an instance fails or you issue a SHUTDOWN ABORT statement, then the datafiles are always inconsistent—unless the database is a read-only database.

The important point is that you can open the database after restoring a consistent whole database backup without needing recovery because the data is already consistent: no action is required to make the data in the restored datafiles correct. Thus, you can restore a year-old consistent backup of your database without performing media recovery and without the database performing instance recovery.


When you restore a consistent whole database backup without applying redo, you lose all transactions that were made after the backup was taken.

A consistent whole database backup is the only valid backup option for databases operating in NOARCHIVELOG mode. Other backup options require recovery for consistency, which is not possible without archived redo logs.

A consistent whole database backup is also a valid backup option for databases operating in ARCHIVELOG mode. When this type of backup is restored and archived logs are available, you have the option of either opening the database immediately and losing transactions that were made after the backup was made, or applying the archived logs to recover those transactions.

Overview of Inconsistent Backups

In an inconsistent database backup, read/write datafiles and control files are not guaranteed to be checkpointed to the same SCN. The files in the backup can contain data taken from different points in time, which means that changes can be missing. This situation can occur when datafiles are modified while backups are being taken.

If you back up the database when it is open or mounted after an inconsistent shutdown, then the backup is inconsistent. A backup of online datafiles is called an online backup. You must run the database in ARCHIVELOG mode for online backups.

As long as the database runs in ARCHIVELOG mode, and you back up the archived redo logs and datafiles, inconsistent backups can be the foundation for a sound backup and recovery strategy. Inconsistent backups offer superior availability because you do not have to shut down the database to make backups that fully protect the database.

Oracle Database recovery makes inconsistent backups consistent by reading all archived and online redo logs, starting with the earliest SCN in any of the datafile headers, and applying the changes from the logs back into the datafiles. After making an inconsistent backup, always ensure that you have the redo necessary to recover the backup by archiving the unarchived redo logs. If you do not have all archived redo logs produced during the backup, then you cannot recover it because you do not have all the redo necessary to make it consistent.

RMAN and User-Managed Backups

The RMAN BACKUP command generates either image copies or backup sets. An image copy is an exact duplicate of a datafile, control file, or archived log. You can create image copies of physical files with operating system utilities or RMAN, and you can restore them as-is without performing additional processing by using either operating system utilities or RMAN.


Unlike operating system copies, RMAN validates the blocks in the file and records the image copy in the repository.

A backup set is a backup in a proprietary format that consists of one or more physical files called backup pieces. A backup set can contain multiple datafiles. The smallest unit of a backup set is a binary file called a backup piece. Backup sets, which are only created and accessed through RMAN, are the only form in which RMAN can write backups to sequential devices such as tape drives.

This section includes the following topics:

Online Backups

Because the database continues writing to the file during an online backup, it is possible to back up inconsistent data within a block. For example, assume that either RMAN or an operating system utility reads the block while database writer is in the middle of updating the block. In this case, RMAN or the copy utility could read the new data in the first half of the block and the old data in the second half of the block. The block is fractured, meaning that the data in this block is not consistent.

During an RMAN backup, the Oracle database reads the datafiles, not an operating system utility. The server reads each block and determines whether the block is fractured. If the block is fractured, then the database re-reads the block until it gets a valid block.

When you back up an online datafile with an operating system utility rather than with RMAN, you must use a different method to handle fractured blocks. You must first place the files in backup mode with the ALTER TABLESPACE BEGIN BACKUP statement (to back up an individual tablespace), or the ALTER DATABASE BEGIN BACKUP statement (to back up the entire database). After an online backup is completed, you must run the ALTER TABLESPACE...END BACKUP or ALTER DATABASE END BACKUP statement to take the files out of backup mode.

When updates are made to files in backup mode, additional redo data is logged. This additional data is needed to repair fractured blocks that might be backed up by the operating system utility.

Control File Backups

Backing up the control file is a crucial aspect of backup and recovery. Without a control file, you cannot mount or open the database. You can instruct RMAN to automatically backup the control file whenever you run backup jobs by executing CONFIGURE CONTROLFILE AUTOBACKUP ON. Because the autobackup uses a default filename, RMAN can restore this backup even if the RMAN repository is unavailable. Hence, this feature is extremely useful in a disaster recovery scenario.

You can make manual backups of the control file by using the following methods:

  • The RMAN BACKUP CURRENT CONTROLFILE command makes a binary backup of the control file, as either a backup set or an image copy.

  • The SQL statement ALTER DATABASE BACKUP CONTROLFILE makes a binary backup of the control file.

  • The SQL statement ALTER DATABASE BACKUP CONTROLFILE TO TRACE exports the control file contents to a SQL script file. You can use the script to create a new control file. Trace file backups have one major disadvantage: they contain no records of archived redo logs, and RMAN backups and copies. For this reason, binary backups are preferable.

Archived Redo Log Backups

You can use archived redo logs to roll a backup forward in time. To recover a backup through the most recent archived redo log, every log generated after the backup was made must be available. In other words, you cannot recover from archived redo log 100 to log 200 if log 173 is missing. If log 173 is missing, then you must halt recovery after applying log 172 and open the database with the RESETLOGS option.

Because archived redo logs are essential to recovery, you should back them up regularly. If you use a media manager, then back up the logs regularly to tape. You can make backups of archived logs by using the following methods:



  • An operating system utility

Problems Requiring Data Repair

The following failures may require DBA intervention, and may even crash a database instance, but will not generally cause data loss or the need to recover from backup.

Typically, data recovery is a response to media failures or user errors.

This section includes the following topics:

Media Failures

A media failure occurs when a problem external to the database prevents Oracle Database from reading from or writing to a file during database operations. Typical media failures include physical failures, such as head crashes, and the overwriting, deletion or corruption of a database file. Media failures are less common than user or application errors, but your backup and recovery strategy should prepare for them.

Database operation after a media failure of online redo log files or control files depends on whether the files are protected by multiplexing. When an online redo log or control file is multiplexed, the database maintains multiple copies of the file.

If a media failure damages a disk containing one copy of a multiplexed online redo log, then the database can usually continue to operate without significant interruption. Damage to a nonmultiplexed online redo log causes database operation to halt and may cause permanent loss of data.

Damage to any control file, whether it is multiplexed or not, halts the database when it attempts to read or write to the damaged control file. The database accesses the control file frequently, for example, at every checkpoint and online redo log switch.

Media failures are either read errors or write errors. In a read error, the instance cannot read a datafile and an operating system error is returned to the application, along with an error indicating that the file cannot be found, cannot be opened, or cannot be read. The database continues to run, but the error is returned each time an unsuccessful read occurs. At the next checkpoint, a write error will occur when the database attempts to write to the datafile header as part of the checkpoint process.

The effect of a datafile write error depends upon which tablespace the datafile is in. If the instance cannot write to a datafile in the SYSTEM tablespace, an undo tablespace, or a datafile with active rollback segments, then the database issues an error and shuts down. All files in the SYSTEM tablespace and all datafiles containing undo or rollback segments must be online in order for the database to operate properly.

If the instance cannot write to a datafile other than those in the preceding list, then the result depends on whether the database is running in ARCHIVELOG mode. In ARCHIVELOG mode, the database records an error in the database writer trace file and takes the affected datafile offline. All other datafiles in the tablespace containing this datafile remain online. You can then rectify the underlying problem and restore and recover the affected tablespace.

In NOARCHIVELOG mode, the database writer background process fails and the instance fails. The cause of the problem determines the required response. If the problem is temporary, then crash recovery usually can be performed using the online redo log files. In such situations, the instance can be restarted without resorting to media recovery. If a datafile is damaged, however, then you must restore a consistent backup of the entire database.

User Errors

A user or application may make unwanted changes to your database, such as erroneous updates, deleting the contents of a table, or dropping database objects. An adequate backup and recovery strategy uses the many features of Oracle Database to let you return your database to the desired state, with the minimum possible impact upon database availability, and minimal DBA effort.

See Also:

Data Repair

Typically, you have more than one way to solve the problems described in "Problems Requiring Data Repair".

Data Repair Advisor is an integrated solution that performs much of the diagnosis and repair work for you. Data Repair Advisor can diagnose failures, suggest both manual and automated repair options, and in some cases automatically repair failures.

To correct problems caused by logical data corruptions or user errors, you can use Oracle Flashback as an alternative to media recovery. Oracle Flashback features enable you to rewind the whole database or a subset of the database to a previous time.

To correct media failures, you can use media recovery. Media recovery is the application of redo or incremental backups to a backup to update it with lost changes. Block media recovery is a more specialized operation that you use when just a few blocks in one or more files have been corrupted.

This section includes the following topics:

Data Recovery Advisor

Oracle Database includes the Data Recovery Advisor tool, which automatically diagnoses persistent data failures, presents appropriate repair options, and executes them at your request. You can use Data Repair Advisor either through the Enterprise Manager interface or through the RMAN client.

A checker is a diagnostic operation or procedure registered with the Health Monitor to assess the health of the database or its components. The health assessment is known as a data integrity check and can be invoked reactively or proactively.

Failures are normally detected reactively. A database operation involving corrupted data results in an error, which automatically invokes a data integrity check that searches the database for failures related to the error. If failures are diagnosed, then they are recorded in the Automatic Diagnostic Repository (ADR). You can also invoke a data integrity check proactively through the Health Monitor or by checking for block corruption with the VALIDATE and BACKUP commands in RMAN.

You can use Data Recovery Advisor to generate repair advice and repair failures only after failures have been detected by the database and stored in ADR. Each failure has a status: open or closed. Each failure also has a priority: critical, high, or low. Failures with critical priority require immediate attention because they make the whole database unavailable. Failures with high priority make a database partly unavailable or unrecoverable, and usually have to be repaired in a reasonably short time. Examples of high-priority failures include data block corruptions and non-fatal I/O errors. Low priority failures can wait until more important failures are fixed.

Data Repair Advisor automatically determines the best repair options and their impact on the database. Typically, Data Repair Advisor generates both manual and automated repair options for each failure or group of failures. The manual options are categorized as either mandatory or optional.

Before presenting an automated repair option, Data Repair Advisor validates it with respect to the specific environment, as well as availability of media components required to complete the proposed repair. If you choose an automatic repair, then Oracle Database executes it for you. The Data Repair Advisor tool verifies the repair success and closes the appropriate failures.

See Also:

Oracle Flashback Technology

Oracle Database provides a group of features known as Oracle Flashback Technology that support viewing past states of data, and winding data back and forth in time, without requiring the restore of the database from backup. Depending on the changes to your database, Flashback features can often reverse the unwanted changes more quickly and with less impact on database availability than media recovery.

See Also:

"Overview of High Availability Features" for an overview of all Oracle Flashback features, including those not directly related to backup and recovery

This section includes the following topics:

Oracle Flashback Database

Oracle Flashback Database enables you to rewind an Oracle database to a previous time to correct problems caused by logical data corruptions or user errors.

If a flash recovery area is configured, and if you have enabled the Flashback database functionality, then you can use the RMAN or SQL FLASHBACK DATABASE command to return the database to a prior time. Flashback Database is not true media recovery because it does not involve restoring physical files. Flashback Database is preferable to using the RESTORE and RECOVER commands in some cases because it is faster and easier and does not require restoring the whole database.

When you use Flashback Database, Oracle Database uses past block images to back out changes to the database. During normal database operation, Oracle Database occasionally logs these block images in flashback logs. Flashback logs are written sequentially and are not archived. Oracle Database automatically creates, deletes, and resizes flashback logs in the flash recovery area. You only need to be aware of flashback logs for monitoring performance and deciding how much disk space to allocate to the flash recovery area for flashback logs.

The time it takes to rewind a database with FLASHBACK DATABASE is proportional to how far back in time you need to go and the amount of database activity after the target time. The time it would take to restore and recover the whole database could be much longer. The before images in the flashback logs are only used to restore the database to a point in the past, and forward recovery is used to bring the database to a consistent state at some time in the past. Oracle Database returns datafiles to the previous point-in-time, but not auxiliary files, such as initialization parameter files.

Flashback database can also be used to compliment Data Guard, Recovery Advisor, and for synchronizing clone databases.

See Also:

Oracle Flashback Table

Oracle Flashback Table enables you to rewind tables to a specified point in time with a single statement. You can restore table data along with associated indexes, triggers, and constraints, while the database is online, undoing changes to only the specified tables. Oracle Flashback Table does not address physical corruption such as bad disks or data segment and index inconsistencies.

Oracle Flashback Table works like a self-service repair tool. Suppose a user accidentally deletes some important rows from a table and wants to recover the deleted rows. You can restore the table to the time before the deletion and see the missing rows in the table with the FLASHBACK TABLE statement.

You can restore the table and its contents to a certain wall clock time or user-specified system change number (SCN). Use Oracle Flashback Table with Oracle Flashback Version Query and Oracle Flashback Transaction Query to find a time to which to restore the table.

For Oracle Flashback Table to succeed, the system must retain enough undo information to satisfy the specified SCN or timestamp, and the integrity constraints specified on the tables cannot be violated. Also, row movement must be enabled on the table.

The availability of retained undo information for Oracle Flashback Table is controlled by the automatically tuned undo retention period of the system. The undo retention period indicates the amount of time that must pass before old undo information—that is, undo information for committed transactions—can be overwritten. The database collects usage statistics and tunes the undo retention period based on these statistics and on undo tablespace size. You can request a minimum undo retention period by setting the UNDO_RETENTION initialization parameter.


Automatic tuning of undo retention occurs only when the database is in automatic undo management mode (the default). The database may or may not be able to honor your request for a minimum undo retention period. This depends on a number of factors, including the current transaction activity on the system, whether the undo tablespace is autoextending or fixed size, and whether you specified RETENTION GUARANTEE for the undo tablespace.

See Oracle Database Administrator's Guide for more information about the automatic tuning of undo retention.

See Also:

Oracle Flashback Drop

Oracle Flashback Drop reverses the effects of a DROP TABLE operation. Flashback Drop is substantially faster than other recovery mechanisms that can be used in this situation, such as point-in-time recovery, and does not lead to any loss of recent transactions or downtime.

When you drop a table, the database does not immediately remove the space associated with the table. Instead, the table is renamed and, along with any associated objects, is placed in the recycle bin of the database. Oracle Database uses the recycle bin to manage dropped database objects until the space they occupied is needed to store new data. The recycle bin is actually a data dictionary table that contains information about the dropped objects.

See Also:

Oracle Database Backup and Recovery User's Guide for details about using Oracle Flashback Drop

Media Recovery

To restore a physical backup of a datafile or control file is to reconstruct it and make it available to the Oracle database. To recover a restored datafile is to update it by applying archived redo logs and online redo logs, that is, records of changes made to the database after the backup was taken. If you use RMAN, then you can also recover datafiles with incremental backups, which are backups of a datafile that contain only blocks that changed after a previous incremental backup.

After the necessary files are restored, media recovery must be initiated by the user. Media recovery involves various operations to restore, roll forward, and roll back a backup of database files.

Media recovery applies archived redo logs and online redo logs to recover the datafiles. Whenever a change is made to a datafile, the change is first recorded in the online redo logs. Media recovery selectively applies the changes recorded in the online and archived redo logs to the restored datafile to roll it forward.

Figure 15-2 illustrates the basic principle of backing up, restoring, and performing media recovery on a database.

Figure 15-2 Media Recovery

Description of Figure 15-2 follows
Description of "Figure 15-2 Media Recovery"

Unlike media recovery, Oracle Database performs crash recovery and instance recovery automatically after an instance failure. Crash and instance recovery recover a database to its transaction-consistent state just before instance failure. Crash recovery is the recovery of a database in a single-instance configuration or an Oracle Real Application Clusters configuration after all instances have crashed. In contrast, instance recovery is the recovery of one or more failed instances by a live instance in an Oracle Real Application Clusters configuration.

This section includes the following topics:

Datafile Media Recovery

Datafile media recovery is used to recover from a lost or damaged current datafile or control file. It is also used to recover changes that were lost when a tablespace went offline without the OFFLINE NORMAL option. Both datafile media recovery and instance recovery must repair database integrity. However, these types of recovery differ with respect to their additional features. Media recovery has the following characteristics:

  • Applies changes to restored backups of damaged datafiles.

  • Can use archived logs as well as online logs.

  • Requires explicit invocation by a user.

  • Does not detect media failure (that is, the need to restore a backup) automatically. After a backup has been restored, however, detection of the need to recover it through media recovery is automatic.

  • Has a recovery time governed solely by user policy (for example, frequency of backups, parallel recovery parameters, number of database transactions since the last backup) rather than by Oracle Database internal mechanisms.

The database cannot be opened if any of the online datafiles needs media recovery, nor can a datafile that needs media recovery be brought online until media recovery is complete. The following scenarios necessitate media recovery:

  • You restore a backup of a datafile.

  • You restore a backup control file (even if all datafiles are current).

  • A datafile is taken offline (either by you or automatically by Oracle Database) without the OFFLINE NORMAL option.

Unless the database is not open by any instance, datafile media recovery can only operate on offline datafiles.

Block Media Recovery

Block media recovery is a technique for restoring and recovering individual data blocks while all database files remain online and available. If only a few blocks are corrupt, then block media recovery may be preferable to datafile recovery.

See Also:

Oracle Database Backup and Recovery User's Guide to learn how to perform block media recovery

Complete Recovery

Complete recovery applies all of the redo changes contained in the archived and online logs to a backup. Typically, you perform complete media recovery after a media failure damages datafiles or the control file.You can perform complete recovery on a database, tablespace, or datafile.

If you are performing complete recovery on the whole database, then you must:

  • Mount the database

  • Ensure that all datafiles you want to recover are online

  • Restore a backup of the whole database

  • Run the RMAN RECOVER DATABASE command, which will apply the correct redo logs and incremental backups.

If you are performing complete recovery on a tablespace or datafile, then you must:

  • Take the tablespace or datafile to be recovered offline if the database is open

  • Restore a backup of the datafiles you want to recover

  • Apply online or archived redo logs, or a combination of the two

Database Point-in-Time Recovery

Database point-in-time recovery, which is also called incomplete recovery, results in a noncurrent version of the database. In other words, you do not apply all of the redo records generated after the restored backup. Typically, you perform point-in-time recovery of the whole database in the following situations:

  • Media failure destroys some or all of the online redo logs.

  • A user error causes data loss, for example, a user inadvertently drops a table.

  • You cannot perform complete recovery because an archived redo log is missing.

  • Complete recovery is possible with a backup control file. If using RMAN it is seamless and automatic.

To perform database point-in-time recovery, you must restore all datafiles from backups created prior to the time to which you want to recover and then open the database with the RESETLOGS option when recovery completes. The RESETLOGS operation creates a new incarnation of the database—in other words, a database with a new stream of log sequence numbers starting with log sequence 1.

Before using the OPEN RESETLOGS command to open the database in read/write mode after an incomplete recovery, it is a good idea to first open the database in read-only mode, and inspect the data to make sure that the database was recovered to the correct point. If the recovery was done to the wrong point, then it is easier to re-run the recovery if no OPEN RESETLOGS has been done. If you open the database read-only and discover that not enough recovery was done, then just run the recovery again to the desired time. If you discover that too much recovery was done, then you must restore the database again and re-run the recovery.


Flashback Database is an alternative to database point-in-time recovery.
Tablespace Point-in-Time Recovery

The tablespace point-in-time recovery (TSPITR) feature lets you recover one or more tablespaces to a point in time older than the rest of the database. TSPITR is most useful when you want to:

  • Recover from an erroneous drop or truncate table operation

  • Recover a table that has become logically corrupted

  • Recover from an incorrect batch job or other DML statement that has affected only a subset of the database

  • Recover one independent schema to a point different from the rest of a physical database (in cases where there are multiple independent schemas in separate tablespaces of one physical database)

  • Recover a tablespace on a very large database (VLDB) rather than restore the whole database from a backup and perform a complete database roll-forward

TSPITR has the following limitations:

  • You cannot use it on the SYSTEM tablespace, an UNDO tablespace, or any tablespace that contains rollback segments.

  • Tablespaces that contain interdependent data must be recovered together. For example, if two tables are in separate tablespaces and have a foreign key relationship, then both tablespaces must be recovered at the same time; you cannot recover just one of them. Oracle Database can enforce this limitation when it detects data relationships that have been explicitly declared with database constraints. There could be other data relationships that are not declared with database constraints. Oracle Database cannot detect these relationships, so the DBA must be careful to always restore a consistent set of tablespaces.

RMAN and User-Managed Recovery

You have a choice between two basic techniques for recovering physical files. You can:

  • Use the RMAN utility to restore and recover the database

  • Restore backups by means of operating system utilities, and then recover them by running the SQL*Plus RECOVER command

Whichever method you choose, you can recover a database, tablespace, or datafile. Before performing media recovery, you need to determine which datafiles to recover. Often you can use the fixed view V$RECOVER_FILE. This view lists all files that require recovery and explains the error that necessitates recovery.

See Also:

Oracle Database Backup and Recovery Reference for more about using V$ views in a recovery scenario
RMAN Restore and Recovery

The basic RMAN recovery commands are RESTORE and RECOVER. Use RESTORE to restore datafiles from backup sets or from image copies on disk, either to their current location or to a new location. You can also restore backup sets containing archived redo logs, but this is usually unnecessary, because RMAN automatically restores the archived logs that are needed for recovery and deletes them after the recovery is finished. Use the RMAN RECOVER command to perform media recovery and apply archived logs or incremental backups.

See Also:

Oracle Database Backup and Recovery Reference for details about how to restore and recover using RMAN
User-Managed Restore and Recovery

If you do not use RMAN, then you can restore backups with operating system utilities and then run the SQL*Plus RECOVER command to recover the database.

See Also:

Oracle Database Backup and Recovery User's Guide for details about how to restore and recover with operating system utilities and SQL*Plus