External Oracle Job Scheduling Oracle Tips by Burleson Consulting

External Oracle Job Scheduling

All popular operating systems have some form of job scheduler that can be used to schedule database specific tasks.  In UNIX and Linux environments, the most established scheduler is CRON; while in Windows environments there are several schedulers available depending on the version of Windows being used.  For this reason, each type of environment will be presented separately, starting with the CRON scheduler.

Using cron and crontab in UNIX and Linux

The CRON scheduler is available on both UNIX and Linux systems, allowing users to schedule operating system commands and shell scripts.  The implementation of CRON varies slightly on each operating system, so it is worth checking the manual pages for cron and crontab to identify the specific files and paths used on the UNIX or Linux platform.  The focus of the rest of this section will be on the implementation of CRON in the Red Hat Enterprise Linux AS distribution.

The CRON program is run as a daemon, typically with the name cron or crond.  To identify if it is running, either of the following commands can be used:

ps -ef | grep crond | grep -v grep
root      2580     1  0 16:43 ?        00:00:00 crond

ps -ef | grep cron | grep -v grep
root      2580     1  0 16:43 ?        00:00:00 cron

Most UNIX and Linux implementations provide scripts that are located in an initialization directory and can be used to control daemon services.  The specific location depends on the operating system, but several popular UNIX and Linux distributions use the /etc/init.d directory.  The following examples show how the CRON daemon can be controlled by calling its initialization script with the parameter relevant to the system, as indicated:

# Solaris and Red Hat Linux
/etc/init.d/crond start
/etc/init.d/crond restart
/etc/init.d/crond stop

# Tru64 and HP-UX
/sbin/init.d/cron start
/sbin/init.d/cron restart
/sbin/init.d/cron stop

Some operating systems provide command line tools for starting and stopping services.  The following are available in Red Hat Linux:

service crond stop
Stopping crond:                                            [  OK  ]
 

service crond start
Starting crond:                                            [  OK  ]

service crond restart
Stopping crond:                                            [  OK  ]
Starting crond:                                            [  OK  ]

The simplest way to use cron in Red Hat Linux is to place the shell scripts into one of the following directories:

* /etc/cron.daily
* /etc/cron.hourly
* /etc/cron.monthly
* /etc/cron.weekly

The scripts will then be run at the time interval indicated by the directory name.  The cron scheduler knows when to run these scripts due to the contents of the /etc/crontab file.  On Red Hat Linux, the default crontab script contains the following entries:

cat /etc/crontab
 

SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin
MAILTO=root
HOME=/

# run-parts
01 * * * * root run-parts /etc/cron.hourly
02 4 * * * root run-parts /etc/cron.daily
22 4 * * 0 root run-parts /etc/cron.weekly
42 4 1 * * root run-parts /etc/cron.monthly

The first part of the script sets up some environment variables, while the second part of the script schedules the jobs associated with the default directories.  The run-parts script runs each of the scripts located in the specified directory.

With the exception of the environment setup, each line in the crontab file represents a scheduled task by using the following syntax:

field          allowed values
-----          --------------
minute         0-59
hour           0-23
day of month   1-31
month          1-12
day of week    0-7 (both 0 and 7 are Sunday)
user           Valid OS user
command        Valid command or script.

The date fields can contain a number of patterns to form complex schedules, as shown below.

*       - All available values or "first-last".

3-4     - A single range representing each possible from the start      to the end of the range inclusive.

1,2,5,6 - A specific list of values.

1-3,5-8 - A specific list of ranges.

0-23/2  - Every other value in the specified range.

In Red Hat Linux, the month and day names can be used to specify these fields.  The following examples show a selection of possible schedules:

30 * * * * root echo "Runs at 30 minutes past the hour."

45 6 * * * root echo "Runs at 6:45 am every day."

45 18 * * * root echo "Runs at 6:45 pm every day."

00 1 * * 0 root echo "Runs at 1:00 am every Sunday."

00 1 * * 7 root echo "Runs at 1:00 am every Sunday."

00 1 * * Sun root echo "Runs at 1:00 am every Sunday."

30 8 1 * * root echo "Runs at 8:30 am on the first day of every month."

00 0-23/2 02 07 * root echo "Runs every other hour on the 2nd of July."

Comments must be on separate lines and must be preceded by a # character. The contents of the /etc/crontab file should not be edited directly but via the crontab command, which has the following usage syntax:

usage:  crontab [-u user] file
        crontab [-u user] { -e | -l | -r }
                (default operation is replace, per 1003.2)
        -e      (edit user's crontab)
        -l      (list user's crontab)
        -r      (delete user's crontab)

As the usage indicates, only user-specific entries can be edited via the crontab command.  There are essentially two ways of editing the contents of the /etc/crontab file using this command.  The crontab -e command opens the current user?s crontab file in the default editor specified by the EDITOR environment variable.  Any changes are applied to the crontab file when the editor saves the file. Alternatively, a text file can be produced containing the current users crontab entries by issuing the crontab -l > filename command.  Once this file is altered, the amendments can be applied by issuing the crontab filename command:

# Produce the file.
crontab -l > /tmp/mycrontab.txt 

# Edit the file.
vi /tmp/mycrontab.txt

# Apply the modified file.
crontab /tmp/mycrontab.txt

The syntax of the entries is the same as that specified earlier with the exception of the user field.  This is not specified, as it is defined on the command line or defaults to the current user. By default, all users can schedule their own jobs, but access can be controlled by creating or editing the contents of the /etc/cron.allow and /etc/cron.deny files.  If these files are not present, all users can access schedule jobs.  If only the cron.deny file is present, it is assumed that all users specified in this file are not allowed to schedule jobs.  Likewise, if only the cron.allow file is present, it is assumed that only users specified in this file are allowed to schedule jobs.  The absence of a file is the equivalent of the file being present containing the word ALL.

By default, the output from a job is electronically mailed to the owner of the job or the user specified by the mailto variable.  If this is unacceptable, the output can be redirected in a number of ways including:

# Mail the output of the job to another user.
command | mail -s "Subject: Output of job" user

# Standard output redirected to a file.
command >> file.log

# Standard output and standard error redirected to a file.
command >> logfile 2>&1

# Throw all the output away.
command >> /dev/null 2>&1

Since the CRON scheduler has been explained, the focus will now shift to the schedulers available in Windows environments.

 

This is an excerpt from the book "Oracle Job Scheduling" by Dr. Tim Hall. You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle job scheduling scripts.