Mastering Oracle Scheduler in Oracle 11g Databases: Schedule, manage, and execute jobs in Oracle 11g Databases that automate your business processes using Oracle Scheduler with this book and eBook

Before we start, we need to agree on what a job is. A job is a list of tasks that perform an action. This list can contain any number of tasks. The jobs we created in the previous chapter are valid jobs that all perform a single task. Next, we will see how the above-mentioned backup scenario can be fitted into a chained job. We will also see that a chained job doesn't mean that it will work correctly as there are opportunities for errors.

If we want to combine multiple logically-connected jobs or tasks, we should build a job chain. As the name implies, a chain is simply a group of concurrent or sequential jobs. Tasks that have no relation to the other tasks should be scheduled individually, and not as a part of a job chain.

In other words, an individual task can get skipped because another part in the chain has been skipped.

Clearly, skipping tasks in any chain is probably not something we want. In a job that consists of multiple steps, we want to see some kind of relationship between the steps. We describe this relationship with rules. The rules in a chain define the order in which the job steps are executed (or, maybe, not executed). Execution depends on the status of the previous job steps. Let's list the statuses and their meanings.

The following is the list of statuses that we can use in rules to control what should be done next:

As mentioned earlier, a chain consists of multiple steps that are logically connected together. We cannot say anything about the behavior of these steps in the job chain. In order to know when these steps are going to be executed, we have to know what rules are applied to them. All steps have a name. We are going to reference these names of steps in our rules, which we will define later. Now, let's return to the backup example and list the steps there:

Without rules, nothing much will happen. The Scheduler will be looking for a step to start, but will be unable to find it. As soon as the Scheduler finds itself in a situation where it is given a chain and it cannot determine what to do next, the job gets a special job status—CHAIN_STALLED. This status means that the Scheduler does not know how to handle the...

Rules in a chain describe the way the Scheduler should read the job description. A chain must have a proper starting point and end point. Steps in a chain are activated when the rules in the chain definition evaluate to true (as rules are Boolean) and mention the specific step names to be started.

Let's return to the backup example once more. We know there is only one location where it makes sense to start the job—the step that shuts down the database. How can we make the Scheduler know that? Let's start with an overview of what we know. The steps are:

As Steven Jobs would often say:

There is one more thing to say about rules. The Scheduler normally checks what to do at the start and the end of each and every job step. In many situations this may be good enough, but there are situations where something more is required. One example is where one step of a job not only depends on the status of a former step, but also on the row count of a table or on the time of day. If this is the case, it would be best if the job step starts running as soon as the condition becomes true; and not after we had to manually intervene to make the Scheduler evaluate the chain again. We can give the chain an evaluation interval to make the Scheduler not only check the status of the job when it starts the job (or a job_step ends), but also to repeat the evaluations on a timed basis. To run these repeated evaluations, we have to specify evaluation_interval. The evaluation interval is the way to make the Scheduler check...

Before we can go any further, we need some extra privileges. These privileges will come along with the CREATE JOB privilege that we saw in the previous chapter. We need to be able to create rule sets, rules, and evaluation contexts. We can do this by using the DBMS_RULE_ADM package to grant the user, MARVIN, the required privileges:

--/
BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE (DBMS_RULE_ADM.CREATE_RULE_OBJ, 'MARVIN');
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE (DBMS_RULE_ADM.CREATE_RULE_SET_OBJ, 'MARVIN');
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE (DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ, 'MARVIN');
END;
/

This tiny bit of code gives MARVIN the privileges to create rule sets, rules, and evaluation contexts.

We discussed earlier in the chapter what a rule is. A rule set, on the other hand, is a collection of the rules that control the chain. Mostly, we will not deal with rule sets as individual items and we will not even need to assign a name to them (but we could do so if we wanted...

When creating a chain, there are certain steps that need to be followed:

Let's go through this list of actions in the upcoming sections.

Let's get our hands dirty and play with chains for a while. The functionality of the programs we use in the chains is not important. What is important is that we can see what happens and why. We will use three programs that perform their own very important task: the first program for shutting down the database, the second program for copying the files, and the third program for starting the database after backing up.

All the three programs call dbms_lock.sleep to enable us to study job logging and see how the chains we have created are run. This is enough for us to simulate the backup scenario we have seen before. In other words, for this example, dbms_lock.sleep is used as a placeholder for the important tasks that the job may eventually run.

We can use DB Console's Job Scheduler admin pages to generate the code; the resulting code was given above. Here is how you can build the programs in DB Console. If you want to use DB Console, log in and navigate to the Server tab in DB Console as shown in the following screenshot:

Select the Programs link under the Oracle Scheduler header, and then hit the Create button as shown in the following screenshot:

In the following dialog box that appears, enter the names and code as specified by using the values suggested earlier:

Repeat this for the other two programs. In the end, we should have the three programs—SHUTDOWN, COPYFILES, and STARTUP—in the programs overview as shown in the following screenshot. See to it that MARVIN has all the three programs as discussed.

To run the job chain you can use the following:

begin
dbms_scheduler.run_job
(
job_name => 'j_cold_backup', use_current_session => false
);
end;

When the job is running, check the contents of the user_scheduler_running_jobs and user_scheduler_running_chains views. Officially, it is impossible for a chain to be listed in *_scheduler_running_chains without it being listed in scheduler_running_jobs as well. If you find this situation on your system, you need to apply the patch for bug 5705385. This bug is fixed in 11.1.0.7 and 10.2.0.5 with back ports available to 10.2.0.4 and 10.2.0.3.

In the user_scheduler_running_chains view, the complete execution of the chain is visible—as long as the chain is running, of course. This means that all the steps that are defined for the chain are visible, including step_name, state, error_code, start_time, and duration. We can use these steps to see how the chain progresses and what steps are still waiting to get started. step_job_subname...

The fact that we use chains does not mean that we cannot build a disaster backup scenario! Using chains does not automatically mean that we can only use it in a predetermined way. In fact, we can make the job run exactly as the original scripts did and still have all the steps in one chain. Chain rules allow us to start a step, following a delay.

Let's assume that we want an action to happen after a delay of 1 minute and 5 seconds. In that case, we would code the rule with after 00:01:05 start x. This will start step x after a delay of 1 minute and 5 seconds. However, the system administrator who created the first backup scenario can accomplish exactly the same with the following rules:

true start shutdown
true after 00:05:00 start startup
true after 00:00:30 start copyfiles
startup succeeded end 0

The following screenshot shows how these rules will look in DB Console:

Implementing these rules will shut down the database, copy the files, and then start up the database...

Chains are very powerful, but they also provide opportunities for errors that we have never seen before. One of the errors is forgetting (as system administrator) how one or more rules might cause our chain to get stalled. Another error is forgetting the evaluation interval for a chain that depends on something that is not a part of the chain. Think about a chain step that is supposed to run only when the current number of sessions is below a specified value. This status could be true when the previous step ends. If not, the number of sessions should be evaluated repeatedly until the step is allowed to start.

In the situation where we can see that the chain step is ready to run (because all the prerequisites are met), but we forgot to implement the evaluation interval, we can manually force the job to evaluate its rules using this piece of code:

begin
dbms_scheduler.evaluate_running_chain (job_name => 'a_running_job' );
end;
/

This piece of code will evaluate...

Chains can quickly become complex. Complexity makes it harder for us to have the rule definitions complete in a chain. Therefore, Oracle decided to build the dbms_scheduler.analyze_chain procedure. This procedure is of particular interest when we are lost. The exact definition is yet unknown, but it can be used to list all the dependencies of a chain. With a little luck, the results are presented in such a way that we can see what is missing or what is wrong. Here is an example based on our famous backup chain:

--/
declare
rules sys.scheduler$_rule_list;
steps sys.scheduler$_step_type_list;
zout sys.scheduler$_chain_link_list;
begin
dbms_scheduler.analyze_chain
(
chain_name      => 'c_cold_backup',
rules           => rules,
steps           => steps,
step_pairs      => zout
);
end;
/

The output is as shown as follows:

SHUTDOWN of type PROGRAM depends on "BEGIN" of type BEGIN . Based on rule MARVIN SCHED_RULE$6 of type START
COPYFILES of type PROGRAM depends...

In this chapter, we have seen:

In the next chapter, we will see how to find out what happens in the Scheduler, and how we can keep the database happily running the Scheduler.