RE: no archivelog incomplete recovery?

From: Elliott, Patrick <>
Date: Mon, 7 Jul 2008 13:42:53 -0500
Message-ID: <>

When I think about it...

If you check the v$loghist table and see that the number of log switches since the last full backup is less than the total number of online redo logs, then you would know that the below situation would apply.


From: [] On Behalf Of Elliott, Patrick Sent: Monday, July 07, 2008 1:31 PM
To:; Hemant K Chitale Cc:
Subject: RE: no archivelog incomplete recovery?

This isn't entirely true. If you are lucky, and the online redo logs were never overwritten since the last cold backup, then you could apply the inactive archive logs up to just before the lost active one. During the recovery you would specify the full path to the online redologs that need to be applied. This will work in either archivelog or noarchivelog mode. In the case where your backup includes the online archive logs, it might be a good idea to save the unbroken online redologs just in case they can be used. You might get lucky again and the online logs may have only been overwritten once. Of course the above scenario will only work with a very quiet database. 99.99% of the databases would not be able to use this.


From: [] On Behalf Of John Smith Sent: Monday, July 07, 2008 11:50 AM
To: Hemant K Chitale
Subject: Re: no archivelog incomplete recovery?

So in effect, it is a trick question because the recovery will only be to the state at the last full backup, even if they do call it 'recovery'? Since no archived logs will be available?

On Mon, Jul 7, 2008 at 10:01 AM, Hemant K Chitale <<>> wrote:

The keywords

"how to recover from a lost active online redo log in noarchivelog mode."


As the database is in NOARCHIVELOG, the only way a redo log could be ACTIVE would be if a Checkpoint hasn't been completed. [had the lost online redo log been an Inactive one, you wouldn't have needed to do a Restore AT ALL, provided that you knew what to do !].

The only option is to Restore the last Consistent Backup. IF the Consistent Backup included the Online Redo Logs, then you do not need to RESETLOGS. If , however, the DBA had followed the advice of most "experts" and the documentation and did not backup his Online Redo Logs [in his Consistent Cold Backup of a NoArchiveLog database] you would HAVE to do a RESETLOGS.

(having said that, I guess it should be obvious that I don't agree with most "experts" and the documentation that you should NEVER backup the Online Redo Logs. This is one of the scenarios where a backup of the Online Redo Logs [in a Consistent Cold Backup] would make an OPEN less "uncomfortable" !

But coming back to the scenario provided in your test : Although a RESETLOGS would be adviced, a RECOVER DATABASE wouldn't be needed at all.   The sequence would be :

  1. Restore Consistent Backup.
  2. Verify if it included Online Redo Logs. If if did not include the Online Redo Logs, go to step e.
  3. STARTUP {ie, the same as STARTUP MOUNT; ALTER DATABASE OPEN (normal)} in sqlplus / as sysdba
  4. exit from sqlplus prompt and get a pay raise

Hemant K Chitale

At 07:57 AM Monday, John Smith wrote:
I am studying for the OCP, and one of the practice test questions is how to recover from a lost active online redo log in noarchivelog mode. the answer they give is to restore a consistent backup, startup in mount mode, perform a cancel based recovery, and open with resetlogs.

Is that answer correct? I didnt think you could do a recovery if you arent in archivelog mode.

Hemant K Chitale

"A 'No' uttered from the deepest conviction is better than a 'Yes' merely uttered to please, or worse, to avoid trouble."
Mohandas Gandhi Quotes :

[CONFIDENTIALITY AND PRIVACY NOTICE] Information transmitted by this email is proprietary to Medtronic and is intended for use only by the individual or entity to which it is addressed, and may contain information that is private, privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please delete this mail from your records. To view this notice in other languages you can either select the following link or manually copy and paste the link into the address bar of a web browser:

Received on Mon Jul 07 2008 - 13:42:53 CDT

Original text of this message