28 Administering the Scheduler

Job Coordinator and Real Application Clusters

Each RAC instance has its own job coordinator. The database monitoring checks that determine whether or not to start the job coordinator do take the service affinity of jobs into account. For example, if there is only one job scheduled in the near future and the job class to which this job belongs has service affinity for only two out of the four RAC instances, only the job coordinators for those two instances will be started.

Using DBMS_SCHEDULER and DBMS_JOB at the Same Time

Even though Oracle recommends you switch from DBMS_JOB to DBMS_SCHEDULER, DBMS_JOB is still supported for backward compatibility. Both Scheduler packages share the same job coordinator, but DBMS_JOB does not have the auto start and stop functionality. Instead, the job coordinator is controlled by the JOB_QUEUE_PROCESSES initialization parameter. When JOB_QUEUE_PROCESSES is set to 0, the coordinator is turned off and when it has a non-zero value it is turned on.The JOB_QUEUE_PROCESSES initialization parameter is only used for DBMS_JOB. When this parameter is set to a non-zero value, auto start and stop no longer apply because the coordinator will always be up and running. In this case, the coordinator will take care of execution of both DBMS_SCHEDULER and DBMS_JOB jobs.

If the initialization parameter is set to 0, or if it is not set at all, no DBMS_JOB jobs will be run, however, the auto start and stop feature will be used for all DBMS_SCHEDULER jobs and windows. If there is a DBMS_SCHEDULER job to be executed, the coordinator will be started and the job will be executed. However, DBMS_JOB jobs still will not be run.

Scheduler Attribute max_job_slave_processes

The initialization parameter JOB_QUEUE_PROCESSES only applies to DBMS_JOB. When DBMS_SCHEDULER is used, the coordinator will automatically determine how many job slaves to start based on CPU load and the number of outstanding jobs. In special scenarios a dba can still limit the maximum number of slaves to be started by the coordinator by setting the MAX_JOB_SLAVE_PROCESSES with the DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE procedure.

Job Logs

A job log has an entry for each time you create or drop a job. To see the contents of the job log, query the DBA_SCHEDULER_JOB_LOG view. An example is the following statement, which shows what happened for past job runs:

SELECT JOB_NAME, OPERATION, OWNER FROM DBA_SCHEDULER_JOB_LOG;

JOB_NAME        OPERATION     OWNER
--------        ---------     -----
MY_JOB13        CREATE        SYS
MY_JOB14        CREATE        OE
MY_NEW_JOB3     CREATE        SYS
MY_JOB1         CREATE        SYS
MY_TEST_JOB1    CREATE        SYS
MY_TEST_JOB2    CREATE        SYS
MY_TEST_JOB2    CREATE        OE
MY_JOB11        CREATE        OE
MY_TEST_JOB4    CREATE        OE
MY_TEST_JOB5    CREATE        OE
MY_JOB12        CREATE        OE
MY_NEW_JOB3     ENABLE        SYS
MY_EMP_JOB1     UPDATE        SYS
MY_JOB1         CREATE        SCOTT
MY_EMP_JOB1     UPDATE        SYS
MY_EMP_JOB      CREATE        SYS
MY_EMP_JOB1     CREATE        SYS
MY_JOB14        RUN           OE
MY_JOB14        RETRY_RUN     OE
MY_JOB14        RETRY_RUN     OE
MY_JOB14        RETRY_RUN     OE
MY_JOB14        RETRY_RUN     OE
MY_JOB14        BROKEN        OE
MY_NEW_JOB1     CREATE        SYS
MY_JOB14        DROP          OE
MY_NEW_JOB2     CREATE        SYS

Job Run Details

To further analyze each job run, for example, why it failed, or what the actual start time was, or how long the job ran, query the DBA_SCHEDULER_JOB_RUN_DETAILS view. As an example, the following statement illustrates the status for my_job14:

SELECT JOB_NAME, STATUS FROM DBA_SCHEDULER_JOB_RUN_DETAILS
WHERE JOB_NAME = 'MY_JOB14';

JOB_NAME                       STATUS
------------------------------ ------------------------------
MY_JOB14                       FAILURE
MY_JOB14                       FAILURE
MY_JOB14                       FAILURE
MY_JOB14                       FAILURE
MY_JOB14                       FAILURE

For every row in SCHEDULER_JOB_LOG that is of operation RUN or RETRY_RUN, there will be a corresponding row in *_JOB_RUN_DETAILS view with the same LOG_ID. LOG_DATE contains the timestamp of the entry, so sorting by LOG_DATE should give you a chronological picture of the life of a job.

Controlling Job Logging

You can control the amount of logging the Scheduler performs on jobs at either a class or job level. Normally, you will want to control jobs at a class level as this offers a full audit trail. To do this, use the logging_level attribute in the CREATE_JOB_CLASS procedure.

For each new class, the creator of the class must specify what the logging level is for all jobs in that class. The three possible options are:

• DBMS_SCHEDULER.LOGGING_OFF

  No logging will be performed for any jobs in this class.

• DBMS_SCHEDULER.LOGGING_RUNS

  The Scheduler will write detailed information to the job log for all runs of each job in this class.

• DBMS_SCHEDULER.LOGGING_FULL

  In addition to recording every run of a job, the Scheduler will record all operations performed on all jobs in this class. In other words, every time a job is created, enabled, disabled, altered, and so on will be recorded in the log.

By default, only job runs are recorded. For job classes that have very short and highly frequent jobs, the overhead of recording every single run might be too much and you might choose to turn the logging off. You might, however, prefer to have a complete audit trail of everything that happened to the jobs in a specific class, in which case you need to turn on full logging for that class.

The second way of controlling the logging level is on an individual job basis. You should keep in mind, however, that the log in many cases is used as an audit trail, thus if you want a certain level of logging, the individual job creator must not be able to turn logging off. The class-specific level is, therefore, the minimum level at which job information will be logged. A job creator can only turn on more logging for an individual job, not less.

This functionality is provided for debugging purposes. For example, if the class-specific level is set to record job runs and the job-specific logging is turned off, the Scheduler will still log the runs. If, on the other hand, the job creator turns on full logging and the class-specific level is set to record runs only, all operations on this individual job will be logged. This way, an end user can test his job by turning on full logging.

To set the logging level of an individual job, you must use the SET_ATTRIBUTE procedure on that job. For example, to turn on full logging for a job called mytestjob, issue the following statement:

DBMS_SCHEDULER.SET_ATTRIBUTE (
   'mytestjob', 'logging_level', DBMS_SCHEDULER.LOGGING_FULL);

Window Logs

A window log has an entry for each time you do the following:

• create a window

• drop a window

• open a window

• close a window

• overlap windows

• disable a window

• enable a window

There are no logging levels for window logging, but every window operation will automatically be logged by the Scheduler.

For every row in a window log that is of a close operation, there will be a corresponding row in the WINDOW_DETAILS view, with the same log_id. To see the contents of the window log, query the DBA_SCHEDULER_WINDOW_LOG view. As an example, issue the following statement:

SELECT WINDOW_NAME, USER_NAME FROM DBA_SCHEDULER_WINDOW_LOG;

WINDOW_NAME                    USER_NAME
------------------------------ ------------------------------
MY_WINDOW10                    SYS
MY_WINDOW100                   SYS
MY_WINDOW100                   SYS
MY_WINDOW10                    SYS
MY_WINDOW100                   SYS
MY_WINDOW100                   SYS
MY_WINDOW1                     SYS
MY_WINDOW2                     SYS
MY_WINDOW3                     SYS
MY_WINDOW10                    SYS
MY_WINDOW100                   SYS

There is also a window details view that gives information about every window that was active and completed.

Purging Logs

To prevent job and window logs from growing indiscriminately, use the SET_SCHEDULER_ATTRIBUTE attribute to specify how much history (in days) to keep. The Scheduler automatically purges all log entries from both the job log and the window log that are older than the history you specify. It does this once a day, and the default is 30 days, which can be changed by using the SET_SCHEDULER_ATTRIBUTE procedure. For example, to change the number of days to 90, issue the following statement:

DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('log_history','90');

Some job classes are more important than others. Because of this, you can override this global history setting by using a class-specific setting. An example is if there are three classes (class1, class2, and class3) and you want to keep 10 days of history for the window log and class1 and class3, but 30 days for class2. To achieve this, issue the following statements:

DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('log_history','10');
DBMS_SCHEDULER.SET_ATTRIBUTE('class2','log_history','30');

You can also set the class-specific history when creating the job class.

Besides the Scheduler automatically purging the log once a day based on the log history specified, you might also want to manually purge the log. To do this, use the PURGE_LOG procedure. As an example, the following statement purges all entries from both the job and window logs:

DBMS_SCHEDULER.PURGE_LOG();

Another example is the following, which purges all entries from the jog log that are older than three days. The window log will stay as is.

DBMS_SCHEDULER.PURGE_LOG(log_history => 3, which_log => 'JOB_LOG');

The following statement purges all window log entries older than 10 days, all job log entries relating to job1, and all jobs in class2 that are older than 10 days:

DBMS_SCHEDULER.PURGE_LOG(log_history => 10, job_name => 'job1, sys.class2');

Types of Privileges and Their Descriptions

The following privileges are important when using the Scheduler.

Failed Jobs

If a job has the status of failed in the job table, it was scheduled to run once but the execution has failed. If the job was specified as restartable, all retries have failed.

If a job fails in the middle of execution, only the last transaction of that job is rolled back. If your job executes multiple transactions, you need to be careful about setting restartable to TRUE. You can query failed jobs by querying the *_SCHEDULER_JOB_RUN_DETAILS views.

Broken Jobs

A broken job is one that has exceeded a certain number of failures. This number is set in max_failures, and can be altered. In the case of a broken job, the entire job is broken, and it will not be run until it has been fixed. For debugging and testing, you can use the RUN_JOB procedure.

You can query broken jobs by querying the *_SCHEDULER_JOBS and *_SCHEDULER_JOB_LOG views.

Disabled Jobs

A job can become disabled for the following reasons:

• The job was manually disabled

• The job class it belongs to was dropped

• The program or schedule that it points to was dropped

• A window or window group is its schedule and it is dropped

Completed Jobs

A job will be completed if end_date or max_runs is reached.