How to forbid maintenance tasks to run during monthly reporting period

It’s a common practice for many companies to have kind of monthly reporting period near the first days of a month, when all parties work hard to provide reports in time. This time could be very strict in term of performance, so any other tasks such as statistics gathering would be harmful. So DBA should be smart (and lazy) enough to turn the AutoTask off. Rather than creating complex scheduling rules we will define a schedule¬†declaratively.
As it is stated in an official documentation set, AutoTask schedules automatic maintenance tasks to run in a set of Oracle Scheduler windows known as maintenance windows. Next 22 days of maintenance windows are seen in dba_autotask_schedule

SQL > select window_name, start_time
  2   from dba_autotask_schedule order by start_time;

WINDOW_NAME                    START_TIME
------------------------------ ---------------------------------------------------
...
TUESDAY_WINDOW                 26-FEB-13 10.00.00.600000 PM +02:00
WEDNESDAY_WINDOW               27-FEB-13 10.00.00.600000 PM +02:00
THURSDAY_WINDOW                28-FEB-13 10.00.00.600000 PM +02:00
FRIDAY_WINDOW                  01-MAR-13 10.00.00.600000 PM +02:00
SATURDAY_WINDOW                02-MAR-13 06.00.00.600000 AM +02:00
SUNDAY_WINDOW                  03-MAR-13 06.00.00.600000 AM +02:00
MONDAY_WINDOW                  04-MAR-13 10.00.00.600000 PM +02:00
TUESDAY_WINDOW                 05-MAR-13 10.00.00.600000 PM +02:00
...

So we don’t want to open maintenance windows from February 28 to March 3.
Let’s create a schedule which covers first 3 days of a month

SQL> exec  DBMS_SCHEDULER.CREATE_SCHEDULE( -
>     schedule_name => 'monthly_reporting', -
>     repeat_interval => 'FREQ=monthly;BYMONTHDAY=-1,1,2,3', -
>     comments => 'Schedule for monthly reporting period' -
>   );

PL/SQL procedure successfully completed.

By the way, this schedule could be used also to set an appropriate resource management plan for this period.

Then one could exclude this period from autotask windows as such

SQL> begin
  2    for w in (select window_name,
  3                     repeat_interval
  4               from dba_scheduler_windows
  5               where window_name not like 'WEEK%')
  6    loop
  7      dbms_scheduler.set_attribute(
  8        name      => w.window_name,
  9        attribute => 'repeat_interval',
 10        value     => w.repeat_interval || ';exclude=monthly_reporting'
 11      );
 12    end loop;
 13  end;
 14  /

PL/SQL procedure successfully completed.

And then check that autotask schedule has changed accordingly

SQL > select window_name, start_time
  2  from dba_autotask_schedule
  3  where start_time between date'2013-03-01' - 3
  4                       and date'2013-03-01' + 5
  5  order by start_time;

WINDOW_NAME                    START_TIME
------------------------------ ------------------------------------------
TUESDAY_WINDOW                 26-FEB-13 10.00.00.100000 PM +02:00
WEDNESDAY_WINDOW               27-FEB-13 10.00.00.100000 PM +02:00
MONDAY_WINDOW                  04-MAR-13 10.00.00.100000 PM +02:00
TUESDAY_WINDOW                 05-MAR-13 10.00.00.100000 PM +02:00

There is no maintenance window to open in first three days as required.

Advertisements
Posted in Oracle, Scheduler

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Oracle ... as usual

Oracle by Laurent Leturgez

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

xt-r.com

Just another oracle developer

Coskan's Approach to Oracle

What I learned about Oracle

Oracle Scratchpad

Just another Oracle weblog

Oleksandr Denysenko's Blog

be prepared for The Future...

%d bloggers like this: