Troubleshooting-DB Scheduler not working

DB Scheduler not working and it seems 0,0 on RUN_COUNT,FAILURE_COUNT columns respectively for USER_SCHEDULER_JOBS Dictionary

OBSERVATION-I

Oracle Initialization Parameter-JOB_QUEUE_PROCESSES
Default value 1000
Modifiable ALTER SYSTEM
Range of values 0 to 1000

JOB_QUEUE_PROCESSES specifies the maximum number of processes that can be created for the execution of jobs.

By default JOB_QUEUE_PROCESSES parameter would be 1000 but we notice that it seems zero

$sqlplus / as sysdba

SQL> select value from v$parameter where name='job_queue_processes';
VALUE
--------------------------------------------------------------------------------
0
SQL> alter system set job_queue_processes=1000;

SQL> select value from v$parameter where name='job_queue_processes';

VALUE
--------------------------------------------------------------------------------
1000

BEGIN
DBMS_SCHEDULER.create_job (
job_name => ‘FAIL_LOGIN_ATTEMPT_SCHEDULE’,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘BEGIN FAIL_LOGIN_ATTEMPT_SCHEDULE(); END;’,
start_date => SYSTIMESTAMP,
repeat_interval => ‘freq=minutely;interval=1;’,
end_date => NULL,
enabled => TRUE,
comments => ‘Job defined entirely by the CREATE JOB procedure.’);
END;
/

SQL> set linesize 10000
SQL> SELECT JOB_NAME,NEXT_RUN_DATE,RUN_COUNT,FAILURE_COUNT FROM USER_SCHEDULER_JOBS;

JOB_NAME NEXT_RUN_DATE RUN_COUNT FAILURE_COUNT
------------------------------ --------------------------------------------------------------------------- ----------
FAIL_LOGIN_ATTEMPT_SCHEDULE 09-NOV-13 02.54.00.000000 AM +05:30 2 2

This time it working fine but got failure count x:)

OBSERVATION-II
we notice that procedure name give as wrong on DB Scheduler job

SELECT DISTINCT NAME FROM USER_SOURCE;
NAME
------------------------------
FAIL_LOGIN_REST_PROC
BEGIN
DBMS_SCHEDULER.drop_job (job_name => 'FAIL_LOGIN_ATTEMPT_SCHEDULE' );
END;
/

BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'FAIL_LOGIN_ATTEMPT_SCHEDULE',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN FAIL_LOGIN_REST_PROC(); END;',
start_date => SYSDATE,
repeat_interval => 'freq=minutely;interval=1;',
end_date => NULL,
enabled => TRUE,
comments => 'Job defined entirely by the CREATE JOB procedure.');
END;
/
set linesize 10000
SELECT JOB_NAME,NEXT_RUN_DATE,RUN_COUNT,FAILURE_COUNT FROM USER_SCHEDULER_JOBS;

JOB_NAME NEXT_RUN_DATE RUN_COUNT FAILURE_COUNT
------------------------------ --------------------------------------------------------------------------- ---------- -------------
FAIL_LOGIN_ATTEMPT_SCHEDULE 09-NOV-13 02.59.48.600000 AM +05:30 1 0

This time it working fine without any failure count 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s