Re: Redo Log Size

From: Paul Drake <paledHOWMUCHSPAMCANYOUEAT_at_home.com>
Date: Tue, 28 Aug 2001 22:36:01 GMT
Message-ID: <3B8C1CC5.2020409@home.com>

Connor McDonald wrote:

>
> 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)');

enjoy.

Paul Received on Tue Aug 28 2001 - 17:36:01 CDT