Re: Log switching at a specific time interval

From: Vladimir Ivanovich <vladimiri_at_bosmedtech.com>
Date: Thu, 27 Sep 2001 17:44:03 -0400
Message-ID: <D4Ns7.3$dd4.93@news.nyc.globix.net>

Here is a suggestion from one of the previous posts. HTH,
Vladimir

---

---

I prefer the best of both worlds. I have "massive" redo logs (where
> "massive" means they virtually "never" switch by themselves) and then
> use dbms_job to manually switch them each 'n' minutes (where 'n' is
> typically 30 or 60).
>
> Then I know exactly when redo switches will be occurring; I know exactly
> how out of date my standby database will be; I can tweak the dbms_jobs
> to do it less frequently over night when large stuff is going on etc etc
> etc
>
> hth
> connor
>

I completely agree. Here's proof:

REM just switch during load periods
REM mon-fri between 8 am and 6 pm
CREATE OR REPLACE PROCEDURE switch_logfile IS

    exec_me varchar2(255);
    this_day varchar2(9);
    this_hour varchar2(2);
BEGIN
     select to_char(sysdate,'DAY') into this_day from dual;
     select to_char(sysdate,'HH24') into this_hour from dual;
     if substr(this_day,1,1) != 'S' and this_hour between 8 and 18
     then
        exec_me := 'ALTER SYSTEM SWITCH LOGFILE';
        EXECUTE IMMEDIATE exec_me;
     end if;

end;
/

REM this code queues a job to switch the logfile once an hour REM the schema under which this runs must have been granted the sys_priv "ALTER SYSTEM" alter session set nls_date_format='DD-MON-RRRR'; variable jobno number;
exec
dbms_job.submit(:jobno,'switch_logfile;',sysdate+(1/24),'sysdate+(1/24)');

---

---

"Mark" <markag_at_wonderstats.com> wrote in message news:ba6d27ca.0109260910.458eef26_at_posting.google.com...
> Hi all,
>
> I want to force a log switch every 20 minutes at a minimum, or the
> normal event of the log filling - which ever comes first.
>
> Can I do that by setting some init.ora params or do I need to create a
> job that runs every 20 minutes that exec's ALTER SYSTEM SWITCH LOGFILE
> command?
>
> How do I do that?
>
> Oracle 8.1.5
Received on Thu Sep 27 2001 - 16:44:03 CDT