RE: Frequency of Log Switches

From: Steven Monaghan <MonaghaS_at_mscdirect.com>
Date: Mon, 4 Dec 2000 09:00:06 -0500
Message-Id: <10700.123519@fatcity.com>

Thanks to all who replied to this thread. The mail sent below has both the correct paraphrase of my question and a workable solution.

Thanks again,
Steve Monaghan

-----Original Message-----
From: SAURY Alain [mailto:ASAURY_at_recherche.loreal.com] Sent: Friday, December 01, 2000 1:02 PM
To: Multiple recipients of list ORACLE-L Subject: RE: Frequency of Log Switches

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 = 'CURRENT';
  if (v_last_switch >= p_interval)
  then
    v_cid := dbms_sql.open_cursor;
    dbms_sql.parse(v_cid, 'alter system switch logfile',

                          dbms_sql.native);

v_dummy := dbms_sql.execute(v_cid); dbms_sql.close_cursor(v_cid);
end if;
end;

Alain.

This e-mail is intended for the use of the addressee(s) only and may contain privileged, confidential, or proprietary information that is exempt from disclosure under law. If you are not the intended recipient, please do not read, copy, use or disclose the contents of this communication to others. Please notify the sender that you have received this e-mail in error by replying to the e-mail. Please then delete the e-mail and destroy any Received on Mon Dec 04 2000 - 08:00:06 CST