What are the Common Methods for Database Recovery?

There are common methods of database recovery. Here we list them:

1. Recovering a Lost/Deleted Database from a Backed-up Version of the Same Database

2. Recovering a Lost/Deleted Database from Point-in-Time Recovery (PITR)

3. Recovering a Lost/Deleted Database from an Archived Redo Logs Based Backup

4. Restoring and Recovering Data files, control files and SPFILE using RMAN 

Let us see one by one:

1. Recovering a Lost/Deleted Database from a Backed-up Version of the Same Database

If you have backed up your database before it got lost, you will use this method to recover. To do that, firstly restore the most recent backup of your database. Then apply all subsequent archived redo log files one after another until the point where you lost the data. All transactions committed after that time are guaranteed to be reapplied and hence recreating all objects in the table will give us our lost data back again. Remember that as soon as you perform a backup of any kind, your whole chain of archived redo logs becomes useless as they only exist on disk to facilitate incremental backups or point-in-time recovery. That is also the motivation behind the advice that all your redo logs should be backed up on a daily basis.

2. Recovering a Lost/Deleted Database from Point-in-Time Recovery (PITR)

If you have been unlucky enough not to have a backup of your database, but have archived redo logs available for recovery, this will be your best hope as they can help you recover data from before they were applied or backed up. In order to do so, all you need to do is remember the timestamp when you lost your data and then restore and apply one archived log file at a time until reaching the desired point in time. This method does require sufficient knowledge of what transactions through during that period so it is very helpful if your database is properly instrumented with Log Miner and Time Travel or equivalent technology.

3. Recovering a Lost/Deleted Database from an Archived Redo Logs Based Backup

If you have an archived redo log-based backup, you can use it to recover lost data from before the time of this backup. The only thing to be careful about is that you need to make sure all required archived redo logs are available in order to get a consistent recovery done. If there are any gaps in the sequence of files needed for recovery, then recovery fails. This method does not require any workaround because all conditions are satisfied by design: your backup will always be built on top of a set of archived log files because that is the only way to get a consistent backup of a database. In other words, if you have an archived redo log file that has been backed up then it can be used to undo any changes from before the timestamp in its header and your recovery will be consistent.

4. Restoring and Recovering Data files, control files and SPFILE using RMAN 

If you have lost your data file(s) or control file or file, there are a number of different techniques for restoring them with RMAN:

a. Point in time recovery of a data file – recovering a single damaged/lost/overwritten data file without bringing down the whole database by restoring from an old archived log-based backup doesn’t require anything special. If you have a consistent database backup then just restore the data file to its location, recover it with RMAN and open it with RESETLOGS.

b. Restoring multiple or all damaged/lost/overwritten data files at once – if you are restoring more than one data file, then to ensure that the whole database is consistent during the restore make sure all related archived logs are available for RMAN. One way to do this is by having an online redo log group created continuously since your last full backup of the database. Then while restoring each data file use flashback database until time <now>; Once all files are restored, flash them forward again using flash archive log all until end of logs; Note that in order for this method to work, any changes to the data files that were made after your last backup must have been written into a different set of files.

c. Restoring control files – if you have an offline copy of a control file then follow these steps: Recover it with RMAN and open it with RESETLOGS; Stop all archiving for the database; Remove all redo log groups from FRA except one required for point in time recovery (see below). Startup the database normally starting with the removal of this redo log group at startup. Once online remove it from FRA as well. If you do not have an offline copy of the control file, use RMAN to recover it using RESTORE CONTROLFILE FROM AUTO BEGIN;

Conclusion:

In order to make the recovery of your Oracle database easier in the event of a loss, it is always a good idea to have an up-to-date backup. If a lost file from your last backup can be recovered from another source, you do not need to restore using RMAN. If none of these methods work for you then either restoring with RMAN or copying the objects back into place at once will work. In case you switch between different types of backups (archived logs and image copies), create two separate backups of all data files and control files so that both archived log and image copy backups remain valid during recovery.