Re: Backup Controlfile and ArchiveLog -- was re Urgent ORA-00600: internal error code

Related to this... we were testing some recovery scenarios for a "tech night" this past monday night and walked through controlfile recovery among other things...

Even if you have a clean database shutdown (immediate) and restore *only* your controlfiles, we found that you still need to process the last online redo log then open with resetlogs. If you just say CANCEL then it won't open the database. This is interesting because your datafiles do not actually need recovery. However Oracle makes you process that last online log and open with resetlogs. My theory is that Oracle doesn't actually update the SCN in the control file until it "recovers" a logfile... even if there's nothing in that logfile to actually process...

FYI, we were testing on 10.2.0.2

-Jeremy

-- 
Jeremy Schneider
Chicago, IL
http://www.ardentperf.com/category/technical


On 5/4/07, Joel.Patterson_at_crowley.com <Joel.Patterson_at_crowley.com> wrote:


>

> Good explanation!  I was just going to say hit cancel.

>

> Joel Patterson

> Database Administrator

> joel.patterson_at_crowley.com

> x72546

> 904  727-2546

>

> -----Original Message-----

> From: oracle-l-bounce_at_freelists.org

> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Hemant K Chitale

> Sent: Friday, May 04, 2007 9:58 AM

> To: JDunn_at_sefas.com; John Dunn; Ghassan Salem

> Cc: oracle-l_at_freelists.org

> Subject: RE: Backup Controlfile and ArchiveLog -- was re Urgent

> ORA-00600: internal error code

>

>

> The "USING BACKUP CONTROLFILE"  tells Oracle that the SCNs in the

> Controlfiles are NOT

> the current ones .  Oracle keeps prompting for as many archivelogs as

> you have available because

> it does not know how many you really have.

> [Say that on Wednesday you restore the databasefiles as of

> Monday.  You also do not have the "current"

> controlfile of Wednesday. You have had to recreate the controlfile

> and tell Oracle that you are

> using a Backup Controlfile.  Oracle then doesn't know if you have

> ArchiveLogs for Monday and also

> ArchiveLogs for Tuesday and also ArchiveLogs for Wednesday.  You may

> have Monday's files

> and Tuesday's files but not Wednesday's ArchiveLogs. Oracle just

> doesn't know because the

> "current" status is not available in the controlfile that you have

> recreated].

> Oracle keeps prompting for the next archivelog until you have applied

> all your archivelogs

> at which point YOU as the DBA must say CANCEL  and then OPEN RESETLOGS.

>

> Why Archivelogs ?    Again, because information is no longer

> available in the controlfile

> that you have intialized !    If you really have no ArchiveLogs [you

> weren't running ArchiveLog !]

> just say CANCEL and then OPEN RESETLOGS.

>

> In all cases, ensure that you HAVE CORRECTLY CREATED THE CONTROLFILEs,

> naming every DataFile  -- once you OPEN, Oracle does a Data

> Dictionary check and any

> datafile in the Data Dictionary but not in your Controlfile becomes

> a   "MISSING" datafile !

> {so sometimes we do another backup, if we can, before the OPEN RESETLOGS

> !}

>

>

> Having said all that, why are you sure that you DID need to recreate

> your controlfiles

> and reset your online logs ?  Was the problem something to do with

> mountpoints and

> permissions only ?

>

> Hemant

>

> At 07:33 PM Friday, John Dunn wrote:

> >OK, I have recreated the control file but when I run RECOVER

> >DATABASE USING BACKUP CONTROLFILE I get the following errors.

> >

> >I am not and never have been using archivelogs so why is it asking

> >for this file?

> >

> >SQL> RECOVER DATABASE USING BACKUP CONTROLFILE;

> >

> >ORA-00279: change 406102834 generated at 05/02/2007 03:19:46 needed

> >for thread1

> >

> >

> >John

> >

> >

> >

> >

> >

> >----------

> >

>

>

> Hemant K Chitale

> http://web.singnet.com.sg/~hkchital

> and

> http://hemantscribbles.blogspot.com

> and

> http://hemantoracledba.blogspot.com

>

> "First they ignore you, then they laugh at you, then they fight you,

> then you win" !"

> Mohandas Gandhi Quotes

> :  http://www.brainyquote.com/quotes/authors/m/mohandas_gandhi.html

>

> --

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

>

>

> --

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

>

>

>



-- 
Jeremy Schneider
Chicago, IL
http://www.ardentperf.com/category/technical

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