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;
select client_name, status FROM dba_autotask_operation;
BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
/
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;
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