Recovery Scenarios


Before starting discussing about recovery let’s have a quick look on REDO and UNDO.

REDO: Redo information is recorded so that all commands that took place can be repeated during recovery. How much back you can go depend on you undo_retention.

UNDO: Undo information is recorded so that you can undo changes made by the transaction but were not committed.

Redo Logs are used to Roll Forward the changes made both committed and non- committed. Then from the Undo segments undo information is used to rollback the uncommitted changes.

Your recovery options should depend on the type of failure and the kind of backup you have.

NO ARCHIVE: If your database is in not running in archive log mode. You can only recover the cold backup of the database. You will lose any new data and changes made since that backup was taken.

ARCHIVE: If your database is in archive log mode you will be able to restore the database up to the failure time.

There are three basic types of recovery:

1. Online Block Recovery.

This is performed automatically by PMON back ground process. It happened when a process dies while changing a buffer (there are multiple reason those can cause a process die). Oracle will reconstruct the buffer using the online redo logs and writes it to disk using rollback and roll forward method.

2. Thread Recovery.

This one also performed automatically by Oracle. It happened when an instance crashes while having the database open. Oracle applies all the redo changes in the thread that occurred since the last time the thread was check pointed.

3. Media Recovery.

This is required when a data file is restored from backup. The checkpoint count in the data files here are not equal to the check point count in the control file.
Media Failure and Recovery in Noarchivelog Mode

In this situation only option is to restore a backup of your Oracle files.

  1. all data files.
  2. all control files.
  3. password file or parameter:  Only if they are lost or corrupted.

Media Failure and Recovery in Archivelog Mode

In this case, there are several kinds of recovery you can perform, depending on what has been lost. The three basic kinds of recovery are:

A. Recover database – here you use the recover database command and the database must be closed and mounted. Oracle will recover all datafiles that are online.

B. Recover tablespace – use the recover tablespace command. The database can be open but the tablespace must be offline.

C. Recover datafile – use the recover datafile command. The database can be open but the specified datafile must be offline.

Note: You must have all archived logs since the backup you restored from, otherwise complete recovery is not possible.

1. Point in Time recovery:

Scenario: you dropped a table at 13:13:13. In order to recover it. You will have to restore the appropriate datafiles and do a point-in-time recovery until 13:13:13.

Below 4 incomplete recovery scenarios all work the same:

Recover database until time ‘2012-12-01:13:13:13’;
Recover database until cancel;
Recover database until change n; NOTE: You need to provide SCN
Recover database until cancel using backup controlfile;

Note: When performing an incomplete recovery, the datafiles must be online. Do a

select * from v$recover_file

to find out if there are any files which are offline. If you were to perform a recovery on a database which has tablespaces offline, and they had not been taken offline in a normal state, you will lose them when you issue the open resetlogs command. This is because the data file needs recovery from a point before the resetlogs option was used.

2. Recovery without control file

If you have lost the current control file, or the current control file is inconsistent with files that you need to recover, you need to recover either by using a backup control file command or create a new control file. You can also recreate the control file based on the current one using the ‘SQL> backup control file to trace’ command which will create a script for you to run to create a new one. Recover database using backup control file command must be used when using a control file other that the current. The database must then be opened with
resetlogs option.

3. Recovery of missing datafile with rollback segments

The tricky part here is if you are performing online recovery. Otherwise you can just use the recover datafile command. Now, if you are performing an online recovery, you will need to create a new undo tablespace to be used. Once the old tablespace has been recovered it can be dropped once any uncommitted transactions have rolled back.

4. Recovery of missing datafile without undo segments

There are three ways to recover in this scenario, as mentioned above.
1. recover database;
2. recover datafile ‘c:\skumar\database\skumar11.ora’;
3. recover tablespace user_data;

5. Recovery with missing online redo logs

Missing online redo logs means you lost redo logs before they had archived. This means that crash recovery cannot be performed and media recovery is required. All datafiles will need to be restored and rolled forwarded until the last available archived log file is applied. Therefore, you will have to do incomplete recovery and

recover  database command is necessary.

NOTE: When an incomplete recovery is performed database must be opened resetlogs.
NOTE:  Best way to avoid this kind of a loss is => mirror your online log files.

6. Recovery with missing archived redo logs

If your archives are missing, the only way to recover the database is to restore from your latest backup. You will have lost any uncommitted transactions which were recorded in the archived redo logs.                                                                      NOTE: mirroring your online redo logs and duplicating copies of the archives is best approach to avoid this scenario.

7. Recovery with resetlogs option

Reset log option should be the last resort, however but we have seen above, it may be required due to incomplete recoveries.                                                                                                                                                                                                                                        NOTE: It is MUST that you backup up the database immediately after you have opened with reset logs. It is possible to recover through a resetlogs, and made easier with Oracle 10g onward, but easier to restore from the backup taken after the resetlogs

8. Recovery with corrupted undo segments.

If an undo segment is corrupted, and contains uncommitted system data you may not be able to open the database.

1.  The best alternative in this situation is to recover the corrupt block using the     RMAN blockrecover command

2.  2nd best would be to restore the datafile from backup and do a complete recovery.

If a backup does not exist and If the database is able to open (non system object in undo) The first step is to find out what object is causing the rollback to appear corrupted. If we can determine that we can drop that object.

So, how do we find out if it’s actually a bad object?

1. Make sure that all tablespaces are online and all datafiles are online. This can be checked through via the v$recover_file view.

2. Put the following in the init.ora: event =”10015 trace name context forever, level 10″

This event will generate a trace file that will reveal information about the transaction Oracle is trying to roll back and most importantly, what object Oracle is trying to apply the undo to.

Note: In Oracle v9 and above this information can be found in the alert log.

Stop and start the database.

3. Check in the directory that is specified by the user_dump_dest parameter (in the init.ora or show parameter command) for a trace file that was generated at startup time.

4. In the trace file, there should be a message similar to: error recovery tx(#,#) object #.

TX(#,#) refers to transaction information.
The object # is the same as the object_id in sys.dba_objects.

5. Use the following query to find out what object Oracle is trying to perform recovery on.

select owner, object_name, object_type, status
from dba_objects where object_id = <object #>;

6. Drop the offending object so the undo can be released. An export or relying on a backup may be necessary to restore the object after the corrupted undo segment is released.

9. Recovery with System Clock change.

You can end up with duplicate timestamps in the datafiles when a system clock changes. This usually occurs when daylight saving comes into or out of the picture. In this case rather than a point in time recovery recover to a specify log or SCN

10. Recovery with missing System tablespace.

The only option is to restore from a backup.

11.  Media Recovery of offline tablespace

When a tablespace is offline, you cannot recover datafiles belonging to this tablespace using recover database command. The reason is because a recover database command will only recover online datafiles. Since the tablespace is offline, it thinks the datafiles are offline as well, so even if you recover database and roll forward, the datafiles in this tablespace will not be touched. Instead, you need to perform a recover tablespace command.


You can restore the data files from a cold backup, mount the database and ensure all data files are online and now you can perform a recover database command.

12. Recovery of Read-Only tablespaces

NOTE: You should take the back up of control file whenever switching tablespace mode.

If you have a current control file then recovery of read only tablespaces is no different than recovering read-write files.

The issues with read-only tablespaces arise if you have to use a backup control file. If the tablespace is in read-only mode, and hasn’t changed to read-write since the last backup, then you will be able to media recovery using a backup control file by taking the tablespace offline. The reason here is that when you are using the backup control file, you must open the database with resetlogs. And we know that Oracle will not let you read files from before a resetlogs was done. However, there is an exception with read-only tablespaces. You will be able to take the data files online after you have opened the database.

When you have tablespaces that switch modes and you don’t have a current control file, you should use a backup control file that recognizes the tablespace in read-write mode. If you don’t have a backup control file, you can create a new one using the create controlfile command.

Actually the point here is you should take a backup of the control file every time you switch a tablespaces mode.

Leave a Reply

You must be logged in to post a comment.