Automatic Maintenance Window Troubleshooting


exec DBMS_AUTO_TASK_ADMIN.ENABLE('auto optimizer stats collection', null, null);
exec DBMS_AUTO_TASK_ADMIN.ENABLE('auto space advisor', null, null);
exec DBMS_AUTO_TASK_ADMIN.ENABLE('sql tuning advisor', null, null);

set markup html on spool on
set lines 250
col attributes for a50
spool DBA_AUTOTASK_OPERATION_n_CLIENT_op.html
select * from DBA_AUTOTASK_OPERATION;
select * from DBA_AUTOTASK_CLIENT;
show parameter JOB_QUEUE_PROCESSES
exit;

set lines 3000
col window_name form a30
col client_name form a30
col REPEAT_INTERVAL form a60
SELECT WINDOW_NAME,SEGMENT_ADVISOR from DBA_AUTOTASK_WINDOW_CLIENTS;
SELECT CLIENT_NAME,STATUS,JOB_SCHEDULER_STATUS,TO_CHAR(LAST_GOOD_DATE,'DD-MON-YY HH24:MI:SS') AS "LAST SUCCESSFUL RUN" FROM DBA_AUTOTASK_TASK;
SELECT CLIENT_NAME,WINDOW_NAME,TO_CHAR(WINDOW_START_TIME,'DD-MON-YY HH24:MI:SS') WINDOW_START_TIME,WINDOW_DURATION,JOBS_STARTED,JOBS_COMPLETED,TO_CHAR
(WINDOW_END_TIME,'DD-MON-YY HH24:MI:SS') WINDOW_END_TIME FROM DBA_AUTOTASK_CLIENT_HISTORY;
SELECT WINDOW_NAME ,LAST_START_DATE,ENABLED ,ACTIVE FROM DBA_SCHEDULER_WINDOWS;
SHOW PARAMETER "_enable_automatic_maintenance"
SELECT WINDOW_NAME,REPEAT_INTERVAL,DURATION FROM DBA_SCHEDULER_WINDOWS;
select window_name,enabled,active,resource_plan,comments from dba_scheduler_windows;
select log_date,window_name,req_start_date,actual_start_date,window_duration,actual_duration,additional_info
from dba_scheduler_window_details
where log_date > sysdate -7 order by log_date desc;

Automatic Task Status

select client_name, status FROM dba_autotask_operation;

Disable Automatic Stats Gathering

BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
/

Automatic Task Schedule

select window_name, to_char(start_time,'dd-mon-rr hh24:mi') start_time, duration
from dba_autotask_schedule
-- where window_name ='TUESDAY_WINDOW'
order by window_name, start_time desc;

Change start time for maintenance window

BEGIN
DBMS_SCHEDULER.DISABLE(
name=>'"SYS"."FRIDAY_WINDOW"',
force=>TRUE);
END;
/

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."FRIDAY_WINDOW"',
attribute=>'REPEAT_INTERVAL',
value=>'FREQ=WEEKLY;BYDAY=TUE;BYHOUR=15;BYMINUTE=30;BYSECOND=0');
END;
/

BEGIN
DBMS_SCHEDULER.ENABLE(
name=>'"SYS"."FRIDAY_WINDOW"');
END;
/


Change Duration

BEGIN
DBMS_SCHEDULER.DISABLE(
name=>'"SYS"."FRIDAY_WINDOW"',
force=>TRUE);
END;
/
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."FRIDAY_WINDOW"',
attribute=>'DURATION',
value=>numtodsinterval(120, 'minute'));
END;
/
BEGIN
DBMS_SCHEDULER.ENABLE(
name=>'"SYS"."FRIDAY_WINDOW"');
END;
/

Please check the value of this parameter . If it is set to zero ,the jobs will not run .

set heading on
col name for a30
col describ for a70
col value for a20
set lin 200

SELECT x.ksppinm NAME, y.ksppstvl VALUE, y.ksppstdf Deflt, x.ksppdesc describ
FROM SYS.x$ksppi x, SYS.x$ksppcv y
WHERE x.inst_id = USERENV ('Instance')
AND y.inst_id = USERENV ('Instance')
AND x.indx = y.indx
AND x.ksppinm = '_enable_automatic_maintenance';

If it is zero , then change to 1 .

alter system set "_enable_automatic_maintenance"=1 ;

If the above does not help - please try the following .

Recycle the Scheduler

--------------------------------

recycle the scheduler once to see if that resolves the issue.

Check/wait till all scheduler jobs have been completed:

select owner,job_name from dba_scheduler_running_jobs;
--Disable the scheduler
exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'TRUE');
alter system set job_queue_processes=0;
exec dbms_ijob.set_enabled(FALSE);

--Flush shared pool once
alter system flush shared_pool;
alter system flush shared_pool;

--Enable the scheduler
exec dbms_ijob.set_enabled(TRUE);
alter system set job_queue_processes=value; --- 4 or 10 or any reasonable value.
exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'FALSE');

Reference ==> IF:11g Autotask Jobs Are Not Running as Scheduled. ( Doc ID 2084941.1 )




Schedule Maintenance Windows

/* BEGIN
DBMS_SCHEDULER.DISABLE(name=>'"SYS"."FRIDAY_WINDOW"',force=>TRUE);END;
/
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."FRIDAY_WINDOW"',
attribute=>'REPEAT_INTERVAL',
value=>'FREQ=WEEKLY;BYDAY=TUE;BYHOUR=18;BYMINUTE=00;BYSECOND=0');
END;
/
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."FRIDAY_WINDOW"',
attribute=>'DURATION',
value=>numtodsinterval(120, 'minute'));
END;
/
BEGIN
DBMS_SCHEDULER.ENABLE(
name=>'"SYS"."FRIDAY_WINDOW"');
END;
/
*/
set lines 180
set pages 55
ttitle 'Schedule for DB Maintenance Window'
SELECT WINDOW_NAME, to_char(START_TIME,'DD-Mon-RR hh24:mi') START_TIME, DURATION
 FROM DBA_AUTOTASK_SCHEDULE
-- WHERE WINDOW_NAME='FRIDAY_WINDOW'
ORDER BY WINDOW_NAME, START_TIME DESC;
clear ttitle

Automate Scheduling - Dashboards

  BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
            job_name => '"DMON"."LAM_STATUS_DASHBOARD"',
            job_type => 'PLSQL_BLOCK',
            job_action => 'declare
  v_dummy char;
begin
  dmon.lam.gendata;
end;
',
            number_of_arguments => 0,
            start_date => TO_TIMESTAMP_TZ('2019-10-16 21:35:48.274173000 +02:00','YYYY-MM-DD HH24:MI:SS.FF TZR'),
            repeat_interval => 'FREQ=MINUTELY;INTERVAL=30',
            end_date => NULL,
            enabled => FALSE,
            auto_drop => FALSE,
            comments => 'Data Collection for LAM Dashboard');

    DBMS_SCHEDULER.SET_ATTRIBUTE( 
             name => '"DMON"."LAM_STATUS_DASHBOARD"', 
             attribute => 'restartable', value => TRUE);
    DBMS_SCHEDULER.SET_ATTRIBUTE( 
             name => '"DMON"."LAM_STATUS_DASHBOARD"', 
             attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_RUNS);
    DBMS_SCHEDULER.SET_ATTRIBUTE( 
             name => '"DMON"."LAM_STATUS_DASHBOARD"', 
             attribute => 'raise_events', value => '236');
  
    DBMS_SCHEDULER.enable(
             name => '"DMON"."LAM_STATUS_DASHBOARD"');
END;

HOME