Master Note: Troubleshooting Oracle Scheduler
By Rehab-Oracle on Mar 19, 2014
Oracle provides a collection of functions and procedures in the DBMS_SCHEDULER package to help simplify management tasks, as well as providing a rich set of functionality for complex scheduling needs. Collectively, these functions are called the Scheduler, and they are callable from any PL/SQL program.
Starting from 12c :
The coordinator looks at the root database and all the child PDBs and selects jobs based on the job priority, the job scheduled start time, and the availability of resources to run the job. The latter criterion depends on the consumer group of the job and the resource plan currently in effect. The coordinator makes no attempt to be fair to every PDB. The only way to ensure that jobs from a PDB are not starved is to allocate enough resources to it.
Known Issues Involving Oracle Scheduler
Problems with the Scheduler can be caused by such things as versions, incorrect setups and/or incorrect usage. The steps that follow will help to clarify where the problem might be and provides information that can be collected to help in diagnosing the issue. Please note that for Oracle Jobs, it is important to distinguish between the usage of the conventional Job queue (DBMS_JOB) and the usage of the Scheduler (DBMS_SCHEDULER). DBMS_JOB is mainly used in Oracle version 9i and lower and has been replaced by the Scheduler, which was introduced in Oracle version 10G. This document will focus mainly on Scheduler (DBMS_SCHEDULER) related issues.
For a thorough list of known issues with the Oracle Scheduler, please refer to:
NOTE 1311355.1 Known Issues And Bugs Using the Scheduler
Diagnosing Scheduler Issues
Firstly, to identify the affected job the following can be used:
Building a Testcase
In the area of the Scheduler, a testcase can often help in solving the issue or finding a solution because there are so many ways to build a job structure. Unexpected behavior or errors may be located in the user commands executed by the job and jobs can execute different types of commands, like PLSQL-programs, procedures, external shell scripts and more. Depending on the problem it is useful to test the successful execution of these commands outside the jobs first to confirm if the problem is in fact located in the job usage. Problems occurring after changes in a job setup or with a newly created job setup often benefits showing the behavior using a small testcase where the issue can be reproduced. A guideline for setting up a simple testcase is provided in the following:
According to the situation, combine the SQL commands to build as simple a job structure as possible with only the objects that are needed. Replacing large and complex job setups and minimizing the job structure to only the relevant parts of the problem is important to developing a simple testcase where the behavior / problem can be shown in an easy way and can actually help in finding any possible workarounds.
When the problem in the job structure cannot be found in an easy manner it is often recommended to activate sql tracing by setting the event 10046. To produce a valuable result, set timed_statistics to true and max_dump_file_size to unlimited before executing the job.
SQL> alter session set max_dump_file_size=unlimited;
SQL> alter session set timed_statistics = true;
SQL> alter session set events '10046 trace name context forever, level 4';
SQL> BEGIN DBMS_SCHEDULER.run_job (job_name => 'runtest_job',
use_current_session => TRUE);
Please note that by setting the event in a parameter file, each process would have produced a trace. In this case, optimal tracing is set at the session level, where the problem can be reproduced and then tracing disabled. Ensure that only matching traces are uploaded.
For the Scheduler, additional tracing has been introduced with setting the attribute logging_level for the jobs and using an event to dump further diagnostics for the job coordinator and the job slaves:
Automatic Scheduling Issues
Common problems with Scheduler are that jobs do not start at the expected time or won't start automatically.
Until Oracle Database version 11.1 , the setting of the parameter JOB_QUEUE_PROCESSES is not mandatory for the Scheduler. The Job Coordinator starts job slaves on demand, to work on the job queue table. This means setting JOB_QUEUE_PROCESSES to 0 caused DBMS_JOB jobs to not run, but DBMS_SCHEDULER jobs were unaffected and would still run.
Note 742683.1 Scheduled Job That Works Does Not Start In Window
Issues with Statistic Jobs (10g)
Note 430636.1 GATHER_STATS_JOB Is Not Running
Issues with 11g Autotasks
Note 865933.1 DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS Procedure Requires SYSDBA Privilege
Helpful Articles / Examples for Scheduler
Note 807159.1 Sample Code and Select Statements to Build A simple Testcase for Scheduler / Jobs
If you were not able to resolve the issue with the details provided in this or referenced documents, please raise a Service Request for further assistance from Oracle Support. Please provide any relevant tracing and/or a testcase as well as the following information:
set ver off
set term off
set page 0
set markup html on spool on
--The proceding lines needs to be un commented when executing aganist 12c Database for a CDB (pluggable instance )
--alter session set container='<pdp name>'
alter session set NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH.MI.SS AM TZR'
alter session set NLS_DATE_FORMAT='DD-MON-RR HH.MI.SS AM'
DURATION from DBA_SCHEDULER_WINDOWS
ACTUAL_DURATION,ADDITIONAL_INFO from DBA_SCHEDULER_WINDOW_DETAILS
order by LOG_DATE
select LOG_DATE,STATUS,WINDOW_NAME, OPERATION from DBA_SCHEDULER_WINDOW_LOG
order by LOG_DATE desc
select WINDOW_GROUP_NAME, ENABLED, NEXT_START_DATE from DBA_SCHEDULER_WINDOW_GROUPS
select LOG_DATE, OWNER,JOB_NAME, STATUS,ERROR#,REQ_START_DATE,ACTUAL_START_DATE,
ADDITIONAL_INFO from DBA_SCHEDULER_JOB_RUN_DETAILS
where JOB_NAME in ('')
order by LOG_DATE
show parameter job_queue
set markup html off spool off
Category: Master Notes