CHAPTER 21

The Scheduler

Exam Objectives

• 062.14.1    Use Oracle Scheduler to Simplify Management Tasks

• 062.14.2    Use Job Chains to Perform a Series of Related Tasks

• 062.14.3    Use Scheduler Jobs on Remote Systems

• 062.14.4    Use Advanced Scheduler Features to Prioritize Jobs

This chapter introduces the Scheduler, which is a facility to automate the running of jobs. These jobs may be procedures that execute within the database, or they may be operating system commands and scripts. They can run locally or on remote machines and databases. They can run according to a schedule, according to the results of a previous job, or in response to events. The Scheduler is integrated with Resource Manager.

An alternative (and older) scheduling mechanism is DBMS_JOB. However, the Scheduler is far more capable (and complex). A job-scheduling facility is also provided by Enterprise Manager, and of course your operating system will have a job scheduler. The Scheduler is probably more sophisticated than any alternative solution.

Use Oracle Scheduler to Simplify Management Tasks

You can configure the Scheduler by creating various objects. At a minimum, a job is all that is needed. But usually, more objects will be involved, at least programs and schedules. In many environments, job classes, windows, and chains are also useful. And at the instance level, the background processes control the environment and run the jobs.

The Scheduler Architecture

The data dictionary includes a table that is the storage point for all Scheduler jobs. You can query this table through the DBA_SCHEDULER_JOBS view. The job queue coordinator background process (the CJQ0 process) monitors this table and, when necessary, launches job queue processes (the Jnnn processes) to run the jobs. The CJQ0 process is launched automatically if there are any defined and active Scheduler jobs. The Jnnn processes are launched on demand, although the maximum number is limited by the JOB_QUEUE_PROCESSES instance parameter, which can have any value from 0 to 1000 (the default). If set to zero, the Scheduler will not function.

The job queue coordinator picks up jobs from the job queue table and passes them to job queue processes for execution. It also launches and terminates the job queue processes on demand. To see the processes currently running, query the V$PROCESS view.

This query shows that the job queue coordinator and two job queue processes are running. In a Unix instance, the processes will be separate operating system processes (as in this query); in a Windows instance, they execute as threads within the ORACLE.EXE process.

Jobs defined as procedures run within the database. Jobs can also be defined as operating system commands or shell scripts; these will run as external operating system tasks. The triggering factor for a job can be a time or an event. Time-based jobs can run once or repeatedly according to a schedule. Event-based jobs run when certain conditions arise. There are some preconfigured events, or you can use user-defined events. Jobs can be connected into a chain using simple rules for branching depending on a job’s success or failure.

An advanced feature of the Scheduler is to associate it with Resource Manager. It may be that certain jobs should be run with certain priorities, and this can be achieved by linking a job to a Resource Manager consumer group via a job class. It is also possible to use the Scheduler to activate a Resource Manager plan, rather than having to activate a plan manually by changing the RESOURCE_MANAGER_PLAN instance parameter or using the DBMS_RESOURCE_MANAGER.SWITCH_PLAN procedure call.

The application programming interface (API) to administer the Scheduler is the DBMS_SCHEDULER package. Also, Enterprise Manager provides a graphical interface.

Scheduler Objects

The most basic object in the Scheduler environment is a job. A job can be completely self-contained, defining the action to be taken and when to take it. In a more advanced configuration, the job is only part of the structure consisting of a number of Scheduler objects of various types.

Jobs

A job specifies what to do and when to do it. The “what” can be an anonymous PL/SQL block (which could consist of just a single SQL statement), a PL/SQL stored procedure (which could invoke a Java stored procedure or an external procedure), or any executable file stored in the server’s file system (either a binary executable or a shell script). A particularly powerful capability (beyond the scope of the Oracle Certified Professional [OCP] curriculum) is the remote external job, which runs on a separate machine. The “when” specifies either the timestamp at which to launch the job and a repeat interval for future runs or the triggering event.

You have several options when creating a job, as shown from looking at the DBMS_SCHEDULER.CREATE_JOB procedure. This procedure is overloaded; it has no fewer than six forms. Figure 21-1 shows part of the output from a DESCRIBE of the DBMS_SCHEDULER package, with the first two forms of CREATE_JOB.

Figure 21-1    The specification of the CREATE_JOB procedure

All forms of the CREATE_JOB procedure must specify a JOB_NAME. This must be unique within the schema where the job is created. Note that jobs are schema objects. Then, taking the first form of the procedure, the JOB_TYPE must be one of the following:

•  PLSQL_BLOCK    An anonymous PL/SQL block

•  STORED_PROCEDURE    A named PL/SQL procedure

•  EXECUTABLE    Anything executable from an operating system (OS) prompt

•  CHAIN    A named job chain object

•  EXTERNAL_SCRIPT    A script launched by the OS’s command interpreter

•  SQL_SCRIPT    A SQL*Plus script

•  BACKUP_SCRIPT    An RMAN script

The JOB_ACTION is the command or script or chain to be run. The NUMBER_OF_ARGUMENTS parameter states how many arguments the JOB_ACTION should take.

The remaining arguments of the first form of the procedure shown in Figure 21-1 are details of when and how frequently to run the job. The first execution will be on the START_DATE; the REPEAT_INTERVAL defines a repeat frequency, such as daily, until END_DATE. JOB_CLASS has to do with priorities and integration of the Scheduler with Resource Manager. The ENABLED argument determines whether the job can actually be run. Perhaps surprisingly, this defaults to FALSE. If a job is not created with this argument on TRUE, it cannot be run (either manually or through a schedule) without being enabled first. Finally, AUTO_DROP controls whether to drop the job definition after the END_TIME. This defaults to TRUE. If a job is created with no scheduling information, it will be run as soon as it is enabled and then dropped immediately if AUTO_DROP is on TRUE, which is the default.

The second form of CREATE_JOB shown in Figure 21-1 creates an event-based job. The EVENT_CONDITION is an expression based on the definition of the messages enqueued to the queue table nominated by the QUEUE_SPEC argument. Between the start and end dates, Oracle will monitor the queue and launch the job whenever a message arrives that conforms to the condition.

Programs

Programs provide a layer of abstraction between the job and the action it will perform. They are created with the DBMS_SCHEDULER.CREATE_PROGRAM procedure.

By pulling the “what” of a job out of the job definition itself and defining it in a program, you can reference the same program in different jobs and thus associate it with different schedules and job classes, without having to define it many times. Note that (as for a job) a program must be ENABLED before it can be used; the default for this is FALSE.

Schedules

A schedule is a specification for when and how frequently a job should run. The basic principle of a schedule is to pull the “when” portion out of a job, thus associating it with different jobs. It is created with the DBMS_SCHEDULER.CREATE_SCHEDULE procedure.

The START_DATE defaults to the current date and time. This is the time that any jobs associated with this schedule will run. The REPEAT_INTERVAL specifies how frequently the job should run until the END_DATE. Schedules without a specified END_DATE will run forever.

The REPEAT_INTERVAL argument can take a wide variety of calendaring expressions. These consist of up to three elements: a frequency, an interval (defaulting to 1), and possibly several specifiers. The frequency may be one of these values:

•  YEARLY

•  MONTHLY

•  WEEKLY

•  DAILY

•  HOURLY

•  MINUTELY

•  SECONDLY

The specifiers can be one of these:

•  BYMONTH

•  BYWEEKNO

•  BYYEARDAY

•  BYMONTHDAY

•  BYHOUR

•  BYMINUTE

•  BYSECOND

Using these elements of a REPEAT_INTERVAL makes it possible to set up schedules that should satisfy any requirement. For example, the following will run the job every 12 hours, starting at the START_DATE:

The next example will run the job on the second day of each of the named three months, starting as early in the day as resources permit:

The final example will run the job at ten past six on alternate Mondays:

Using programs and schedules normalizes the job structure, allowing reuse of predefined programs and schedules for many jobs, as shown in Figure 21-2. Note that the figure includes other Scheduler objects and also refers to Resource Manager objects Groups, Directives, and Plans.

Figure 21-2    A normalized view of the Scheduler and Resource Manager objects

A Self-Contained Job

To create and schedule a job with one procedure call, use the CREATE_JOB procedure. For example, the following will create an enabled job that calls the procedure HR.REFRESH_SUMMARIES at 11 P.M. on Mondays and Fridays, starting today:

The job is created in the HR schema.

Exercise 21-1: Create a Job with the Scheduler API    Use the DBMS_SCHEDULER package to create a job and confirm that it is working.

1.  Connect to your database as user SYSTEM using SQL*Plus.

2.  Create a table to store times and set your date format to show the date and time.

3.  Create a job to insert the current time into the table every minute.

4.  Query the job table a few times to see that the job is scheduled and running.

Query the times table to demonstrate that the inserts are occurring.

5.  Disable the job.

6.  Rerun the queries from step 4 to confirm that the job is disabled and that no more inserts are occurring.

7.  Drop the job.

Use Job Chains to Perform a Series of Related Tasks

A chain represents a set of linked programs with execution dependencies. The logic connecting the steps in a chain is branching based on a success/failure test.

Here are the steps to follow to use job chains:

1.  Create a chain object.

2.  Define the steps (the individual programs) of the chain.

3.  Define the rules connecting the steps.

4.  Enable the chain.

5.  Create a job to launch the chain.

This code fragment demonstrates the first four steps:

These commands create and enable a simple chain of three steps. The execution of either the second or third step is dependent on the outcome of the first step. The syntax for creating rules permits the use of keywords such as SUCCEEDED and FAILED, which will test the outcome of another step. In this example, it is assumed that the programs have already been created with CREATE_PROGRAM procedure calls.

To launch the chain, you must create a job. This could be based on a schedule or an event. This job will run the chain on the last Tuesday of alternate months:

Use Scheduler Jobs on Remote Systems

It is possible to schedule and run jobs on remote systems. These jobs can be database jobs (typically calling a PL/SQL procedure) or external jobs (typically shell scripts). The job definition exists in the calling database, but the procedure or shell script must reside at the remote site.

Every machine on which a remote job is to run requires a Scheduler Agent. Install this agent from the Oracle Client CD, selecting the Custom Install option. The agent process will, by default, listen on port 1500, although any port can be selected at install time. The protocol between agent and database is Hypertext Transfer Protocol (HTTP). The database and listener will already be configured to accept HTTP if Database Express has been configured. To confirm the listening port and set it if it has not been configured, use procedures in the DBMS_XDB_CONFIG package.

Once communications have been established between database and agent, create jobs that specify the DESTINATION_NAME argument of the CREATE_JOB procedure, giving the network name of the remote machine.

Use Advanced Scheduler Features to Prioritize Jobs

The more advanced capabilities of the Scheduler enable you to integrate it with Resource Manager to control and prioritize jobs. These are the relevant components:

•  Job classes    Jobs can be assigned a class, and a class can be linked to a Resource Manager consumer group. Classes also control the logging level for their jobs.

•  Consumer groups    Resource Manager consumer groups are restricted in the resources they can use, being limited in, for instance, central processing unit (CPU) usage or the number of active sessions.

•  Resource plans    A Resource Manager plan defines how to apportion resources to groups. Only one plan is active in the instance at any one time.

•  Windows    A window is a defined (probably recurring) period of time during which certain jobs will run and a certain plan will be active.

•  Window groups    It is possible to combine windows into window groups for ease of administration.

Prioritizing jobs within a window is done at two levels. Within a class, jobs can be given different priorities by the Scheduler, but because all jobs in a class are in the same consumer group, Resource Manager will not distinguish between them. But if jobs in different classes are scheduled within the same window, Resource Manager will assign resources to each class according to the consumer groups for that class.

Using Job Classes

Create a class with the DBMS_SCHEDULER API. Here is an example:

Then assign the jobs to the class, either at job creation time by specifying the JOB_CLASS attribute or by modifying the job later. To assign a job to a class after creation, you must use the SET_ATTRIBUTE procedure. Here is how to create a job called REPORTS_JOB and place it in the class just created:

If there are several jobs in the one class, prioritize them with further SET_ATTRIBUTE calls.

If several jobs in the same class are scheduled to be executed at the same time, the job priority determines the order in which jobs from that class are picked up for execution by the job coordinator process. It can be a value from 1 to 5, with 1 being the first to be picked up for job execution. The default for all jobs is 3. This could be critical if, for example, the class’s consumer group has an active session pool that is smaller than the number of jobs; those jobs with the highest priority will run first, while the others are queued.

Logging levels are also controlled by the job’s class. There are three options:

•  DBMS_SCHEDULER.LOGGING_OFF    No logging is done for any jobs in this class.

•  DBMS_SCHEDULER.LOGGING_RUNS    Information is written to the job log regarding each run of each job in the class, including when the run was started and whether the job ran successfully.

•  DBMS_SCHEDULER.LOGGING_FULL    In addition to logging information about the job runs, the log will record management operations on the class, such as creating new jobs.

To view logging information, query the DBA_SCHEDULER_JOB_LOG view.

More detailed information is written to the DBA_SCHEDULER_JOB_RUN_DETAILS view, including the job’s run duration and any error code it returned. Logging information is cleared by the automatically created PURGE_LOG job. By default, this runs daily according to the preconfigured schedule DAILY_PURGE_SCHEDULE and will remove all logging information more than 30 days old.

Using Windows

Create windows with the CREATE_WINDOW procedure. Here is an example:

This window activates a Resource Manager plan called NIGHT_PLAN. This might be a plan that gives priority to the DSS consumer groups over the OLTP group. It opens according to the schedule WEEKDAY_NIGHTS, which might be Monday through Friday at 20:00. The window will remain open for four hours; the DURATION argument accepts an INTERVAL DAY TO SECOND value, as does the REPEAT_INTERVAL for a schedule. Setting the priority to LOW means that if this window overlaps with another window, the other window will be allowed to impose its Resource Manager plan. This would be the case if you created a different window for your end-of-month processing and the end-of-month happened to be on a weekday. You could give the end-of-month window HIGH priority to ensure that the end-of-month Resource Manager plan, which could give top priority to the BATCH group, does come into effect.

If two windows with equal priority overlap, the window with the longest duration will open (or remain open). If both windows have the same time to run, the window currently open will remain open.

Exercise 21-2: Use Scheduler Windows to Control Resource Manager    In this exercise, you will use the Scheduler to automate the activation of the Resource Manager plan MY_PLAN created in Exercise 20-1.

1.  Connect to your database as user SYSTEM with SQL*Plus.

2.  Run this query to determine whether a window is currently open:

3.  Run this query to determine which Resource Manager plan is currently active:

4.  Temporarily clear whatever Resource Manager plan may be currently active.

5.  Confirm that there is no Resource Manager Plan active.

Rerun the query from step 3. This will show that the INTERNAL_PLAN is active, which is the plan used when no other has been set.

6.  Execute this procedure call to create a window named MY_WINDOW that will activate the MY_PLAN plan:

This will open the window from now onward every day at 6 A.M. for 12 hours.

7.  Force the database to open the new window immediately.

This procedure call will open the window immediately and activate its plan but only for five minutes.

8.  Rerun the queries from steps 2 and 3 to confirm that the DAYTIME window is open and the MY_PLAN plan is active.

9.  After five minutes, repeat step 8. You will see that the window has closed and that no plan is active. This situation will persist until the next scheduled opening of a window.

10.  Tidy up.

Two-Minute Drill

Use Oracle Scheduler to Simplify Management Tasks

•  A job can specify what to do and when to do it, or it can point to a program or a schedule.

•  A job (or its program) can be an anonymous PL/SQL block, a stored procedure, or an external operating system command or script.

•  Jobs are launched by the CJQ0 background process and executed by Jnnn processes.

•  Jnnn processes are launched on demand, up to the limit set by the JOB_QUEUE_PROCESSES parameter. Setting this to zero disables the job system.

Use Job Chains to Perform a Series of Related Tasks

•  A chain object consists of a number of steps.

•  Each step can launch a program.

•  Simple logic (such as the success or failure of a previous step) can control the flow of execution through a job chain with branching steps.

•  The chain itself is launched by a job, triggered by either an event or a schedule.

Use Scheduler Jobs on Remote Systems

•  Remote jobs can run in remote databases or on remote hosts.

•  Every system where a remote job may run must run a Scheduler Agent.

•  Remote jobs are defined in the source database and sent to the remote agent with credentials.

•  Create a remote job by specifying the DESTINATION argument of CREATE_JOB.

Use Advanced Scheduler Features to Prioritize Jobs

•  Jobs can be prioritized at two levels: Resource Manager will allocate resources via consumer groups to all the jobs in a class, and the class will prioritize the jobs within it according to the job priority set by the Scheduler.

•  Scheduler priority varies between levels 1 and 5 (highest to lowest).

Self Test

1.  When a job is due to run, what process will run it? (Choose the best answer.)

A.  A CJQn process

B.  A Jnnn process

C.  A server process

D.  A background process

2.  Which of the following is a requirement if the Scheduler is to work? (Choose the best answer.)

A.  The instance parameter JOB_QUEUE_PROCESSES must be set.

B.  A Resource Manager plan must be enabled.

C.  A schedule must have been created.

D.  All of the above.

E.  None of the above.

3.  A Scheduler job can be of several types. (Choose all that apply.)

A.  An anonymous PL/SQL block

B.  An executable operating system file

C.  A PL/SQL stored procedure

D.  A Java stored procedure

E.  An operating system command

F.  An operating system shell script (Unix) or batch file (Windows)

4.  How can jobs best be chained together? (Choose the best answer.)

A.  Put them in the same class and use priorities to control the running order.

B.  Create them as programs, connected in a chain.

C.  Assign them to different schedules, timed to start consecutively.

D.  Run them within a window, giving them window priorities.

5.  Which process runs a remote database job? (Choose the best answer.)

A.  A job queue process on the originating machine

B.  A job queue process on the destination machine

C.  The Scheduler Agent on the destination machine

D.  The Enterprise Manager agent on the destination machine

6.  You create a job with the syntax

and find that it is not running when expected. What might be a reason for this? (Choose the best answer.)

A.  The schedule is associated with a window, which has not opened.

B.  The job has not been enabled.

C.  The class is part of a Resource Manager consumer group with low priority.

D.  The permissions on the job are not correct.

7.  What are the possible priority levels of a job within a class? (Choose the best answer.)

A.  1 to 5.

B.  1 to 999.

C.  HIGH or LOW.

D.  It depends on the Resource Manager plan in effect.

8.  You want a job to run every 30 minutes. Which of the following possibilities for the REPEAT_INTERVAL argument are correct syntactically and will achieve this result? (Choose three answers.)

A.  'freq=minutely;interval=30'

B.  'freq=hourly;interval=1/2'

C.  '0 00:30:00'

D.  'freq=minutely;byminute=30'

E.  'freq=byminute;interval=30'

9.  You create a job class, and you set the LOGGING_LEVEL argument to LOGGING_RUNS. What will be the result? (Choose the best answer.)

A.  There will be a log entry for each run of each job in the class but no information on whether the job was successful.

B.  There will be a log entry for each run of each job in the class and information on whether the job was successful.

C.  There will be a single log entry for the class whenever it is run.

D.  You cannot set logging per class, only per job.

Self Test Answers

1.      B. Jobs are run by job queue processes.
    A, C, and D are incorrect. The job queue coordinator does not run jobs; it assigns them to job queue processes. These are ephemeral processes, not background processes that run continuously, and they are not server processes.

2.      E. The Scheduler is available by default, with no preconfiguration steps needed.
    A, B, C, and D are incorrect. A is incorrect because the JOB_QUEUE_PROCESSES instance parameter defaults to 1000; therefore, it does not need to be set. B and C are incorrect because Resource Manager is not required, and neither are schedules. Therefore, D is incorrect also.

3.      A, B, C, D, E, and F. The JOB_TYPE can be PLSQL_BLOCK, STORED_PROCEDURE (which can be PL/SQL or Java), EXECUTABLE (which includes executable files and OS commands), or EXTERNAL_SCRIPT (either a shell script or a SQL*Plus script).
    All the answers are correct.

4.      B. This is the best way, connecting them with branches based on success or failure.
    A, C, and D are incorrect. A and C are incorrect because although they might work, there is no guarantee of this. D is incorrect because window priorities control which window will open, not which job runs within a window.

5.      B. PL/SQL jobs are always run by job queue processes in the instance where the job runs.
    A, C, and D are incorrect. A is incorrect because a job queue process can run jobs only within its local instance. C is incorrect because although the agent launches the job, it does not actually run it. D is incorrect because the Enterprise Manager job system is not part of the Scheduler job system.

6.      B. The job will, by default, not be enabled and therefore cannot run.
    A, C, and D are incorrect. A is incorrect because the job is not controlled by a window but by a schedule. C is incorrect because although Resource Manager can control job priority, it would not in most circumstances block a job completely. D is incorrect because although permissions might cause a job to fail, they would not stop it from running.

7.      A. Job priorities are 1 to 5 (highest to lowest).
    B, C, and D are incorrect. B is incorrect because it is the wrong range. C is the choice for window priority, not job priority. D is incorrect because Resource Manager controls priorities between classes, not within them.

8.      A, B, and D. These will provide a half-hour repeat interval.
    C and E are incorrect. C is incorrect because it is the syntax for a window’s duration, not a repeat interval. E is syntactically incorrect.

9.      B. With logging set to LOGGING_RUNS, you will get records of each run of each job, including the success or failure.
    A, C, and D are incorrect. A is incorrect because LOGGING_RUNS will include the success or failure. C and D are incorrect because even though logging is set at the class level, it is applied at the job level. Note that logging can also be set at the job level.