Re: v$log_history

From: <fitzjarrell_at_cox.net>
Date: Thu, 22 May 2008 10:10:46 -0700 (PDT)
Message-ID: <eb4c2d5c-1f39-4c3f-b369-6d1a22568d4a@59g2000hsb.googlegroups.com>

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 Received on Thu May 22 2008 - 12:10:46 CDT