CONTROL_FILE_RECORD_KEEP_TIME and MAXLOGHISTORY

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!
========================================