“ORA-23422”: Oracle Server could not generate an unused job number

Problem Statement

The application team has informed us that they encountered difficulties initiating a transactional SMSALERT job. In initial examination, it was noted that the SMS job submission was unsuccessful due to the occurrence of the error “ORA-23422”.

Diagnosis

At the outset of our investigation, we attempted to reset the ‘job_queue_processes’ in order to address any stalled job-related issues. However, this action proved ineffective, as job submissions continued to fail by the following error.

  
 ERROR at line 1:
 ORA-23422: Oracle Server could not generate an unused job number 
 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
 ORA-06512: at "SYS.DBMS_JOB", line 155
 ORA-06512: at line 9

This error signifies that Oracle is attempting to create a new job. It is unable to do so because a job with the same JOB_ID already exists in the database.
This error originates from the front-end and not recorded in any of the database logs.

We’ve conducted an examination of the dba_jobs/dba_scheduler_jobs and found no pre-existing SMS alert jobs within it.

We requested application team to reproduce the issue by running this job manually, but unable to do so because of functional dependencies.
We have gone through database PACKAGE use by application. It enables us to determine whether the application explicitly defines a new JOB_ID in the code. This was not the situation, as JOB_IDs were automatically generated by Oracle.

In an attempt to replicate the problem, we manually created a dummy job. It encountered same error during execution.

  
 BEGIN
 DECLARE
 v_JobNum NUMBER;
 BEGIN
 DBMS_JOB.SUBMIT(v_JobNum,'analyze_user;',sysdate,'sysdate +1'); 
 commit;
 end;
 END;
 /

 BEGIN
 *
 ERROR at line 1:
 ORA-23422: Oracle Server could not generate an unused job number 
 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
 ORA-06512: at "SYS.DBMS_JOB", line 155
 ORA-06512: at line 9

We opted to capture the ERRORSTACK for ORA-23422.

ALTER SESSION SET EVENTS ‘23422 trace name errorstack level 3’;

After validating the ERRORSTACK trace file, we discovered that the issue stemmed from an internal sequence SYS.JOBSEQ for generating JOB_ID’s. This results in subsequent job submission failures accompanied by ORA-23422 errors.

We manually executed SYS.JOBSEQ.nextval to pinpoint the numbers that are failing.

 
 SELECT sys.jobseq.nextval 
 FROM DUAL;

 NEXTVAL
 ----------
 318628156

None of the available job ID’s matched the generated sequence number.

 
       JOB                           WHAT
 --------- ------------------------------ 
  53666139          update_abc_src_table;
    151608   begin abc_check_alert_1;end;

So, what led to this????

The sys.scheduler$_dbmsjob_map dictionary table provides the mapping between the old DBA_JOBS and the DBA_SCHEDULER_JOBS.

Orphan entries were identified in the this base table. They were not visible in the formal dictionary view dba_jobs/dba_scheduler_jobs.
We conducted a validation of the base table’s content, examining entries beyond the last used JOB_ID. We did not find any irregular or anomalous entries.

 
 SELECT DBMS_JOB_NUMBER,JOB_NAME
 FROM sys.scheduler$_dbmsjob_map
 WHERE DBMS_JOB_NUMBER >= 318628156;

 DBMS_JOB_NUMBER 	       JOB_NAME
 --------------- ----------------------- 
       319077517     DBMS_JOB$_319077517
       319077542     DBMS_JOB$_319077542
       319077568     DBMS_JOB$_319077568
       319077569     DBMS_JOB$_319077569
       319077570     DBMS_JOB$_319077570
       319077571     DBMS_JOB$_319077571
       319077572     DBMS_JOB$_319077572
       319077586     DBMS_JOB$_319077586

To bypass unsuccessful numbers, we opted to increase the sequence value. The increment value determined as follows:

 
 319077586 (Max Number from base table sys.scheduler$_dbmsjob_map) 
 318628156 (sys.jobseq.nextval)

 319077586 - 318628156 = 449430

Solution Implemented

To workaround the situation, we concluded by increasing the sequence by 450,000 using the following command.

ATLER SEQUENCE sys.jobseq INCREMENT BY 450000;

After doing this step, our dummy job submission completed successfully.

 
 BEGIN
 DECLARE
 v_JobNum NUMBER;
 BEGIN
 DBMS_JOB.SUBMIT(v_JobNum,'analyze_user;',sysdate,'sysdate +1'); 
 commit;
 end;
 END;
 /

 PL/SQL procedure successfully completed.

We again set back sequence INCREMENT value to 1 as follows:

ATLER SEQUENCE sys.jobseq INCREMENT BY 1;

Post this change application team confirmed that SMS ALERT job has been submitted successfully.

Well !!!!
Stop ????
It’s not over just yet !!!!

What is the primary cause behind the substantial number of job_id?

In an optimal situation, when opting for the “JOB/SCHEDULER_JOB” feature, the intention is to create a job once and execute it multiple times by specifying recurring intervals.
Although, in this particular instance, the application was submitting a new job for every single execution of SMSALERT group (20+ jobs/second) , resulting in a substantial proliferation of JOB_ID and extremely high job numbers.
This marked the second occurrence of a comparable issue faced by the same application.

Lastly to establish a sustainable solution, we’ve asked application team to structure these jobs based on their categorisation. Then determine the recurrence intervals basis application’s schedule, rather than submitting new job for each execution.