Oracle maintenance window -- Change scheduled Window time

 

As most of us are  aware Oracle  default maintenance window starts from 10pm server time for weekdays and   at 6am  for Weekends .   There  are situations where this time clashes with Application  jobs and we need to change default maintenance window time .

Below is step we followed to change window time for all days .
 

What Happens During maintenance Widow : 

In oracle Database 10g, we had only the first two automatic maintenance tasks mentioned below. With Oracle 11g, we have a third task name  “Automatic SQL Tunning Advisor”

Automatic Optimizer Statistics Collection:

Collects optimizer statistics for all schema objects in the database for which there are no statistics or only stale statistics. The statistics gathered by this task are used by the SQL query optimizer to improve the performance of SQL execution.
 
Automatic Segment Advisor:
Identifies segments that have space available for reclamation, and makes recommendations on how to de-fragment those segments.
 
Automatic SQL Tuning Advisor :
Examines the performance of high-load SQL statements, and makes recommendations on how to tune those statements.

To view the task, run the below SQL

 SQL> select client_name from DBA_AUTOTASK_CLIENT;

 
CLIENT_NAME
----------------------------------------------------------------
auto optimizer stats collection
auto space advisor
sql tuning advisor
 
 SELECT client_name, status, consumer_group, window_group
FROM dba_autotask_client
ORDER BY client_name;

 SELECT client_name, status FROM dba_autotask_operation;
CLIENT_NAME                                                      STATUS  
---------------------------------------------------------------- --------
auto optimizer stats collection                                  ENABLED  
auto space advisor                                                    ENABLED  
sql tuning advisor                                                     ENABLED  

Check existing window  timing .

set line 400
col window_name format a20 
select window_name,window_next_time from DBA_AUTOTASK_WINDOW_CLIENTS;
select window_name, REPEAT_INTERVAL, DURATION, NEXT_START_DATE from DBA_SCHEDULER_WINDOWS; 
 

Change  maintenance window  schedule as below  to  2pm    Server  time 

SQL> select window_name,window_next_time from DBA_AUTOTASK_WINDOW_CLIENTS;

WINDOW_NAME                    WINDOW_NEXT_TIME
------------------------------ --------------------------------------
MONDAY_WINDOW                  14-MAY-12 10.00.00.000000 PM EST5EDT
TUESDAY_WINDOW                 08-MAY-12 10.00.00.000000 PM EST5EDT
WEDNESDAY_WINDOW               09-MAY-12 10.00.00.000000 PM EST5EDT
THURSDAY_WINDOW                10-MAY-12 10.00.00.000000 PM EST5EDT
FRIDAY_WINDOW                  11-MAY-12 10.00.00.000000 PM EST5EDT
SATURDAY_WINDOW                12-MAY-12 06.00.00.000000 AM EST5EDT
SUNDAY_WINDOW                  13-MAY-12 06.00.00.000000 AM EST5EDT
TEST_WINDOW                    09-MAY-12 05.00.00.000000 AM EST5EDT

BEGIN
DBMS_SCHEDULER.set_attribute (
name => 'MONDAY_WINDOW',
attribute => 'repeat_interval',
VALUE => 'freq=daily;byday=MON;byhour=14;byminute=0;bysecond=0');
END;
/
BEGIN
DBMS_SCHEDULER.set_attribute (
name => 'TUESDAY_WINDOW',
attribute => 'repeat_interval',
VALUE => 'freq=daily;byday=TUE;byhour=14;byminute=0;bysecond=0');
END;
/

BEGIN
DBMS_SCHEDULER.set_attribute (
name => 'WEDNESDAY_WINDOW',
attribute => 'repeat_interval',
VALUE => 'freq=daily;byday=WED;byhour=14;byminute=0;bysecond=0');
END;
/



BEGIN
DBMS_SCHEDULER.set_attribute (
name => 'THURSDAY_WINDOW',
attribute => 'repeat_interval',
VALUE => 'freq=daily;byday=THU;byhour=14;byminute=0;bysecond=0');
END;
/


BEGIN
DBMS_SCHEDULER.set_attribute (
name => 'FRIDAY_WINDOW',
attribute => 'repeat_interval',
VALUE => 'freq=daily;byday=FRI;byhour=14;byminute=0;bysecond=0');
END;
/


BEGIN
DBMS_SCHEDULER.set_attribute (
name => 'SATURDAY_WINDOW',
attribute => 'repeat_interval',
VALUE => 'freq=daily;byday=SAT;byhour=14;byminute=0;bysecond=0');
END;
/


BEGIN
DBMS_SCHEDULER.set_attribute (
name => 'SUNDAY_WINDOW',
attribute => 'repeat_interval',
VALUE => 'freq=daily;byday=SUN;byhour=14;byminute=0;bysecond=0');
END;
/

Best practice is  that you must use the DBMS_SCHEDULER.DISABLE subprogram to disable the window before making changes to it, and then re-enable the window with DBMS_SCHEDULER.ENABLE when you are finished. If you change a window when it is currently open, the change does not take effect until the next time the window opens.

BEGIN 

dbms_scheduler.disable( name => 'MONDAY_WINDOW');

dbms_scheduler.set_attribute( name => 'MONDAY_WINDOW', 

attribute => 'DURATION',

 value => numtodsinterval(3, 'hour')); 

dbms_scheduler.enable( name => 'MONDAY_WINDOW');

END;

See if there is a window currently open. If there is, you can try closing it to see if that helps .

select * from DBA_SCHEDULER_GLOBAL_ATTRIBUTE where attribute_name='CURRENT_OPEN_WINDOW';

exec DBMS_SCHEDULER.close_window ('WEEKNIGHT_WINDOW');

Creating the new Maintenance Window
 
To create a maintenance window, you must create a Scheduler window and then add it to the window group MAINTENANCE_WINDOW_GROUP
 
 
The DBMS_SCHEDULER PL/SQL package provides the ADD_WINDOW_GROUP_MEMBER subprogram, which adds a window to a window group
 
 BEGIN
dbms_scheduler.create_window(
    window_name     => 'TEST_WINDOW',
    duration        =>  numtodsinterval(1, 'hour'),
    resource_plan   => 'DEFAULT_MAINTENANCE_PLAN',
    repeat_interval => 'FREQ=DAILY;BYHOUR=5;BYMINUTE=0;BYSECOND=0');
    dbms_scheduler.add_window_group_member(
    group_name  => 'MAINTENANCE_WINDOW_GROUP',
    window_list => 'TEST_WINDOW');
END;

Change Run Duration of Window : 

In case we want to control window how long 

EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('WEEKNIGHT_WINDOW', 'duration', '+000 06:00:00');

SELECT *

FROM USER_SCHEDULER_JOB_RUN_DETAILS 

ORDER BY LOG_DATE DESC  ;

How to configure (disable/enable) the Automated Maintenance Tasks
 
DBMS_AUTO_TASK_ADMIN PL/SQL package can be used to do the following.
  
To enable the task:
 
BEGIN
dbms_auto_task_admin.disable(
    client_name => 'sql tuning advisor',
    operation   => NULL,
    window_name => NULL);
END;
 
 
To enable it
 
BEGIN
dbms_auto_task_admin.enable(
    client_name => 'sql tuning advisor',
    operation   => NULL,
    window_name => NULL);
END;

Reference : 

How to Change The Automated Maintenance Tasks Weekend Schedule (Doc ID 2036557.1)