# Troubleshooting Scheduler Autotask Issues (Doc ID 1561498.1)

+关注继续查看

In this Document

 Purpose
 Troubleshooting Steps
 References

## APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.1.0.6 and later
Information in this document applies to any platform.

## PURPOSE

This document aims to provide troubleshooting steps and scripts to help solve some known Scheduler Autotasks or Maintainance issues. The most common problems are either    windows stopped running or they do run but tasks are not invoked.

## TROUBLESHOOTING STEPS

1. The following script gathers most of the information needed to troubleshoot the problem, the output is generated in a well formatted (HTML) file for the ease of viewing and analyzing

ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR TZD';
ALTER SESSION SET NLS_DATE_FORMAT ='DD-MM-YYYY HH24:MI:SS';
set pagesize 9999
set markup html on
select * from DBA_AUTOTASK_JOB_HISTORY order by JOB_START_TIME;
select * from DBA_AUTOTASK_SCHEDULE order by START_TIME;
select * from DBA_AUTOTASK_WINDOW_HISTORY order by WINDOW_START_TIME;
select * from dba_scheduler_windows;
select * from dba_scheduler_window_groups;
select * from dba_scheduler_job_run_details order by ACTUAL_START_DATE;
select * from DBA_SCHEDULER_JOB_LOG;
SELECT program_name, program_action, enabled FROM dba_scheduler_programs;
spool off

2. The following script disables and reenables Autotasks, it also creates a test window to check whether the window is running and the tasks are being invoked during its open time as expected

exec dbms_isched.set_scheduler_attribute('SCHEDULER_DISABLED','FALSE','SYS',16 );
exec dbms_scheduler.create_window(window_name=>'TEST_WINDOW',resource_plan=>'DEFAULT_MAINTENANCE_PLAN',repeat_interval=>'freq=daily;byday=WED;byhour=12;' ||'byminute=20; bysecond=0',duration=>interval '4' hour,comments=>'TEST window for maintenance tasks'); -- Please modify this command to create a test window in an appropriate time for your system (and doesn't interfere with already created windows)
exec dbms_scheduler.set_attribute('TEST_WINDOW','SYSTEM',TRUE);
exec dbms_scheduler.set_attribute('TEST_WINDOW','FOLLOW_DEFAULT_TIMEZONE',TRUE);

3. If one of the windows was open when it shouldn't or in other words "DBA_SCHEDULER_WINDOWS.ACTIVE=TRUE" during hours where the window should be closed then please close the window manually. Please replace SATURDAY_WINDOW with the appropriate window name

EXECUTE DBMS_SCHEDULER.CLOSE_WINDOW ('SATURDAY_WINDOW');

4. If step 3 didn't help or if "DBA_AUTOTASK_WINDOW_CLIENTS.WINDOW_NEXT_TIME" showed a date in the past then please drop and recreate the windows

@?/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');

5. If the tasks stopped working then possibly the last successful (or failing) job is still stuck, if DBA_AUTOTASK_TASK showed a job consistently present then try to drop this job

exec DBMS_SCHEDULER.drop_job (job_name => 'ORA$AT_OS_OPT_SY_2611',force =>TRUE); 6. If the Windows were running but auto optimizer stats collection is not running then check DBA_SCHEDULER_PROGRAMS.PROGRAM_NAME, if you didn't find an entry for "GATHER_STATS_PROG" then gather statistics manually and create the windows as follows: exec dbms_stats.gather_database_stats_job_proc; @$ORACLE_HOME/rdbms/admin/catnomwn.sql  -- this drops

## REFERENCES

NOTE:1320246.1 - Why Auto Optimizer Statistics Collection May Appear to be "Stuck"?
BUG:16599612 - AUTO TASKS NOT RUNNING:4W
BUG:16787364 - AUTOTASK STAS JOB IS NOT RUNNING

- 主要操作包括: print table,DataSet 转换成table,Scan,select,as,where / filter,groupBy,distinct,join,leftOuterJoin,rightOuterJoin union,unionAll,...
1346 0
How the Public Cloud Increases DevOps Success
The public cloud can help you to improve DevOps workflows and make application delivery more efficient and reliable.
1331 0
AIX平台上11.2 Grid Infrastructure RDBMS进程的user是grid用户?

961 0
StringUtils的工具类isBlank与isEmply
1. public static boolean isEmpty(String str)   　　判断某字符串是否为空，为空的标准是 str==null 或 str.length()==0   　　下面是 StringUtils 判断是否为空的示例：   　　StringUtils.
801 0

912 0
+关注
jimbuster

280

0