Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: V$LOG_HISTORY on 7.3.4, Q on SQL&PL/SQL Annotated Archives book
On Wed, 20 Jan 1999 14:30:40 +0000, Andy Hardy
<Andy.Hardy_at_camk.demon.co.uk> wrote:
>Hello,
>
>I've just been browsing through Loney and Carmichaels book 'Oracle SQL &
>PLS/SQL Annotated Archives' from Osborne Press.
>
>The section that I'm looking at is about REDO logs and suggests that
>they should be switching no more frequently than every 20-30 minutes.
>The text and the piece of SQL to verify this state that the
>V$LOG_HISTORY view shows the last 100 log switches.
>
>This doesn't seem to match my db which has over 1600 switches in its
>v$log_history!
The maximum number of records in this view is dependant of MAXLOGHISTORY parameter of the "CREATE DATABASE ...." command. The default value for this parameter is OS specific.
The table of redo log history is actually mantained in the control files (and thus consuming some space, although not worth mentioning). Once the maximum number of records in this table is reached, the oldest record is deleted whenever a new one is entered.
You can verify your setting of MAXLOGHISTORY if you dump your controlfile, using:
alter session set events 'immediate trace name controlf level 10';
Then find the dump file and find a LOG FILE HISTORY ENTRIES section at its end. In mine it looks like this:
LOG FILE HISTORY ENTRIES: (offset = 0x8650, size = 28, max = 1600, hi = 601)
"max" means there can be maximum 1600 entries in the log history
table.
"hi" means the highest log sequence number currently in the log
history table is 601.
Note that all of the above is valid for O7. The structure of control file is slightly changed in O8, but the meaning of the log file history section is very simmilar.
>So, what is the truth behind the view? And, is the 20-30 minute rule of
>thumb of practical use?
Remember, it is just a rule of thumb. The actual preferable interval between the log switches depends on many factors (type of application, maximum allowed time for recovery, the amount of uncommited data you can afford to loose in a case of crash, the desired performance, ...).
So, actualy it is your decision.
>Andy
>--
>Andy Hardy
>Senior IT Systems Engineer, Alstom Automation
>Tel: +44 1908 696200
>Fax: +44 1908 696225
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>Opinions are mine and do not necessarily reflect those of Alstom Automation
HTH,
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)