1. What does restore and recovery mean?
"Restore" means restoring a database backup (data files, control files and/or redologs) from a backup medium.
"Recovery" means recovering redolog information. This is necessary if the restored backup itself is inconsistent (for example, an online backup) or if you do not want to lose the work carried out on the system after the backup.
2. What is the difference between media recovery and instance recovery?
The recovery explicitly triggered after a restore that is dealt with in this note is called "media recovery". It is required to reproduce database changes up to a particular time following a restore (to avoid loss of data).
In contrast, an "instance recovery" or a "crash recovery" is automatically carried out by the SMON process when you start up after an Oracle crash to change the database to a consistent status. In this case, the redolog information accrued since the last checkpoint was written is regenerated and then non-committed transactions are rolled back again.
3. What are the different types of restore and (media) recoveries?
Full restore: Restoring all data files from the backup medium
Partial restore: Restoring part of the database from the backup medium
Complete recovery: Regenerating the redologs up to the current time (no loss of data)
Incomplete recovery/point-in-time recovery: Regenerating the redologs up to a time in the past (data changed after this time is lost) - this recovery is only possible after a full restore.
Database reset: Full restore without a subsequent recovery
Tablespace point-in-time recovery: Incomplete recovery of a tablespace that is also possible without a preceding full restore (this only helps in particular constellations, such as MCOD)
Disaster recovery: Reconstruction of the entire system (including operating system, R/3, Oracle software, Oracle database, and so on)
4. In what situations do I need to carry out a restore and recovery?
The following situations could require a restore and recovery:
User errors
If data is lost when a user error occurs (for example, "DROP TABLE"), a full restore of a backup that was created BEFORE the user error must be imported. An incomplete recovery can then be carried out up to the time before the user error occurred. Thus, all data changes made after the user error are lost.
Note that you cannot restore and incompletely recover the affected data file(s) only. Oracle would then refuse to open the database because, on the basis of the time stamp, the incompletely recovered data files would no longer match the rest of the database.
Physical loss of database files
If individual data files are lost, you can usually restore these (partial restore) and recover them completely. With lost control files or redologs, the procedure differs on an individual basis. Note 491160 contains exact details of all constellations.
Block corruptions
Generally, block corruptions in data files can be eliminated by restoring a "good" backup of the affected data files and carry out a complete recovery. Also refer to Note 365481 for more information about block corruptions.
Unsuccessful administrative activity (reorganization, upgrade, and so on)
If you have to reset a started administrative activity, such as a reorganization or an upgrade due to a problem or time restrictions, this can only occur using a full restore of a backup created before the administrative activity.
5. Can I skip corrupt or missing redologs during the recovery?
Oracle does not allow you to ignore corrupt redologs during the recovery because this would no longer guarantee application consistency. If a gap exists in the redolog chain, you can only recover the last redolog that existed up to the time before this gap (incomplete recovery).
6. How can I avoid an incomplete recovery?
Note 434645 contains options on how to avoid an incomplete recovery in some circumstances if data is lost.
As of Oracle 9i, you have the option if using flashback queries to access data from the past without restore or recovery. This option is described in Note 937492.
7. What should I consider before starting a restore/recovery?
Make sure that a restore/recovery is the most suitable method of solving the current problem. Clarify exactly which type of restore/recovery must be carried out. Also make sure that a consistent backup is available before you use a restore to overwrite the current database that is still available. If possible, create a backup of the current database before you start a restore. If you can no longer use BRBACKUP for a stopped database, you can also create this backup using other operating system tools. Save at least one control file and the online redologs (for a stopped database) before you start a full restore.
Before you start the recovery, you must ensure that no data files are offline. Otherwise, these will not be recovered (exception: BRRECOVER sets the data files to ONLINE itself). Among other things, Note 328785 describes how to find offline data files and how to set them to online.
8. Which tools exist for restore and recovery?
The BRRECOVER SAP tool provides a standard user interface for restore and recovery (see Note 602497) which calls BRRESTORE and the Oracle recovery function.
You can also access BRRECOVER using the BRTOOLS and BRGUI SAP tools in a graphical or text-oriented user interface (Note 611493).
You can use the SQLPLUS Oracle tool for a recovery. Note 4161 describes the procedure for a complete recovery. Note 4164 describes the incomplete recovery.
The BRRESTORE BR tool is called implicitly by BRRECOVER and does not need to be called directly.
9. What are the main functions provided by BRRECOVER?
Partial restore and complete recovery
Full restore and incomplete recovery
Tablespace point-in-time recovery
Database reset (full restore, no recovery)
Restore of individual files
Restore and import of offline redologs
Disaster recovery
10. How can I set up a system again after a total loss of data?
If a disaster recovery is required (for example, if the hardware is physically destroyed), you can set up the system again as described in Note 96848.
11. What do I do if a restore/recovery is not possible?
A restore/recovery may not be possible for various reasons:
Insufficient backup strategy
A corrupt redolog prevents a complete recovery, and an incomplete recovery is not acceptable due to the loss of subsequent changes.
After a logical loss of data (DROP TABLE, incorrect transports imported and so on), an incomplete recovery is not acceptable due to the loss of subsequent changes.
In all of these cases, it is not possible to keep the application side of the database consistent. A large amount of data will be lost or inconsistencies may occur in the applications. In a worst-case scenario, the following suggestions should only be used as a guide. However, SAP will not provide any support should you decide to use these tips. If required, you should contact SAP Remote Consulting (see Note 83020) instead.
If only one or a few tables are affected from which a backup exists (but a complete recovery has been ruled out due to the reasons mentioned above), a minimum database can be restored on another system and the tables can be extracted there and imported into the target system. The procedure is described in Note 594601. However, note that, for each table retrieved in this way, you must specifically check which data is lost compared to the current version and how you can eliminate application inconsistencies that may have occurred as a result.
If you cannot restore individual data files because a backup does not exist, contact the SAP Remote Consulting directly. The same applies if only one inconsistent backup exists (for example, an online backup that was created without a BACKUP mode, or an online backup, for which not all the redologs accumulated in the backup period exist).
12. What can I do if the recovery hangs?
Note 639986 describes the causes and solutions if a recovery hangs.
13. Why does Oracle insist on a recovery after an online backup has terminated?
If an online backup terminates or if the database crashes during an online backup, individual tablespaces may remain in backup mode, which must be set for an online backup. In backup mode, the data file headers are frozen, so Oracle is mistakenly led to believe that this is an older version of the data file, even though the data in the data file is actually current. For this reason, Oracle insists on a recovery when you next start the database:
ORA-01113: file <nr> needs media recovery
Instead of this recovery (which may take up a lot of time), place the relevant data files into "end backup" mode in accordance with the instructions in Note 4162.
14. How can I recover data after a structure change?
If the database structure has changed (for example, because new data files were added) between the time of the backup and the time that the recovery ended, this change must therefore be taken into account for the recovery. See Note 4165 for further information.
The BRRECOVER tool can recognize these types of changes itself and react to them accordingly. With this tool, manual procedures are no longer required.
15. How I can check which data is contained in redologs?
You can use the LogMiner tool to read redolog information. For more information, see Note 644527.
16. How I can check whether the database is consistent after completion of a restore/recovery?
The only indication that a database is consistent from an Oracle and application point of view is if it can be opened without errors after a restore/recovery. However, block corruptions are possible. You can determine this by using the consistency checks described in Note 23345. If these checks do not find any errors, you can assume that the database is consistent.
17. How can I ensure the consistency of the system landscape after a restore?
One of the most difficult questions regarding restore and recovery is how to ensure interface consistency in a complex system landscape after a restore / recovery. Solutions to this question are:
Avoid an incomplete recovery (Note 434645).
If an incomplete recovery is necessary, see Note 434647.
See Note 438820 for ALE interfaces.
If you require the option of an absolutely consistent restore of the system landscape, in addition to a simultaneous offline backup of all databases contained in the SAP system group, you can implement a federated backup based on a coordinated database suspend or consistent-split technology. You can find further details under:
http://service.sap.com/atg
-> Backup & Restore
18. Where can I find more information about restore and recovery?
SAP Service Marketplace:
- http://service.sap.com/dbaora
-> Media Library
-> Backup and Recovery
-> Restore and Recovery
SAP online documentation:
- SAP Database Guide: Oracle (BC-DB-ORA-DBA)
-> Backup, restore and recovery
Oracle documentation:
- "Oracle9i Backup and Recovery Concepts" book