In the following post we’ll see how to restore a NoArchivelog database, however I never understood the requirement for keeping the database in NoArchivelog mode… Its Developement..!! Space Crunches..!! Oh C’mon I don’t buy this, whats the point of using Oracle then if you don’t care about recovery, use a SQL Server or Sybase instead :)…

Anyway, coming back to the situation where you have a daily or weekly full database backup. In case database crashes then even when the users don’t care about the data but they do want there database, you have to restore it. The catch here is when you restore and recover but when you are using current controlfile ( assuming you don’t have controlfile backup, cause if you do its recommended to use the backup controlfile, cause No one cares for the data loss here ) and after restore, when you open the database Oracle will not allow you to do so because the control file needs to be in sync with the datafiles which is not because database backup was not current and SCN in controlfile might have advanced than that in datafile headers of the datafiles in the backupset.

To understand the above confusing paragraph see the steps below:-

• RMAN> connect target /
• RMAN> startup mount;
• RMAN> restore database;
• RMAN> alter database open resetlogs;

Since the online redo log files are not included in the RMAN backup, then you must issue alter database open resetlogs to create new online redo log files, as well as to synchronize the control files and datafiles in terms of their checkpoint SCNs.

At this point, when trying to open the database with resetlogs, you’ll get an error:-

ORA-01139: RESETLOGS option only valid after an incomplete database recovery

And that’s true because the code written for resetlogs works only if the above IF clause is met, so now we have to find a loophole in the code. The IF clause only sees if recover statement ( i.e. the snippet behind the recover command) is executed and then only it goes to the section where the code for opening the database with resetlogs is executed. So in order to satisfy the IF clause condition we’ll do Cancel-Based Recovery, here’s how we do it:-

• SQL> recover database until cancel;

And once you execute this statement RMAN would prompt you like:-

Specify log: {=suggested | filename | AUTO | CANCEL}

All you have to do is press CANCEL, because you don’t have any archive logs, BUT the code doesn’t checks that, it thinks whatever logs user wanted to apply he has applied so recovery is done. Thus the IF clause is matched and BINGO..!!!

• RMAN> alter database open resetlogs;

This time it would work and you can give back the database to application guys to crash it again. 🙂

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.