Friday, December 2, 2022

Changing Oracle Scheduler Maintenance Windows Timing and Settings

Since the advent of Oracle Scheduler, there have been maintenance windows defined with default setting to run some automatic maintenance jobs like auto gather stats job or auto space advisor job etc. There are scenarios whereby DBAs need to change the maintenance windows setting if maintenance windows span peak hours. By default, maintenance jobs run during nights and weekends as these are the off-peak hours in most of the case. However, this may not be the case for every production database. In this article I would explain how to simply change any maintenance window start time and/or duration. For12c and above, this setting is individually defined in root container and PDBs.

As you can see my database has daily maintenance windows start time set to 10 PM and duration is 4 hours. Weekend (Saturday and Sunday) maintenance windows start time is 6 AM and duration is 20 hours. 

col duration for a15
col start_date for a40
col window_name for a17
col LAST_START_DATE for a40
col NEXT_START_DATE for a40

select window_name,last_start_date,duration,REPEAT_INTERVAL from DBA_SCHEDULER_WINDOWS;
WINDOW_NAME       LAST_START_DATE                          DURATION        REPEAT_INTERVAL
----------------- ---------------------------------------- --------------- ----------------------------------------
MONDAY_WINDOW     28-NOV-22 PM ETC/UTC     +000 04:00:00   freq=daily;byday=SAT;byhour=2;byminute=0;bysecond=0

TUESDAY_WINDOW    29-NOV-22 PM ETC/UTC     +000 04:00:00   freq=daily;byday=SAT;byhour=2;byminute=0 ;bysecond=0

WEDNESDAY_WINDOW  30-NOV-22 PM ETC/UTC     +000 04:00:00   freq=daily;byday=SAT;byhour=2;byminute=0;bysecond=0

THURSDAY_WINDOW   01-DEC-22 PM ETC/UTC     +000 04:00:00   freq=daily;byday=SAT;byhour=2;byminute=0;bysecond=0

FRIDAY_WINDOW     25-NOV-22 PM ETC/UTC     +000 04:00:00   freq=daily;byday=SAT;byhour=2;byminute=0;bysecond=0

SATURDAY_WINDOW   26-NOV-22 AM ETC/UTC     +000 20:00:00   freq=daily;byday=SAT;byhour=10;byminute=0;bysecond=0

SUNDAY_WINDOW     27-NOV-22 AM ETC/UTC     +000 20:00:00   freq=daily;byday=SAT;byhour=10;byminute=0;bysecond=0

If I want to set the start time of daily maintenance windows to 2 AM and weekend windows start time to 10 AM, I will execute following set of statements.

execute DBMS_SCHEDULER.SET_ATTRIBUTE('SYS.SATURDAY_WINDOW','repeat_interval','freq=daily;byday=SAT;byhour=10;byminute=0;bysecond=0');

execute DBMS_SCHEDULER.SET_ATTRIBUTE('SYS.SUNDAY_WINDOW','repeat_interval','freq=daily;byday=SUN;byhour=10;byminute=0;bysecond=0');

execute DBMS_SCHEDULER.SET_ATTRIBUTE('SYS.MONDAY_WINDOW','repeat_interval','freq=daily;byday=MON;byhour=2;byminute=0;bysecond=0');

execute DBMS_SCHEDULER.SET_ATTRIBUTE('SYS.TUESDAY_WINDOW','repeat_interval','freq=daily;byday=TUE;byhour=2;byminute=0;bysecond=0');

execute DBMS_SCHEDULER.SET_ATTRIBUTE('SYS.WEDNESDAY_WINDOW','repeat_interval','freq=daily;byday=WED;byhour=2;byminute=0;bysecond=0');

execute DBMS_SCHEDULER.SET_ATTRIBUTE('SYS.THURSDAY_WINDOW','repeat_interval','freq=daily;byday=THU;byhour=2;byminute=0;bysecond=0');

execute DBMS_SCHEDULER.SET_ATTRIBUTE('SYS.FRIDAY_WINDOW','repeat_interval','freq=daily;byday=FRI;byhour=2;byminute=0;bysecond=0');

After this, the following query will show the next start date/time of each of these maintenance windows.

select window_name,next_start_date,duration,REPEAT_INTERVAL from DBA_SCHEDULER_WINDOWS;

WINDOW_NAME       NEXT_START_DATE                          DURATION        REPEAT_INTERVAL
----------------- ---------------------------------------- --------------- ----------------------------------------
MONDAY_WINDOW     05-DEC-22 AM ETC/UTC     +000 04:00:00   freq=daily;byday=MON;byhour=2;byminute=0;bysecond=0

TUESDAY_WINDOW    06-DEC-22 AM ETC/UTC     +000 04:00:00   freq=daily;byday=TUE;byhour=2;byminute=0 ;bysecond=0

WEDNESDAY_WINDOW  07-DEC-22 AM ETC/UTC     +000 04:00:00   freq=daily;byday=WED;byhour=2;byminute=0;bysecond=0

THURSDAY_WINDOW   08-DEC-22 AM ETC/UTC     +000 04:00:00   freq=daily;byday=THU;byhour=2;byminute=0 ;bysecond=0

FRIDAY_WINDOW     09-DEC-22 AM ETC/UTC     +000 04:00:00   freq=daily;byday=FRI;byhour=2;byminute=0;bysecond=0

SATURDAY_WINDOW   03-DEC-22 AM ETC/UTC     +000 20:00:00   freq=weekly;byday=SAT;byhour=10;byminute=0;bysecond=0

SUNDAY_WINDOW     04-DEC-22 AM ETC/UTC     +000 20:00:00   freq=daily;byday=SUN;byhour=10;byminute=0;bysecond=0

I have only changed start time of the maintenance windows here, but if you also want to change the duration of any window, you may set the duration attribute for the window. For example, in order to change run duration of SATURDAY_WINDOW to 9 hours, execute following procedure.

exec DBMS_SCHEDULER.set_attribute( name => 'SYS.SATURDAY_WINDOW',  attribute => 'DURATION',  value => numtodsinterval(540, 'minute'));

No comments:

Post a Comment

Popular Posts - All Times