Re: CONTROL_FILE_RECORD_KEEP_TIME and MAXLOGHISTORY

Only that you learn something new every day!

What version is this again, Brian (my BRAIN_RECORD_KEEP_TIME is significantly under 21 days!!)?

I had heard (months ago) that maxdatafiles was variable in 8i, but fixed in earlier versions. It would certainly seem that maxloghistory is experiencing the same effect -but, as I say, I have a sneaking suspicion that this is 8i-specific behaviour. I can't test that, having no 7 databases to hand... but if you've got 8.0, that will do to dismiss this line of thought. I'll give in gracefully!

Regards
HJR "Brian Peasland" <peasland_at_usgs.gov> wrote in message news:3A8819C9.3C078DD6_at_usgs.gov...
> This is not a question, but rather a followup to a previous thread.
> Sometime ago, Howard J Rogers and Jonathan Lewis responded to a thread
> concerning the CONTROL_FILE_RECORD_KEEP_TIME init.ora parameter and the
> MAXLOGHISTORY parameter in the control file (issued at database
> creation). The following is an excerpt from that thread:
>
> > Brian Peasland wrote in message

 <3A6EF404.6D5991CB_at_edcmail.cr.usgs.gov>...
> > >Howard,
> > >
> > >I did some more checking on this...
> > >
> > >If I look at the entries in V$ARCHIVED_LOG, I can see that the entries
> > >go back exactly 7 days!! This matches my CONTROL_FILE_RECORD_KEEP_TIME
> > >parameter. If I examine my control file, I can see that I have
> > >MAXLOGHISTORY set to 3630. But my V$ARCHIVED_LOG view only contains
 1524
> > >entries for those 7 days. So it seems to me that the
> > >CONTROL_FILE_RECORD_KEEP_TIME parameter is the factor that determines
> > >how long entries are kept in V$ARCHIVED_LOG.
> > >
> > >More food for thought...
> > >Brian
>
> Which was followed up by...
>
> Jonathan Lewis wrote:
> >
> > I suppose you wouldn't like to complete the experiment -
> > for use and change CONTROL_FILE_RECORD_KEEP_TIME
> > to 21, and see if v$archived_log extends to about 4,500 rows,
> > or sticks at 3,630. That's if your control file can grow safely,
> > of course and you don't mind bouncing your database.
> >
> > --
> > Jonathan Lewis
> > Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
> >
> > Practical Oracle 8i: Building Efficient Databases
> >
> > Publishers: Addison-Wesley
> > See a first review at:
> > http://www.ixora.com.au/resources/index.htm#practical_8i
> > More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html
>
> Here is what I found out....
>
> I modified my init.ora parameter as can be verified by:
>
> SQL> select name,value from v$parameter
> 2 where name='control_file_record_keep_time';
>
> NAME VALUE
> ----------------------------------- ----------
> control_file_record_keep_time 21
>
> If you examine my nightly control file dump to trace, you will see:
>
> <<snip>>
> STARTUP NOMOUNT
> CREATE CONTROLFILE REUSE DATABASE "sid_name" NORESETLOGS ARCHIVELOG
> MAXLOGFILES 32
> MAXLOGMEMBERS 3
> MAXDATAFILES 100
> MAXINSTANCES 8
> MAXLOGHISTORY 4764
> LOGFILE
> <<snip>>
>
> What is interesting is that my MAXLOGHISTORY parameter changes over time
> to accomodate my CONTROL_FILE_RECORD_KEEP_TIME!!!!!! If I examine my
> trace files from my nightly control file dump, I see the following:
>
> oracle% grep MAXLOGHISTORY *
> <my_sid>_ora_1220.trc: MAXLOGHISTORY 4537
> <my_sid>_ora_1370.trc: MAXLOGHISTORY 4764
> <my_sid>_ora_17223.trc: MAXLOGHISTORY 4764
> <my_sid>_ora_19273.trc: MAXLOGHISTORY 4764
> <my_sid>_ora_19284.trc: MAXLOGHISTORY 4310
> <my_sid>_ora_19734.trc: MAXLOGHISTORY 4764
> <my_sid>_ora_23939.trc: MAXLOGHISTORY 4310
> <my_sid>_ora_28266.trc: MAXLOGHISTORY 4537
> <my_sid>_ora_3018.trc: MAXLOGHISTORY 4310
> <my_sid>_ora_7826.trc: MAXLOGHISTORY 4310
>
> These trace files are not listed in chronological order. But notice how
> the MAXLOGHISTORY changes over time to respond to my growing control
> file! If I examine V$ARCHIVED_LOG, I find the following today:
>
> SQL> select count(*) from v$archived_log;
>
> COUNT(*)
> ----------
> 4797
>
> I expect that tonight's control file trace will exhibit a growth in the
> MAXLOGHISTORY parameter.
>
> My conclusion is that the CONTROL_FILE_RECORD_KEEP_TIME is the driving
> factor in how many redo logs are available for recovery. This is
> reinforced by the fact that the control file is updated with the max
> number of logs available.
>
> Jonathan or Howard, comments???
>
> Enjoy the day,
> Brian
>
>
> --
> ========================================
> Brian Peasland
> Raytheons Systems at
> USGS EROS Data Center
> These opinions are my own and do not
> necessarily reflect the opinions of my
> company!
> ========================================
Received on Mon Feb 12 2001 - 19:12:09 CST