标题: Scheduled Maintenance Auto Tasks Not Working After 11.2.0.3 Upgrade (文档 ID ... [打印本页] 作者: 郑全 时间: 2017-4-21 12:56 标题: 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.