Enable Auto Task Maintenance Jobs in Oracle Scheduler Window Manually

There are 3 auto task maintenance jobs in Oracle and they are listed below.
1.   auto optimizer stats collection
2.   auto space advisor
3.   sql tuning advisor

You can use below mentioned SQL to list the status of the jobs.
select client_name, status

from dba_autotask_client
where client_name in ( 'auto space advisor', 'auto optimizer stats collection','sql tuning advisor');

CLIENT_NAME                                                      STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection                                  DISABLED
auto space advisor                                               DISABLED
sql tuning advisor                                               DISABLED

If you wanted to drop and recreate these jobs manually you can use these steps.

Here is the query to list the group details for the jobs.

SELECT CLIENT_NAME,WINDOW_GROUP FROM DBA_AUTOTASK_CLIENT;
SQL > /

CLIENT_NAME
--------------------------
WINDOW_GROUP
--------------------------
auto optimizer stats collection
ORA$AT_WGRP_OS

auto space advisor
ORA$AT_WGRP_SA

sql tuning advisor
ORA$AT_WGRP_SQ

Here is the PL/SQL block to drop the window group. Below one will drop Optimizer Stats group 'ORA$AT_WGRP_OS'. Similarly you can use above window group for the respective auto task jobs deletion.

BEGIN
  DBMS_SCHEDULER.drop_window_group (
    group_name => 'ORA$AT_WGRP_OS',
    force      => TRUE);
END;
/

If you wanted to schedule a job again after the drop you can follow these steps. Mainly two things you have to take care while creating the window and group member. First window resource_plan must be 'DEFAULT_MAINTENANCE_PLAN'. Secondly the group_name should be 'MAINTENANCE_WINDOW_GROUP'.

BEGIN
dbms_scheduler.create_window(
      window_name     => 'SATURDAY_WINDOW',
      duration        =>  numtodsinterval(1, 'hour'),
      resource_plan   => 'DEFAULT_MAINTENANCE_PLAN',
      repeat_interval => 'FREQ=WEEKLY;BYDAY=SAT;BYHOUR=22;BYMINUTE=0;BYSECOND=0');
    dbms_scheduler.add_group_member(
      group_name  => 'MAINTENANCE_WINDOW_GROUP',
      member      => 'SATURDAY_WINDOW');
END;
/

PL/SQL procedure successfully completed.

Now the all the jobs are disabled status.
SQL > select WINDOW_NAME,OPTIMIZER_STATS,SEGMENT_ADVISOR,SQL_TUNE_ADVISOR                                                                    , WINDOW_NEXT_TIME from DBA_AUTOTASK_WINDOW_CLIENTS;

WINDOW_NAME        OPTIMIZE SEGMENT_ SQL_TUNE WINDOW_NEXT_TIME
------------------ -------- -------- -------- ----------------------------------------
SATURDAY_WINDOW    DISABLED DISABLED DISABLED 11-JUL-15 10.00.00.000000 PM PST8PDT

Subsequently you have to create another window group with respect to each jobs as mentioned below.

BEGIN
  DBMS_SCHEDULER.CREATE_window_group (
    group_name  => 'ORA$AT_WGRP_SQ',
    window_list => 'SATURDAY_WINDOW',
    comments    => 'sql tuning advisor');
END;
/
BEGIN
  DBMS_SCHEDULER.CREATE_window_group (
    group_name  => 'ORA$AT_WGRP_OS',
    window_list => 'SATURDAY_WINDOW',
    comments    => 'auto optimizer stats collection');
END;
/
BEGIN
  DBMS_SCHEDULER.CREATE_window_group (
    group_name  => 'ORA$AT_WGRP_SA',
    window_list => 'SATURDAY_WINDOW',
    comments    => 'auto space advisor');
END;
/
Next enable the jobs using following commands.

BEGIN
  dbms_auto_task_admin.enable(
    client_name => 'sql tuning advisor',
    operation   => NULL,
    window_name => NULL);
END;
/

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

BEGIN
  dbms_auto_task_admin.enable(
    client_name => 'auto space advisor',
    operation   => NULL,
    window_name => NULL);
END;
/
 select WINDOW_NAME,OPTIMIZER_STATS,SEGMENT_ADVISOR,SQL_TUNE_ADVISOR                                                                    , WINDOW_NEXT_TIME from DBA_AUTOTASK_WINDOW_CLIENTS;

WINDOW_NAME        OPTIMIZE SEGMENT_ SQL_TUNE WINDOW_NEXT_TIME
------------------ -------- -------- -------- ----------------------------------------

SATURDAY_WINDOW    ENABLED  ENABLED  ENABLED  11-JUL-15 10.00.00.000000 PM PST8PDT
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google