Re: no archivelog incomplete recovery?

From: Hemant K Chitale <hkchital_at_singnet.com.sg>
Date: Tue, 08 Jul 2008 22:26:39 +0800
Message-Id: <200807081426.m68EQiDk007950@smtp12.singnet.com.sg>

Aah ! That is a problem. I want the RESETLOGS because I do not have usable online redo logs but RESETLOGS requires an Incomplete Recovery ?!

So there would be an additional step inside e) then ! STARTUP MOUNT ; RECOVER DATABASE UNTIL CANCEL ; ALTER DATABASE OPEN RESETLOGS; However, it wouldn't really be an "Incomplete" Recovery. We'd fool Oracle into thinking that it was an Incomplete Recovery.

Here I show how a database that was shutdown normal (ie a consistent backup !!) can be OPEN RESETLOGS without really doing an Incomplete Recovery :

SQL> shutdown normal
Database closed.
Database dismounted.
ORACLE instance shut down.
===> assume that I backed up this database and restored it. SQL> startup mount
ORACLE instance started.

Total System Global Area 536870912 bytes

Fixed Size                  1291652 bytes
Variable Size             339741308 bytes
Database Buffers          192937984 bytes
Redo Buffers                2899968 bytes

Database mounted.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL> At 12:11 AM Tuesday, Stefan Knecht wrote:
>idle> alter database open resetlogs;
>alter database open resetlogs
>*
>ERROR at line 1:
>ORA-01139: RESETLOGS option only valid after an incomplete database recovery
>
>That's what your step e) should give you I think ?
>
>Cheers
>
>Stefan
>
>
>On Mon, Jul 7, 2008 at 5:01 PM, Hemant K Chitale
><<mailto:hkchital_at_singnet.com.sg>hkchital_at_singnet.com.sg> wrote:
>
>The keywords
>
>"how to recover from a lost active online redo log in noarchivelog mode."
>
>is ACTIVE !
>
>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 :
> a. Restore Consistent Backup.
> b. Verify if it included Online Redo Logs. If if did not include
> the Online Redo Logs, go to step e.
> c. STARTUP {ie, the same as STARTUP MOUNT; ALTER DATABASE
> OPEN (normal)} in sqlplus / as sysdba
> d. exit from sqlplus prompt and get a pay raise
> e. STARTUP MOUNT ; ALTER DATABASE OPEN RESETLOGS in sqlplus / as sysdba
>
>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
>
>
>
>
>
>
>--
>=========================
>
>Stefan P Knecht

Hemant K Chitale

http://hemantoracledba.blogspot.com

"A 'No' uttered from the deepest conviction is better than a 'Yes' merely uttered to please, or worse, to avoid trouble." Mohandas Gandhi Quotes
: http://www.brainyquote.com/quotes/authors/m/mohandas_gandhi.html

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jul 08 2008 - 09:26:39 CDT