Re: no archivelog incomplete recovery?
- From: "Stefan Knecht" <knecht.stefan@xxxxxxxxx>
- To: "Hemant K Chitale" <hkchital@xxxxxxxxxxxxxx>
- Date: Tue, 8 Jul 2008 16:40:10 +0200
Yep, as I said before, you *need* to perform a "dummy" recovery :)
Cheers
Stefan
On Tue, Jul 8, 2008 at 4:26 PM, Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
wrote:
>
> 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@xxxxxxxxxxxxxx>hkchital@xxxxxxxxxxxxxx> 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
>
>
--
=========================
Stefan P Knecht
Senior Consultant
Infrastructure Managed Services
Trivadis AG
Europa-Strasse 5
CH-8152 Glattbrugg
Phone +41-44-808 70 20
Fax +41-808 70 12
Mobile +41-79-571 36 27
stefan.knecht@xxxxxxxxxxxx
http://www.trivadis.com
OCP 9i/10g SCSA SCNA
=========================
Other related posts:
