Scheduled Maintenance Auto Tasks Not Working After 11.2.0.3 Upgrade (文档 ID ...

郑全

In this Document

Symptoms
Cause
Solution


Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
Information in this document applies to any platform.
Symptoms
Auto Tasks not working after upgrade to 11.2.0.3.
Cause
As per output of the following:

SQL> column next_start_date format a36
SQL> column comments format a38
SQL> select window_group_name, enabled, number_of_windows, next_start_date, comments from dba_scheduler_window_groups;
WINDOW_GROUP_NAME              ENABL NUMBER_OF_WINDOWS
------------------------------ ----- -----------------
NEXT_START_DATE                      COMMENTS
------------------------------------ --------------------------------------
MAINTENANCE_WINDOW_GROUP       TRUE                  7
03-APR-12 10.00.00.000000 PM CST6CDT Window group for Automated Maintenance
ORA$AT_WGRP_OS                 TRUE                  7
03-APR-12 10.00.00.000000 PM CST6CDT auto optimizer stats collection
ORA$AT_WGRP_SA                 TRUE                  7
03-APR-12 10.00.00.000000 PM CST6CDT auto space advisor
ORA$AT_WGRP_SQ                 TRUE                  7
03-APR-12 10.00.00.000000 PM CST6CDT sql tuning advisor

The problem seems to be due to the MAINTENANCE_WINDOW_GROUP being set to start in the past.
  
Solution
Please drop the maintenance window and recreate it:
Login as SYSDBA
@?/rdbms/admin/catnomwn.sql -- this drops the maintenance window, it will give some errors that can be ignored.
-- Drop the windows manually:
execute dbms_scheduler.drop_window('MONDAY_WINDOW');
execute dbms_scheduler.drop_window('TUESDAY_WINDOW');
execute dbms_scheduler.drop_window('WEDNESDAY_WINDOW');
execute dbms_scheduler.drop_window('THURSDAY_WINDOW');
execute dbms_scheduler.drop_window('FRIDAY_WINDOW');
execute dbms_scheduler.drop_window('SATURDAY_WINDOW');
execute dbms_scheduler.drop_window('SUNDAY_WINDOW');
@?/rdbms/admin/catmwin.sql -- this recreates it

After that check if the Auto tasks run as expected.

Another solution is to close the active windows and then reset the START_DATE for a date in the future
To close the window you can use EXECUTE DBMS_SCHEDULER.CLOSE_WINDOW procedure
To reset the START_DATE you can use the EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE procedure.