Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 

 Oracle Job Scheduling Fundamentals
Oracle Tips by Burleson Consulting

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by the top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

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.


Fo
r 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.

     

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.