v$log_history
Uwe Schmidt
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
gazzag
Off the top of my head, does it relate to when the database was last
opened with the RESETLOGS option?
ora...@msn.com
On May 22, 10:01 am, "Uwe Schmidt" <uwe.schm...@dataport.de> wrote:
> Hy NG,
>
> how long are the entries stored in the view v$log_history ?
Until the FIRST_CHANGE# is less than the ARCHIVE_CHANGE# in v
$database. The value in ARCHIVE_CHANGE# is the SCN below which redo
logs are forced to be archived.
> I looked into 2 diffrent databases and saw diffrent entries; 1= 2weeks ***
And the ARCHIVE_CHANGE# in each indicates the earliest entry you'll
see in V$LOG_HISTORY.
> 2=3month ??? Wher can i customize the time how long the entries are stored?
You'll need to create a new controlfile to change this, as this is
controlled by the MAXLOGHISTORY parameter. An 'alter database backup
controlfile to trace;' command will provide the currently set
parameters in a script you can modify to increase or decrease the
MAXLOGHISTORY value.
>
> So long
> Uwe
David Fitzjarrell
tim2...@gmail.com
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
ora...@msn.com
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
tim2...@gmail.com
>
> - Show quoted text -
I don't think so:
Some references to view:
http://www.mydatabasesupport.com/forums/oracle-server/8800-archive-log-files-reaches-maxloghistory.html
http://download.oracle.com/docs/cd/B10501_01/server.920/a96536/ch3100.htm#1117380
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.htm#REFRN10020
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/statements_54a.htm#2078255
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
ora...@msn.com
> On May 22, 12:01 pm, "fitzjarr...@cox.net" <orat...@msn.com> wrote:
>
>
>
>
>
> > On May 22, 10:56 am, tim2bo...@gmail.com wrote:
>
> > > On May 22, 11:01 am, "Uwe Schmidt" <uwe.schm...@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:
>
>
> 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.
>
> 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).
>
> 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
>
> - 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
ora...@msn.com
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
ora...@msn.com
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
Mark D Powell
There seems to have been some changes related to maxloghistory as
Oracle has released new versions.
From
Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number
B14200-01
>>
MAXLOGHISTORY Clause
This parameter is useful only if you are using Oracle Database in
archivelog mode with Real Application Clusters. Specify the maximum
number of archived redo log files for automatic media recovery of Real
Application Clusters. The database uses this value to determine how
much space to allocate in the control file 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.
<<
The Reference manual entry for CONTROL_FILE_RECORD_KEEP_TIME states it
is responsible for the number of archive log records maintained.
I suspect neither manual entry is complete and the unspecified
apparently OS dependent minimum value plays into how many you actually
get.
HTH -- Mark D Powell --
tim2...@gmail.com
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.