Learn a bit Oracle Scheduler with BROKEN state

On Oracle Database, DBAs can check broken job for Oracle Job (dbms_job) at *_JOBS.BROKEN column. Anyway, DBAs have changed from DBMS_JOB to DBMS_SCHEDULER. So, I was curious How to check broken job for Oracle Scheduler (DBMS_SCHEDULER)? After found out... DBAs can check on *_SCHEDULER_JOBS.STATE column.

STATE

VARCHAR2(15)

Current state of the job: DISABLED RETRY SCHEDULED SCHEDULED RUNNING COMPLETED BROKEN FAILED REMOTE SUCCEEDED CHAIN_STALLED

When does Oracle Scheduler change STATE to be BROKEN? Then, DBAs should know some columns as well.

FAILURE_COUNT	NUMBER		Number of times the job has failed to run
MAX_FAILURES	NUMBER		Number of times the job will be allowed to fail before being marked broken

*_SCHEDULER_JOBS.STATE column will change to "BROKEN", when *_SCHEDULER_JOBS.FAILURE_COUNT value = _SCHEDULER_JOBS.MAX_FAILURES value. Really! yes... but I had some example to show about it. I tested on 11.2.0.1.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL>
SQL>
SQL> show user;
USER is "DEMO"
SQL>
SQL> BEGIN
  2    DBMS_SCHEDULER.create_job (
  3      job_name        => 'test_my_job',
  4      job_type        => 'PLSQL_BLOCK',
  5      job_action      => 'BEGIN DBMS_STATS.gather_schema_stats(''SCOTT1''); END;',
  6      start_date      => SYSTIMESTAMP,
  7      repeat_interval => 'freq=daily',
  8      end_date        => NULL,
  9      enabled         => TRUE);
 10  END;
 11  /

PL/SQL procedure successfully completed.

SQL> select job_name, state,enabled, failure_count, max_failures from user_scheduler_jobs;

JOB_NAME                       STATE           ENABL FAILURE_COUNT MAX_FAILURES
------------------------------ --------------- ----- ------------- ------------
TEST_MY_JOB                    SCHEDULED       TRUE              1

SQL> exec  DBMS_SCHEDULER.run_job (job_name            => 'test_my_job',use_current_session => FALSE);

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> select job_name, state,enabled, failure_count, max_failures from user_scheduler_jobs;

JOB_NAME                       STATE           ENABL FAILURE_COUNT MAX_FAILURES
------------------------------ --------------- ----- ------------- ------------
TEST_MY_JOB                    SCHEDULED       TRUE              2

SQL> exec  DBMS_SCHEDULER.run_job (job_name            => 'test_my_job',use_current_session => FALSE);

PL/SQL procedure successfully completed. 

SQL> select job_name, state,enabled, failure_count, max_failures from user_scheduler_jobs;
JOB_NAME                       STATE           ENABL FAILURE_COUNT MAX_FAILURES
------------------------------ --------------- ----- ------------- ------------
TEST_MY_JOB                    SCHEDULED       TRUE              3

SQL>
SQL>

I created Oracle Scheduler and set max_failures attribute. First Idea: set MAX_FAILURES = FAILURE_COUNT.

 SQL> exec DBMS_SCHEDULER.set_attribute (name => 'test_my_job', attribute => 'max_failures',value => 3);

PL/SQL procedure successfully completed.

SQL> select job_name, state,enabled, failure_count, max_failures from user_sched
uler_jobs;

JOB_NAME                       STATE           ENABL FAILURE_COUNT MAX_FAILURES
------------------------------ --------------- ----- ------------- ------------
TEST_MY_JOB                    SCHEDULED       TRUE              3            3

SQL>
SQL> exec  DBMS_SCHEDULER.run_job (job_name            =>t 'test_my_job',use_current_session => FALSE);

PL/SQL procedure successfully completed.

SQL>  select job_name, state,enabled, failure_count, max_failures from user_sched
uler_jobs;

JOB_NAME                       STATE           ENABL FAILURE_COUNT MAX_FAILURES
------------------------------ --------------- ----- ------------- ------------
TEST_MY_JOB                    SCHEDULED       TRUE              3            3

Case 1: set MAX_FAILURES = FAILURE_COUNT after ran JOB... Nothing change! in user_scheduler_jobs view, I checked on *_scheduler_job_log and *_scheduler_job_run_details views. Nothing change either!
I belief DBAs should disable JOB, then set MAX_FAILURE attribute and enable JOB, because after enabled... FAILURE_COUNT value will be reset to be 0.
Anyway, Tried MAX_FAILURES value = 4.

SQL> exec  DBMS_SCHEDULER.run_job (job_name           => 'test_my_job',use_current_session=> FALSE);

PL/SQL procedure successfully completed.

SQL> select job_name, state,enabled, failure_count, max_failures from user_sched
uler_jobs;

JOB_NAME                       STATE           ENABL FAILURE_COUNT MAX_FAILURES
------------------------------ --------------- ----- ------------- ------------
TEST_MY_JOB                    SCHEDULED       TRUE              3            3

SQL>
SQL> exec DBMS_SCHEDULER.set_attribute (name => 'test_my_job', attribute => 'max_failures',value => 4);
PL/SQL procedure successfully completed.

SQL> select job_name, state,enabled, failure_count, max_failures from user_sched
uler_jobs;

JOB_NAME                       STATE           ENABL FAILURE_COUNT MAX_FAILURES
------------------------------ --------------- ----- ------------- ------------
TEST_MY_JOB                    SCHEDULED       TRUE              3            4

SQL> exec  DBMS_SCHEDULER.run_job (job_name           => 'test_my_job',use_current_session ==> FALSE);

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> select job_name, state,enabled, failure_count, max_failures from user_scheduler_jobs;

JOB_NAME                       STATE           ENABL FAILURE_COUNT MAX_FAILURES
------------------------------ --------------- ----- ------------- ------------
TEST_MY_JOB                    BROKEN          FALSE             4            4

Now!  *_SCHEDULER_JOBS.STATE = "BROKEN". How to fix "BROKEN" state? - Just enable Job.

SQL> EXEC DBMS_SCHEDULER.enable(name=> 'test_my_job');

PL/SQL procedure successfully completed.

SQL>
SQL> select job_name, state,enabled, failure_count, max_failures from user_sched
uler_jobs;

JOB_NAME                       STATE           ENABL FAILURE_COUNT MAX_FAILURES
------------------------------ --------------- ----- ------------- ------------
TEST_MY_JOB                    SCHEDULED       TRUE              0            4

SQL>
SQL> exec  DBMS_SCHEDULER.run_job (job_name           => 'test_my_job',use_current_session => FALSE);

PL/SQL procedure successfully completed.

SQL> select job_name, state,enabled, failure_count, max_failures from user_sched
uler_jobs;

JOB_NAME                       STATE           ENABL FAILURE_COUNT MAX_FAILURES
------------------------------ --------------- ----- ------------- ------------
TEST_MY_JOB                    SCHEDULED       TRUE              1            4

How to unset MAX_FAILURES value?

SQL> exec DBMS_SCHEDULER.set_attribute (name => 'test_my_job', attribute => 'max_failures',value=> NULL);
BEGIN DBMS_SCHEDULER.set_attribute (name=> 'test_my_job', attribute=> 'max_failures',value=> NULL); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00307: too many declarations of 'SET_ATTRIBUTE' match this call
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


SQL>
SQL>
SQL>
SQL>
SQL>
SQL> exec DBMS_SCHEDULER.set_attribute (name => 'test_my_job', attribute => 'max_failures',value=> 0);
BEGIN DBMS_SCHEDULER.set_attribute (name => 'test_my_job', attribute => 'max_failures',value => 0); END;

*
ERROR at line 1:
ORA-27465: invalid value 0 for attribute MAX_FAILURES
ORA-06512: at "SYS.DBMS_ISCHED", line 4370
ORA-06512: at "SYS.DBMS_SCHEDULER", line 2850
ORA-06512: at line 1

SQL> exec DBMS_SCHEDULER.set_attribute (name => 'test_my_job', attribute=> 'max_failures',value => '');

PL/SQL procedure successfully completed.

SQL> select job_name, state,enabled, failure_count, max_failures from user_sched
uler_jobs;

JOB_NAME                       STATE           ENABL FAILURE_COUNT MAX_FAILURES
------------------------------ --------------- ----- ------------- ------------
TEST_MY_JOB                    SCHEDULED       TRUE              1

SQL> exec  DBMS_SCHEDULER.run_job (job_name            => 'test_my_job',use_current_session => FALSE);

PL/SQL procedure successfully completed.

SQL> exec  DBMS_SCHEDULER.run_job (job_name            => 'test_my_job',use_current_session => FALSE);

PL/SQL procedure successfully completed.

SQL> exec  DBMS_SCHEDULER.run_job (job_name            => 'test_my_job',use_current_session => FALSE);

PL/SQL procedure successfully completed.

SQL> exec  DBMS_SCHEDULER.run_job (job_name            => 'test_my_job',use_current_session => FALSE);

PL/SQL procedure successfully completed.

SQL> select job_name, state,enabled, failure_count, max_failures from user_scheduler_jobs;

JOB_NAME                       STATE           ENABL FAILURE_COUNT MAX_FAILURES
------------------------------ --------------- ----- ------------- ------------
TEST_MY_JOB                    SCHEDULED       TRUE              5

Just run "DBMS_SCHEDULER.set_attribute (name => 'test_my_job', attribute=> 'max_failures',value => '')".

Go back to CASE 1: ... Idea to set MAX_FAILURE attribute!

SQL> EXEC DBMS_SCHEDULER.disable(name => 'test_my_job');

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> select job_name, state,enabled, failure_count, max_failures from user_sched
uler_jobs;

JOB_NAME                       STATE           ENABL FAILURE_COUNT MAX_FAILURES
------------------------------ --------------- ----- ------------- ------------
TEST_MY_JOB                    DISABLED        FALSE             5

SQL>

SQL> exec DBMS_SCHEDULER.set_attribute (name =>  'test_my_job', attribute =>  'max_failures',value => 5);

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> select job_name, state,enabled, failure_count, max_failures from user_scheduler_jobs;

JOB_NAME                       STATE           ENABL FAILURE_COUNT MAX_FAILURES
------------------------------ --------------- ----- ------------- ------------
TEST_MY_JOB                    DISABLED        FALSE             5            5

SQL>
SQL> EXEC DBMS_SCHEDULER.enable(name=> 'test_my_job');

PL/SQL procedure successfully completed.

SQL>
SQL> select job_name, state,enabled, failure_count, max_failures from user_scheduler_jobs;

JOB_NAME                       STATE           ENABL FAILURE_COUNT MAX_FAILURES
------------------------------ --------------- ----- ------------- ------------
TEST_MY_JOB                    SCHEDULED       TRUE              0            5

SQL> exec  DBMS_SCHEDULER.run_job (job_name            => 'test_my_job',use_current_session => FALSE);

PL/SQL procedure successfully completed.

SQL> exec  DBMS_SCHEDULER.run_job (job_name            => 'test_my_job',use_current_session => FALSE);

PL/SQL procedure successfully completed.

SQL> exec  DBMS_SCHEDULER.run_job (job_name           => 'test_my_job',use_current_session => FALSE);

PL/SQL procedure successfully completed.

SQL> exec  DBMS_SCHEDULER.run_job (job_name            => 'test_my_job',use_current_session => FALSE);

PL/SQL procedure successfully completed.

SQL> exec  DBMS_SCHEDULER.run_job (job_name            => 'test_my_job',use_current_session => FALSE);

PL/SQL procedure successfully completed.

SQL> select job_name, state,enabled, failure_count, max_failures from user_scheduler_jobs;

JOB_NAME                       STATE           ENABL FAILURE_COUNT MAX_FAILURES
------------------------------ --------------- ----- ------------- ------------
TEST_MY_JOB                    BROKEN          FALSE             5            5

Read More... Oracle Document.