Section 8.7. Managing Logging
8.7. Managing LoggingLike most scheduling systems, the Oracle Scheduler executes programs in the background, where no direct feedback to users or administrators can take place. Given this situation, how can you diagnose issues related to jobs after those jobs have stopped running? The Scheduler allows you to do so with the extensive logging information generated by your job operations and window operations. I'll describe logging for both in the following sections. 8.7.1. Job LogsThere are two data dictionary views that show information about job logs : DBA_SCHEDULER_JOB_LOG and DBA_SCHEDULER_JOB_RUN_DETAILS. 8.7.1.1. DBA_SCHEDULER_JOB_LOGWhen jobs are created, altered, dropped, and run, the results are loaded into a summary table that is visible through the DBA_SCHEDULER_JOB_LOG view . Its columns are listed in the following table.
8.7.1.2. DBA_SCHEDULER_JOB_RUN_DETAILSThe DBA_SCHEDULER_JOB_LOG view described in the previous section shows the summary log for your jobs, not the details. For detailed information about job execution, see the view DBA_SCHEDULER_JOB_RUN_DETAILS. Its columns are listed in the following table.
8.7.1.3. Pruning the job logJob logging provides very helpful information, but unless the logs are pruned from time to time, they will grow to an unmanageable size and overwhelm your database space. By default, the table where this information is stored (SYS. SCHEDULER$_JOB_RUN_DETAILS) is located in the SYSAUX tablespace . Oracle addresses this issue by automatically pruning the log entries after a certain period of time. This operation is accomplished by a Scheduler job, named PURGE_LOG, which is owned by the SYS user. This job is automatically installed when the Oracle database is created as a part of the job class DEFAULT_JOB_CLASS. It calls a named program called PURGE_LOG_PROG, which points to the stored procedure AUTO_PURGE in the DBMS_SCHEDULER package itself. The job is scheduled on a named schedule called DAILY_PURGE_SCHEDULE, which runs at 3:00 A.M. every day. Although this is an automatic job, it is still a scheduling job. As with any other kind of job, you can change its propertiesfor example, when it runs, what program it calls, and whether it is assigned a window. PURGE_LOG prunes only the log entries that are marked for deletion because their retention periods have expired. To control this retention period, see the later section "Setting the retention period." 8.7.1.4. Log levelsOne way that you can control the size of your log is to limit the amount of logging information that is recorded for a job. For example, you may want to record when the jobs are run but not when they are created. In some cases, you may not want any logging at all. You can control the level of logging by assigning a value for the CREATE_JOB_CLASS parameter logging_level when you create a job class. Later, when the job class is referenced in a job creation, the properties of the job will be inherited from the job class. DBMS_SCHEDULER supports three levels of logging:
I can specify the logging_level parameter in the CREATE_JOB_CLASS procedure as follows: BEGIN DBMS_SCHEDULER.create_job_class (job_class_name => 'MONITOR', logging_level => dbms_scheduler.logging_full ); END; I can also change the level by setting the logging_level attribute of the job class as follows. (See the discussion of attributes in the later section "Managing Attributes.") BEGIN DBMS_SCHEDULER.set_attribute (NAME => 'sys.default_job_class', ATTRIBUTE => 'logging_level', VALUE => dbms_scheduler.logging_full ); END; 8.7.1.5. Setting the retention periodAnother way to limit the size of your logs is to set the retention period of the log entries in the log tables. When the automatic PURGE_LOG job runs, it purges from the logs only those entries that are older than the associated retention period. The default retention period is 30 days. After 30 days, the 31st oldest day's log is automatically deleted. If you wish, you can specify a different retention period for each job class. You do this by setting the log_history parameter in the CREATE_JOB_CLASS procedure. (The default value is 30 days.) You can also modify the retention period for an existing job class by setting the log_history attribute at a later time. In the following example, I specify a log retention period of 120 days when the job class is created: BEGIN DBMS_SCHEDULER.create_job_class (job_class_name => 'MONITOR', log_history => 120 ); END; The next example sets the retention period for an existing job class called DEFAULT_JOB_CLASS: BEGIN DBMS_SCHEDULER.set_attribute (NAME => 'sys.default_job_class', ATTRIBUTE => 'log_history', VALUE => 120 ); END; I can check the current settings for log retention in the data dictionary view DBA_SCHEDULER_JOB_CLASSES, as shown here: SQL> SELECT job_class_name, logging_level, log_history 2 FROM dba_scheduler_job_classes; JOB_CLASS_NAME LOGG LOG_HISTORY ------------------------------ ---- ----------- DEFAULT_JOB_CLASS RUNS 120 AUTO_TASKS_JOB_CLASS RUNS MONITOR RUNS 120 The default logging level and retention period are adequate in most cases. However, in certain cases, you may want to override these defaults. Logs usually provide the only way that you can diagnose problems after jobs have completed. For particularly important jobs, you may want to enable full logging. For jobs where you may need to look further into the past than is customary, you may want to specify a higher retention period than the default. 8.7.2. Window LogsLike jobs, windows produce logging entries , which you can examine via the two data dictionary views described in the following sections.
8.7.2.1. DBA_SCHEDULER_WINDOW_LOGWhen windows are created, altered, and dropped, the results are loaded into a summary table that is visible through the DBA_SCHEDULER_WINDOW_LOG view . Its columns are listed in the following table.
8.7.2.2. DBA_SCHEDULER_WINDOW_DETAILSThe DBA_SCHEDULER_WINDOW_LOG view described in the previous section shows the summary log for your windows, not the details. For detailed information about window use, see the view DBA_SCHEDULER_WINDOW_DETAILS. Its columns are listed in the following table.
|
