Automated Maintenance tasks for Oracle

Automated maintenance tasks are provided in the Oracle database to ensure that the Oracle database can run more efficiently. In different versions of Oracle, there is a difference between the types of automated tasks and how they are executed. This article is based on 10g and 11g to understand the automatic tasks in Oracle.

1. Type

There are two types of automatic tasks in 10g:

• Auto_space_advisor_job, automatic space advisor JOB

• Gather_stats_job, automatic statistics JOB Collection

There are three automatic tasks in 11g:

• Auto Optimizer stats Collection, automated statistical information collection Advisor

• Auto space Advisor, automatic spatial advisor

• SQL Tuning Advisor,sql Tuning Advisor

2. Realization method

The automatic task in 10g is run as scheduler job, which can be found in the Dba_scheduler_jobs view.

Sql> set Linesize 200sql> col owner for A30sql> Col job_name for a30sql> select Owner,job_name from dba_schedu Ler_jobs where job_class= ' Auto_tasks_job_class '; OWNER job_name--------------------------------------------------- ---------SYS Auto_space_advisor_jobsys Gather_stats_job

The automatic task in 11g uses the new features of 11g Autotask the way it is implemented. You can find relevant information from the Dba_autotask_client view.

[Email protected]>select client_name,status from dba_autotask_client;  Client_name STATUS----------------------------------------------------------------Auto Optimizer stats collection Enabledauto Space Advisor Enabledsql Tuning Advisor ENABLED

3. Maintenance window Time

The execution time of automatic tasks is different in two versions.

The 10g is divided into two time periods Weeknight_window and Weekend_window, which can be found in the Dba_scheduler_windows view. The start time and duration of their respective runs are:

Weeknight_window, starting from Monday to Friday 22:00:00, lasts 8 hours

Weekend_window, Saturday 00:00:00, lasts 2 days

11g Maintenance window is divided into 7, that is, the first day of Maintenance window, you can make more granular adjustments for different needs, dba_scheduler_windows view, respectively, Monday_window,tuesday_window, Wednesday_window,thursday_window,friday_window,saturday_window,sunday_window. The start time and duration are:

Starting from Monday to Friday 22:00:00, it lasts 4 hours, Saturday and Sunday 6:00:00, and executes 20 hours.

4. Maintenance Mode

Due to the different implementations in 10g and 11g, the maintenance method will naturally differ.

The 10g is maintained in the same way as a normal scheduler job.

--Deactivate jobsql> exec dbms_scheduler.disable (' auto_space_advisor_job ');P L/sql procedure successfully completed. Sql> Select Enabled from Dba_scheduler_jobs where job_name= ' auto_space_advisor_job '; Enabled---------------false--enable jobsql> exec dbms_scheduler. ENABLE (' auto_space_advisor_job ');P L/sql procedure successfully completed. Sql> Select Enabled from Dba_scheduler_jobs where job_name= ' auto_space_advisor_job '; ENABLED---------------TRUE

The following highlights the maintenance of automatic tasks in 11g.

1) Deactivate and enable automatic maintenance tasks

--Deactivate Sql tuning advisor[email protected]>begin  2    dbms_auto_ Task_admin.disable (  3      client_name =>  ' sql  Tuning advisor ',  4      operation   =>  Null,  5      window_name => null);  6   END;  7  /    PL/SQL procedure successfully  Completed. [email protected]>select client_name,status from dba_autotask_client; client_name                                STATUS---------------------------------------- ------------------------auto optimizer stats  collection          enabledauto space advisor                        ENABLEDsql tuning  advisor                        disabled--Enable Sql tuning advisor[email protected]>begin   2    dbms_auto_task_admin.enable (  3       client_name =>  ' Sql tuning advisor ',  4       operation   => null,  5      window_name  => null);  6  end;  7  /pl/sql procedure  Successfully completed. [email protected]>select client_name,status from dba_autotask_client; client_name                               status----------------------------------------  ------------------------auto optimizer stats collection           ENABLEDauto space advisor                        enabledsql  tuning advisor                        enabled--Disable all automatic tasks under all maintenance windows [email protected]>exec  dbms_auto_task_admin.disable;pl/sql procedure successfully completed. [email protected]>select window_name,autotask_status from dba_autotask_window_clients; window_name      &Nbsp;             autotask_ STATUS------------------------------ ------------------------monday_window                   DISABLEDTUESDAY_WINDOW                   disabledwednesday_window                DISABLEDTHURSDAY_WINDOW                 DISABLEDFRIDAY_WINDOW                   DISABLEDSATURDAY_WINDOW                 DISABLEDSUNDAY_WINDOW                &nbsp  disabled7 rows selected.--enable all automatic tasks under all maintenance windows [Email protected]>exec dbms_auto _task_admin.enable;pl/sql procedure successfully completed. [email protected]>select window_name,autotask_status from dba_autotask_window_clients; window_name                     autotask_status------------------------------ ------------------------Wednesday_ Window               enabledfriday_ window                   ENABLEDSATURDAY_WINDOW                 ENABLEDTHURSDAY_WINDOW                 enabledtuesday_window &nbsP;               enabledsunday_ window                   ENABLEDMONDAY_WINDOW                   enabled7 rows selected.--Disable automatic tasks for a maintenance window [Email protected]>begin   2    dbms_auto_task_admin.disable (  3       client_name =>  ' Sql tuning advisor ',   4       operation   => NULL,   5       window_name =>  ' Monday_window ');   6  end;  7  /pl/sql  procedure successfully completed. [Email protected]>select window_name,autotask_status,sql_tune_advisor from dba_autotask_window_clients; window_name                     autotask_status          sql_tune_ ADVISOR------------------------------ ------------------------ ------------------------Monday_window                    enabled                   DISABLEDTUESDAY_WINDOW                  ENABLED                   ENABLEDWEDNESDAY_WINDOW                enabled                  enabledthursday_window                 ENABLED                   ENABLEDFRIDAY_WINDOW                   ENABLED                   enabledsaturday_ Window                enabled                    enabledsunday_window                   ENABLED                  &nBsp Enabled7 rows selected.

2. Configuration Maintenance window

Similar to 10g, this is done using the Dbms_scheduler package.

--Modify the properties of the Maintenance window to change the Saturday_window duration to 4 hours [email protected]>begin  2     Dbms_scheduler.disable (  3      name  =>  ' Saturday_ WINDOW ');   4    dbms_scheduler.set_attribute (  5       name      =>  ' Saturday_window ',  6       attribute =>  ' DURATION ',  7       value     => numtodsinterval (4,  ' Hour '));  8     dbms_scheduler.enable (  9      name =>  ' Saturday_window '); 10  end; 11  /pl/sql procedure successfully  Completed. [Email protected]>select duration from dba_scheduler_windows where window_ Name= ' Saturday_window ';D uration---------------------------------------------------------------------------+000 04:00:00 

11g Automatic Maintenance Task Reference Official document: http://docs.oracle.com/cd/E11882_01/server.112/e25494/tasks.htm#ADMIN12344

This article is from the "DBA fighting!" blog, so be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1923449

Automated Maintenance tasks for Oracle