8. The Scheduler in Real Life – Mastering Oracle Scheduler in Oracle 11g Databases

Chapter 8. The Scheduler in Real Life

In this chapter, we will see how we can incorporate Oracle Scheduler into real-life scenarios and solve various problems in a variety of ways. The scenarios themselves are not really important, but they are real enough to show the power of Oracle Scheduler.

One of the first things to consider when thinking about what to schedule in the Scheduler database is the statistics collection. This is the collection of the optimizer statistics in the database. The challenge here is to have the statistics run whenever they are needed, without slowing down the business application that is supposed to run on the database.

Another thing to consider is running the backups. For the backups, we can use the remote external job agent. Here, we can use the Scheduler Agent and see how to use the Scheduler events to react on the results of a job. This can be seen as an advanced form of chaining where we do not build a chain as an object definition, but as a chain defined by events that call for actions.

Statistics collection

From Oracle 10g onwards, Oracle has included a job that automatically collects optimizer statistics for us. So, there is no real need to make such a process again. It might look like inventing a different-colored wheel, but it can be useful if you want more control on how the statistics are collected.

For this, we will make a simple package named sched_stats. As many readers are more than likely to be still using Oracle 10g, the code that we will use is compatible for Oracle 10g as well as 11g. The package has the following four procedures:

  • schedule_run

  • run

  • statob

  • Drop_jobs

The schedule_run procedure

The Schedule_run procedure creates the job that is actually scheduled on a twice-daily basis and has all the parameters with defaults that can be overridden:

procedure schedule_run
( jobclass in varchar2 default 'DEFAULT_JOB_CLASS',
jobname in varchar2 default 'run_stats',
sjobprefix in varchar2 default 'stats_',
repinterval in varchar2 default'FREQ=DAILY;BYHOUR=5,17;BYMINUTE=4',
pctchange in number default 5);

This procedure simplifies the scheduling of the generation run. The result of schedule_run procedure can be replaced by a Grid Control session in which we create a job that calls the run procedure. The schedule_run procedure is most useful when deploying the sched_stats package and the job in many databases using a standard script.

The run procedure

The run procedure generates a single job for every object that is to be analyzed and whenever possible, it uses the cascade option. So in the end, it is possible that when analyzing a table, the indexes that belong to that table are also analyzed. For the partitioned tables, the run procedure generates a job for every partition that needs new statistics, and not for the table by itself. This can save a lot of time. Sometimes a partitioned table has global indexes. If this is the case, such an index has to be analyzed on its own and not as a cascade of the table because we do not make global statistics for the table.

For larger objects, we use a larger degree of parallelism than for small objects. For objects that have no statistics at all, we generate a job with top priority where the other jobs have normal priority. The interface for the run procedure is as follows:

procedure run ( stale_pct in number, sjob_class in varchar2 default 'DEFAULT_JOB_CLASS', sjob_prefix in varchar2 default null);

The job that is generated by the run procedure is of a type PL/SQL and it calls the statob procedure. This procedure makes the actual call to dbms_stats to collect the requested statistics for the specified object. The statob procedure will be detailed next.

The statob procedure

The statob procedure performs the actual analyze the task and is called by the generated jobs. The interface to statob is as follows:

procedure statob ( owner in varchar2, object_type in varchar2, object_name in varchar2, partition_name in varchar2, psample_pct in number, pdegree in number);

If you leave all of the defaults in place, it is called by the stats_NNNN jobs that are generated by the run procedure. Every object that is selected by the run procedure will be processed by a job of its own that calls this statob procedure with the parameters it needs.

The drop_jobs procedure

At this point, we may suddenly decide that we do not want the generated jobs to run anymore. In this case, we can call the drop_jobs procedure that drops all the generated jobs that are not running, using the following code:

procedure drop_jobs ( sjobprefix in varchar2 default 'stats');

In case we don't want the already generated jobs to run, we can call the sched_stats package using the following code:

begin
sched_stats.drop_jobs;
end;
/

And this code will take care of removing all generated STATS_NNNN jobs that are not already running. However, this assumes you left all the defaults in place.

Generating the jobs

As mentioned before, the run procedure generates jobs. The jobs get a name that is generated by combining a self-chosen prefix that defaults to STATS, with a sequence number added to it in a call to dbms_scheduler.generate_job_name. This ensures that all the generated job names are unique. The run procedure checks the DBA_TAB_MODIFICATION view and uses this information combined with ALL_TABLES, ALL_TAB_PARTITONS, and ALL_TAB_STATISTICS views to find table or partitions that should be analyzed. A table or partition should be analyzed if more than stale_pct of its rows have been modified, added, or deleted.

A table or partition should not be analyzed if the statistics are already locked for different reasons. One reason for this may be that it enables us to manage the statistics for the locked table by hand (and not by an automated procedure), which works well for most tables.

Note

Some tables have very irregular behaviors. For instance, during a batch run, a table can be empty while the generation of the stats process takes place. After that, the application will see the statistics where the table seems to be empty; whereas in reality, it already has millions of rows loaded. If this is the case, the statistics for such a table have to be managed by the application and should be locked to prevent them from being analyzed by the generic procedures. The problem is that when the optimizer sees that a table has no rows, it will make an execution plan that is different than when it sees that there are a hundred million rows in place. In such a situation where the application has a very big variation in the number of rows during a run, it is smarter to have the application generate the statistics at the appropriate moments.

Let's dive into some pieces of the code to explore how we can generate the jobs.

Firstly, for every table or partition that is selected, we create a separate job with a name that is defined by a given prefix of sjob_prefix. The sjob_prefix is the prefix that we give during the schedule_run procedure call, and the default is stats_. The code that generates the job names is:

begin
l_job_name := dbms_scheduler.generate_job_name (sjob_prefix);

generate_job_name generates a unique job name with the specified prefix followed by a serial number. If the prefix is STATS_, the generated name could be STATS_1 or STATS_2009, depending on how often a job name has been generated this way.

As we can only specify a stored procedure as a valid job_type and not a package procedure, the generated job is of the type PLSQL_BLOCK.

In the PL/SQL block, we will call the statob procedure with the parameters needed. The parameters for the statob procedure are owner, object_name, object_type, and optionally, partition_name. The parallel degree is to be used for sampling and the percentage of the table is to be used for sampling the table. For these kinds of short-lived jobs, we specify auto_drop = true, which ensures that the job is dropped at the end of the run. The job class defines how the job will be handled by the Resource Manager and when the job log entries will be purged. The following snippet of code is used to create the job using the generated name and passing it the arguments that eventually will be passed to dbms_stats:

dbms_scheduler.create_job(job_name => l_job_name,
job_type => 'PLSQL_BLOCK',
job_action => 'begin'||'sched_stats.statob('''||i.table_owner||''', '''||i.table_name||''', '''||i.partition_name||''', '||''||i.degree||'); end;', comments => 'gather stats for '|| i.table_owner||'.'||i.table_name||' '||i.partition_name, job_class => sjob_class, auto_drop => true, enabled => false);

In this PL/SQL block, we simply call the second procedure of the package, STATOB, as it does the actual analysis of the specified table or partition.

If a table or a partition is selected to be analyzed, the segmentsize controls the degree of parallelism that is used to perform the analysis of the object. Jobs have the job_weight attribute. According to the manual, this attribute can be used to do the advanced things involving parallelism. It can be very tempting to use it and assign it the same value as the degree that we use to analyze the object we are working on. It might look like a good idea, but the definition is very vague and when used, it can very well happen that the jobs to run stop being selected. Don't use it until the correct usage is clearly defined.

It does not make much sense to keep these generated jobs for future use, so it is a good idea to make these jobs auto_drop. This means that the job is dropped automatically when it is executed as follows:

dbms_scheduler.set_attribute ( name => l_job_name, attribute => 'auto_drop', value => true );

The job log entries are saved, purged, and controlled by the JOB_CLASS definition or by the global Scheduler attributes.

There is no further schedule. We will just start the job as soon as possible by enabling the job like this:

dbms_scheduler.enable (name => l_job_name);

When you submit lots of jobs this way, you will notice that after having created a few, the first jobs start running before the generation of the other jobs has completed. How many jobs run together depends on how the active Resource Manager plan is defined. If no Resource Manager plan is active, the number of concurrent jobs will be close to the number of CPUs in the system.

Performing the analysis

The statob procedure performs the actual analysis. I guess there is no need to explain that dbms_stats should be used for this as you all are familiar with the dbms_stats package. Check the "PL/SQL Packages and Types Reference" and "Concepts" manuals for more information. The selection that generates the list of tables and partitions is made in such a way that it only generates the partition statistics for the partitioned tables, and not the global statistics. If all of the partitions of a table have statistics, the table has its statistics derived from the partitions. This is recognizable by the GLOBAL_STATS column of the table that has NO when it falls back to the aggregated partition statistics and YES when the table has been analyzed by itself. The granularity parameter controls for what parts (default, partition, subpartition, or global) of the table the statistics are gathered. In the code, we use the granularity partition. granularity=global means that the statistics are gathered for the complete table. granularity=partition means that only the specified partition is to be analyzed and this is mostly much quicker than analyzing the whole table. Here is the code that is used to call the dbms_stats.gather_table_stats procedure:

else
-- partitioned table, only do the specified partition
execute immediate 'begin dbms_stats.gather_table_stats'||
'(ownname => :owner'||
',tabname => :table_name'||
,partname=>:partition_name'||
',granularity=>''partition'''||
',cascade => true' ||
',degree => :pdegree' ||
'); end;'
using owner, table_name, partition_name, pdegree;
end if;
END statob;

Handling the tables that are not partitioned is slightly simpler as no partname and granularity are needed.

Generating the scheduled run

The schedule_run procedure creates a scheduled job that calls the run procedure at the specified STIME on which the statistics collection should start passing the stale_pct, sjob_class, and sjob_prefix to the run procedure as shown in the previous code. We can have this job scheduled just before the batches start at 17:00 hours and near the end of the batch window at 05:00 hours in the morning. This makes sure that the batches get accurate statistics when they start and also guarantees that the online users get accurate statistics when the batches are ready.

The job that is generated by SCHEDULE_RUN has just one moment on the day that it runs. It is easy to change this to whatever you want by modifying the job procedures of dbms_scheduler. When deploying this code to many databases, it should be customized to meet your specific needs.

In this Scheduler Jobs screen, you can see the statistics collection in action. At this very moment, 13 STATS jobs are active. This will put some load on the system.

At first sight, this approach might seem a little overwhelming, but there is apparently no control on the load that is put on the system. This is where the real power of the Scheduler and Resource Manager combination kicks in. The jobs are defined and when their start time comes, they are queued. As soon as the Resource Manager tells the Scheduler that there is room for an extra job, the Scheduler selects the next job to start. It is completely controlled by the Resource Manager plan and consumer group definitions. This code is in use in data warehouses with a STALE_PCT of 5, and there the statistics are normally gathered within an hour. In the Resource Manager plan, the online users have the same amount of resources as the analyze process. They hardly notice the load that the analyze process puts on the system.

This was a simple example, but don't be fooled as it uses the full power of the product. A slightly more advanced use would be when we look more closely at the backups, as there are a few extra requirements that make this an interesting subject.

Backups

Before we begin, we will need to make sure the databases are running in the ARCHIVELOG mode. We are doing this just to be able to make an online backup, and do it at any arbitrary moment of the day. On many sites, there are databases that do not require full recovery, but they cannot be easily taken offline for a backup. Having the database run in ARCHIVELOG mode enables us to make a backup while users are still working in the database. When only a simple backup is enough and no full recovery is required, we might as well ditch the archived log files that are not needed to restore the database. When full recovery is required, we should also backup the archived log files. As said before, all the databases are running in the ARCHIVELOG mode and we need to save all of the archives for a few databases. For all the databases, we need to back up the archives that are generated during the back up. Failing to do so will make the backup completely useless. Because the customer is not paying to save all the archives, we are not going to back them up. Instead, we are going to trash them as soon as possible. If we don't do that, the ARCHIVELOG destinations will flood, causing the database to hang. This is where another part of the Service Level Agreement (SLA) comes in, which forces us to make the system available during the business hours. We need to make sure that the database is available at all times. This is because when the archiver can no longer write new archives, the database will come to a halt and users will no longer be able to do their work.

This is an interesting scenario that is not easily scheduled in cron, without making assumptions about the runtime of a backup. And before you know it, there is a time bomb in your hands!

Things that can scare you

On many sites, the actions handling the archives are scheduled in cron. The interesting decision to make here is whether to drop the archives or not, and is the backup system ready for use. The solution shown here takes lots of things into account. As is the case with many solutions, it's certainly not the only way and it also won't be the best possible way. However, it does show the power of the Scheduler. There are essentially three programs, and each program has an argument named ORACLE_SID.

One program calls the backup database script, another calls the backup archives script, and the last one calls the delete archives script. All of these programs are of the type EXECUTABLE and will be called as the remote external jobs.

To make things easier, there is a table that controls the way the databases listed must be backed up. These are a few things to enter for every database:

  • ORACLE_SID

  • Interval in minutes to run the backup or delete the archives script

  • The host where the database resides

  • The backup server to be used

  • Last database backup duration

  • The days on which the database backup has to run

  • The time when the database backup preferably has to run

This is enough data to be able to generate the remote external jobs that we are going to deploy for this task. There are a few assumptions in place that are as follows:

  • The database backup script is called HOT_${ORACLE_SID}.

  • The archive backup script is called BARC_${ORACLE_SID}.

  • The archive delete script is called DARC_${ORACLE_SID}.

  • The archives are generated and backed up at a frequency that prevents flooding in the archives file location. When the archive delete script is running, it uses the same frequency as the delete script.

  • If the backuP_type is SEMIHOT, all the archives that are not needed to restore the database are trashed.

  • If the backuP_type is HOT, the simplest form, no task-specific control is needed.

  • For a SEMIHOT backup, we stop the DARC script as soon as the HOT script is found to be starting.

  • When the SEMIHOT backup is started, we schedule a BARC script to back up the archives that are generated during the backup with the same frequency that the DARC script used.

  • When the SEMIHOT backup finishes, we run the final BARC script as soon as possible, before re-submitting the archives killer script DARC again.

The following flowchart gives a visual image of what should be done in case of a SEMIHOT backup:

The last line, "Schedule DARC process" is the most important one here. We can, of course, schedule the DARC script as soon as the HOT script ends and give the BARC script a start time that tells the Scheduler, "Hey, start me now!" How long should we keep the DARC script waiting before it can go and do its job? There is only one answer for this—it has to wait until the BARC script has completed. Of course, we can use the priority attribute to make sure that the (short-running) BARC script gets priority over a (long-running) HOT script. However, there is no guarantee that it does get executed before the DARC job if both are scheduled at the same time. As mentioned before, this idea will be conveyed further when we consider the following figure, which represents the full cycle of a SEMIHOT backup:

The white and grey boxes on the upper level represent the executions of the DARC process—shown as white where it is OK to run this and grey where it will cause havoc to the recoverability of the backup. The execution of the backup is represented by the striked box on the middle level, and the black boxes at the bottom represent the execution of the BARC processes intended to make the backup consistent. What we don't want is the DARC processes running while the HOT process is running. This also means that the execution of the DARC process "c" will have to be stopped when the HOT process starts. We also don't want the DARC process to run before the first BARC process after the HOT backup has ended. To make the period in which we are vulnerable to the loss of archives needed to make the backup consistent, we fire a BARC process as soon as the HOT process ends. After the BARC processes are ready, it is OK for the DARC processes to start running again. So, when BARC process "D" has succeeded, we can start killing archives again by scheduling the DARC processes.

The Scheduler gives us lots of handles to accomplish this. The most important thing is the HOT backup process and knowing when it is scheduled. If we know this and the frequency of the DARC processes, we can predefine the last execution of the DARC process. In the screenshot, we can clearly see that the third execution of the DARC process is marked "c", and will invalidate the backup. It should preferably not start running at all. For this, we define the DARC process with an end_date attribute set to the start time of the HOT backup minus the interval at which the DARC process runs. This will make sure that the DARC process execution marked "b" will be the last DARC process to start. To prevent accidents, we also set auto_drop, which makes sure that the process does not even exist during the vulnerable part of the backup process. In case it does run, we kill the job.

Because the DARC process has not been running for a while when the HOT backup starts, the first thing we need to do is schedule a BARC process and set it to start immediately when the HOT backup is seen starting. It gets the same run interval as the DARC process did. When the HOT backup finishes, we set the max_runs attribute of the BARC process in such a way that we have one BARC process starting after the HOT backup finishes and it starts within a minute after the completion of the HOT backup. When the final BARC process has finished, we reschedule the DARC processes again with the end_date attribute set to the next HOT backup start time. The BARC process also has auto_drop set to true. So when it is no longer needed, it does not exist.

Try this with any other Scheduler. It won't happen! Let's take a closer look at the interesting parts of the code.

Diving into the code

The most important part of this setup is the Scheduler event queue. The jobs will be generating events and we will use the Scheduler event queue to catch them and see the event that job generated. The events are very important and knowing which events are taking place is also important for us when debugging the system. For this reason, I normally make an event_log table that lists all the events that are seen along with the time at which the event is seen. In the payload for the event message is the timestamp at which the event has been generated.

Reading the event queue

We can read the Scheduler event queue as follows:

options_in dbms_aq.dequeue_options_t;
props_out dbms_aq.message_properties_t;
sid varchar2(30);
host varchar2(30);
destination varchar2(30);
hot_state varchar2(30);
begin
loop
options_in.consumer_name := consumer;
options_in.wait := dbms_aq.FOREVER;
begin
dbms_aq.dequeue ('sys.SCHEDULER$_EVENT_QUEUE'
, options_in,props_out
, msg
, msgid
);
exception
when dequeue_timeout
then
dbms_output.put_Line ('no event');
props_out.state := DBMS_AQ.PROCESSED;
when others
backupsscheduler event queue, readingthen
raise;
end;
if ( props_uit.state = dbms_aq.READY )
then
dbms_output.put_line ('msg.event_type:'||msg.event_type);
dbms_output.put_line ('msg.object_owner:'||msg.object_owner);
dbms_output.put_line ('msg.object_name:'||msg.object_name);
dbms_output.put_line ('msg.event_timestamp:' ||msg.event_timestamp);
dbms_output.put_line ('msg.error_code:'||msg.error_code);
dbms_output.put_line ('msg.error_msg:'||msg.error_msg);
dbms_output.put_line ('msg.event_status:'||msg.event_status);
dbms_output.put_line ('msg.log_id:'||msg.log_id);
dbms_output.put_line ('msg.run_count:'||msg.run_count);
dbms_output.put_line ('msg.failure_count:'|| msg.failure_count);
dbms_output.put_line ('msg.retry_count:'||msg.retry_count);
/* for debugging mostly */
insert into event_log
(
receive_date,
event_type,
object_owner,
object_name,
event_timestamp,
error_code,
error_msg,
event_status,
log_id,
run_count,
failure_count,
retry_count
)
values
(
current_timestamp
msg.event_type,
msg.object_owner,
msg.object_name,
msg.event_timestamp,
msg.error_code,
msg.error_msg,
msg.event_status,
msg.log_id,
msg.run_count,
msg.failure_count,
msg.retry_count
);
if msg.object_name != 'BACKUP_MONITOR_STOP' and
msg.object_name != 'BACKUP_MONITOR_RUN'

This code is the heart of the system and waits for a Scheduler event to take place. The expected events are not only those of the BARC, DARC, and HOT processes, but also the BACKUP_MONITOR_STOP and BACKUP_MONITOR_RUN processes. The latter one is running the backup monitor code itself and the only task of the stop job is to pass its active existence to the backup monitor code, so that the monitor can stop in an orderly fashion. As you can see in the code above, the options_in.wait is given FOREVER. This keeps the system silent when nothing happens:

if msg.error_code != 0
then
if (msg.error_code <> 27370 and msg.object_name not like DARC_%')
/* we kill DARC_ as soon as HOT_ begins so 27370 for DARC is ok */
then
send_nok
(
msg.object_owner,
msg.object_name,
msg.error_code,
msg.error_msg
);
end if;
end if;
if (msg.object_name like 'HOT_%' and
(msg.event_type = 'JOB_STARTED'
or msg.event_type = 'JOB_SUCCEEDED')
)
or (msg.object_name like 'BARC_%' and
msg.event_type = 'JOB_COMPLETED')
then
/*
* is HOT backup ended check for need of change BARC<>DARC
*/
switch_BARC_DARC (msg.object_name, msg.event_type);
end if; -- event received dbms_aq.ready
commit;
exit when msg.object_owner = consumer and
msg.object_name = 'BACKUP_MONITOR_STOP';
end if; /* msg received */
end loop;
dbms_output.put_line ('exiting watch_queue loop');
END watch_queue;

As there is a chance that we need to kill the DARC job (we do this when it appears to run when HOT starts), ora-27370 is not an error when it is found for the DARC process. For other errors, we generate a mail for whoever should get notified. There are a few events that ask for our attention: JOB_STARTED and JOB_SUCCEEDED for the HOT backup job and also the JOB_COMPLETED event for the BARC job. There is a subtle difference between the JOB_SUCCEEDED and JOB_COMPLETED events. JOB_SUCCEEDED means that the job succeeded, whereas JOB_COMPLETED means that the JOB has completed its functional life and auto_drop has been executed. In our case, this means that the last BARC process (the one that has to start after the HOT backup competed) has done its job.

Scheduling for the HOT backups

The scheduling of the HOT backups is performed by the following snippet of code. The HOT backups are in control, as the start and the stop of the HOT backups control the behavior of the DARC and BARC processes.

dbms_scheduler.create_job
(
job_name => i.hot_name,
program_name => 'backuP_db_hot',
repeat_interval => 'freq=weekly'; byday='||i.backuP_days||'; byhour='||to_char(i.backuP_time,'hh24')||';'|| 'byminute='||to_char(i.backuP_time,'mi')||';',
start_date => current_timestamp,
job_class => i.server,
auto_drop => false,
enabled => false
);
dbms_scheduler.set_attribute
(
name => i.hot_name,
attribute => 'raise_events',
value => dbms_scheduler.job_started +
dbms_scheduler.job_succeeded +
dbms_scheduler.job_failed +
dbms_scheduler.job_broken +
dbms_scheduler.job_completed +
dbms_scheduler.job_sch_lim_reached +
dbms_scheduler.job_disabled +
dbms_scheduler.job_chain_stalled
);
dbms_scheduler.set_attribute
(
name => i.hot_name,
attribute => 'logging_level',
value => DBMS_SCHEDULER.LOGGING_full
);
dbms_scheduler.set_attribute
(
name => i.hot_name,
attribute => 'job_priority',
value => 4
);
dbms_scheduler.set_attribute
(
name => i.hot_name,
attribute => 'MAX_RUN_DURATION',
value => numtodsinterval (i.backuP_length * 1.2, 'hour')
);
dbms_scheduler.set_attribute
(
name => i.hot_name,
attribute => 'destination',
value => i.destination
);
dbms_scheduler.set_attribute
(
name => i.hot_name,
attribute => 'credential_name',
value => i.credential_owner||'.'||i.credential_name
);
dbms_scheduler.set_job_argument_value
(
job_name => i.hot_name,
argument_name => 'ORACLE_SID',
argument_value => i.oracle_sid
);
dbms_scheduler.enable( i.hot_name );

This is a pretty straightforward piece of code! The priority for the HOT backup is 4, meaning low priority. This is relative to the others in the same resource consumer group. The normal BARC and DARC processes get a little higher priority, that is 3; whereas the final BARC process gets a priority 2. We want to get a notification when the backup runs significantly longer than expected. So the MAX_RUN_DURATION value is used here, which will raise an event when this happens. However, it will not stop the job. The destination and the credential needed to run the job at the specified destination are specified as attributes of the job.

Scheduling the DARC process

When the SEMIHOT backup is done and the BARC process is completed, we can schedule the DARC process again. From this point in time, it can safely be scheduled until the next backup. We do this in such a way that the DARC job no longer exists when the backup starts.

procedure schedule_darc
(
job in varchar2, sid in varchar2,
dest in varchar2, cred in varchar2,
server in varchar2, interval in number,
hot_job in varchar2
)
as
begin_hot timestamp;
begin
dbms_scheduler.create_job
(
job_name => job,
program_name => 'delete_arch',
repeat_interval => 'FREQ=MINUTELY;INTERVAL='||interval,
start_date => current_timestamp,
job_class => 'default_job_class',
auto_drop => true,
enabled => false
);
dbms_scheduler.set_attribute
(
name => job, attribute => 'raise_events', value => dbms_scheduler.job_started +
dbms_scheduler.job_succeeded +
dbms_scheduler.job_failed +
dbms_scheduler.job_broken +
dbms_scheduler.job_completed +
dbms_scheduler.job_sch_lim_reached +
dbms_scheduler.job_disabled +
dbms_scheduler.job_chain_stalled
);
dbms_scheduler.set_attribute
(
name => job,
attribute => 'logging_level',
value => DBMS_SCHEDULER.LOGGING_full
);
dbms_scheduler.set_attribute
(
name => job,
attribute => 'job_priority',
value => 4
);
dbms_scheduler.set_attribute
(
name => job,
attribute => 'destination',
value => dest
);
dbms_scheduler.set_attribute
( name => job,
attribute => 'credential_name',
value => cred
);
/* the backup is scheduled, as is the DARC job.
* if this monitor code is not running, make sure that
* the DARC job is NOT going to interfere with the HOT backup.
* there will be problems because the archives are not backed up but
* that will be noticed by other monitoring processes in grid control
*/
select next_run_date - ((1/24/60)*interval) into begin_hot
/* last run is ultimately available to start until interval (minutes)
* before the start of the hot backup
*/
from user_scheduler_jobs
where job_name = hot_job;
dbms_scheduler.set_attribute
(
name => job,
attribute => 'end_date',
value => begin_hot -- HOT backups begins after interval minutes
);
dbms_scheduler.set_job_argument_value
backupsDARC process, scheduling(
job_name => job,
argument_name => 'ORACLE_SID',
argument_value => sid
);
dbms_scheduler.set_job_argument_value
(
job_name => job,
argument_name => 'KEEP_HOURS',
argument_value => '0'
);
dbms_scheduler.enable( job );

The majority of the code used here is the same as for the BARC process. The most important difference is the setting of the end_date, which is based on the NEXT_RUN_DATE of the corresponding HOT job and the interval in which the DARC and BARC processes run. This is incorporated to make sure that no DARC process will be running when the corresponding HOT backup is going to start. This also ensures that when the backup monitoring process is not running, the first series of backups will have correct results. Normally, the backup monitoring software will be running, but you are not always aware.

Scheduling the final BARC process

When the end of the backup is detected, we will need to make sure that the final ARCHIVELOG backups are created and that too as soon as possible.

begin
select state, run_count into barc_state, barc_runs
from user_scheduler_jobs
where job_name = i.job_name;
if barc_state <> 'RUNNING'
then
/* start the last archives backup ASAP */
dbms_scheduler.disable (i.job_name);
dbms_scheduler.set_attribute
(
name => i.job_name, attribute => 'job_priority', value => 1
);
dbms_scheduler.set_attribute
(
name => i.job_name,
attribute => 'start_date',
value => current_timestamp
);
dbms_scheduler.enable (i.job_name);
else /* already running, ad 1 extra run ASAP for BARC */
dbms_scheduler.set_attribute
(
name => i.job_name,
attribute => 'max_runs',
value => barc_runs + 2
);
dbms_scheduler.set_attribute
(
name => i.job_name,
attribute => 'repeat_interval',
value => 'FREQ=MINUTELY; INTERVAL=1'
);
dbms_scheduler.set_attribute
(
name => i.job_name,
attribute => 'job_priority',
value => 1
);
dbms_scheduler.set_attribute
(
name => i.job_name,
attribute => 'start_date',
value => current_timestamp
);
end if;

If the BARC job is already running, we set max_runs to the current run_count plus 2, while at the same time we set the repeat interval to 1 minute. This makes sure that when the currently running BARC is ready, the next one is started within a minute thereafter. After that final run, the max_runs count setting makes sure that the BARC job is dropped and we receive the JOB_COMPLETED event for the BARC process. The job priority is set to 1, the topmost priority, just to ensure that our most wanted job does not have to wait any longer and is served as soon as possible. On the web site (http://www.packtpub.com/files/code/5982_Code.zip), the full code will be available for download.

How to use the calendar

In the forums, some questions are repeated over and over again. One of them is how to use or create a schedule. The Oracle documentation makes some attempt to explain the use of the calendar, but fails to use real examples. Here, we will see some examples that explain some of the real-life questions:

  • How to schedule on the first day of a month?

  • How to schedule only on Monday?

  • How to schedule on the first Monday of a month?

  • How to schedule on the first working day of a month?

  • How to schedule on the first working Monday of a month?

  • How to schedule on the nth Monday of a month?

  • How to schedule on the last working day of a month?

  • How to schedule in the first quarter of a year?

  • How to schedule on the first Monday of the first quarter of a year?

Tools

Before diving into the calendars, it would be good to know that there is a very useful tool in the database that can help debugging a calendar. This tool is the evaluate_calendar, which is found in the dbms_scheduler package. This procedure can show the next run date for a given start date. To make it easier to use, we can add the following code to the database:

create or replace type calendar_row_type as object (next_run_date timestamp with time zone);
create or replace type calendar_table_type as table of calendar_row_type;
CREATE OR REPLACE FUNCTION ANA_SCHED (start_time in timestamp, calendar in varchar2, steps in number)
RETURN calendar_table_type pipelined AS start_date TIMESTAMP;
return_date_after TIMESTAMP;
next_run_date TIMESTAMP;
BEGIN
start_date := start_time;
return_date_after := start_date;
FOR i IN 1.steps
LOOP
dbms_scheduler.evaluate_calendar_string(calendar,
start_date, return_date_after, next_run_date);
-- next_run_date has to popup somewhere as a column
pipe row (calendar_row_type(next_run_date));
return_date_after := next_run_date;
END LOOP;
return;
END ana_sched;
/

The code is a simple pipelined function that generates a row for the requested number of next executions, starting from the specified start date. We can use the ana_sched function as follows:

select next_run_date, to_char(next_run_date,'dy') day from table (ana_sched(sysdate, 'freq=weekly;byday=mon;',5));

This shows the next 5 Mondays since today. This function is also available in DbVisualizer and DB Console.

How to schedule on the first day of a month

The create_schedule procedure is all we need for creating a simple calendar. The following code shows a calendar that simply lists the first day of every month:

BEGIN
dbms_scheduler.create_schedule(
schedule_name => 'firstday',
start_date => NULL,
repeat_interval => 'freq=monthly;'||'bymonthday=1',
end_date => NULL, comments=>NULL
);
END;

This gives a run date for the first day of every month. The start time of the runs is equal to the time on which we created the schedule.

How to schedule only on Monday

This is also a simple example that enlists all the Mondays. Again, the start time is the same as the time on which we created the schedule.

BEGIN
dbms_scheduler.create_schedule(
schedule_name => 'mondays',
start_date => NULL,
repeat_interval => 'freq=weekly;'|| 'byday=mon',
end_date => NULL,
comments => NULL
);
END;

This code defines a schedule called mondays that lists all the Mondays—nothing more, nothing less. As we have a Monday in every week, the frequency is weekly.

select next_run_date, to_char(next_run_date,'dy') day from table (ana_sched(sysdate, 'mondays',5));

How to schedule on the first Monday of a month

Now, list only the first Monday of every month:

BEGIN
dbms_scheduler.create_schedule(
schedule_name => 'first_mondays',
start_date => NULL,
repeat_interval => 'freq=monthly;'|| 'byday=mon; bysetpos=1;',
end_date => NULL,
comments => NULL
);
END;

As we have a monthly interval now, we have to define accordingly. In the monthly interval, we choose the day by byday=mon. As there are multiple Mondays in a month, we specify which Monday we want to use by bysetpos=1. This gives us the first Monday of the month.

How to schedule on the first working day of a month

What should be listed depends a lot on what the definition of a working day is. In this case, we define a working day as the days that do not fall into the weekend or the free day category. Here comes more power of the calendaring possibilities of dbms_scheduler. We can combine the schedules. Let's first define the weekends and then the free days. The weekend schedule can be defined as follows:

--/
begin
dbms_scheduler.create_schedule
(
schedule_name => 'weekend', start_date => NULL,
repeat_interval => 'freq=weekly; byday=Sat,Sun', end_date => NULL,
comments => 'weekends'
);
end;
/

We have a weekend every week, so the frequency is weekly. Now let's make a schedule that defines the special days:

--/
BEGIN
dbms_scheduler.create_schedule
(
schedule_name => 'july_6',
start_date => NULL,
repeat_interval => 'freq=yearly;
bydate=0706', end_date => NULL,
comments => NULL
);
dbms_scheduler.create_schedule
(
schedule_name => 'june_14',
start_date => NULL,
repeat_interval => 'freq=yearly; bydate=0614',
end_date => NULL,
comments => NULL
);
END;
/

The code above lists some very important days that are not working days. We will combine them in schedule special_days:

--/
BEGIN
dbms_scheduler.create_schedule
(
schedule_name => 'special_days',
start_date => NULL,
repeat_interval => 'june_14,july_6',
end_date => NULL,
comments => NULL
);
END;
/

Now we have a list of special days that we don't work on and when they are combined with the weekends, it returns the free days. So why not make a schedule for this? Let's prepare it using the following:

--/
BEGIN
dbms_scheduler.create_schedule
(
schedule_name => 'free_days',
start_date => NULL,
repeat_interval => 'weekend,free_days',
end_date => NULL,
comments => NULL
);
END;
/

The free_days schedule lists all the days that are not working days. The original question was about the first working day of a month. Here, again we can make a combination of existing schedules, in this case by using exclude. We essentially use all the days, minus the free_days. We can do this as follows:

--/
BEGIN
dbms_scheduler.create_schedule
(
schedule_name => 'firstworkingday',
start_date => NULL,
repeat_interval => 'freq=monthly; byday=Mon,Tue,Wed,Thu,Fri;'|| 'exclude=free_days;bysetpos=1',
end_date => NULL,
comments => NULL
);
END;
/

The firstworkingday schedule lists the first working day of a month, assuming a working day is on a week day.

How to schedule on the first working Monday of a month

From the previous example, it is easy to refine to the first working Monday of a month:

--/
BEGIN
dbms_scheduler.create_schedule
(
schedule_name => 'firstworkingmonday',
start_date => NULL,
repeat_interval => 'freq=monthly; byday=Mon;'|| 'exclude=free_days; bysetpos=1',
end_date => NULL,
comments => NULL
);
END;
/

The firstworkingmonday schedule lists the first working Monday of a month.

How to schedule on the nth Monday of a month

We can use an approach that is very similar to the above using setbypos. We pick the second Monday of every month as follows:

--/
BEGIN
dbms_scheduler.create_schedule
(
schedule_name => 'secondworkingmonday',
start_date => NULL,
repeat_interval => 'freq=monthly; byday=Mon;'|| 'exclude=free_days; bysetpos=2',
end_date => NULL,
comments => NULL
);
END;
/

The secondworkingmonday schedule lists the second working Monday of a month based on the free_days definition as mentioned before.

How to schedule on the last working day of a month

The step to the last Monday of a month is also defined by bysetpos, counting back from the end specified by a negative number.

--/
BEGIN
dbms_scheduler.create_schedule
(
schedule_name => 'lastworkingday',
start_date => NULL,
repeat_interval => 'freq=monthly;'|| 'byday=Mon,Tue,Wed,Thu,Fri;'|| 'exclude=free_days;bysetpos=-1',
end_date => NULL,
comments=>NULL
);
END;
/

Here, we used the fact that a negative value for bysetpos leads to backward counting.

How to schedule in the first quarter of a year

It is simple to define the first quarter:

--/
BEGIN
dbms_scheduler.create_schedule
(
schedule_name => 'q1',
start_date => NULL,
repeat_interval => 'freq=daily; bymonth=1,2,3',
end_date => NULL,
comments => NULL
);
END;
/

The q1 schedule selects all the days of the first quarter of a year.

How to schedule on the first Monday of the first quarter

How can we use the previously defined schedule firstworkingmonday to show only the first working Mondays in the first quarter? This can be done with the intersect operator as follows:

select next_run_date, to_char(next_run_date,'dy') day
from table (ana_sched(sysdate, 'q1;intersect=firstworkmonday; bymonth=1',40));

This shows the schedule that lists the dates in the q1 schedule, which are also present in the firstworkingmondays schedule, delimited to month 1. Needless to say, this is incredibly powerful, impossible to describe in cron or dbms_jobs. This should give enough grip to be able to create almost any schedule that one can imagine.

Summary

In this chapter, we examined the various uses of Oracle Scheduler and, ultimately, the power of the Oracle Scheduler when used in real-world scenarios.

We covered:

  • How to dequeue the Scheduler event queue

  • How to save the events for debugging

  • How to specify job_weight for a job with parallelism

  • How to specify job_priority

  • How to specify max_job_duration

  • How to specify which events should be generated by a job

  • How to interpret job_completed

  • How to make use the max_runs job attribute

  • How to make use the end_date job attribute

  • How to make use of the auto_drop job attribute

  • How to generate the job names using a prefix

  • How to use the Scheduler for advanced backup setups

  • How to use the Scheduler to generate object statistics without impacting the other users

  • How to create a schedule

  • How to combine schedules to make advanced schedules

There seems to be only one limitation of the Oracle Scheduler—our lack of imagination to make it work to its full advantage. I hope now it's overcome. The only known limitation is the poor imagination of human beings who have to make the Scheduler work. In the next chapter, we will explore other configurations that Oracle Scheduler can run in, and various other things that can be arranged.