oracle maintenance task

Database Administrator’s Guide
Database Resource Management and Task Scheduling
Managing Automated Database Maintenance Tasks

25 Managing Automated Database Maintenance Tasks

Oracle Database has automated several common maintenance tasks typically performed by database administrators. These automated maintenance tasks are performed when the system load is expected to be light. You can enable and disable individual maintenance tasks, and can configure when these tasks run and what resource allocations they are allotted.

This chapter explains how to administer automated maintenance tasks using PL/SQL packages. An easier way is to use the graphical interface of Oracle Enterprise Manager Cloud Control (Cloud Control).

To manage automatic maintenance tasks with Cloud Control:

Access the Database Home Page.

From the Administration menu, select Oracle Scheduler , then Automated Maintenance Tasks .

On the Automated Maintenance Tasks page, click Configure .

Parent topic: Database Resource Management and Task Scheduling

25.1 About Automated Maintenance Tasks

Automated maintenance tasks are tasks that are started automatically at regular intervals to perform maintenance operations on the database. An example is a task that gathers statistics on schema objects for the query optimizer.

Automated maintenance tasks run in maintenance windows , which are predefined time intervals that are intended to occur during a period of low system load. You can customize maintenance windows based on the resource usage patterns of your database, or disable certain default windows from running. You can also create your own maintenance windows.

Oracle Database has these predefined automated maintenance tasks:

Automatic Optimizer Statistics Collection— Collects optimizer statistics for all schema objects in the database for which there are no statistics or only stale statistics. The statistics gathered by this task are used by the SQL query optimizer to improve the performance of SQL execution.

Oracle Database SQL Tuning Guide for more information on automatic statistics collection

Optimizer Statistics Advisor— Analyzes how statistics are being gathered and suggests changes that can be made to fine tune statistics collection.

Automatic Segment Advisor— Identifies segments that have space available for reclamation, and makes recommendations on how to defragment those segments.

You can also run the Segment Advisor manually to obtain more up-to-the-minute recommendations or to obtain recommendations on segments that the Automatic Segment Advisor did not examine for possible space reclamation.

" Using the Segment Advisor " for more information.

Automatic SQL Tuning Advisor— Examines the performance of high-load SQL statements, and makes recommendations on how to tune those statements. You can configure this advisor to automatically implement SQL profile recommendations.

Oracle Database SQL Tuning Guide for more information on SQL Tuning Advisor

SQL Plan Management (SPM) Evolve Advisor— Evolves plans that have recently been added to the SQL plan baseline. The advisor simplifies plan evolution by eliminating the requirement to do it manually.

Oracle Database SQL Tuning Guide for more information on SPM Evolve Advisor

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

Parent topic: Managing Automated Database Maintenance Tasks

25.2 About Maintenance Windows

A maintenance window is a contiguous time interval during which automated maintenance tasks are run. Maintenance windows are Oracle Scheduler windows that belong to the window group named MAINTENANCE_WINDOW_GROUP .

A Scheduler window can be a simple repeating interval (such as "between midnight and 6 a.m., every Saturday"), or a more complex interval (such as "between midnight and 6 a.m., on the last workday of every month, excluding company holidays").

When a maintenance window opens, Oracle Database creates an Oracle Scheduler job for each maintenance task that is scheduled to run in that window. Each job is assigned a job name that is generated at run time. All automated maintenance task job names begin with ORA$AT . For example, the job for the Automatic Segment Advisor might be called ORA$AT_SA_SPC_SY_26 . When an automated maintenance task job finishes, it is deleted from the Oracle Scheduler job system. However, the job can still be found in the Scheduler job history.

To view job history, you must log in as the SYS user.

In the case of a very long maintenance window, all automated maintenance tasks except Automatic SQL Tuning Advisor are restarted every four hours. This feature ensures that maintenance tasks are run regularly, regardless of window size.

The framework of automated maintenance tasks relies on maintenance windows being defined in the database. Table 25-1 lists the maintenance windows that are automatically defined with each new Oracle Database installation.

" About Jobs and Supporting Scheduler Objects " for more information on windows and groups.

25.3 Configuring Automated Maintenance Tasks

To enable or disable specific maintenance tasks in any subset of maintenance windows, you can use the DBMS_AUTO_TASK_ADMIN PL/SQL package.

25.3.1 Enabling and Disabling Maintenance Tasks for all Maintenance Windows

With a single operation, you can disable or enable a particular automated maintenance task for all maintenance windows.

You can disable a particular automated maintenance task for all maintenance windows with a single operation. You do so by calling the DISABLE procedure of the DBMS_AUTO_TASK_ADMIN PL/SQL package without supplying the window_name argument. For example, you can completely disable the Automatic SQL Tuning Advisor task as follows:

To enable this maintenance task again, use the ENABLE procedure, as follows:

The task names to use for the client_name argument are listed in the DBA_AUTOTASK_CLIENT database dictionary view.

To enable or disable all automated maintenance tasks for all windows, call the ENABLE or DISABLE procedure with no arguments.

" Automated Maintenance Tasks Database Dictionary Views "

Oracle Database PL/SQL Packages and Types Reference for more information on the DBMS_AUTO_TASK_ADMIN PL/SQL package.

Parent topic: Configuring Automated Maintenance Tasks

25.3.2 Enabling and Disabling Maintenance Tasks for Specific Maintenance Windows

By default, all maintenance tasks run in all predefined maintenance windows. You can disable a maintenance task for a specific window.

The following example disables the Automatic SQL Tuning Advisor from running in the window MONDAY_WINDOW :

25.4 Configuring Maintenance Windows

You may want to adjust the predefined maintenance windows to a time suitable to your database environment or create a new maintenance window. You can customize maintenance windows using the DBMS_SCHEDULER PL/SQL package.

25.4.1 Modifying a Maintenance Window

The DBMS_SCHEDULER PL/SQL package includes a SET_ATTRIBUTE procedure for modifying the attributes of a window.

For example, the following script changes the duration of the maintenance window SATURDAY_WINDOW to 4 hours:

Note that you must use the DBMS_SCHEDULER.DISABLE subprogram to disable the window before making changes to it, and then re-enable the window with DBMS_SCHEDULER.ENABLE when you are finished. If you change a window when it is currently open, the change does not take effect until the next time the window opens.

" Managing Job Scheduling and Job Priorities with Windows " for more information about modifying windows.

Parent topic: Configuring Maintenance Windows

25.4.2 Creating a New Maintenance Window

To create a new maintenance window, you must create an Oracle Scheduler window object and then add it to the window group MAINTENANCE_WINDOW_GROUP .

You use the DBMS_SCHEDULER . CREATE_WINDOW package procedure to create the window, and the DBMS_SCHEDULER . ADD_GROUP_MEMBER procedure to add the new window to the window group.

The following example creates a maintenance window named EARLY_MORNING_WINDOW . This window runs for one hour daily between 5 a.m. and 6 a.m.

" Creating Windows "

Oracle Database PL/SQL Packages and Types Reference for information on the DBMS_SCHEDULER package

25.4.3 Removing a Maintenance Window

To remove an existing maintenance window, remove it from the MAINTENANCE_WINDOW_GROUP window group.

The window continues to exist but no longer runs automated maintenance tasks. Any other Oracle Scheduler jobs assigned to this window continue to run as usual.

The following example removes EARLY_MORNING_WINDOW from the window group:

" Removing a Member from a Window Group "

" Dropping Windows "

25.5 Configuring Resource Allocations for Automated Maintenance Tasks

You can reduce or increase resource allocation to the automated maintenance tasks.

Managing Resources with Oracle Database Resource Manager

25.5.1 About Resource Allocations for Automated Maintenance Tasks

By default, all predefined maintenance windows use the resource plan DEFAULT_MAINTENANCE_PLAN . Automated maintenance tasks run under its subplan ORA$AUTOTASK . This subplan divides its portion of total resource allocation equally among the maintenance tasks.

DEFAULT_MAINTENANCE_PLAN defines the following resource allocations:

In this plan, any sessions in the SYS_GROUP consumer group get priority. (Sessions in this group are sessions created by user accounts SYS and SYSTEM .) Any resource allocation that is unused by sessions in SYS_GROUP is then shared by sessions belonging to the other consumer groups and subplans in the plan. Of that allocation, 5% goes to maintenance tasks and 20% goes to user sessions. The maximum utilization limit for ORA$AUTOTASK is 90. Therefore, even if the CPU is idle, this group/plan cannot be allocated more than 90% of the CPU resources.

To reduce or increase resource allocation to the automated maintenance tasks, you make adjustments to DEFAULT_MAINTENANCE_PLAN . See " Changing Resource Allocations for Automated Maintenance Tasks " for more information.

Note that as with any resource plan, the portion of an allocation that is not used by a consumer group or subplan is available for other consumer groups or subplans. Note also that the Database Resource Manager does not begin to limit resource allocations according to resource plans until 100% of CPU is being used.

Although DEFAULT_MAINTENANCE_PLAN is the default, you can assign any resource plan to any maintenance window. If you do change a maintenance window resource plan, ensure that you include the subplan ORA$AUTOTASK in the new plan.

Managing Resources with Oracle Database Resource Manager for more information on resource plans.

Parent topic: Configuring Resource Allocations for Automated Maintenance Tasks

25.5.2 Changing Resource Allocations for Automated Maintenance Tasks

To change the resource allocation for automated maintenance tasks within a maintenance window, you must change the percentage of resources allocated to the subplan ORA$AUTOTASK in the resource plan for that window.

(By default, the resource plan for each predefined maintenance window is DEFAULT_MAINTENANCE_PLAN .) You must also adjust the resource allocation for one or more other subplans or consumer groups in the window's resource plan such that the resource allocation at the top level of the plan adds up to 100%. For information on changing resource allocations, see Managing Resources with Oracle Database Resource Manager .

25.6 Automated Maintenance Tasks Reference

Oracle Database has predefined maintenance windows. It also has data dictionary views that you can query for information about automated maintenance.

25.6.1 Predefined Maintenance Windows

By default there are seven predefined maintenance windows, each one representing a day of the week.

The weekend maintenance windows, SATURDAY_WINDOW and SUNDAY_WINDOW , are longer in duration than the weekday maintenance windows. The window group MAINTENANCE_WINDOW_GROUP consists of these seven windows. The list of predefined maintenance windows is given in Table 25-1 .

Table 25-1 Predefined Maintenance Windows

Parent topic: Automated Maintenance Tasks Reference

25.6.2 Automated Maintenance Tasks Database Dictionary Views

You can query a set of data dictionary views for information about automated maintenance tasks.

Table 25-2 displays information about database dictionary views for automated maintenance tasks:

Table 25-2 Automated Maintenance Tasks Database Dictionary Views

" Resource Manager Data Dictionary Views " for column descriptions for views.

8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 11g » Here

Automated Database Maintenance Task Management in Oracle Database 11g Release 1

Introduction, basic task configuration, task parameter configuration, maintenance window configuration, resource plan configuration, relevant views.

This article is a brief overview of the management of automated database maintenance tasks in Oracle database 11g. I've tried to keep this article quite light for two of reasons:

Oracle 11g includes three automated database maintenance tasks:

These tasks run during maintenance windows scheduled to open over night. Configuration of the maintenance tasks, their schedules and resource usage is possible using Enterprise Manager or PL/SQL APIs.

The "Automated Maintenance Tasks" screen displays the maintenance window for each task (Server > Automated Maintenance Tasks (link under Scheduler section)). Click the "Configure" button to navigate to the configuration screens.

Automated Maintenance Tasks

The "Automated Maintenance Tasks Configuration" screen is the stating point for all maintenance task configuration.

Automated Maintenance Tasks Configuration

The "Global Status" switch allows you to enable or disable all automated tasks for all maintenance windows.

The DISABLE and ENABLE procedures of the DBMS_AUTO_TASK_ADMIN package achieve the same result if they are called with no parameters.

The "Task Settings" section allows you to enable or disable individual tasks from all maintenance windows.

This can be done using the DBMS_AUTO_TASK_ADMIN package by specifying the task name in the CLIENT_NAME parameter of the DISABLE and ENABLE procedures.

The "Maintenance Window Group Assignment" section provides the most granular level of control. It allows tasks to be removed or added to individual maintenance windows.

Maintenance Window Group Assignment

This can be done using the DBMS_AUTO_TASK_ADMIN package by specifying the CLIENT_NAME and WINDOW_NAME parameters of the DISABLE and ENABLE procedures.

The "Task Settings" section of the "Automated Maintenance Tasks Configuration" screen includes two "Configure" buttons.

The "Configure" button next to the "Optimizer Statistics Gathering" task takes you to the "Global Statistics Gathering Options" screen.

Global Statistics Gathering Options

With the exception of the history retention, these settings can all be altered with the SET_GLOBAL_PREFS procedure in the DBMS_STATS package. Click here for a list of the possible parameter values.

The "Configure" button next to the "Automatic SQL Tuning" task takes you to the "Automatic SQL Tuning Settings" screen.

Automatic SQL Tuning Settings

These settings can all be altered with the SET_TUNING_TASK_PARAMETER procedure in the DBMS_SQLTUNE package.

The maintenance windows are defined using the Oracle Scheduler . Oracle provide a separate active maintenance window for each day, with all windows collected into a window group called "MAINTENANCE_WINDOW_GROUP". Clicking the "Edit Window Group" button on the "Automated Maintenance Tasks Configuration" screen allows you to view and modify the window group.

Edit Window Group

The whole window group can be enabled or disabled using this screen, or using the ENABLE and DISABLE procedures in the DBMS_SCHEDULER package.

Clicking on the window name on this screen, or in the "Automated Maintenance Tasks Configuration" screen, takes you to the "View Windows" screen, which gives you a summary of the window configuration.

Click the "Edit" button to alter the window definition.

These configuration changes can be performed using the SET_ATTRIBUTE procedure of the DBMS_SCHEDULER package. Click here for a full list of window attributes.

If you wish to create additional maintenance windows, make sure they are assigned to the "MAINTENANCE_WINDOW_GROUP" window group.

A full discussion of the scheduler is beyond the scope of this article, but relevant articles are listed below.

The "Edit Window" screen includes "View Resource Plan" and "Create Resource Plan" buttons. These buttons take you to the resource manager maintenance screens. All maintenance windows are assigned to the "DEFAULT_MAINTENANCE_PLAN" resource plan by default.

View Resource Plan

If necessary, you can edit this plan, or create a new plan.

Edit Resource Plan

If you create a new resource plan, you need assign it to the relevant maintenance windows in the "Edit Window" screen, or using the DBMS_SCHEDULER package.

To blank the resource plan issue the following command. Thanks to "Geert" in the comments for pointing out this fails when using NULL, but works with ''.

A full discussion of the resource manager is beyond the scope of this article, but relevant articles are listed below.

The following views display information related to the automated database maintenance tasks:

In addition, you may need to refer to the scheduler and resource manager views.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.

Created: 2008-06-23 Updated: 2019-07-31

Home | Articles | Scripts | Blog | Certification | Videos | Misc | About

About Tim Hall Copyright & Disclaimer

Oracle DBA Scripts and Articles (Montreal)

This blog is intended to share my knowledge on oracle and various scripts that i use as an oracle dba., database maintenance tasks.

Recent Posts

oracle maintenance

The default maintenance window is not a good fit for every database, by default the maintenance window start at 10 PM and run for 4 hours during the week and start at 6 AM and last for 20 hours during the week-end. A different window exists for each day of the week and all the windows are grouped inside the MAINTENANCE_WINDOW_GROUP. It is possible to alter each window independently or to create a single window for all days of the week.

The following maintenance tasks are performed during the maintenance window:

And the default window group named MAINTENANCE_WINDOW_GROUP is associated with a DEFAULT_MAINTENANCE_PLAN which has the following configuration:

In this plan 75% of the resources are affected to sessions created by SYS or SYSTEM user. If these resources are not used by the SYS_GROUP then they are transfered to the other groups. As you can see there is also a restriction for the automatic maintenance tasks, they cannot use more that 90% of the resources.

here is what you is configured by default, independently of the management pack configured and the edition you use. This configuration is from a standard edition database with control_management_pack_access set to NONE.

Maintenance example

In my case the database i’m working on is a 24/7 database which has less activity between 10 AM and 12 AM every day. I will create a new maintenance window named MORNING_WINDOW:

Then we need to add the maintenance window to the MAINTENANCE_WINDOW_GROUP:

Let’s remove the default windows from MAINTENANCE_WINDOW_GROUP, we don’t need them anymore.

Here is the new configuration:

Deactivating some maintenance tasks

If you, like me and are in Standard Edition you have not use of the SQL Tuning advisor for example, you have no right to use it, but it runs in your maintenance window for nothing, let’s disable it:

If you want to deactivate the task only for a specific window you can use :

If you want to deactivate all the tasks, use the following command:

Altering the default maintenance windows

If you choose to keep the default maintenance windows and just change one of them to adapt it to you needs you can use the DBMS_SCHEDULER.SET_ATTRIBUTE prodcedure.

Change the interval :

Change the duration of the Window:

If you need more information you can have a look at the oracle documentation:

Managing Automated Database Maintenance Tasks

Good maintenance 🙂

You may also like:

6 thoughts on “ Database maintenance tasks ”

Thanks Cyrille for nice article. The docs you pointed too are 12c, I believe your article is true too for 11g, the version at which the framework ‘autotask’ appeared for maintenace tasks dba_autotask_client , DBA_AUTOTASK_TASK , DBA_AUTOTASK_WINDOW_CLIENTS .. Foued

Thanks for your support and your feedback, you are absolutely right, these views appeared in 11g even if some of these task were also available in 10g.

Thank you Cyrille. With this article you transform boring tasks into interesting ones.

I want to create an Oracle DBMS Job that runs every week day (not on weekends) from 09:00 to 20:00 every 10 min.

FREQ=MINUTELY;INTERVAL=10;BYDAY=MON,TUE,WED,THU,FRI;BYHOUR=9,10,11,12,13,14,15,16,17,18,19,20

This should work

Leave a Reply Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed .

Oracle Training Oracle Tips Oracle Forum Class Catalog Remote DBA Oracle Tuning Emergency 911 RAC Support Apps Support Analysis Design Implementation Oracle Support

The Oracle docs note this on automatic maintenance tasks:

"In Oracle 11g, there is no GATHER_STATS_JOB. It has been now incorporated into DBMS_AUTO_TASK_ADMIN job along with Automatic segment advisor and Automatic tuning advisor. All these 3 run in all maintenance windows"

Also see my dba_autotask_operation tips

Oracle 11g introduces a new feature called Automated Maintenance Tasks (AMR) and AMR starts with some of the more common duties of a database administrator. Information gathered from the AWR repository is analyzed by Autotask and it then builds the correct tasks to be executed in next maintenance window. These tasks might include:

n Optimizer statistics gathering

n Automatic Segment Advisor

n SQL Tuning Advisor

One way of changing the Autotask configuration is through the dbms_auto_task_admin package. Alternately, the Oracle Enterprise Manager (OEM) can be used. Some important views used to collect information through the Autotask configuration are explained here:

n dba_autotask_task : This view shows information about task execution time, current status, priority and historical data like last and best times

n dba_autotask_window_clients : This view displays information about current windows available in the database belonging to maintenance_window_group

n dba_autotask_client_history : View used to show historical information for each job execution

n dba_autotask_operation : View used to display operation information for each client, such as attributes and status

n dba_autotask_job_history : This view provides information about job runs after each execution

n dba_autotask_client : View used to display statistical data for each task for the last seven days. It also shows an evaluation for the last 30 days.

n dba_autotask_window_history : Shows historical information for each maintenance task window

The next example shows how to change the Autotask configuration as well as disable it if desired. If the Autotask feature needs to be disabled from the database, execute the disable procedure as follows:

< Code 7.2 - dbms_auto_task_admin.sql

conn [email protected] as sysdba

Connected to Oracle 11g Enterprise Edition Release 11.1.0.6.0

conn / as sysdba

--check the actual status of your task select autotask_status from dba_autotask_window_clients; --disable Autotask exec dbms_auto_task_admin.disable ; --check the status again select autotask_status from dba_autotask_window_clients; --enable autotask exec dbms_auto_task_admin.enable ;

--disable all taks for a client set serveroutput on declare v_status_before varchar2(10); v_status_after varchar2(10); begin select status into v_status_before from dba_autotask_client where client_name='sql tuning advisor'; dbms_output.put_line(a => 'client status before command: '||v_status_before); dbms_auto_task_admin.disable ('sql tuning advisor',NULL,NULL); select status into v_status_after from dba_autotask_client where client_name='sql tuning advisor'; dbms_output.put_line(a => 'client status after command: '||v_status_after); end; /

Next, the get_p1_resources procedure is used to return the percentage of resources allocated to each Autotask included in the High Priority Group.

--Get information the percent of resources used by each Autotask that is in high priority group declare v_stats_group_pct number; v_seq_group_pct number; v_tune_group_pct number; v_health_group_pct number; begin dbms_auto_task_admin.get_p1_resources (v_stats_group_pct,v_seq_group_pct,v_tu

ne_group_pct,v_health_group_pct); dbms_output.put_line(a => 'Percentage of resources for Statistics Gathering: '||v_stats_group_pct||chr(10)|| 'Percentage of resources for Space Management: '||v_seq_group_pct||chr(10)|| 'Percentage of resources for SQL Tuning: '||v_tune_group_pct||chr(10)|| 'Percentage of resources for Health Checks: '||v_health_group_pct); end; /

If it is necessary to change resource consumption utilization, this can be done through the set_p1_resources procedure.

--If it is necessary to change resource consumption utilization, then it can be done through set_p1_resources procedure set serveroutput on declare v_stats_group_pct number; v_seq_group_pct number; v_tune_group_pct number; v_health_group_pct number; begin v_stats_group_pct := 15; v_seq_group_pct := 30; v_tune_group_pct := 30; v_health_group_pct := 25; dbms_auto_task_admin.set_p1_resources ( stats_group_pct => v_stats_group_pct, seg_group_pct => v_seq_group_pct, tune_group_pct => v_tune_group_pct, health_group_pct => v_health_group_pct); end; /

Now, assume that I have a Real Applications Cluster (RAC) environment with three nodes and we want to spread tasks between them. The service names are dbms1 , dbms2 and dbms2 :

col client_name for a35 col service_name for a15 select client_name, service_name from dba_autotask_client; --Get the service name that will be assossiated with client_name select name from dba_services; --Make the association begin dbms_auto_task_admin.set_client_service ( client_name => 'sql tuning advisor', service_name => 'dbms'); end; /

In order to get the attribute values of a certain client, use the get_client_attributes procedure.

--In order to get the attribute values of a certain client you can use get_client_attributes procedure --Get the client name select client_name from dba_autotask_client; declare v_service_name varchar2(20); v_service_name_1 varchar2(20); v_window varchar2(20); v_client_name varchar2(30); begin v_client_name := 'auto space advisor'; dbms_auto_task_admin.get_client_attributes ( client_name => v_client_name, service_name => v_service_name, window_group => v_window); select decode(v_service_name,NULL,'NULL') into v_service_name_1 from dual; dbms_output.put_line(a => ' Attributes for client '||v_client_name||chr(10)|| ' - Service_Name is: '||v_service_name_1||chr(10)|| ' - Window Group is: '||v_window); end; /

Make the association. If the client attribute values need to be changed, use the set_attribute procedure as follows:

--If you need to change client attribute values then use set_attribute procedure as follows: --Get the client name and current attributes select client_name ,attributes from dba_autotask_client; --Change some attribute values begin dbms_auto_task_admin.set_attribute( client_name =>'auto space advisor' , attribute_name =>'safe_to_kill' , attribute_value =>'FALSE'); end; / begin dbms_auto_task_admin.set_attribute( client_name =>'auto space advisor' , attribute_name =>'volatile' , attribute_value =>'FALSE'); end; / --Get the results select client_name ,attributes from dba_autotask_client;

If the intent is to change the task priority of any client, operation and/or individual task level, use the override_priority procedure.

--To override the priority of auto space advisor client you can execute this command below: --Get the current value select client_name, priority_override from dba_autotask_client; --Change the priority to urgent begin dbms_auto_task_admin.override_priority( client_name => 'auto space advisor', priority => dbms_auto_task_admin.priority_urgent); end; /

--Get the new value select client_name, priority_override from dba_autotask_client;

Copyright © 1996 - 2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Oracle Database Maintenance

In this section of the Oracle DBA tutorial, you will learn about database maintenance, the tasks used for database maintenance, consistency checks, updates, system and object statistics, job scheduling, creating a job in Oracle Scheduler, and more.

What is Database Maintenance

Maintenance tasks.

general maintenance tasks in sql server and oracle

Consistency Checks

Consistency checks validate database blocks and look for corruption in datafiles. Consistency checks look at the physical integrity of the data blocks and rows of objects. They can also validate the structures of objects and whether the tables and indexes still have corresponding values.

Oracle checks for block corruption as database writers handle the blocks of data. The DB_BLOCK_CHECKSUM parameter determines if blocks will be checked in memory. The TYPICAL setting for this parameter (the default) verifies checksums before writing to disk. With more data movement possibly happening in memory, it is detecting the corruption here, before even writing to disk, is useful.

To have Oracle check the blocks in memory (the buffer cache), set DB_BLOCK_CHECKSUM to FULL. This setting will perform checksums on all changes before and after writing to the log. This does add overhead to the system, but FULL is the only setting that will check for block corruption in the buffer cache. This parameter is dynamic, so it can be altered to check its effects on your environment.

Enroll in this professional Oracle DBA Training to excel in your career!

Health Checks

health checks in sql server and oracle

Updating Statistics

In both SQL Server and Oracle, statistics are updated by default. In SQL Server, the AUTO_UPDATE_STATISTICS database option, when turned on, will update the statistics when they become stale. We can also run updates manually, using sp_updatestats or UPDATE STATISTICS.

In Oracle, the parameter STATISTICS_LEVEL set to TYPICAL or ALL enables automatic statistics gathering. In Oracle Database 10g, the GATHER_STATS_JOB job is scheduled to gather stale statistics and keep them updated.

Intellipaat provides SQL Certification to make you industry ready.

System Statistics

The gathered statistics information is used by the cost-based optimizer to create query plans. Capturing statistics at different times for various activities is especially useful when the workload on the database is different, such as batch processing or reporting at night and processing transactions during the day.

We can also capture system statistics on the fixed data dictionary tables, which should be done during the regular workload and run once.

Object Statistics

update statistics procedures in sql server and oracle

If you want to get certified in Oracle DBA, learn Oracle DBA from top Oracle DBA experts with Intellipaat’s Oracle DBA Certification !

Object Maintenance

Along with gathering statistical information about objects, some maintenance and checks need to be done on them. There might be fragmentation, so the objects need to be rebuilt. Invalid objects might need to be recompiled. Even grants and permissions can be considered part of object maintenance.

Index Rebuild

While examining the database objects, some of them might appear fragmented and in need of a rebuild. Such rebuilds increase log activity, put additional resources on the system, and may put locks on the objects. Therefore, we should be selective about which indexes to include in tasks. We can generate reports to plan maintenance on indexes at another time, if necessary.

Table Reorganization

Like indexes, tables can become fragmented, due to chained rows or changes occurred by updates or deletions, which leaves space available that is not being reused. In some cases, these tables can benefit from reorganization. For example, a table might need to be reorganized after doing some data cleanup or if monitoring shows that the free space can be reclaimed.

Oracle’s ASSM feature manages the space within a segment. Allowing Oracle to manage the space in segments for tables reduces the fragmentation of the tables. Segment Advisor again comes into the play with tables, checking for chained rows and space that can be freed up.

Learn Oracle Dba

Invalid Objects

Objects such as procedures, functions, and views can become invalid if a dependent object is altered. Normally, an object will recompile the next time the procedure is executed or the view is accessed, as long as there are no errors in the code. However, making sure that the objects are valid should be included in the maintenance plan. Alerts will pop up in OEM about the invalid objects in a schema. We can recompile the invalid objects in a few ways:

The dba_tab_privs and dba_col_privs views show the current grants that have been added to either a role or a user. One way to maintain grants is to have a copy of the grants that have been granted in a table and compare that information against the current dba_tab_privs view.

Users other than the schema owner may need access to a particular table or view, which requires them to fully qualify the object with schema_name.object_name. Alternatively, a synonym can be created for that object.

In Oracle, when tables are altered, the synonyms created on the object are not changed and they remain in place. However, if an object is dropped, the synonym will become invalid, and when the object is recreated, the synonym might need to be recompiled. The object will appear with the INVALID status in the dba_objects table.

Job Scheduling

scheduling in sql server and oracle

Creating a Job in Oracle Scheduler

Oracle Scheduler is available from the Server tab in OEM (Oracle Database 11g). Selecting Jobs will show the current jobs scheduled against the database, and jobs can be viewed, edited, executed, and created from here.

creating a job in oem

For Command Type, we have the following options:

Check out the Top Oracle DBA Interview Questions to learn what is expected from Oracle DBA professionals!

Using DBMS_SCHEDULER

We can also create jobs with the DBMS_SCHEDULER package. It takes parameters for job name, description, and action. We can set up a one-time job on a repeated interval, which can be by time, days, weeks, and so on. The start date could be either the current time for immediate execution or a future date.

File Maintenance

Datafiles, log files, error logs, history logs, trace files—oh my! File maintenance is very important to the health of the database and maintaining a stable environment. Developing tasks and scripts will be useful for managing different files.

Shrinking and Resizing Files

In Oracle, logs are sized and remain that same size, so shrinking a file is not an issue.

However, depending on how many times the transactions are looping through the redo logs, there might be a reason to adjust the size of the logs.

Datafiles are slightly different in this case because they are normally growing. But, we might need to clean up data or start an archive process that frees up space.

Tablespace Monitoring

Oracle tablespaces are created with one or more datafiles. As the database grows, the tablespaces and datafiles need to be maintained to allow for the growth. Although, planning the size of the system tablespaces is recommended.

Oracle datafiles are set to a fixed size or to auto-extend. We can monitor space at the tablespace level in OEM. From the Server tab, under the Storage category, choose Tablespaces to see the list of tablespaces.

The free space available threshold can be a specific amount or percentage. The actual size of the free space is useful for very large tablespaces. For example, 20 percent free space of a 10 GB datafile and 20 percent free space of a 2 TB datafile may have very different levels of urgency. The percentage of allocated space does not take into account auto-extend for the datafiles.

tablespace listing in oem

Come to Intellipaat’s Oracle DBA Community to post your queries and get them clarified!

Error Logs, Alert Logs, and Trace Files

Oracle alert logs contain information about the status of the database and error messages. The alert log errors are on the first page of OEM. When the instance is restarted, the alert log is not cycled to the next log; writing continues to the current log.

The directories , as set by the parameters background_dump_dest, user_dump_dest, and background_core_dump, contain trace files and log files. In Oracle 11g, there is also diagnostic_dest which can be the single directory for the trace and log files. If there is enough space allocated to the server to have unlimited files, not much maintenance needs to be done. Otherwise, these directories should be purged by days.

Using operating system commands, we can find the files in the dump destinations, and mtime sets how many days the files should be retained.

We should clean up the older alert logs as well. The command to do so might include the date or *.log, depending on how the alert log is named when rotating logs.

Course Schedule

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Browse Categories

Find SQL Certification Training in Other Regions

Bangalore Chennai Virginia Kansas City Texas Pune Hyderabad Dublin London Chicago San Francisco Denver Seattle Mumbai Delhi Kolkata Malaysia San Diego San Jose New Jersey Adelaide Atlanta Boston Johannesburg Noida Phoenix Orange County Philippines Singapore Sydney Toronto Melbourne Minneapolis New York Perth Charlotte Cincinnati Columbus Manchester Detroit Velachery Manila Maryland Miami Michigan Minnesota Nashville New Zeal South Africa St Louis Dallas Tampa Los Angeles Houston Brisbane Calgary Baltimore Bay Area Bellevue Birmingham Northern Virginia Washington Edinburgh USA Bristol

Download Salary Trends

Learn how professionals like you got upto 100% hike!

Expert-Led No.1

Changing the Oracle database default maintenance window time

oracle maintenance task

I’ve been working recently on a system that has been a performance nightmare. I’ve been able to do a lot to get it running well, but I often noticed that load on the system would always increase in the afternoon even though it didn’t appear the workload was increasing. I’d notice this often around 3-4PM (Mountain Time) and thought maybe it was because people in the East and Central time zones were returning home and using it. I had observed however, that the traffic pattern would rise in the morning, stay high during the day, and then lower in the afternoon and into the evening. This made my theory not make sense. It didn’t make sense the load would increase when traffic on the system should be going down, until one day I did “date” on the Linux command line and got reminded the system runs on UTC and this system supports customers in North America. Ah-hah!

Some quick time math revealed that 3PM (Mountain Time) was 10PM UTC. What did that matter? Well, I recalled from OCP study that the default Oracle Database maintenance windows start at 10PM during the week. That meant that the Oracle maintenance windows would be starting during the normal user time for this product. I wanted to modify these to get them to start during the North American night.

My recollection was that Oracle had weeknight and weekend windows and that is what I’d need to change. I searched for these to verify the start time:

Good, I’d found the windows as figured I just needed to change them. So, I modified them in the scheduler. To move them on a server in UTC to the “normal” time in Mountain Time Zone I did:

The next day I checked the scheduler to see if the jobs ran at the new correct time. I checked in dba_scheduler_job_details but couldn’t find what I wanted. Some research reminded me that in 11g the DBMS_AUTO_TASK_ADMIN functionality is used. To find the information I needed to check another view:

Still 10PM UTC! That wasn’t what I expected, so I decided to check on the Auto Task Admin settings:

OK, so I’ve got the Window Groups, now I had to dig in further to find out what window’s they using:

So, it appeared that the Auto Task Admin does not use the parent WEEKNIGHT and WEEKEND window groups, but instead the daily windows.

So, I needed to change the regular weekly windows too:

Voila! The next day the jobs were running when I wanted them to, not during the North American day, but during the night.

So, whatever timezone you’re in, if it isn’t UTC or close to it and you prefer these to run during the evening (or whatever time really), you now will know how to change it.

Share this:

21 responses to “ changing the oracle database default maintenance window time ”.

Pingback: Changing the Oracle database default maintenance window time | Jed's

Copy & paste error there. You set byday=FRI for all your daily jobs!!!

Thank you Mike! I’ve updated it.

Huh! Dude, you just saved my Job. I had the same nightmare, a couple of weeks back!

Explained and nailed 🙂

thankyou. I did not have to look anywhere else. good blog.

Thanks, this was very helpful after we discovered Amazon RDS Oracle instances still had DBMS Scheduler running UTC even after we changed the instances to ET.

awesome note!! Save me lots of headache!! Thanks

Liked your way of explaining stuff. Helped a lot.

Thank you for this great explanation. Very clear what is going on and how to change this window. Oracle should learn from examples like this.

Fantastic! great explanation and examples, very useful.

Excellent walkthrough, saved me some time and helped me locate my own issues. Thank you!

Me too faced the same issue and just now testing in TEST environment. Thanks a lot for your blog

Nice write-up! Thank-you. It’s always confusing, and this answers the question.

Great post, many thanks!

Bravo Zulu! (Navy term for job well done) Great article!

Good stuff! Thanks for the help!

Leave a Reply Cancel reply

Fill in your details below or click an icon to log in:

You are commenting using your WordPress.com account. ( Log Out / Change )

You are commenting using your Twitter account. ( Log Out / Change )

You are commenting using your Facebook account. ( Log Out / Change )

Connecting to %s

Notify me of new comments via email.

Notify me of new posts via email.

23 Managing Automatic System Tasks Using the Maintenance Window

Oracle Database is preconfigured to perform some routine database maintenance tasks so that you can run them at times when the system load is expected to be light. You can specify for such a time period a resource plan that controls the resource consumption of those maintenance tasks. When the designated time period ends, the database can switch to a different resource plan that lowers the resource allocation for any remaining maintenance tasks.

This chapter consists of the following sections:

Maintenance Windows

Automatic statistics collection job, resource management.

The Oracle Scheduler enables you to create time windows during which jobs are automatically run. A typical Scheduler window defines a start time, a duration, and optionally a resource plan to activate. A Scheduler job can then name a window as its schedule. (When the window "opens," the job begins to run.) In addition, windows can be combined into window groups, and if a job names a window group as its schedule instead of naming a window, the job runs whenever any of the windows in the window group opens.

Two Scheduler windows are predefined upon installation of Oracle Database:

WEEKNIGHT_WINDOW starts at 10 p.m. and ends at 6 a.m. every Monday through Friday.

WEEKEND_WINDOW covers whole days Saturday and Sunday.

Together these windows constitute the MAINTENANCE_WINDOW_GROUP in which all system maintenance tasks are scheduled. Oracle Database uses the maintenance windows for automatic statistics collection and for some other internal system maintenance jobs. If you are using the Resource Manager, you can also assign resource plans to these windows.

You can adjust the predefined maintenance windows to a time suitable to your database environment using the DBMS_SCHEDULER.SET_ATTRIBUTE procedure. For example, the following script moves the WEEKNIGHT_WINDOW to midnight to 8 a.m. every weekday morning:

You can also use the SET_ATTRIBUTE procedure to adjust any other property of a window. For example, the following script sets resource plan DEFAULT_MAINTENANCE_PLAN for the WEEKNIGHT_WINDOW :

In this case, if you have already enabled a different resource plan, Oracle Database will make the DEFAULT_MAINTENANCE_PLAN active when the WEEKNIGHT_WINDOW opens, and will reactivate the original resource plan when the WEEKNIGHT_WINDOW closes.

A Scheduler program GATHER_STATS_PROG and Scheduler job GATHER_STATS_JOB are predefined on installation of Oracle Database. GATHER_STATS_PROG collects optimizer statistics for all objects in the database for which there are no statistics or only stale statistics. GATHER_STATS_JOB is defined on GATHER_STATS_PROG and is scheduled to run in the MAINTENANCE_WINDOW_GROUP .

If you prefer to manage statistics collection manually, you can disable the job as follows:

A Resource Manager consumer group, AUTO_TASK_CONSUMER_GROUP , is predefined on installation of Oracle Database, and a Scheduler job class AUTO_TASKS_JOB_CLASS is defined based on this consumer group. The GATHER_STATS_JOB is defined to run in the AUTO_TASKS_JOB_CLASS job class.

When a resource plan is activated in the system, GATHER_STATS_JOB and any internal system tasks conform to the resource consumption specified for AUTO_TASK_CONSUMER_GROUP in this resource plan.

IMAGES

Oracle Maintenance Applications
ORACLE-BASE
SCCM Secondary Site Maintenance Tasks List Primary Site Server HTMD Blog
Oracle Maintenance Cloud 20A What's New
Oracle Turntable Maintenance Kit
ORACLE-BASE

VIDEO

Bowlfix: Lower Ball Elevator Roller Replacement: Brunswick A2 Pinsetter Maintenance
Landing light
How to change engine oil in bikes? #automotive #maintanence #mka
How can you calm your mind
Oracle Utilities 21A Improved To Do Management and Dashboard
Why should I clean my gutters?

COMMENTS

26 Managing Automated Database Maintenance Tasks
Oracle Database has automated several common maintenance tasks typically performed by database administrators. These automated maintenance tasks are
25 Managing Automated Database Maintenance Tasks
Oracle Database has automated several common maintenance tasks typically performed by database administrators. These automated maintenance tasks are
How to Change The Automated Maintenance Tasks Weekend
Oracle Database - Enterprise Edition - Version 11.2.0.1 to 21.3 [Release 11.2 to 21.0]: How to Change The Automated Maintenance Tasks
Automated Database Maintenance Task Management in Oracle
The "Automated Maintenance Tasks" screen displays the maintenance window for each task (Server > Automated Maintenance Tasks (link under
Database maintenance tasks
Database maintenance tasks · Optimizer statistics (OPTIMIZER_STATS) · Segment advisor (SEGMENT_ADVISOR) · SQL Tuning (SQL_TUNE_ADVISOR) · Health
automatic maintenance tasks tips
Oracle Database Tips by Donald BurlesonSeptember 11, 2015. The Oracle docs note this on automatic maintenance tasks: "In Oracle 11g, there is no
Database Maintenance
In this section of the Oracle DBA tutorial, you will learn about database maintenance, the tasks used for database maintenance
Changing the Oracle database default maintenance window time
Well, I recalled from OCP study that the default Oracle Database maintenance windows start at 10PM during the week. That meant that the Oracle maintenance
Client Management
Since version 11g Oracle includes the 'Automated Database Maintenance Task' that allows keeping a higher level of performance on databases.
23 Managing Automatic System Tasks Using the Maintenance Window
Oracle Database is preconfigured to perform some routine database maintenance tasks so that you can run them at times when the system load is expected to be