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

0

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)

 

About Author

Job is a solution architect at AMIS Services with a special interest in Oracle licensing, High Availability architectures and managing complex (Oracle) environments, which includes Cloud environments. With a background as Oracle developer, DBA, team-manager and license-consultant he is able to utilize the Oracle technologies to a cost-efficient architecture for his customers. He is regularly involved in consultancy regarding: - Unlimited License Agreements (ULA). - License compliancy-checks and advice regarding optimizing the environment. - Second opinions. - Education / presentations about licensing and managing your infrastructure in the most cost-efficient manner. Twitter: @jobaclenl

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.