Advanced Scheduler Concepts

Job Classes

Job classes provide a way to:

• Assign the same set of attribute values to member jobs

  Each job class specifies a set of attributes, such as logging level. When you assign a job to a job class, the job inherits those attributes. For example, you can specify the same policy for purging log entries for all payroll jobs.

• Set service affinity for member jobs

  You can set the service attribute of a job class to a desired database service name. This determines the instances in a Real Application Clusters environment that run the member jobs, and optionally the system resources that are assigned to member jobs. See "Service Affinity when Using the Scheduler" for more information.

• Set resource allocation for member jobs

  Job classes provide the link between the Database Resource Manager and the Scheduler, because each job class can specify a resource consumer group as an attribute. Member jobs then belong to the specified consumer group, and are assigned resources according to settings in the current resource plan.

  Alternatively, you can leave the resource_consumer_group attribute NULL and set the service attribute of a job class to a desired database service name. That service can in turn be mapped to a resource consumer group. If both the resource_consumer_group and service attributes are set, and the designated service maps to a resource consumer group, the resource consumer group named in the resource_consumer_group attribute takes precedence.

  See Chapter 25, "Managing Resource Allocation with Oracle Database Resource Manager" for more information on mapping services to consumer groups.

• Group jobs for prioritization

  Within the same job class, you can assign priority values of 1-5 to individual jobs so that if two jobs in the class are scheduled to start at the same time, the one with the higher priority takes precedence. This ensures that you do not have a less important job preventing the timely completion of a more important one.

  If two jobs have the same assigned priority value, the job with the earlier start date takes precedence. If no priority is assigned to a job, its priority defaults to 3.

  Note:

  Job priorities are used only to prioritize among jobs in the same class.

  There is no guarantee that a high priority job in class A will be started before a low priority job in class B, even if they share the same schedule. Prioritizing among jobs of different classes depends on the current resource plan and on the designated resource consumer group or service name of each job class.

When defining job classes, you should try to classify jobs by functionality. Consider dividing jobs into groups that access similar data, such as marketing, production, sales, finance, and human resources.

Some of the restrictions to keep in mind are:

• A job must be part of exactly one class. When you create a job, you can specify which class the job is part of. If you do not specify a class, the job automatically becomes part of the class DEFAULT_JOB_CLASS.

• Dropping a class while there are still jobs in that class results in an error. You can force a class to be dropped even if there are still jobs that are members of that class, but all jobs referring to that class are then automatically disabled and assigned to the class DEFAULT_JOB_CLASS. Jobs belonging to the dropped class that are already running continue to run under class settings determined at the start of the job.

Windows

You create windows to automatically start jobs or to change resource allocation among jobs during various time periods of the day, week, and so on. A window is represented by an interval of time with a well-defined beginning and end, such as "from 12am-6am".

Windows work with job classes to control resource allocation. Each window specifies the resource plan to activate when the window opens (becomes active), and each job class specifies a resource consumer group or specifies a database service, which can map to a consumer group. A job that runs within a window therefore has resources allocated to it according to the consumer group of its job class and the resource plan of the window.

Figure 26-2 shows a workday that includes two windows. In this configuration, jobs that belong to the job class that links to Consumer Group 1 get more resources in the morning than in the afternoon. The opposite is true for jobs in the job class that links to Consumer Group 2.

Figure 26-2 Windows help define the resources that are allocated to jobs

Description of "Figure 26-2 Windows help define the resources that are allocated to jobs"

See Chapter 25, "Managing Resource Allocation with Oracle Database Resource Manager" for more information on resource plans and consumer groups.

You can assign a priority to each window. If windows overlap, the window with the highest priority is chosen over other windows with lower priorities. The Scheduler automatically opens and closes windows as window start times and end times come and go.

A job can name a window in its schedule_name attribute. The Scheduler then starts the job when the window opens. If a window is already open, and a new job is created that points to that window, the job is not started until the next time the window opens.

See "Creating Windows" for examples of creating and using windows.

Window Groups

You can group windows for ease of use in scheduling jobs. If a job must run during multiple time periods throughout the day, week, and so on, you can create a window for each time period, and then add the windows to a window group. You can then set the schedule_name attribute of the job to the name of this window group, and the job executes during all the time periods specified in the window group.

For example, if you had a window called "Weekends" and a window called "Weeknights," you could add these two windows to a window group called "Downtime." The data warehousing staff could then create a job to run queries according to this Downtime window group—on weeknights and weekends—when the queries could be assigned a high percentage of available resources.

If a window in a window group is already open, and a new job is created that points to that window group, the job is not started until the next window in the window group opens.

See "Creating Window Groups" for examples of creating window groups.

External Jobs

This section discusses the Scheduler's support for external jobs.

An external job is an operating system executable that runs outside the database. For an external job, job_type is specified as EXECUTABLE. (If using named programs, the corresponding program_type would be EXECUTABLE.) The job_action (or corresponding program_action if using named programs) is the full operating system–dependent path of the desired external executable, excluding any command line arguments. An example might be /usr/local/bin/perl or C:\perl\bin\perl. The program or job arguments for type EXECUTABLE must be a string type such as CHAR or VARCHAR2. They are set with the DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE procedure.

Most, but not all, platforms support external jobs. For platforms that do not support external jobs, creating or setting the attribute of a job or a program to type EXECUTABLE returns an error. See your operating system-specific documentation for more information. Note that a Windows batch file is not directly executable and must be run with cmd.exe.

Like any Scheduler job, you can assign a schema when you create the job. That schema then becomes the job owner. Note that although it is possible to create an external job in the SYS schema, Oracle recommends against this practice.

The CREATE JOB and CREATE EXTERNAL JOB privileges are both required for any schema that runs external jobs.

External jobs must run on the host computer as some operating system user. Thus, you must be able to assign operating system credentials to any external job that you create. You do so with a database object introduced in Oracle Database 11g Release 1 called a credential.

There are two types of external jobs: local external jobs and remote external jobs. Local external jobs run on the same computer as the database that schedules them. Remote external jobs run on a remote host—that is, on a host computer other than the computer running the database that schedules them.

Credentials, local external jobs, and remote external jobs are discussed in detail in the following sections:

• About Credentials

• About Local External Jobs

• About Remote External Jobs

BEGIN
 DBMS_SCHEDULER.CREATE_CREDENTIAL('HRCREDENTIAL', 'HR', 'hr001515');
END;

GRANT EXECUTE ON SYSTEM.HRCREDENTIAL to HRJOBUSER;

Scheduler Support for Oracle Data Guard

Beginning with Oracle Database 11g Release 1, the Scheduler can run jobs based on whether a database is a primary database or a logical standby in an Oracle Data Guard environment.

For a physical standby database, any changes made to Scheduler objects or any database changes made by Scheduler jobs on the primary database are applied to the physical standby like any other database changes. For the primary database and logical standby databases, there is additional functionality that enables you to specify that a job can run only when the database is in the role of the primary database, or can run only when the database is in a logical standby role.

You do this using the DBMS_SCHEDULER.SET_ATTRIBUTE procedure to set the database_role job attribute to one of two values: PRIMARY or LOGICAL STANDBY. (To run a job in both roles, you can make a copy of the job and set database_role to PRIMARY for one job and to LOGICAL STANDBY for the other).

On switchover or failover, the Scheduler automatically switches to running jobs specific to the new role. DML is replicated to the job event log so that on failover, a record of what had run successfully on the primary database until it failed is available.