Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: CONTROL_FILE_RECORD_KEEP_TIME and MAXLOGHISTORY

Re: CONTROL_FILE_RECORD_KEEP_TIME and MAXLOGHISTORY

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 13 Feb 2001 07:35:01 -0000
Message-ID: <982049472.15667.0.nnrp-10.9e984b29@news.demon.co.uk>

Gosh,
Doesn't time fly - was it really two weeks ago ?

Do you also see in your alert log lines something like:

  expanding section NN

Bit of a headache if you have your control files on raw devices, isn't it ? You have to get it right first time. Mind you, the best, and inexpensive, bet is to go completely over the top and allocate the maximum possible (which I think is 64MB but perhaps someone can correct me on that).

Thanks for doing the experiment and sending back the results.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases
Publishers:  Addison-Wesley

Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Brian Peasland wrote in message <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 Tue Feb 13 2001 - 01:35:01 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US