ORA-31626, (Database-pump)-job does not exist

A short post about the ORA-31626 error. Found out a scheduled export- and import- job was broken in an Oracle Database. Error:  ORA-31626: job does not exist. After checking the scheduled jobs I realized that it’s not the scheduled job, but the datapump-job (should have read the error more carefully – it states ‘database pump job does not exists…. ).

And it seems all related to being cost-effective in the cloud….

The full error:

Error at line 2
ORA-20010: ORA-20010: ORA-31626: job does not exist –
ORA-06512: at “<user>.<program>”, line 169
ORA-06512: at line 1
ORA-06512: at “SYS.DBMS_ISCHED”, line 150
ORA-06512: at “SYS.DBMS_SCHEDULER”, line 441
ORA-06512: at line 2
Script Terminated on line 2.

First it seemed a scheduled job, but it appeared to be a database-pump-job in a kind of ‘stale’ – situation.

Tue Jun 05 09:00:49 Central Europe Daylight Time 2018
Errors in file <path><file>53244.trc:
ORA-12012: error on auto execute of job 53244
ORA-20010: ORA-20010: ORA-39002: invalid operation – ORA-06512: at “SYS.DBMS_DATAPUMP”, line 4882
ORA-06512: at “<owner>.<program>”, line 146
ORA-06512: at “<owner>.<program>”, line 169

What datapump jobs are there?

SELECT owner_name, job_name, operation, job_mode, state, attached_sessions FROM   dba_datapump_jobs;

The job was still there, but ‘not running’. And that’s the one which is causing the error.

Finding out the master table:

SELECT o.status, o.object_id, o.object_type, o.owner||’.’||object_name “OWNER.OBJECT” FROM dba_objects o, dba_datapump_jobs j WHERE o.owner=j.owner_name AND o.object_name=j.job_name AND j.job_name NOT LIKE ‘BIN$%’ ORDER BY 4,2;

Drop this master table:

drop table <owner>.<object_name>;

And the job is running without problems.

But what causes this failure? Most likely: as we are running the database in the cloud, the server is being shut down at a regular interval when not being used to save costs. Not realizing that this job is scheduled around that time…..

So be aware of shutting servers down to save costs in the cloud.

Sources:

–> Note “How To Drop A NOT RUNNING DataPump Job? (Doc ID 1456784.1)