ITPub博客

首页 > 数据库 > Oracle > 自动维护作业 AUTO TASK 管理

自动维护作业 AUTO TASK 管理

原创 Oracle 作者:jhon_lee 时间:2015-07-24 14:03:11 0 删除 编辑

1.监视:

视图DBA_AUTOTASK_JOB_HISTORY来跟踪自动作业情况。

可以通过查询视图DBA_AUTOTASK_HISTORY追踪job运行情况:

set line 160 pages 500

col client_name for a40

col window_name for a20

col job_name for  a30

col job_start_time for a45

col job_status for a10

select client_name,window_name,job_name,job_start_time,job_status

from dba_autotask_job_history

order by 4,1;

 

 CLIENT_NAME          JOB_NAME             JOB_START_TIME
-------------------- -------------------- ------------------------------------
auto optimizer stats ORA$AT_OS_OPT_SY_1   09-APR-12 10.00.02.039000 PM +08:00
auto space advisor   ORA$AT_SA_SPC_SY_2   09-APR-12 10.00.02.050000 PM +08:00
sql tuning advisor   ORA$AT_SQ_SQL_SW_3   09-APR-12 10.00.02.015000 PM +08:00

 

2.自动收集作业状态:

SQL> select client_name,status,WINDOW_GROUP from dba_autotask_client;

 

 

CLIENT_NAME                                                      STATUS   WINDOW_GROUP

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

auto optimizer stats collection                          DISABLED ORA$AT_WGRP_OS

auto space advisor                                               ENABLED  ORA$AT_WGRP_SA

sql tuning advisor                                               ENABLED  ORA$AT_WGRP_SQ

 

 

启用和禁止维护任务:

使用DBMS_AUTO_ADMIN pl/sql包来启用或禁用任务:

禁用任务:

BEGIN

dbms_auto_task_admin.disable(

    client_name => 'auto optimizer stats collection',

    operation   => NULL,

    window_name => NULL);

END;

启用任务:

BEGIN

dbms_auto_task_admin.enable(

    client_name => 'auto optimizer stats collection',

    operation   => NULL,

    window_name => NULL);

END;

 

 

3.自动作业 job 与 窗口 的关系

SYS@edw1> select * from DBA_AUTOTASK_WINDOW_CLIENTS;

 

WINDOW_NAME          WINDOW_NEXT_TIME                                                            WINDO AUTOTASK OPTIMIZE SEGMENT_ SQL_TUNE HEALTH_M

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

MONDAY_WINDOW        01-JUN-15 10.00.00.000000 PM PRC                                            FALSE ENABLED  ENABLED  ENABLED  ENABLED  DISABLED

TUESDAY_WINDOW       02-JUN-15 10.00.00.000000 PM PRC                                            FALSE ENABLED  ENABLED  ENABLED  ENABLED  DISABLED

WEDNESDAY_WINDOW     03-JUN-15 10.00.00.000000 PM PRC                                            FALSE ENABLED  ENABLED  ENABLED  ENABLED  DISABLED

THURSDAY_WINDOW      04-JUN-15 10.00.00.000000 PM PRC                                            FALSE ENABLED  ENABLED  ENABLED  ENABLED  DISABLED

FRIDAY_WINDOW        29-MAY-15 10.00.00.000000 PM PRC                                            FALSE ENABLED  ENABLED  ENABLED  ENABLED  DISABLED

SATURDAY_WINDOW      30-MAY-15 06.00.00.000000 AM PRC                                            FALSE ENABLED  ENABLED  ENABLED  ENABLED  DISABLED

SUNDAY_WINDOW        31-MAY-15 06.00.00.000000 AM PRC                                            FALSE ENABLED  ENABLED  ENABLED  ENABLED  DISABLED

 

为某一个维护作业启用或禁用维护窗口

缺省情况下,所有维护任务在所有预定义的维护窗口都运行。可以对某一个维护窗口启用或禁用自动化任务。

BEGIN

dbms_auto_task_admin.disable(

client_name => 'sql tuning advisor',

operation   => NULL,

window_name => 'MONDAY_WINDOW');

END;

 

 

 

窗口信息:

select t1.window_name, t1.repeat_interval, t1.duration

  from dba_scheduler_windows t1, dba_scheduler_wingroup_members t2

 where t1.window_name = t2.window_name

   and t2.window_group_name in

       ('MAINTENANCE_WINDOW_GROUP', 'BSLN_MAINTAIN_STATS_SCHED');

  

WINDOW_NAME          REPEAT_INTERVAL                                                                                      DURATION

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

MONDAY_WINDOW            freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0                                                +000 04:00:00

TUESDAY_WINDOW            freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0                                                +000 04:00:00

WEDNESDAY_WINDOW     freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0                                                +000 04:00:00

THURSDAY_WINDOW        freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0                                                +000 04:00:00

FRIDAY_WINDOW              freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0                                                +000 04:00:00

SATURDAY_WINDOW       freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0                                                 +000 20:00:00

SUNDAY_WINDOW           freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0                                                 +000 20:00:00

 

修改窗口属性:

BEGIN

dbms_scheduler.set_attribute(

    name      => '"SYS"."THURSDAY_WINDOW"',

    attribute => 'DURATION',

    value     => numtodsinterval(12, 'hour'));

END;

/        

 

BEGIN

  DBMS_SCHEDULER.SET_ATTRIBUTE(

  name => '"SYS"."FRIDAY_WINDOW"',

  attribute => 'REPEAT_INTERVAL',

  value => 'FREQ=WEEKLY;BYDAY=FRI;BYHOUR=16;BYMINUTE=0;BYSECOND=0');

END;

/        

 

 

 

配置维护窗口:

 

可是使用DBMS_SCHEDULER 包来修改窗口属性。

1:修改维护窗口

--先禁用维护窗口

BEGIN

dbms_scheduler.disable(

    name  => 'SATURDAY_WINDOW');

--修改维护窗口属性:

dbms_scheduler.set_attribute(

    name      => 'SATURDAY_WINDOW',

    attribute => 'DURATION',

    value     => numtodsinterval(4, 'hour'));

--启用维护窗口  www.2cto.com 

dbms_scheduler.enable(

    name => 'SATURDAY_WINDOW');

END;

/

对于当前打开的窗口,你需要首先禁用,然后修改再启用,配置立即生效,如果你不通过这三个过程来修改属性,属性是不会生效的,直到下一次窗口打开。

2:创建新窗口:

BEGIN

dbms_scheduler.create_window(

    window_name     => 'EARLY_MORNING_WINDOW',

    duration        =>  numtodsinterval(1, 'hour'),

    resource_plan   => 'DEFAULT_MAINTENANCE_PLAN',

    repeat_interval => 'FREQ=DAILY;BYHOUR=5;BYMINUTE=0;BYSECOND=0');

dbms_scheduler.add_window_group_member(

    group_name  => 'MAINTENANCE_WINDOW_GROUP',

    window_list => 'EARLY_MORNING_WINDOW');

END;

/

3:删除窗口:

BEGIN

DBMS_SCHEDULER.REMOVE_WINDOW_GROUP_MEMBER(

    group_name  => 'MAINTENANCE_WINDOW_GROUP',

    window_list => 'EARLY_MORNING_WINDOW');

END;

/

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26474945/viewspace-1749566/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2012-07-07

  • 博文量
    42
  • 访问量
    288169