prevent oracle database scheduled job from running post scheduled time

Question

i have an oracle dbms scheduled job that runs at 5am. The server went down at 4am and came back up at 6am. The 5am job automatically started as soon as the server and db went online. How can i prevent this? Do i put an end date? Upated

BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'SYSTEM.DAILY_JOB'
      ,start_date      => TO_TIMESTAMP_TZ('2021/04/09 03:00:00.000000 +00:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')
      ,repeat_interval => 'FREQ=DAILY;INTERVAL=1'
      ,end_date        => NULL
      ,job_class       => 'DEFAULT_JOB_CLASS'
      ,job_type        => 'PLSQL_BLOCK'
      ,job_action      => 'SOME JOB'
      ,comments        => 'NA'
    );
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'SYSTEM.DAILY_JOB'
     ,attribute => 'RESTARTABLE'
     ,value     => FALSE);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'SYSTEM.DAILY_JOB'
     ,attribute => 'LOGGING_LEVEL'
     ,value     => SYS.DBMS_SCHEDULER.LOGGING_OFF);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'SYSTEM.DAILY_JOB'
     ,attribute => 'MAX_FAILURES');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'SYSTEM.DAILY_JOB'
     ,attribute => 'MAX_RUNS');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'SYSTEM.DAILY_JOB'
     ,attribute => 'STOP_ON_WINDOW_CLOSE'
     ,value     => FALSE);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'SYSTEM.DAILY_JOB'
     ,attribute => 'JOB_PRIORITY'
     ,value     => 3);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'SYSTEM.DAILY_JOB'
     ,attribute => 'SCHEDULE_LIMIT');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'SYSTEM.DAILY_JOB'
     ,attribute => 'AUTO_DROP'
     ,value     => FALSE);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'SYSTEM.DAILY_JOB'
     ,attribute => 'RESTART_ON_RECOVERY'
     ,value     => FALSE);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'SYSTEM.DAILY_JOB'
     ,attribute => 'RESTART_ON_FAILURE'
     ,value     => FALSE);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'SYSTEM.DAILY_JOB'
     ,attribute => 'STORE_OUTPUT'
     ,value     => TRUE);

  SYS.DBMS_SCHEDULER.ENABLE
    (name                  => 'SYSTEM.DAILY_JOB');
END;
/

All jobs under SYSTEM user - where these are created are set to 'FALSE' for RESTART_ON_RECOVERY . Why does it still restart? — nick, Oct 18 '21 at 10:27
have you take in consideration the hour when the job starts ( as you use to_timestamp_tz ) . Which version of Oracle are you using ? — Roberto Hernandez, Oct 18 '21 at 12:25
I updated the post below with an example shutting down the database — Roberto Hernandez, Oct 18 '21 at 13:01
see below my example,. although I am using 19c, it should be the same. — Roberto Hernandez, Oct 18 '21 at 15:37

Roberto Hernandez · Answer 1 · 2021-10-18T13:01:16.427

You have to change the attribute RESTART_ON_RECOVERY which by default is set to true

restart_on_recovery

If set to TRUE for a job and the job is stopped by a database shutdown, then the job is restarted when the database is recovered.

If set to FALSE, and the job is stopped by a database shutdown, then the job is marked as stopped when the database is recovered.

Example

BEGIN
    dbms_scheduler.create_job( job_name => 'MY_TEST',
                               job_type => 'PLSQL_BLOCK', 
                               job_action => 'BEGIN sleee; END;', 
                               number_of_arguments => 0,
                               start_date=> systimestamp + 1 , 
                               repeat_interval=>'freq=hourly; byminute=0; bysecond=0;',
                               enabled =>FALSE, 
                               auto_drop=>TRUE,
                               comments=> 'Test' 
                             );
   dbms_scheduler.set_attribute('MY_TEST','RESTART_ON_RECOVERY',false);
   dbms_scheduler.enable('MY_TEST');
END;
/

Demo

SQL> BEGIN
    dbms_scheduler.drop_job ( job_name => 'MY_TEST');
        dbms_scheduler.create_job( job_name => 'MY_TEST',
                                                           job_type => 'PLSQL_BLOCK',
                                                           job_action => 'BEGIN sleee; END;',
                                                           number_of_arguments => 0,
                                                           start_date=> systimestamp + 1 ,
                                                           repeat_interval=>'freq=hourly; byminute=0; bysecond=0;',
                                                           enabled =>FALSE,
                                                           auto_drop=>TRUE,
                                                           comments=> 'Test'
                                                         );
   dbms_scheduler.set_attribute('MY_TEST','NLS_ENV','NLS_LANGUAGE=''AMERICAN'' NLS_TERRITORY=''AMERICA'' ');
   dbms_scheduler.set_attribute('MY_TEST','RESTART_ON_RECOVERY',false);
   dbms_scheduler.enable('MY_TEST');
END;
/  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17

PL/SQL procedure successfully completed.

SQL> select job_name , restart_on_recovery from dba_scheduler_jobs where job_name = 'MY_TEST' ;

JOB_NAME
--------------------------------------------------------------------------------
RESTA
-----
MY_TEST
FALSE

Test Scenario

SQL> begin
  sys.db  2  ms_scheduler.create_job
    (
       job_name        => 'TEST_JOB'
  3    4    5        ,start_date      => systimestamp
      ,repeat_interval => 'freq=minutely;interval=1'
      ,job_class       => 'default_job_class'
  6    7    8        ,job_type        => 'plsql_block'
      ,job_action      => 'begin dbms_lock.sleep(2); end;'
      ,comments        => 'Test restart_on_recovery'
  9   10   11      );
  sys.dbms_scheduler.set_attribute
    ( name      => 'TEST_JOB'
 12   13   14       ,attribute => 'auto_drop'
     ,value     => false);
  sys.dbms_scheduler.set_attribute
 15   16   17      ( name      => 'TEST_JOB'
     ,attribute => 'restart_on_recovery'
 18   19       ,value     => false);
  sys.dbms_scheduler.set_attribute
    ( name      => 'TEST_JOB'
 20   21   22       ,attribute => 'restart_on_failure'
     ,value     => false);
 23   24    sys.dbms_scheduler.enable ('TEST_JOB');
 25  end;
/ 26

PL/SQL procedure successfully completed.

SQL> select job_name,state,LAST_START_DATE,NEXT_RUN_DATE,RESTART_ON_RECOVERY,RESTART_ON_FAILURE from dba_scheduler_jobs where job_name='TEST_JOB'


JOB_NAME                       STATE           LAST_START_DATE                                                             NEXT_RUN_DATE                                                               RESTA RESTA
------------------------------ --------------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- ----- -----
TEST_JOB                       RUNNING         18-OCT-21 02.33.05.519165 PM +02:00                                         18-OCT-21 02.33.05.348417 PM +02:00                                         FALSE FALSE

Let the job run several times

SQL> select job_name,state,LAST_START_DATE,NEXT_RUN_DATE,RESTART_ON_RECOVERY,RESTART_ON_FAILURE,run_count,FAILURE_COUNT from dba_scheduler_jobs where job_name='TEST_JOB'

JOB_NAME                       STATE           LAST_START_DATE                          NEXT_RUN_DATE                            RESTA RESTA  RUN_COUNT FAILURE_COUNT
------------------------------ --------------- ---------------------------------------- ---------------------------------------- ----- ----- ---------- -------------
TEST_JOB                       SCHEDULED       18-OCT-21 02.39.05.537625 PM +02:00      18-OCT-21 02.40.05.540345 PM +02:00      FALSE FALSE          9             0

Shutdown and startup ( with gap in the middle )

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup mount
ORACLE instance started.

Total System Global Area 1694495520 bytes
Fixed Size                  8897312 bytes
Variable Size            1442840576 bytes
Database Buffers          234881024 bytes
Redo Buffers                7876608 bytes
Database mounted.
SQL>
SQL> alter database open ;

Database altered.

SQL> select job_name,state,LAST_START_DATE,NEXT_RUN_DATE,RESTART_ON_RECOVERY,RESTART_ON_FAILURE,run_count,FAILURE_COUNT from dba_scheduler_jobs where job_name='TEST_JOB'
  2  ;

JOB_NAME                       STATE           LAST_START_DATE                          NEXT_RUN_DATE                            RESTA RESTA  RUN_COUNT FAILURE_COUNT
------------------------------ --------------- ---------------------------------------- ---------------------------------------- ----- ----- ---------- -------------
TEST_JOB                       SCHEDULED       18-OCT-21 02.39.05.537625 PM +02:00      18-OCT-21 02.58.05.317688 PM +02:00      FALSE FALSE         9             0

In my case, frequency of 1 minute, the job was not failing and it was not restarted either after recovery. The next execution will be the next iteration of my schedule window.

All jobs under SYSTEM user - where these are created are set to 'FALSE'. Why does it stil restart? — nick, Oct 18 '21 at 10:24
the jobs I created on my example are under `SYS` , not under `SYSTEM`. The job is marked as `stopped` and it will not run when the database starts up. In this scenario, if the job is scheduled to run and the db is down, it will not run when the db is up again — Roberto Hernandez, Oct 18 '21 at 12:13

prevent oracle database scheduled job from running post scheduled time

1 Answers1