"Ora-01878: Specified Field Not Found In Datetime Or Interval in Alert.log after DST change" in DBO_Jobs_Queue collection (4229366)

An error similar to the following appears in the Oracle database agent log file after a daylight savings time change (forward).

Failed to execute collection [DBO_Jobs_Queue], reason=ORA-01878: specified field not found in datetime or interval

- Profile:OracleProfile{host='HOSTNAME', instance='INSTANCENAME', username='USERNAME', asSysDBA=false, ports='1521', properties={oracle.jdbc.ReadTimeout=900000}} 

Oracle bug 11800845 (this is a link an Oracle metalink article) which should be fixed in Oracle 11.2.0.3 and 12.1.

Note:
* This will only happen when the time during the DST transition "moves forward" (= one hour "missing" ) not when the time moves backward (skips one hour)
* this issue has no relation to "DST patches" or so, applying newer Oracle DST patches will not help
* in most cases this is seen  if the DBTIMEZONE is a named timezone that has DST, for example:

SQL> select DBTIMEZONE from dual;

DBTIMEZONE
----------
US/Eastern

Note however that this can also be seen when DTIMEZONE is an OFFSET ( -02:00) or a named timezone this has no DST (like UTC )
* this issue is exclusive for ORA-01878: specified field not found in datetime or interval seen with executing jobs on 11.2.0.2 and lower
* ORA-01878: specified field not found in datetime or interval in other situations is almost never a bug but an effect of DST , see note 417105.1 ORA-1878 During DST, Timestamp or Timezone Testing

To get the jobs going again check what jobs are stuck:

SQL>select job ,to_char(LAST_DATE,'YYYYMMDD HH24:MI:SS'),to_char( NEXT_DATE,'YYYYMMDD HH24:MI:SS') from job$ where NEXT_DATE < sysdate;

Determine the owner of the job with the query shown below:

SQL>select job, what, log_user, priv_user from dba_jobs where job=;

note: you need to adapt of course the command with the values for our system

SQL>Exec DBMS_JOB.NEXT_DATE(--job--, to_date( -- correct time for execution-- ,'YYYYMMDD HH24:MI:SS');
SQL>Commit;

To avoid the same issue again when next year the clocks are moving forward it is possible to

1) Apply Oracle patch 11900845 for Bug 11900845 - after dst change lot of dbms_jobs started failing with ora-1878
the fix is included in 11.2.0.3 (and up) and 12.1

or

2) change the time of execution of the jobs so that they are not executed during the non existing time when the clock moves forward.

or

3) use DBMS_SCHEDULER, DBMS_JOB is replaced by Oracle Scheduler, which is more powerful and flexible. Although Oracle recommends that you switch from DBMS_JOB to Oracle Scheduler, DBMS_JOB is still supported for backward compatibility.