Restoring a Database (NoArchivelog mode)

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.


7 thoughts on “Restoring a Database (NoArchivelog mode)

  1. Hi,

    normally in noarchivelog mode we can’t recover without data loss. if I use reset log option, previously taken backup can’t be used anymore.

    Is there any other possible ways to recover without data loss in noarchivelog mode or without using reset log option?


    • Hi,

      There is no way to recover data without data loss in noarchivelog mode (I am assuming that there has been change in data since the backup was taken) which should be obvious because you do not have the change records with you.

      Also I believe from 10g onwards you can recover through resetlogs, the RMAN can steep through the incarnations of the database and can recover the database from a previous incarnation. Thus the previous backups are not obsolete after resetlogs. (Would try to post an article on this topic)



  2. Pingback: Tips & Tricks – Be an Oracle Database 12c OCP Pro | IT Briefcase

  3. Hi

    I have enterprise edition database backup. I want restore it into standard edition is this possible or any alternate way is available? Pls let me know.


  4. Pingback: Tips & Tricks – Be an Oracle Database 12c OCP Pro By Passing Oracle 1Z0-067 Exam – ozprosper

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.