Oracle Job Scheduling Fundamentals
Oracle Tips by Burleson Consulting

Introduction to Job Scheduling

Job scheduling is an important but often overlooked part of every database environment.  Even the simplest systems require scheduled jobs such as data loads, data extraction, report generation, backups and general cleanup.  When scheduling these tasks, the DBA or developer has to make a choice whether to use an external scheduler provided by the operating system or an internal scheduler provided by the Oracle database.

 

This chapter introduces both external and internal schedulers, beginning with the external schedulers available in UNIX, Linux, and Windows systems.  Then it will proceed to examine Oracle’s internal scheduler and cover how to schedule jobs with the Oracle dbms_scheduler utility.

Fundamentals of Job Scheduling

Job scheduling is as old as data processing itself, dating back to the 1960s with the JES (job entry system) on the IBM mainframes, back in the days of punched cards.  Job scheduling is not a new technology, although it is new to Oracle.

 

Very few Oracle systems are random in nature.  Most business databases are driven by business cycles like the inline processing day, nightly batch processing and end-of-week reporting as well as monthly scheduled tasks.

 

Even within the realm of Oracle database administration, there is a need for regularly scheduled jobs:

• Every 5 minutes – check alert log for error messages, check for new dump or trace files, verify that Oracle processes are running

• Every night – perform hot backups, check OS disk space

• Every Week – clean out elderly redo log files, perform predictive analytics to seek important trends, collect table growth reports

• Every month – Check for database fragmentation, apply patches

It is databases with this sort of repeating cycles that are ideal for Oracle job scheduling.  To understand how scheduled jobs work, start with an overview of external vs. internal job scheduling and see the differences.

UNIX Job Scheduling (cron) vs dbms_scheduler

Back before the advent of the dbms_jobs package (the predecessor to dbms_scheduler), all Oracle tasks were scheduled at the OS layer using the cron deamon with crontab files.  While cron reliably executes jobs when the server is up, effective job chain management relies on sophisticated user-written shell scripts to manage internal job chain consistency. 

 

These scripts were written in Korn shell (ksh), Bourne Again Shell (bash) or C Shell (csh) command languages, and job chaining activities like checking step status and e-mailing failures have to be coded manually.  For full details on the nuances of Oracle shell scripting, see Jon Emmons’ book “Linux Shell Scripting for Oracle” by Rampant TechPress.

 

In sum, using cron for Oracle job scheduling has several important drawbacks:

• It is difficult to code complex job chains – Maintaining job step status and restartability requires significant shell language coding skills.

• No centralized job location - Each UNIX/Linux user may have their own crontab file and it can be difficult to locate and manage OS scheduled tasks.

• Does not detect missed jobs – If the server is down during the time a job is executing, the OS will not inform the DBA upon server restart.

On the other hand, the dbms_scheduler package is not perfect either and it also has some deficiencies, foremost of which is the inability to easily communicate with the OS.  For this reason, cron is still used for many OS-intensive jobs, such as scheduled jobs to clean out the archived redo log directory.  Other deficiencies include:

• Difficult to shell out to the OS – Some Oracle jobs require information from the server environment (disk space, RAM usage) and these are not easy to collect within Oracle PL/SQL.  Also, special techniques must be used to send signals to the OS such as when one needs to send an email or pager alert for a failed job.

• Cryptic syntax – The syntax for scheduling complex jobs requires detailed knowledge for Oracle’s internal date-time formatting masks; performing error checking and restarts requires complex syntax.

While neither approach is perfect, these two approaches together allow the Oracle professional to create complex conditional job streams and automate complex nightly processing with relative ease.

 

Now take a closer look at the fundamentals of job scheduling for Oracle.

For more details on Oracle utilities, see the book "Advanced Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan. You can buy it direct from the publisher for 30% off directly from Rampant TechPress.