Changing the Oracle database default maintenance window time

I’ve been working recently on a system that has been a performance nightmare. I’ve been able to do a lot to get it running well, but I often noticed that load on the system would always increase in the afternoon even though it didn’t appear the workload was increasing. I’d notice this often around 3-4PM (Mountain Time) and thought maybe it was because people in the East and Central time zones were returning home and using it. I had observed however, that the traffic pattern would rise in the morning, stay high during the day, and then lower in the afternoon and into the evening. This made my theory not make sense. It didn’t make sense the load would increase when traffic on the system should be going down, until one day I did “date” on the Linux command line and got reminded the system runs on UTC and this system supports customers in North America. Ah-hah!

Some quick time math revealed that 3PM (Mountain Time)  was 10PM UTC. What did that matter? Well, I recalled from OCP study that the default Oracle Database maintenance windows start at 10PM during the week. That meant that the Oracle maintenance windows would be starting during the normal user time for this product. I wanted to modify these to get them to start during the North American night.

My recollection was that Oracle had weeknight and weekend windows and that is what I’d need to change. I searched for these to verify the start time:

select window_name, repeat_interval, duration from dba_scheduler_windows where window_name like 'WEEK%';
WINDOW_NAME          REPEAT_INTERVAL                                                            DURATION
-------------------- -------------------------------------------------------------------------- --------------------
WEEKNIGHT_WINDOW     freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0      +000 08:00:00
WEEKEND_WINDOW       freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0                        +002 00:00:00

Good, I’d found the windows as figured I just  needed to change them. So, I modified them in the scheduler. To move them on a server in UTC to the “normal” time in Mountain Time Zone I did:

EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('WEEKNIGHT_WINDOW','repeat_interval','freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=05;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('WEEKEND_WINDOW','repeat_interval',' freq=daily;byday=SAT;byhour=07;byminute=0;bysecond=0');

The next day I checked the scheduler to see if the jobs ran at the new correct time. I checked in dba_scheduler_job_details but couldn’t find what I wanted. Some research reminded me that in 11g the DBMS_AUTO_TASK_ADMIN functionality is used. To find the information I needed to check another view:

SQL> select client_name, job_status, job_start_time, job_duration from dba_autotask_job_history order by job_start_time
auto optimizer stats collection          SUCCEEDED                      22-FEB-12 PM UTC    +000 03:31:40
auto space advisor                       STOPPED                        22-FEB-12 PM UTC    +000 03:59:52
sql tuning advisor                       SUCCEEDED                      22-FEB-12 PM UTC    +000 00:02:00

Still 10PM UTC! That wasn’t what I expected, so I decided to check on the Auto Task Admin settings:

SQL> select client_name, status, window_group from dba_autotask_client;
CLIENT_NAME                              STATUS     WINDOW_GROUP
---------------------------------------- ---------- ----------------------------------------------------------------
auto optimizer stats collection          ENABLED    ORA$AT_WGRP_OS
auto space advisor                       ENABLED    ORA$AT_WGRP_SA
sql tuning advisor                       ENABLED    ORA$AT_WGRP_SQ

OK, so I’ve got the Window Groups, now I had to dig in further to find out what window’s they using:

SQL> select * from dba_scheduler_window_groups;
------------------------------ ----- ----------------- ------------------------------------ ----------------------------------------
MAINTENANCE_WINDOW_GROUP       TRUE                  7 24-FEB-12 AM UTC     Window group for Automated Maintenance
ORA$AT_WGRP_OS                 TRUE                  7 24-FEB-12 AM UTC     auto optimizer stats collection
ORA$AT_WGRP_SA                 TRUE                  7 24-FEB-12 AM UTC     auto space advisor
ORA$AT_WGRP_SQ                 TRUE                  7 24-FEB-12 AM UTC     sql tuning advisor

SQL> select * from dba_scheduler_wingroup_members order by 1,2;
------------------------------ ------------------------------

So, it appeared that the Auto Task Admin does not use the parent WEEKNIGHT and WEEKEND window groups, but instead the daily windows.

SQL> select window_name, repeat_interval, duration from dba_scheduler_windows order by window_name;
WINDOW_NAME                    REPEAT_INTERVAL                                                        DURATION
------------------------------ ---------------------------------------------------------------------- ------------------
FRIDAY_WINDOW                  freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0                  +000 04:00:00
MONDAY_WINDOW                  freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0                  +000 04:00:00
SATURDAY_WINDOW                freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0                   +000 20:00:00
SUNDAY_WINDOW                  freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0                   +000 20:00:00
THURSDAY_WINDOW                freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0                  +000 04:00:00
TUESDAY_WINDOW                 freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0                  +000 04:00:00
WEDNESDAY_WINDOW               freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0                  +000 04:00:00
WEEKEND_WINDOW                 freq=daily;byday=SAT;byhour=07;byminute=0;bysecond=0                   +002 00:00:00
WEEKNIGHT_WINDOW               freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=05;byminute=0; bysecond=0  +000 08:00:00

So, I needed to change the regular weekly windows too:

EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('MONDAY_WINDOW',   'repeat_interval','freq=daily;byday=MON;byhour=05;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('TUESDAY_WINDOW',  'repeat_interval','freq=daily;byday=TUE;byhour=05;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('WEDNESDAY_WINDOW','repeat_interval','freq=daily;byday=WED;byhour=05;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('THURSDAY_WINDOW', 'repeat_interval','freq=daily;byday=THU;byhour=05;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('FRIDAY_WINDOW',   'repeat_interval','freq=daily;byday=FRI;byhour=05;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('SATURDAY_WINDOW', 'repeat_interval','freq=daily;byday=SAT;byhour=13;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('SUNDAY_WINDOW',   'repeat_interval','freq=daily;byday=SUN;byhour=13;byminute=0; bysecond=0');

Voila! The next day the jobs were running when I wanted them to, not during the North American day, but during the night.

SQL> select client_name, job_status, job_start_time, job_duration from dba_autotask_job_history order by job_start_time;
CLIENT_NAME                              JOB_STATUS                     JOB_START_TIME                      JOB_DURATION
---------------------------------------- ------------------------------ ----------------------------------- ------------------
auto optimizer stats collection          SUCCEEDED                      24-FEB-12 AM UTC    +000 03:23:57
auto space advisor                       STOPPED                        24-FEB-12 AM UTC    +000 03:59:52
sql tuning advisor                       SUCCEEDED                      24-FEB-12 AM UTC    +000 00:01:58

So, whatever timezone you’re in, if it isn’t UTC or close to it and you prefer these to run during the evening (or whatever time really), you now will know how to change it.


17 responses to “Changing the Oracle database default maintenance window time

  1. Pingback: Changing the Oracle database default maintenance window time | Jed's

  2. Mike Holden

    Copy & paste error there. You set byday=FRI for all your daily jobs!!!

  3. Huh! Dude, you just saved my Job. I had the same nightmare, a couple of weeks back!

    Many thanks!


  4. elhajjaji youssef

    good thing

  5. Explained and nailed 🙂

  6. thankyou. I did not have to look anywhere else. good blog.

  7. Thanks, this was very helpful after we discovered Amazon RDS Oracle instances still had DBMS Scheduler running UTC even after we changed the instances to ET.

  8. awesome note!! Save me lots of headache!! Thanks

  9. Liked your way of explaining stuff. Helped a lot.

  10. Thank you for this great explanation. Very clear what is going on and how to change this window. Oracle should learn from examples like this.

  11. Fantastic! great explanation and examples, very useful.

  12. Excellent walkthrough, saved me some time and helped me locate my own issues. Thank you!

  13. Muhammad Rahman

    nice post

  14. Vinothini A

    Me too faced the same issue and just now testing in TEST environment. Thanks a lot for your blog

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s