In the following post, we’ll see how to perform complete database level recovery when we have lost all the datafiles BUT we still have online redo log files and we have a backup’s in place & all the archived redo log files from when the backup was taken till the recent.

Recovery Steps:-

Step 1:- Put the database in mount mode.

• Select open_mode from v$database;

If mode is open then shutdown the database

• shutdown immediate;

Then Start the database in mount mode.

• startup mount;

Now the question that comes to mind is why we have to shutdown and startup the database in mount mode, and the answer is that the system tablespace datafile(s) must be offline when being restored and recovered and Oracle won’t allow you to operate your database in open mode with the system datafile offline.

Step 2:- Restore the database.

• rman target /
• restore database;

The restore database command will restore all the datafiles in your database from last backup. The exception to this is when RMAN detects that datafiles have already been restored, then it will not restore them again.

Step 3:- Recover the database

• recover database;

Once the datafiles are restored from last backup, the recover database command will apply redo to datafiles wherever recovery is required (i.e the SCN in the restored datafiles header is older than the SCN written in the control file). The recovery process includes applying changes found in incremental backups, Archived Redo log files, and online redo log files whichever RMAN computes is necessary to apply based on the gap between the SCN in the restored datafiles header is older than the SCN written in the control file

Step 4:- Open the database

• alter database open;

As now the database is open your complete database level recovery is successfully completed.

Also suppose in this case you also have lost the control file (assuming that you have controlfile autobackup on)  and you want the complete database level recovery, following steps are needed to be followed:-

• RMAN> connect target /
• RMAN> startup nomount;
• RMAN> restore controlfile from autobackup;
• RMAN> alter database mount;
• RMAN> restore database;
• RMAN> recover database;
• RMAN> alter database open resetlogs;

I hope this post would be helpful in scenarios when you know the recovery process but as there is lot of stress on DBAs during recovery scenarios even googling at that time for the syntax is an overhead, hence the idea is to make a repository that cover all possibile recovery scenarios step by step with syntax of commands at a single destination. I would try to cover all the scenarios in my subsequent posts. Please comment if any of the curious readers are interested to know the internals of the recovery process like which process does that, what information is in redo that is applied to the datafiles to recover them to point in time and etc.

PLEASE COMMENT IF YOU FACE ANY ERRORS WHEN PERFORMING THIS RECOVERY SO THAT WE CAN WORK ON THOSE AND ENHANCE THIS REPOSITORY FOR OTHER READERS.