Scheduler Overview

As a database feature the Oracle Scheduler has several advantages:

  • Leverage existing database knowledge
  • DBAs have total control over what runs on the database and when
  • The Scheduler is platform independent. Therefore jobs can be managed similarly on all platforms. Jobs can be easily moved from one system to another, for example from a development environment to production, using the EXPORT/IMPORT utility in the database

The Scheduler can execute OS jobs (shell scripts, executables etc.), PL/SQL blocks, and PL/SQL or Java stored procedures. OS jobs can run as any OS user on or across different operating platforms, such as Unix, Windows, z/OS and OS/400. It can be accessed using a Web Interface (EM) as well as an API - DBMS_SCHEDULER. The Web Interface makes the Scheduler accessible from anywhere using a web browser. The Scheduler provides complex enterprise scheduling functionality. You can use this functionality to do the following:

  • Schedule Job Execution
  • Define Multi-Step jobs
  • Schedule Job Processing that Models Business Requirements
  • Manage and Monitor Jobs
  • Execute and Manage Jobs in a Clustered Environment

Schedule Job Execution

The most basic capability of a job scheduler is to schedule the execution of a job. The Scheduler supports both time-based and event-based scheduling.

Time-based scheduling

Time-based scheduling enables users to specify a fixed date and time (for example, Jan. 23rd 2006 at 1:00 AM), a repeating schedule (for example, every Monday ), or a defined rule (for example the last Sunday of every other month or the fourth Thursday in November which defines Thanksgiving).

Users can create new composite schedules with minimum effort by combining existing schedules. For example if a HOLIDAY and WEEKDAY schedule were already defined, a WORKDAY schedule can be easily created by excluding the HOLIDAY schedule from the WEEKDAY schedule.

Companies often use a fiscal calendar as opposed to a regular calendar and thus have the requirement to schedule jobs on the last workday of their fiscal quarter.  The Scheduler supports user-defined frequencies which enables users to define not only the last workday of every month but also the last workday of every fiscal quarter.

Event-based scheduling

Event-based scheduling as the name implies triggers jobs based on real-time events. Events are defined as any state changes or occurrences in the system such as the arrival of a file. Scheduling based on events enables you to handle situations where a precise time is not known in advance for when you would want a job to execute.

Define Multi-step jobs

The Scheduler has support for single or multi-step jobs. Multi-step jobs are defined using a Chain. A Chain consists of multiple steps combined using dependency rules. Since each step represents a task, Chains enable users to specify dependencies between tasks, for example  execute task C one hour after the successful completion of task A and task B.

Schedule Job Processing that Models Business Requirements

The Scheduler enables job processing in a way that models your business requirements. It enables limited computing resources to be allocated appropriately among competing jobs, thus aligning job processing with your business needs. Jobs that share common characteristic and behavior can be grouped into larger entities called job classes. You can prioritize among the classes by controlling the resources allocated to each class. This lets you ensure that critical jobs have priority and enough resources to complete. Jobs can also be prioritized within a job class.

The Scheduler also provides the ability to change the prioritization based on a schedule. Because the definition of a critical job can change across time, the Scheduler lets you define different class priorities at different times.

Manage and Monitor Jobs

There are multiple states that a job undergoes from its creation to its completion. All Scheduler activity is logged, and information, such as the status of the job and the time to completion, can be easily tracked. This information is stored in views. It can be queried with Enterprise Manager or a SQL query. The views provide information about jobs and their execution that can help you schedule and manage your jobs better. For example, you can easily track all jobs that failed for user SCOTT.

In order to facilitate the monitoring of jobs, users can also flag the Scheduler to raise an event if unexpected behavior occurs and indicate the actions that should be taken if the specified event occurs. For example if a job failed an administrator should be notified.

Execute and Manage Jobs in a Clustered Environment

A cluster is a set of database instances that cooperates to perform the same task. Oracle Real Application Clusters provides scalability and reliability without any change to your applications. The Scheduler fully supports execution of jobs in such a clustered environment. To balance the load on your system and for better performance, you can also specify the service where you want a job to run.

BIWA Summit 2016

Oracle Database Cloud