Scenario 3: How to perform a Complete Database Recovery

It has been a long time as I completely forgotten to blog a new post. So as a DBA, everyone should know how to perform a complete database recovery. Anyway without wasting your time, I have simplified the steps as given below:

Scenario:
There is a power outage and most of the tablespace data files got corrupted. So as a DBA how to fix this issue ? Well keep calm and read on....


Solution:
The point-in-time recovery is possible but given the nature of the scenario where most of the datafiles are corrupted and also to save a lot of time, we are going to do a complete database recovery

Here I am assuming that we do have the copy of the backup of the database of the previous day.

Step 1: Shutdown and start the database in "mount stage"

RMAN> SHUTDOWN IMMEDIATE;    RMAN> STARTUP MOUNT;

Step 2: Go to RMAN terminal and use the below command to restore the data files from the backup

RMAN> RESTORE DATABASE;

Step 3: Now use the below command to recover the database. If you are getting any errors at this point of time, do a media recovery at step 5

RMAN> RECOVER DATABASE;

Step 4: Now try to bring the database to "open stage"

RMAN> ALTER DATABASE OPEN;

Step 5: Do an incomplete recovery. (cancel until the missing logs are reached)

SQL> RECOVER DATABASE UNTIL CANCEL;

Step 6: Open the database in reset log mode    

SQL> ALTER DATABASE OPEN RESETLOGS;

Step 7: To register a new incarnation of the database, use the below command to do so.

RMAN> RESET DATABASE;

Step 8: Take a new backup of the database

Now you know how to perform a complete database backup.