Oracle Scheduler is a tool that provides a way of automating the scheduling of jobs. Oracle Scheduler is implemented via the DBMS_SCHEDULER internal PL/SQL package.
Oracle Scheduler offers a sophisticated set of features for scheduling jobs, such as detailed scheduling, privileged-based models, and storing of schedules.
There are more than 70 procedures and functions available within the DBMS_SCHEDULER package, and more details are in the Oracle Database PL/SQL Reference Guide documentation.
Note In earlier releases, DBMS_JOBS was the package for scheduling. DBMS_JOBS has been deprecated, and DBMS_SCHEDULER should now be used.
Creating and Scheduling a Job
The example in this section shows how to use DBMS_SCHEDULER to run an OS shell script on a daily basis. First, a shell script is created that contains an RMAN backup command. For this example, the shell script is named rmanback.bsh and is located in the /orahome/ oracle/bin directory.
The shell script also assumes that there is an /orahome/oracle/ bin/log directory available. Here is the shell script:
Next, the CREATE_JOB procedure of the DBMS_SCHEDULER package is used to create a daily job.
Next, connect as SYS or as the SYSBACKUP user with CREATE and ALTER JOB permissions, and execute the following command:
In the prior code the JOB_TYPE parameter can be one of the following types: STORED_ PROCEDURE, PLSQL_BLOCK, EXTERNAL_SCRIPT, SQL_SCRIPT, or EXECUTABLE. In this example, an external shell script is executed, so the job is of type EXTERNAL.
The REPEAT_INTERVAL parameter is set to FREQ=DAILY;BYHOUR=9;BYMINUTE=35. This instructs the job to run daily, at 9:35 a.m. The REPEAT_INTERVAL parameter of the CREATE_JOB is capable of implementing sophisticated calendaring frequencies.
For instance, it supports a variety of yearly, monthly, weekly, daily, hourly, by the minute, and by the second schedules.
The Oracle Database PL/SQL Packages and Types Reference Guide contains several pages of syntax details for just the REPEAT_INTERVAL parameter.
The JOB_CLASS parameter specifies which job class to assign the job to. Typically, you would create a job class and assign a job to that class, whereby the job would inherit the attributes of that particular class.
For example, you may want all jobs in a particular class to have the same logging level or to purge log files in the same manner.
There is a default job class that can be used if you have not created any job classes. The previous example uses the default job class.
Credentials should be set up for local and remote external jobs. Oracle can use default users, but for security policies and capturing of the execution of the job, it is better to create a user for authenticating the external jobs.
DBMS_CREDENTIAL stores the user details and can store a Windows domain user, such as a service account, to execute these jobs. Credentials are owned by SYS and can also be managed using DBMS_ CREDENTIAL.
The AUTO_DROP parameter is set to FALSE in this example. This instructs the Oracle Scheduler not to drop the job automatically after it completes (the default is TRUE, although TRUE does not drop jobs that are set to repeat forever since they will never “complete”).
Leave a Reply