troubleshoot - scheduler job not running

troubleshoot - scheduler job not running

sqlplus  Scott/tiger

-- list scheduler job

set lines 120
col SCHEDULE_NAME format a25
col SCHEDULE_TYPE form   a10
prompt from dba_scheduler_jobs
prompt =============
select job_name, schedule_name, schedule_type, enabled from dba_scheduler_jobs where owner=upper('&schema') ;

-NOTE in the enabled column - TRUE means working, FALSE means not working

-- identify scheduler running jobs
 select
   rj.job_name,
   s.username,
   s.sid,
   s.serial#,
   p.spid,
   s.lockwait,
   s.logon_time
from 
   user_scheduler_running_jobs rj,
   v$session s,
   v$process p
where
   rj.session_id = s.sid
and
   s.paddr = p.addr
order by
   rj.job_name
;



SELECT max(log_date) FROM user_scheduler_job_run_details where job_name='SCH_JOB_NAME_HERE'' 


-- to kill by sid


select 'alter system kill session '||chr(39)||s.sid||','||s.serial#||chr(39)||';'
from user_scheduler_running_jobs rj,
     v$session s,
     v$process p
where rj.session_id = s.sid
  and s.paddr = p.addr
order by rj.job_name;


-- to kill by by pid

select 'kill -9 '||p.spid
from
   user_scheduler_running_jobs rj,
   v$session s,
   v$process p
where
   rj.session_id = s.sid
and
   s.paddr = p.addr
order by
   rj.job_name
;


useful commands
exec dbms_scheduler.disable('SCH_JOB_NAME_HERE');
exec dbms_scheduler.enable('"SCH_JOB_NAME_HERE"');

exec dbms_scheduler.stop_job('SCH_JOB_NAME_HERE');

Extract DDL of scheduler job in oracle

set long 10000

select dbms_metadata.get_ddl('PROCOBJ','SCOTT_JOB','SCOTT') from dual;

Note : The job in sys cannot be extracted, so, copy it to another user and then extract

exec dbms_scheduler.copy_job('SYS.MY_JOB','SCOTT.MY_JOB');

DBMS_SCHEDULER create schedule create job

- create schedule

 -- Creating a common schedule which could be used to schedule a JOB to run Daily at 1:00 AM
     -- US/Eastern Time.
DBMS_SCHEDULER.create_schedule(
                                    schedule_name   => 'DLY0100_COMMON_SCHED'                     ,
                                    start_date      =>  trunc(SYSDATE+1) at TIME ZONE 'US/Eastern',
                                    repeat_interval => 'freq=daily; byhour=1;'                    ,
                                    comments        => 'Runtime - Everyday (Mon-Sun) at 01:00 AM US/Eastern Time'
                                   );


 --
     -- Create a job with an inline program and a defined schedule to
     -- do daily purge for g  tables.
     --
     DBMS_SCHEDULER.create_job(
                               job_name      => 'DLY0100_DAILY_PURGE_GRP1'         ,
                               schedule_name => 'DLY0100_COMMON_SCHED'             ,
                               job_type      => 'PLSQL_BLOCK'                      ,
                               job_action    => 'BEGIN daily_purge(null,1); END;'  ,
                               enabled       => TRUE                               ,
                               auto_drop     => FALSE                              ,
                               comments      => 'Daily job to purge group 1 tables.'
                              );

 -- Creating a common schedule which could be used to schedule a JOB to run Weekly at 3:00 AM
     -- US/Central Time.
     --
     DBMS_SCHEDULER.create_schedule(
                                    schedule_name   => 'WKLY0300_COMMON_SCHED'                     ,
                                    start_date      =>  trunc(SYSDATE+1) at TIME ZONE 'US/Eastern',
                                    repeat_interval => 'freq=weekly; byday=sun; byhour=2;'                    ,
                                    comments        => 'Runtime - Every Sun at 3:00AM '
                                   );

-- Create a job with  proecdure
   DBMS_SCHEDULER.create_job(
                               job_name      => 'WKLY0300_SP_COLLECT_MIS_STATS'       ,
                               schedule_name => 'WKLY0300_COMMON_SCHED'              ,
                               job_type      => 'STORED_PROCEDURE'                       ,
                               job_action    => 'sp_collect_mis_stats',
                               enabled       => TRUE                                ,
                               auto_drop     => FALSE                               ,
                               comments      => 'Weekly job to collect mis stats'
                              );

Scheduler disable/enable

exec dbms_scheduler.drop_job('SCOTT.TESTJOB');
exec dbms_scheduler.disable('TESTJOB');
exec dbms_scheduler.enable('"TESTJOB"');
exec dbms_scheduler.stop_job('TESTJOB');

Scheduler Jobs report

SPOOL verify_scheduler_changes.log
--
SELECT to_char(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') "START_TIME"
  FROM DUAL;
--
PROMPT '*****************************************************************';
--
DEFINE TEST='CHECK Scheduler Jobs Creation and various status';
--
PROMPT 'Verify:    &TEST'
PROMPT 'User:      &_USER'
PROMPT 'Database:  &_CONNECT_IDENTIFIER';
PROMPT '**** CHECK Scheduler Jobs Creation and various status Report ****';
PROMPT '*****************************************************************';
--
SET linesize 157;
--
PROMPT ' '
PROMPT  '**** Query Number 1 ****'
PROMPT ' 1) The below query is used to check following things:'
PROMPT ' '
--
PROMPT '   a) Whether the scheduler jobs have been created successfully.'
PROMPT '   b) All the jobs should be in enabled state i.e. ENABLED=TRUE. If it is not then the job would probably not run on the schedule date/time'
PROMPT '   c) Check the state column, it should be 'SCHEDULED''
PROMPT '   d) Check the last_start_date, the next_run_date to verify whether the job ran at the scheduled time.'
PROMPT '   e) Check the failure_count, ideally it should be zero. If not then use query # 2 or 3 to check why the job failed.'
PROMPT '   f) Check the run_count column to see how many times the job ran.'

--
  SELECT  job_name
         ,enabled
         ,state
         ,run_count
         ,failure_count
         ,rpad(last_start_date,39) AS "LAST_START_DATE"
         ,rpad(next_run_date,39)   AS "NEXT_RUN_DATE"
    FROM  user_scheduler_jobs
ORDER BY  job_name;
--
PROMPT ' '
PROMPT ' '
PROMPT  '**** Query Number 2 ****'
PROMPT ' 1) The below query is used to check following things:'
PROMPT ' '
--
PROMPT '   a) Whether each job ran at the requested scheduled time.'
PROMPT '   b) Check the actual start date.'
PROMPT ' '
--
COLUMN job_name FORMAT A30
COLUMN status FORMAT A12
--
  SELECT  to_char(log_date, 'DD-MON-YY HH24:MI:SS') AS "LOG_DATE"
         ,job_name
         ,status
         ,to_char(req_start_date, 'DD-MON-YY HH24:MI:SS') AS "REQ_START_DATE"
         ,to_char(actual_start_date, 'DD-MON-YY HH24:MI:SS') AS "ACTUAL_START_DATE"
         ,rpad(run_duration,25) AS "JOB_RUN_DURATION"
    FROM  user_scheduler_job_run_details
ORDER BY  log_date DESC;
--
--
PROMPT ' '
PROMPT ' '
PROMPT  '**** Query Number 3 ****'
PROMPT ' 1) The below query is used to check following things:'
PROMPT ' '
--
PROMPT '   a) How many jobs are in failed or broken status.'
PROMPT '   b) Check the additional_info column to check why the job failed'
PROMPT ' '
--
COLUMN job_name FORMAT A30
COLUMN status FORMAT A12
COLUMN additional_info word_wrapped
--
  SELECT  to_char(log_date, 'DD-MON-YY HH24:MI:SS') AS "LOG_DATE"
         ,job_name
         ,status
         ,additional_info
    FROM  user_scheduler_job_run_details
   WHERE  status != 'SUCCEEDED'
ORDER BY  log_date DESC;
--
--
--
PROMPT ' '
PROMPT ' '
PROMPT  '**** Query Number 4 ****'
PROMPT ' 1) The below query is used to check following things:'
PROMPT ' '
--
PROMPT '   a) Whether all the schedules have been created.'
PROMPT '   b) Start date of each schedule should match with the create scripts.'
PROMPT ' '
--
COLUMN schedule_name FORMAT A30
COLUMN repeat_interval FORMAT A50
COLUMN comments FORMAT A55 word_wrapped

  SELECT  schedule_name
         ,to_char(start_date, 'DD-MON-YY HH24:MI:SS') AS "START_DATE"
         ,repeat_interval
         ,comments
    FROM  user_scheduler_schedules
ORDER BY  schedule_name;
--
--
--
PROMPT ' '
PROMPT ' '
PROMPT  '**** Query Number 5 ****'
PROMPT ' 1) The below query is used to check following things:'
PROMPT ' '
--
PROMPT '   a) Whether all the programs have been created.'
PROMPT '   b) Program action for each program, this tells us what package/procedure is been referenced.'
PROMPT '   c) All the programs should be in Enables state'
PROMPT ' '
--
COLUMN program_name FORMAT A30
COLUMN program_action FORMAT A60 word_wrapped
COLUMN comments FORMAT A55 word_wrapped
--
  SELECT  program_name
         ,program_action
         ,enabled
         ,comments
    FROM  user_scheduler_programs
ORDER BY  program_name;
--
--
--
PROMPT ' '
PROMPT ' '
PROMPT  '**** Query Number 6 ****'
PROMPT ' 1) The below query is used to check following things:'
PROMPT ' '
--
PROMPT '   a) This query will show all the programs which are not in enabled state. Ideally you should see no rows.'
PROMPT '   b) All the programs should be in Enables state'
PROMPT ' '
--
  SELECT  program_name
         ,program_action
         ,enabled
         ,comments
    FROM  user_scheduler_programs
   WHERE  enabled != 'TRUE'
ORDER BY  program_name;
--
--
--
PROMPT ' '
PROMPT ' '
PROMPT  '**** Query Number 7 ****'
PROMPT ' 1) The below query is used to check following things:'
PROMPT ' '
--
PROMPT '   a) How many dbms_jobs are still running and their function as defined in the "what column"'
PROMPT '   b) Ideally there should not be any dbms_jobs in the critical DB and there should be one dbms_job on the log DB to delete the event log'
PROMPT '      tables. This would be soon removed once the partitioning changes are checked in.'
PROMPT '   c) If you find dbms_jobs in the critical then all of them should be removed by running remove_all_user_jobs.sql (checkd in and part of 9A kit)'
PROMPT '      This script is not part of the rebuild script as it had to be run only once.'
PROMPT '   d) Once the partition maintenance code is part of the kit, then the dbms_job on the log side has to be removed.'
PROMPT ' '
--
COLUMN job FORMAT 9999
COLUMN what word_wrapped
--
SELECT  job
       ,what
  FROM  user_jobs;
--
PROMPT ' '
--
SELECT to_char(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') "END_TIME"
  FROM DUAL;
--
PROMPT '*****  END Of  Report *****'
--
SPOOL OFF;
QUIT

Killing the Oracle DBMS JOB

Summary

1. Find the Job You Want to Bring Down
2. Mark the DBMS_JOB as Broken
3. Kill the Oracle Session

4. Kill the O/S Process
5. Check if the Job is Still Running

6. Determine the Current Number of Job Queue Processes
7. Alter the Job Queue to Zero
8. Validate that No Processes are Using the Job Queue
9. Mark the DBMS_JOB as Not Broken
10. Alter the Job Queue to Original Value
11. Validate that DBMS_JOB Is Running

scheduled_dbms_jobs.sql

set linesize 250
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60

select j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj) j;

What Jobs are Actually Running

Returns sid of the jobs running

running_jobs.sql

set linesize 250
col sid for 9999 head 'Session|ID'
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60
select j.sid,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select djr.SID,
dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j;

What Sessions are Running the Jobs
returned values
spid  is the operating system process identifier and sid, serial# which is the session id and session  serial number.

session_jobs.sql

set linesize 250
col sid for 9999 head 'Session|ID'
col spid head 'O/S|Process|ID'
col serial# for 9999999 head 'Session|Serial#'
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60
select j.sid,
s.spid,
s.serial#,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select djr.SID,
dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j,
(select p.spid, s.sid, s.serial#
from v$process p, v$session s
where p.addr = s.paddr ) s
where j.sid = s.sid;

Bringing Down a DBMS_JOB

1. Find the Job You Want to Bring Down

In order to do anything you first need to find the job that is giving you a headache. Go ahead and run the session_jobs.sql. This will give you the prime information, job, sid, serial#, and spid, for the following actions in bringing down the job.

2. Mark the DBMS_JOB as Broken
Use the following command  to break the  job. It won’t stop the running job, just marks it broken

SQL> EXEC DBMS_JOB.BROKEN(job#,TRUE);

NOTE :  you will need to mark the jobs as unbroken when the database comes back up, more on that later.

3. Kill the Oracle Session

Since the job is still running and it isn't going to end soon, you will need to kill the Oracle session that is executing the job. Use the following command for to kill the job.

ALTER SYSTEM KILL SESSION 'sid,serial#';

4. Kill the O/S Process

More often than not the previous step will still leave the job attached to the database and still running. When this happens you will need to go out to the operating system level and get rid of the process that has spawned from the running job. In order to do this you must login to the database box and issue the following command, depending on the type of operating system you have.

For Windows, at the DOS Prompt: orakill sid spid

For UNIX at the command line> kill '9 spid

The orakill is an Oracle command, while kill is a Unix command.

5. Check if the Job is Still Running

Re-run the session_jobs.sql script to see if you have gotten rid of the job. If you have there is no reason to go further. Usually steps 1 through 4 will be sufficient to get rid of a job but when the job is running wild you will have to continue with steps 6 through 11 which describes a process for bouncing the job queue process.

6. Determine the Current Number of Job Queue Processes

SQL> col value for a10
SQL> select name,value from v$parameter where name = 'job_queue_processes';

7. Alter the Job Queue to Zero

SQL> ALTER SYSTEM SET job_queue_processes = 0;

This will bring down the entire job queue processes.

8. Validate that No Processes are Using the Job Queue

Re-run the session_jobs.sql script to see if any jobs are still running. Since we have given a hard stop to the job queue and issued the kill commands, you can now wait until no more jobs are running. After all the jobs have quit running, you can do whatever maintenance or tuning you need to do before proceeding.

9. Mark the DBMS_JOB as Not Broken

You can now reset the broken job to not broken so they can run again. Just issue the command.

SQL>EXEC DBMS_JOB.BROKEN(job#,FALSE):

10. Alter the Job Queue to Original Value

Set the job queue to its' original value so that the jobs can run again.

ALTER SYSTEM SET job_queue_processes = original_value;

11. Validate that DBMS_JOB Is Running

To make sure everything is back to normal, re-run the above scripts to validate that jobs are scheduled, not broken, and are executing with the next and last dates columns changing.

Hope this Help! Rupam

dbms_scheduler

SCHEDULER_ADMIN role for job scheduling

CREATE JOB privilege for creating jobs