Please see my other blog for Oracle EBusiness Suite Posts - EBMentors

Search This Blog

Note: All the posts are based on practical approach avoiding lengthy theory. All have been tested on some development servers. Please don’t test any post on production servers until you are sure.

Sunday, April 07, 2013

Managing Automated Maintenance Tasks

Automated maintenance tasks are tasks that are started automatically at regular intervals to perform maintenance operations on the database. These tasks run automatically by the database and are executed during the "maintenance window" which is a contiguous time interval during which automated maintenance tasks are run under.
Oracle Database 11g comes with three predefined automatic maintenance tasks:
  • The Automatic Optimizer Statistics Collection task collects statistics for all objects with no or stale statistics.
  • The Automatic Segment Advisor task provides advice on which database segments have free space that can be reclaimed.
  • Automatic SQL Tuning Advisor task, which examines SQL statement performance and makes SQL profile recommendations to improve the statements.
The Automated Maintenance Tasks are managed by the AutoTask Background Process (ABP). Following are the process functionalties:
  •  It converts automatic tasks into Scheduler jobs. It does not execute the maintenance tasks.
  •  It determines the jobs that need to be created for each maintenance task window.
  •  It stores task execution history in the SYSAUX tablespace
Task Information
 To view the task, run the below SQL
select client_name from DBA_AUTOTASK_CLIENT;
CLIENT_NAME
auto optimizer stats collection
auto space advisor
sql tuning advisor
In Oracle database 11g, there are seven predefined maintenance windows. Their names are from MONDAY_WINDOW to SUNDAY_WINDOW.

To display list schedule of the predefined maintenance windows in the next 32 days, issue the following query:

SELECT WINDOW_NAME, to_char(START_TIME,'DD-Mon-RR hh24:mi') START_TIME, DURATION
FROM DBA_AUTOTASK_SCHEDULE
ORDER BY WINDOW_NAME, START_TIME DESC;



WINDOW_NAME START_TIME DURATION
FRIDAY_WINDOW 26-Apr-13 22:00 +00 04:00:00.000000
FRIDAY_WINDOW 19-Apr-13 22:00 +00 04:00:00.000000
FRIDAY_WINDOW 12-Apr-13 22:00 +00 04:00:00.000000
FRIDAY_WINDOW 03-May-13 22:00 +00 04:00:00.000000
MONDAY_WINDOW 29-Apr-13 22:00 +00 04:00:00.000000
MONDAY_WINDOW 22-Apr-13 22:00 +00 04:00:00.000000
MONDAY_WINDOW 15-Apr-13 22:00 +00 04:00:00.000000
MONDAY_WINDOW 08-Apr-13 22:00 +00 04:00:00.000000
MONDAY_WINDOW 06-May-13 22:00 +00 04:00:00.000000
SATURDAY_WINDOW 27-Apr-13 06:00 +00 20:00:00.000000
SATURDAY_WINDOW 20-Apr-13 06:00 +00 20:00:00.000000
SATURDAY_WINDOW 13-Apr-13 06:00 +00 20:00:00.000000
SATURDAY_WINDOW 04-May-13 06:00 +00 20:00:00.000000
SUNDAY_WINDOW 28-Apr-13 06:00 +00 20:00:00.000000
SUNDAY_WINDOW 21-Apr-13 06:00 +00 20:00:00.000000
SUNDAY_WINDOW 14-Apr-13 06:00 +00 20:00:00.000000
SUNDAY_WINDOW 05-May-13 06:00 +00 20:00:00.000000
THURSDAY_WINDOW 25-Apr-13 22:00 +00 04:00:00.000000
THURSDAY_WINDOW 18-Apr-13 22:00 +00 04:00:00.000000
THURSDAY_WINDOW 11-Apr-13 22:00 +00 04:00:00.000000
THURSDAY_WINDOW 02-May-13 22:00 +00 04:00:00.000000
TUESDAY_WINDOW 30-Apr-13 22:00 +00 04:00:00.000000
TUESDAY_WINDOW 23-Apr-13 22:00 +00 04:00:00.000000
TUESDAY_WINDOW 16-Apr-13 22:00 +00 04:00:00.000000
TUESDAY_WINDOW 09-Apr-13 22:00 +00 04:00:00.000000
TUESDAY_WINDOW 07-May-13 22:00 +00 04:00:00.000000
WEDNESDAY_WINDOW 24-Apr-13 22:00 +00 04:00:00.000000
WEDNESDAY_WINDOW 17-Apr-13 22:00 +00 04:00:00.000000
WEDNESDAY_WINDOW 10-Apr-13 22:00 +00 04:00:00.000000
WEDNESDAY_WINDOW 08-May-13 22:00 +00 04:00:00.000000
WEDNESDAY_WINDOW 01-May-13 22:00 +00 04:00:00.000000


Weekdays windows open at 10:00 p.m. and close at 2:00 a.m (4 hours). The weekend windows (Saturday and Sunday) open at 6:00 a.m. and close in 20 hours.

Disable/Enable Tasks
BEGIN
dbms_auto_task_admin.disable(
    client_name => 'sql tuning advisor',
    operation   => NULL,
    window_name => NULL);
END;


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



Check the status

 select * from DBA_AUTOTASK_OPERATION;

CLIENT_NAME OPERATION_NAME STATUS
auto optimizer stats collection auto optimizer stats job ENABLED
auto space advisor auto space advisor job ENABLED
sql tuning advisor automatic sql tuning task ENABLED


By default, all the maintenance tasks run in all pre-defined windows. However, if you want to enable or disable the task for a "specific window", use the below script


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


 
Creating the new Maintenance Window

To create a maintenance window, you must create a Scheduler window and then add it to the window group MAINTENANCE_WINDOW_GROUP

The DBMS_SCHEDULER PL/SQL package provides the ADD_WINDOW_GROUP_MEMBER subprogram, which adds a window to a window group

BEGIN
dbms_scheduler.create_window(
    window_name     => 'HOME_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 => 'HOME_WINDOW');
END;



select window_name,window_next_time from DBA_AUTOTASK_WINDOW_CLIENTS;

WINDOW_NAME WINDOW_NEXT_TIME
MONDAY_WINDOW 4/8/2013 10:00:00.000000 PM +03:00
TUESDAY_WINDOW 4/9/2013 10:00:00.000000 PM +03:00
WEDNESDAY_WINDOW 12/28/2011 10:00:00.000000 PM +03:00
THURSDAY_WINDOW 4/11/2013 10:00:00.000000 PM +03:00
FRIDAY_WINDOW 4/12/2013 10:00:00.000000 PM +03:00
SATURDAY_WINDOW 4/13/2013 06:00:00.000000 AM +03:00
SUNDAY_WINDOW 4/7/2013 06:00:00.000000 AM +03:00
HOME_WINDOW 4/8/2013 05:00:00.000000 AM +03:00


Removing the Maintenance Window:

BEGIN
DBMS_SCHEDULER.REMOVE_WINDOW_GROUP_MEMBER(
    group_name  => 'MAINTENANCE_WINDOW_GROUP',
    window_list => 'HOME_WINDOW');
END;




Relevant Views

The following views display information related to the automated database maintenance tasks:
  • DBA_AUTOTASK_CLIENT
  • DBA_AUTOTASK_CLIENT_HISTORY
  • DBA_AUTOTASK_CLIENT_JOB
  • DBA_AUTOTASK_JOB_HISTORY
  • DBA_AUTOTASK_OPERATION
  • DBA_AUTOTASK_SCHEDULE
  • DBA_AUTOTASK_TASK
  • DBA_AUTOTASK_WINDOW_CLIENTS
  • DBA_AUTOTASK_WINDOW_HISTORY

No comments: