dbms_scheduler jobs are not running automatically- a case study

Issue Description:

Suddenly I received a call few database jobs are not running automatically which are stopped automatically. I verified all those jobs. From the DDL I did not find any issues. All used procedures are also working fine which are not changed since six months. I started my investigation.

Investigation :

Step-1:
Disabled and enabled both the jobs, dropped and re-created both jobs with existing DDL. But issue not resolved.

Step-2:
Tried to call the job manually using below method. But Job is called and runned sucessfully. Then I thoght any J00* and CJQ process may be creating issue. Tried to trace both jobs.

-- Manually calling the job

SQL> exec DBMS_SCHEDULER.run_job('owner.jobname');

-- Started tracing
Note : Give this grant to your user/job owner  --> grant alter session to user
and conect to user/ job owner

SQL> alter session set tracefile_identifier='10046';
SQL> alter session set timed_statistics = true;
SQL> alter session set statistics_level=all;
SQL> alter session set max_dump_file_size = unlimited;
SQL> alter session set events '10046 trace name context forever,level 12';
SQL> exec DBMS_SCHEDULER.run_job('jobname');
SQL> select * from dual;
SQL> exit;

But when verified the tkprof output no symptoms identified.

Step-3:
I doubted the number of job queue slave process. job_queue_processes may be too low (this is the most common problem). The value of job_queue_processes limits the total number of dbms_scheduler and dbms_job jobs that can be running at a given time. To check whether this is the case check the current value of job_queue_processes with:

SQL> select value from v$parameter where name='job_queue_processes';
Then check the number of running jobs
SQL> select count(*) from dba_scheduler_running_jobs;
SQL> select count(*) from dba_jobs_running;

If this is the problem you can increase the parameter job_queue_processes:

SQL> alter system set job_queue_processes=1000;

But, I found counts are very less and job_queue_process value as 1000. Then I doubt sessions/ process may be low. But I found the set value is sufficient enough for this environment.

Click here to increase session/ process parameter value...

Step-4:
I checked any error in alert log. But not found anything related to this job. If any issue it must write to alert log and write to j00* named .trc file.

Step: 5:
If either of the above indicates that the SYSAUX tablespace (where the scheduler stores its logging tables) is full, you can use the dbms_scheduler.purge_log procedure to clear out old log entries.

-- To purge dbms_scheduler logging tables manually
sql> exec dbms_scheduler.purge_log ();

-- To purge dbms_scheduler logging tables automatically ( keep 90 days log)
sql> exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('log_history','90');

-- Verify the count
sql> select count(*) from user_scheduler_job_run_details;

Step-6:
Database is up and running. Other jobs are running. This job is not associated to any other window job. No session issue. Job is calling manually. Then I doubt on job priority which may cause not to run. Some times this issue may come.

-- How to see job priority

SQL> select owner,job_name,job_type,job_action,
repeat_interval,enabled,job_priority
from dba_scheduler_jobs where owner='owner';

I found the job priority is 3. i.e., very less priority. I thouht the job schould be disabled and enabled with followed by setting priority 1. I followed these steps:
-- if your doing in sys user:
SQL> exec dbms_scheduler.disable(name => 'owner.jobname');
SQL> exec dbms_scheduler.enable(name => 'owner.jobname');
SQL> exec DBMS_SCHEDULER.SET_ATTRIBUTE (name => 'owner.jobname',attribute => 'job_priority',value => 1 );

Then I verified with following query, I found the stopped jobs id running fine.

SQL> select * from dba_scheduler_job_run_details
where owner = 'owner' and trunc(log_date) = trunc(log_date) 
order by log_date desc;

Ohh. Sometimes issue may be resolved with tricky way but we may take it very serious to resolve this. Any way with this case study lots of things are covered.


Another workaround:

Issue may be for Oracle default window issues / job_queue process value.
-- Current job_queue_processes parameter value:
SQL> show parameter job_queue_processes;

NAME                      TYPE        VALUE
------------------------- ----------- ------------
job_queue_processes       integer     1000

-- alter to '0'
SQL> alter system set job_queue_processes=0;
System altered.

-- Againt set to original value/ required value:

SQL> alter system set job_queue_processes=100;


Note: Keeping a larger value may create issue also. Based on env and jobs you can decide.

Some Oracle default values I made False in Env. You choose yours:

SQL> select window_name,active from DBA_SCHEDULER_WINDOWS;

WINDOW_NAME          ACTIVE
------------------------------   -----
MONDAY_WINDOW        FALSE
TUESDAY_WINDOW       FALSE
WEDNESDAY_WINDOW     FALSE
THURSDAY_WINDOW      FALSE
FRIDAY_WINDOW        FALSE
SATURDAY_WINDOW      FALSE
SUNDAY_WINDOW        FALSE
WEEKNIGHT_WINDOW     FALSE
WEEKEND_WINDOW       TRUE

9 rows selected.

Note- I found WEEKNIGHT_WINDOW job was still running(active). I stopped like below, then all jobs started running automatically. Run below query to find some info about your jobs:

select * from DBA_SCHEDULER_RUNNING_JOBS;
select * from dba_scheduler_global_attribute;
select * from dba_scheduler_windows;
select * from dba_scheduler_jobs;

SQL> EXECUTE DBMS_SCHEDULER.CLOSE_WINDOW ('WEEKNIGHT_WINDOW');

Note:
Some times a miss-configuration exist in your scheduler windows settings that may lead the WEEKNIGHT_WINDOW kept ACTIVE and make the scheduler hang, the WEEKNIGHT_WINDOW must not be active as well as WEEKEND_WINDOW in 11g because they must not be used in 11g as there is a window for each week day, so please run the below to disable these windows:

exec DBMS_SCHEDULER.DISABLE ('WEEKNIGHT_WINDOW, WEEKEND_WINDOW'); 

Thanks you. Please feel free to ask questions via this blog.
Have a nice day...