Re: v$log_history

From: <fitzjarrell_at_cox.net>
Date: Thu, 22 May 2008 10:30:06 -0700 (PDT)
Message-ID: <d5aa8ef8-ff56-40d1-bad0-13186e864de1@l42g2000hsc.googlegroups.com>


On May 22, 12: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 -

I forgot to list the archivelog mode for this database:

SQL> archive log list

Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            #################
Oldest online log sequence     5308
Current log sequence           5310

SQL> David Fitzjarrell Received on Thu May 22 2008 - 12:30:06 CDT

Original text of this message