开发者社区> jimbuster> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

Troubleshooting Scheduler Autotask Issues (Doc ID 1561498.1)

简介: In this Document   Purpose   Troubleshooting Steps   References   APPLIES TO: Oracle Database - Enterprise Edition - Version 11.
+关注继续查看

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
spool /tmp/dba_autotask_client.html
set markup html on
select * from DBA_AUTOTASK_CLIENT;
select * from DBA_AUTOTASK_CLIENT_HISTORY;
select * from DBA_AUTOTASK_CLIENT_JOB;
select * from DBA_AUTOTASK_JOB_HISTORY order by JOB_START_TIME;
select * from DBA_AUTOTASK_OPERATION;
select * from DBA_AUTOTASK_SCHEDULE order by START_TIME;
select * from DBA_AUTOTASK_TASK;
select * from DBA_AUTOTASK_WINDOW_CLIENTS;
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 );
execute DBMS_AUTO_TASK_ADMIN.DISABLE;
execute DBMS_AUTO_TASK_ADMIN.ENABLE;
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);
exec dbms_autotask_prvt.setup(0);            
exec dbms_scheduler.add_window_group_member('MAINTENANCE_WINDOW_GROUP','TEST_WINDOW');
exec dbms_autotask_prvt.setup(3); 

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');

@?/rdbms/admin/catmwin.sql -- this recreates them

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 
@$ORACLE_HOME/rdbms/admin/catmwin.sql   -- this recreates

 

 

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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
flink1.7.2 tableapi批处理示例
- 主要操作包括: 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用户?
转自:http://www.askmaclean.com/archives/aix-11-2-grid-infrastructure-rdbms-owned-by-grid.
961 0
StringUtils的工具类isBlank与isEmply
1. public static boolean isEmpty(String str)     判断某字符串是否为空,为空的标准是 str==null 或 str.length()==0     下面是 StringUtils 判断是否为空的示例:     StringUtils.
801 0
为11.2.0.2 Grid Infrastructure添加节点
转自:http://www.askmaclean.com/archives/add-node-to-11-2-0-2-grid-infrastructure.html 在之前的文章中我介绍了为10g RAC Cluster添加节点的具体步骤。
912 0
+关注
jimbuster
从事数据库管理和运维
280
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载