Re: v$log_history
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