Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to understand ORACLE 11G maintenance window and automatic maintenance task". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to understand ORACLE 11G maintenance window and automatic maintenance task".

The maintenance window is a continuous time interval, which is used to manage automatic maintenance tasks. The so-called automatic maintenance window is defined according to a predefined period of time. For example, 22-10-04-00 at night can be called a window. Automatic maintenance task is a kind of database maintenance operation task that starts automatically according to rules. Automatic maintenance tasks run automatically when the maintenance window opens. (for example, when the alarm clock goes off, it's time to get up, the time is up, and the task is executed automatically.)

In Oracle Database 11g, the automatic maintenance task feature depends on the resource manager that is enabled during the maintenance window. Therefore, when the window opens, the resource plan associated with the window is automatically enabled, and the goal is to prevent maintenance from consuming too much system resources. Each maintenance window is associated with a resource plan that specifies how resources are allocated for the duration of the window.

There are three predefined automatic maintenance tasks for oracle11g databases:

■ Automatic Optimizer Statistics Collection:

■ Automatic Segment Advisor

■ Automatic SQL Tuning Advisor (this is a new automatic maintenance task added by oracle 11g)

By default, these three tasks are configured to run in all maintenance windows.

Maintenance window: the maintenance window is the oracle scheduling window, which belongs to the window group MAINTENANCE_WINDOW_GROUP.

Control automatic maintenance tasks

The automatic maintenance tasks feature determines the time and order in which the tasks are executed. DBA can control the following items:

? When the maintenance window is ultimately insufficient to handle the maintenance workload, adjust the duration and start time of the maintenance window.

?

?

? In a RAC environment, maintenance work is transferred to one or more instances by mapping maintenance work to a service. Enabling services on a subset of instances transfers maintenance work to those instances.

One: configure automatic maintenance tasks:

1: enable and disable maintenance tasks:

Use the DBMS_AUTO_ADMIN pl/sql package to enable or disable tasks:

Disable tasks:

BEGIN

Dbms_auto_task_admin.disable (

Client_name = > 'sql tuning advisor'

Operation = > NULL

Window_name = > NULL)

END

Enable tasks:

BEGIN

Dbms_auto_task_admin.enable (

Client_name = > 'sql tuning advisor'

Operation = > NULL

Window_name = > NULL)

END

Sys@ ORA > select client_name from dba_autotask_client

CLIENT_NAME

Auto optimizer stats collection

Auto space advisor

Sql tuning advisor

If you want to enable or disable all automatic window maintenance tasks, call the ENABLE or DISABLE procedure:

EXECUTE DBMS_AUTO_TASK_ADMIN.DISABLE

2: enable or disable the maintenance window for a maintenance window

By default, all maintenance tasks run in all predefined maintenance windows. Automation tasks can be enabled or disabled for a maintenance window.

BEGIN

Dbms_auto_task_admin.disable (

Client_name = > 'sql tuning advisor'

Operation = > NULL

Window_name = > 'MONDAY_WINDOW')

END

The above example is to disable sql tuning advisor for the maintenance window monday_window.

Second, configure the maintenance window:

However, use the DBMS_SCHEDULER package to modify the window properties.

1: modify the maintenance window

-- disable maintenance window first

BEGIN

Dbms_scheduler.disable (

Name = > 'SATURDAY_WINDOW')

-- modify maintenance window properties:

Dbms_scheduler.set_attribute (

Name = > 'SATURDAY_WINDOW'

Attribute = > 'DURATION'

-- enable maintenance window

Dbms_scheduler.enable (

Name = > 'SATURDAY_WINDOW')

END

/

For the currently open window, you need to disable it first, then modify it and then enable it, and the configuration takes effect immediately. If you do not modify the properties through these three processes, the properties will not take effect until the next window opens.

2: create a new window:

BEGIN

Dbms_scheduler.create_window (

Window_name = > 'EARLY_MORNING_WINDOW'

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: delete window:

BEGIN

DBMS_SCHEDULER.REMOVE_WINDOW_GROUP_MEMBER (

Group_name = > 'MAINTENANCE_WINDOW_GROUP'

Window_list = > 'EARLY_MORNING_WINDOW')

END

/

Third, track the operation of job:

You can track the performance of job through the query view DBA_AUTOTASK_HISTORY:

SQL > select client_name,job_name,job_start_time from dba_autotask_job_history

CLIENT_NAME JOB_NAME JOB_START_TIME

Auto optimizer stats ORA$AT_OS_OPT_SY_1 12-JUN-18 12.00.02.039000 PM + 08:50

Auto space advisor ORA$AT_SA_SPC_SY_2 12-JUN-18 12.00.02.050000 PM + 08:50

Sql tuning advisor ORA$AT_SQ_SQL_SW_3 12-JUN-18 12.00.02.015000 PM + 08:50

Thank you for your reading, the above is the content of "how to understand ORACLE 11G maintenance window and automatic maintenance task". After the study of this article, I believe you have a deeper understanding of how to understand ORACLE 11G maintenance window and automatic maintenance task. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.