Monitoring and Managing the Scheduler

Job Logs

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     ENABLE        SYS
MY_EMP_JOB1     UPDATE        SYS
MY_JOB1         CREATE        SCOTT
MY_EMP_JOB1     UPDATE        SYS
MY_EMP_JOB      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_JOB14        DROP          OE

When logging_level for a job is set to LOGGING_FULL, the additional_info column of the job log contains the before and after values of the modified attribute on update operations, and contains the values of all attributes on drop operations. This enables you to trace backwards from the current job state to the state of the job on previous job runs.

Job Run Details

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

select log_id, job_name, status, 
to_char(log_date, 'DD-MON-YYYY HH24:MI') log_date 
from dba_scheduler_job_run_details
where job_name = 'MY_JOB14';

    LOG_ID JOB_NAME               STATUS       LOG_DATE
---------- ---------------------- ------------ -----------------
        69 MY_JOB14               SUCCEEDED    02-JUN-2005 03:14
       124 MY_JOB14               SUCCEEDED    03-JUN-2005 03:15
       133 MY_JOB14               FAILURE      04-JUN-2005 03:00
       146 MY_JOB14               FAILURE      05-JUN-2005 03:01

For every row in SCHEDULER_JOB_LOG that is of operation RUN, RETRY_RUN, or RECOVERY_RUN, there will be a corresponding row in the *_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 that 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.

To see the contents of the window log, query the DBA_SCHEDULER_WINDOW_LOG view. The following statement shows sample output from this view:

SELECT LOG_ID, TO_CHAR(LOG_DATE, 'MM/DD/YYYY'), WINDOW_NAME, OPERATION
  FROM DBA_SCHEDULER_WINDOW_LOG;

    LOG_ID TO_CHAR(LO WINDOW_NAME       OPERATION
---------- ---------- ----------------- ------------------------------
         1 10/01/2004 WEEKNIGHT_WINDOW  CREATE
         2 10/01/2004 WEEKNIGHT_WINDOW  UPDATE
         3 10/01/2004 WEEKNIGHT_WINDOW  UPDATE
         4 10/01/2004 WEEKEND_WINDOW    CREATE
         5 10/01/2004 WEEKEND_WINDOW    UPDATE
         6 10/01/2004 WEEKEND_WINDOW    UPDATE
        22 10/06/2004 WEEKNIGHT_WINDOW  OPEN
        25 10/06/2004 WEEKNIGHT_WINDOW  CLOSE
        26 10/06/2004 WEEKNIGHT_WINDOW  OPEN
        29 10/06/2004 WEEKNIGHT_WINDOW  CLOSE

The DBA_SCHEDULER_WINDOWS_DETAILS view provides information about every window that was active and is now closed (completed). The following statement shows sample output from that view:

SELECT LOG_ID, WINDOW_NAME, ACTUAL_START_DATE, ACTUAL_DURATION
  FROM DBA_SCHEDULER_WINDOW_DETAILS;

    LOG_ID WINDOW_NAME      ACTUAL_START_DATE                    ACTUAL_DURATI
---------- ---------------- ------------------------------------ -------------
        25 WEEKNIGHT_WINDOW 06-OCT-04 03.12.48.832438 PM PST8PDT +000 01:02:32
        29 WEEKNIGHT_WINDOW 06-OCT-04 06.19.37.025704 PM PST8PDT +000 03:02:00

Notice that log IDs correspond in both of these views, and that in this case the rows in the DBA_SCHEDULER_WINDOWS_DETAILS view correspond to the CLOSE operations in the DBA_SCHEDULER_WINDOW_LOG view.

Purging Logs

To prevent job and window logs from growing indiscriminately, use the SET_SCHEDULER_ATTRIBUTE procedure to specify how much history (in days) to keep. Once per day, the Scheduler automatically purges all log entries that are older than the specified history period from both the job log and the window log. The default history period is 30 days. For example, to change the history period to 90 days, 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. For example, suppose that there are three job classes (class1, class2, and class3), and that you want to keep 10 days of history for the window log, 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.

Note that log entries pertaining to steps of a chain run are not purged until the entries for the main chain job are purged.

Purging Logs Manually

The PURGE_LOG procedure enables you to manually purge logs. 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 is not affected by this statement.

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

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

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