Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Frequency of Log Switches

RE: Frequency of Log Switches

From: SAURY Alain <ASAURY_at_recherche.loreal.com>
Date: Fri, 1 Dec 2000 17:40:58 +0100
Message-Id: <10697.123416@fatcity.com>


This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_001_01C05BB5.7B48CA30
Content-Type: text/plain;
 charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Michael, it seems to me that the original question was about a standby database not lagging more than X minutes behind the prod database.

My answer is to force a log switch (a checkpoint won't help) every X = minutes
on the prod database.
Here is a proc that forces the log switch only if it has not already happened. (courtesy of Stephane Faroult at oriolecorp). All I did is schedule it through dbms_job and it worked fine.

Create or replace p_force_switch(p_interval in number default 30) as

 v_last_switch number;
 v_cid         number;
 v_dummy       number;

begin
  select round((sysdate - first_time) * 1440)   into v_last_switch
  from sys.v_$log
  where status =3D 'CURRENT';
  if (v_last_switch >=3D p_interval)
  then
    v_cid :=3D dbms_sql.open_cursor;
    dbms_sql.parse(v_cid, 'alter system switch logfile',
                          dbms_sql.native);
    v_dummy :=3D dbms_sql.execute(v_cid);     dbms_sql.close_cursor(v_cid);
  end if;
end;

Alain.

	-----Message d'origine-----
	De:	Michael Ray [SMTP:Michael.Ray_at_trw.com]
	Date:	vendredi 1 d=E9cembre 2000 14:51
	=C0:	Multiple recipients of list ORACLE-L
	Objet:	RE: Frequency of Log Switches

	It seems people are arguing over semantics, and the original poster
was asking the wrong question. All he wanted was to not lose more than = 15
minutes of data if he crashed. This is controlled by checkpoints, NOT = log
switches.

        Setting the parameters Kieran mentioned appropriately will accomplish that (along with FAST_START_IO_TARGET if using 8i). The = INTERVAL
parameter specifies the maximum number of OS blocks you wish to be read = in
case of instance recovery. The TIMEOUT one specifies the maximum = seconds of
blocks you wish to read which obviously is much more ambiguous and why = I
believe Satar suggested staying away from it. If you are pre-8i these parameters have different meanings.

        If you wish to force a log switch, ALTER SYSTEM SWITCH LOGFILE; but that is only going to increase IO on your system and gain you nothing.

	Michael Ray
	Oracle DBA
	TRW, Marshall, IL
	217-826-3011 x2438


>>> ASAURY_at_recherche.loreal.com 11/30/00 04:55AM >>>
I beg to differ : I am running 8.1.6 on NT with log_checkpoint_timeout =3D 300 and can have as much as 40 minutes between log switches. I suppose that forcing a checkpoint does not forces a log switch (whereas the log switch forces the checkpoint).=20 Anybody having an idea on how to force a log switch ? Alain. -----Message d'origine----- Steven, try changing the initialization parameter LOG_CHECKPOINT_TIMEOUT to be 900 and set the LOG_CHECKPOINT_INTERVAL to be a very high value (greater than the size of the redo logs), this will cause log switches every 15 minutes or when the log file is full. Both of these values can=20 be dynamically altered using ALTER SYSTEM. Regards, Kieran Murray Norkom Technologies, 43 Upper Mount Street, Dublin 2, Ireland Tel:- +353 86 8117916 --=20 Please see the official ORACLE-L FAQ: http://www.orafaq.com --=20 Author: Michael Ray INET: Michael.Ray_at_trw.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

------_=_NextPart_001_01C05BB5.7B48CA30
Content-Type: text/html;
 charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2651.75">
<TITLE>RE: Frequency of Log Switches</TITLE>
</HEAD>
<BODY>

<P><FONT SIZE=3D2 FACE=3D"Arial">Michael, it seems to me that the = original question was about a standby database not lagging more than X = minutes behind the prod database.</FONT></P>

<P><FONT SIZE=3D2 FACE=3D"Arial">My answer is to force a log switch (a = checkpoint won't help) every X minutes on the prod database.</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">Here is a proc that forces the log = switch only if it has not already happened. (courtesy of Stephane = Faroult at oriolecorp).</FONT></P>

<P><FONT SIZE=3D2 FACE=3D"Arial">All I did is schedule it through = dbms_job and it worked fine.</FONT>
</P>

<P><FONT SIZE=3D2 FACE=3D"Arial">Create or replace = p_force_switch</FONT><FONT SIZE=3D2 FACE=3D"Arial">(p_interval in = number default 30)</FONT>

<BR><FONT SIZE=3D2 FACE=3D"Arial">as</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">&nbsp;v_last_switch number;</FONT>
<BR><FONT SIZE=3D2 =

FACE=3D"Arial">&nbsp;v_cid&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs= p; number;</FONT>
<BR><FONT SIZE=3D2 =
FACE=3D"Arial">&nbsp;v_dummy&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; = number;</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">begin</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">&nbsp; select round((sysdate - = first_time) * 1440)</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">&nbsp; into v_last_switch</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">&nbsp; from sys.v_$log</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">&nbsp; where status =3D =
'CURRENT';</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">&nbsp; if (v_last_switch &gt;=3D = p_interval)</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">&nbsp; then</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">&nbsp;&nbsp;&nbsp; v_cid :=3D = dbms_sql.open_cursor;</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">&nbsp;&nbsp;&nbsp; = dbms_sql.parse(v_cid, 'alter system switch logfile',</FONT> <BR><FONT SIZE=3D2 =
FACE=3D"Arial">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp; dbms_sql.native);</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">&nbsp;&nbsp;&nbsp; v_dummy :=3D = dbms_sql.execute(v_cid);</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">&nbsp;&nbsp;&nbsp; = dbms_sql.close_cursor(v_cid);</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">&nbsp; end if;</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">end;</FONT>
</P>

<P><FONT SIZE=3D2 FACE=3D"Arial">Alain.</FONT>
</P>
<BR>

<UL>
<P><A NAME=3D"_MailData"><FONT SIZE=3D1 FACE=3D"Arial">-----Message = d'origine-----</FONT></A>
<BR><B><FONT SIZE=3D1 =
FACE=3D"Arial">De:&nbsp;&nbsp;&nbsp;&nbsp;</FONT></B> <FONT SIZE=3D1 = FACE=3D"Arial">Michael Ray [SMTP:Michael.Ray_at_trw.com]</FONT> <BR><B><FONT SIZE=3D1 FACE=3D"Arial">Date:&nbsp;&nbsp;</FONT></B> <FONT = SIZE=3D1 FACE=3D"Arial">vendredi 1 d=E9cembre 2000 14:51</FONT> <BR><B><FONT SIZE=3D1 =
FACE=3D"Arial">=C0:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</FONT></B> <FONT = SIZE=3D1 FACE=3D"Arial">Multiple recipients of list ORACLE-L</FONT> <BR><B><FONT SIZE=3D1 FACE=3D"Arial">Objet:&nbsp;</FONT></B> <FONT = SIZE=3D1 FACE=3D"Arial">RE: Frequency of Log Switches</FONT> </P>

<P><FONT SIZE=3D2 FACE=3D"Courier New">It seems people are arguing over = semantics, and the original poster was asking the wrong question. All = he wanted was to not lose more than 15 minutes of data if he crashed. = This is controlled by checkpoints, NOT log switches.</FONT></P>

<P><FONT SIZE=3D2 FACE=3D"Courier New">Setting the parameters Kieran = mentioned appropriately will accomplish that (along with = FAST_START_IO_TARGET if using 8i). The INTERVAL parameter specifies the = maximum number of OS blocks you wish to be read in case of instance = recovery. The TIMEOUT one specifies the maximum seconds of blocks you = wish to read which obviously is much more ambiguous and why I believe = Satar suggested staying away from it. If you are pre-8i these = parameters have different meanings.</FONT></P>

<P><FONT SIZE=3D2 FACE=3D"Courier New">If you wish to force a log = switch, ALTER SYSTEM SWITCH LOGFILE; but that is only going to increase = IO on your system and gain you nothing.</FONT></P>

<P><FONT SIZE=3D2 FACE=3D"Courier New">Michael Ray</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Courier New">Oracle DBA</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Courier New">TRW, Marshall, IL</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Courier New">217-826-3011 x2438</FONT>
</P>

<P><FONT SIZE=3D2 FACE=3D"Courier New">&gt;&gt;&gt; = ASAURY_at_recherche.loreal.com 11/30/00 04:55AM &gt;&gt;&gt;</FONT> <BR><FONT SIZE=3D2 FACE=3D"Courier New">I beg to differ : I am running = 8.1.6 on NT with log_checkpoint_timeout =3D 300</FONT> <BR><FONT SIZE=3D2 FACE=3D"Courier New">and can have as much as 40 = minutes between log switches.</FONT>
</P>

<P><FONT SIZE=3D2 FACE=3D"Courier New">I suppose that forcing a = checkpoint does not forces a log switch (whereas</FONT> <BR><FONT SIZE=3D2 FACE=3D"Courier New">the log switch forces the = checkpoint). </FONT>
</P>

<P><FONT SIZE=3D2 FACE=3D"Courier New">Anybody having an idea on how to = force a log switch ?</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Courier New">Alain.</FONT> </P>

<P>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT SIZE=3D2 = FACE=3D"Courier New">-----Message d'origine-----</FONT> </P>

<P>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT SIZE=3D2 = FACE=3D"Courier New">Steven,</FONT>
</P>

<P>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT SIZE=3D2 = FACE=3D"Courier New">try changing the initialization parameter = LOG_CHECKPOINT_TIMEOUT to</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Courier New">be 900</FONT> <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT SIZE=3D2 = FACE=3D"Courier New">and set the LOG_CHECKPOINT_INTERVAL to be a very = high value (greater</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Courier New">than</FONT> <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT SIZE=3D2 = FACE=3D"Courier New">the size of the redo logs),</FONT> <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT SIZE=3D2 = FACE=3D"Courier New">this will cause log switches every 15 minutes or = when the log file</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Courier New">is full.</FONT> <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT SIZE=3D2 = FACE=3D"Courier New">Both of these values can </FONT> <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT SIZE=3D2 = FACE=3D"Courier New">be dynamically altered using ALTER SYSTEM.</FONT> </P>

<P>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT SIZE=3D2 = FACE=3D"Courier New">Regards,</FONT>
<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT SIZE=3D2 = FACE=3D"Courier New">Kieran Murray</FONT> <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT SIZE=3D2 = FACE=3D"Courier New">Norkom Technologies,</FONT> <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT SIZE=3D2 = FACE=3D"Courier New">43 Upper Mount Street, Dublin 2, Ireland</FONT> <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT SIZE=3D2 = FACE=3D"Courier New">Tel:- +353 86 8117916</FONT> </P>

<P><FONT SIZE=3D2 FACE=3D"Courier New">-- </FONT> <BR><FONT SIZE=3D2 FACE=3D"Courier New">Please see the official = ORACLE-L FAQ: <A HREF=3D"http://www.orafaq.com" = TARGET=3D"_blank">http://www.orafaq.com</A></FONT>

<BR><FONT SIZE=3D2 FACE=3D"Courier New">-- </FONT>
<BR><FONT SIZE=3D2 FACE=3D"Courier New">Author: Michael Ray</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Courier New">&nbsp; INET: =
Michael.Ray_at_trw.com</FONT>
</P>

<P><FONT SIZE=3D2 FACE=3D"Courier New">Fat City Network = Services&nbsp;&nbsp;&nbsp; -- (858) 538-5051&nbsp; FAX: (858) = 538-5051</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Courier New">San Diego, = California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Public Internet = access / Mailing Lists</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Courier =

New">-------------------------------------------------------------------=
-</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Courier New">To REMOVE yourself from this = mailing list, send an E-Mail message</FONT> <BR><FONT SIZE=3D2 FACE=3D"Courier New">to: ListGuru_at_fatcity.com (note = EXACT spelling of 'ListGuru') and in</FONT> <BR><FONT SIZE=3D2 FACE=3D"Courier New">the message BODY, include a = line containing: UNSUB ORACLE-L</FONT> Received on Fri Dec 01 2000 - 10:40:58 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US