Re: v$log_history

On May 22, 1:10 pm, "fitzjarr..._at_cox.net" <orat..._at_msn.com> wrote:
> On May 22, 11:48 am, "fitzjarr..._at_cox.net" <orat..._at_msn.com> wrote:
>
>
>
>
>
> > On May 22, 11:32 am, tim2bo..._at_gmail.com wrote:
>
> > > On May 22, 12:01 pm, "fitzjarr..._at_cox.net" <orat..._at_msn.com> wrote:
>
> > > > On May 22, 10:56 am, tim2bo..._at_gmail.com wrote:
>
> > > > > On May 22, 11:01 am, "Uwe Schmidt" <uwe.schm..._at_dataport.de> wrote:
>
> > > > > > Hy NG,
>
> > > > > > how long are the entries stored in the view v$log_history ?
> > > > > > I looked into 2 diffrent databases and saw diffrent entries; 1= 2weeks ***
> > > > > > 2=3month ??? Wher can i customize the time how long the entries are stored?
>
> > > > > > So long
> > > > > > Uwe
>
> > > > > I believe that these entries are based on the controlfile and the
> > > > > controlfile keep time is based on the initialization parameter
> > > > > CONTROL_FILE_RECORD_KEEP_TIME .
>
> > > > > Regards
> > > > > tim Boles
>
> > > > The redo log history is not controlled by that parameter, it is
> > > > governed by the MAXLOGHISTORY setting.  I've explained how to change
> > > > this in a prior response.
>
> > > > David Fitzjarrell- Hide quoted text -
>
> > > > - Show quoted text -
>
> > > I don't think so:
>
> > > Some references to view:http://www.mydatabasesupport.com/forums/oracle-server/8800-archive-lo...
>
> > >http://download.oracle.com/docs/cd/B10501_01/server.920/a96536/ch3100...
> > > V$LOG_HISTORY -- This view contains log history information from the
> > > control file.
>
> > >http://download.oracle.com/docs/cd/B10501_01/server.920/a96536/ch126....
> > > CONTROL_FILE_RECORD_KEEP_TIME specifies the minimum number of days
> > > before a reusable record in the control file can be reused. In the
> > > event a new record needs to be added to a reusable section and the
> > > oldest record has not aged enough, the record section expands.  This
> > > parameter applies only to records in the control file that are
> > > circularly reusable (such as archive log records and various backup
> > > records).
>
> > >http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/statem...
> > > MAXLOGHISTORY Clause
> > > This parameter is useful only if you are using Oracle in ARCHIVELOG
> > > mode with Real Application Clusters. Specify the maximum number of
> > > archived redo log file groups for automatic media recovery of Real
> > > Application Clusters. Oracle uses this value to determine how much
> > > space in the control file to allocate for the names of archived redo
> > > log files. The minimum value is 0. The default value is a multiple of
> > > the MAXINSTANCES value and depends on your operating system. The
> > > maximum value is limited only by the maximum size of the control file.
>
> > > So basically you are correct except when the number of log files you
> > > write exceeds the MAXLOGHISTORY and the CONTROL_FILE_RECORD_KEEP_TIME
> > > has not been meet.  But for example if your MAXLOGHISTORY is 20, your
> > > CONTROL_FILE_RECORD_KEEP_TIME is set for 3 days and you are producing
> > > about 10 logs day.  Then your CONTROL_FILE_RECORD_KEEP_TIME over rides
> > > your MAXLOGHISTORY and you wind up with 30 logs instead of just 20.
> > > If your CONTROL_FILE_RECORD_KEEP_TIME is set to 0 then the redo log
> > > history entries in teh control file can be reused as required rather
> > > than retained until time expired.  See METALINK note 101343.102
>
> > > Regards
> > > Tim- Hide quoted text -
>
> > > - Show quoted text -
>
> > And in the databases Uwe mentions the length of time the log history
> > reports is far longer than the 7 day default for
> > controlfile_record_keep_time, leading me to believe that MAXLOGHISTORY
> > is set to a larger value, which will override the
> > controlfile_record_keep_time setting.  I have at least one 10.2.0.3
> > database which is not in ARCHIVELOG mode and the log history far
> > exceeds the controlfile_record_keep_time setting of 7 because
> > MAXLOGHISTORY is set to 1168.
>
> > There are times when Metalink notes are not the reference standard one
> > would expect.
>
> > David Fitzjarrell- Hide quoted text -
>
> > - Show quoted text -
>
> To prove my previous statements:
>
> SQL> select to_char(min(first_time), 'DD-MON-YYYY HH24:MI:SS')
> first_time
>   2  from v$log_history;
>
> FIRST_TIME
> --------------------
> 13-APR-2008 19:58:51
>
> 1 row selected.
>
> SQL> show parameter keep_time
>
> NAME                                 TYPE        VALUE
> ------------------------------------ -----------
> ------------------------------
> control_file_record_keep_time        integer     7
> SQL> select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') curr_date
>   2  from dual;
>
> CURR_DATE
> --------------------
> 22-MAY-2008 12:02:53
>
> 1 row selected.
>
> SQL> with cdate as(
>   2  select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') curr_date
>   3  from dual
>   4  ),
>   5  ldate as(
>   6  select to_char(min(first_time), 'DD-MON-YYYY HH24:MI:SS') f_time,
> min(first_time) first_time
>   7  from v$log_history
>   8  )
>   9  select curr_date, f_time, sysdate - first_time dt_diff
>  10  from cdate, ldate;
>
> CURR_DATE            F_TIME                      DT_DIFF
> -------------------- -------------------- --------------
> 22-MAY-2008 12:09:01 13-APR-2008 19:58:51 38.67372685185
>
> 1 row selected.
>
> SQL>
>
> I think almost 39 days exceeds the control_file_record_keep_time
> setting of 7.
>
> David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -

David,

I believe that we are saying the same thing that the number of entries is based on both the CONTROL_FILE_KEEP_TIME and MAXLOGHISTORY. Let me write it as an AND statement.

"Records of logs within the control file can be over written when they exceed CONTROL_FILE_KEEP_TIME ---AND--- when they they number greater than MAXLOGHISTORY.

The logic behind this statement is the following: 1. CONTROL_FILE_KEEP_TIME only represents when entries CAN be over written, NOT when they will be over written. (so you can not over write logs until after this time).
2. MAXLOGHISTORY is the number of logs that will be keep usually. 3. CONTROL_FILE_KEEP_TIME will override MAXLOGHISTORY when the number of logs is GREATER than MAXLOGHISTORY within the time frame set by CONTROL_FILE_KEEP_TIME. Received on Fri May 23 2008 - 08:51:21 CDT