ORA-31626: job does not exist and How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS:
=================================================================

expdp system/xxxxxxx full=y directory=EXPORT_DUMP_MYTEST dumpfile=${DUMPFILE} logfile=${LOGFILE}

Export: Release 10.2.0.4.0 – 64bit Production on Monday, 23 May, 2013 11:06:13

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

ORA-31626: job does not exist.
ORA-31650: timeout waiting for master process response.

Solution:
=========

We need to rerun catproc.sql and delete orphaned jobs

Frist of all the CATPROC component should not be INVALID in the database.
For this you have to rerun the catproc.sql script which can be found in $ORACLE_HOME/rdbms/admin

Verify the dictionary objects are valid per the dba_registry view:
——————————————————————

set pages 1000
set lines 120
col comp_id format a20
col comp_name format a40
col version format a10
col status format a15
select comp_id,comp_name,version,status from dba_registry order by 1;

Note:
—–

If any components show as invalid, run the utlrp.sql script again to try to validate.
At any time the utlrp script can be run independently of the catalog and catproc scripts.
There should be no other DDL occurring on the database while running the utlrp script.
Not following this recommendation may lead to deadlocks.
Recommend running the utlrp script when the database is in restricted mode.

ACTION PLAN
============
1. startup restrict
2. spool catproc.log
3. @?/rdbms/admin/catproc.sql
4. spool off

How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS:
===========================================================

Step 1. Determine in SQL*Plus which Data Pump jobs exist in the database:


SET lines 200
COL owner_name FORMAT a10;
COL job_name FORMAT a20
COL state FORMAT a12
COL operation LIKE state
COL job_mode LIKE state

— locate Data Pump jobs:

SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs
WHERE job_name NOT LIKE 'BIN$%'
ORDER BY 1,2;

OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED
---------- ------------------- --------- --------- ----------- --------
USER1 EXPDP_20051121 EXPORT SCHEMA EXECUTING 1
USER1 EXP_EXPORT_TABLE_01 EXPORT TABLE NOT RUNNING 0
USER1 EXP_EXPORT_TABLE_02 EXPORT TABLE NOT RUNNING 0
SYSTEM EXP_EXPORT_FULL_01 EXPORT FULL NOT RUNNING 0

Step 2. Ensure that the listed jobs in dba_datapump_jobs are not export/import Data Pump jobs that are active: status should be ‘NOT RUNNING’.

Step 3. Check with the job owner that the job with status ‘NOT RUNNING’ in dba_datapump_jobs is not an export/import Data Pump job that has been temporary stopped, but is actually a job that failed. (E.g. the full database export job by SYSTEM is not a job that failed, but was deliberately paused with STOP_JOB).

Step 4. Determine in SQL*Plus the related master tables:

— locate Data Pump master tables:

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;

STATUS OBJECT_ID OBJECT_TYPE OWNER.OBJECT
------- ---------- ------------ -------------------------
VALID 85283 TABLE USER1.EXPDP_20051121
VALID 85215 TABLE USER1.SYS_EXPORT_TABLE_02
VALID 85162 TABLE SYSTEM.SYS_EXPORT_FULL_01

Step 5. For jobs that were stopped in the past and won't be restarted anymore,
delete the master table. E.g.:

DROP TABLE USER1.sys_export_table_02;

-- For systems with recycle bin additionally run:

purge dba_recyclebin;

Step 6. Re-run the query on dba_datapump_jobs and dba_objects (step 1 and 4). If there are still jobs listed in dba_datapump_jobs,
and these jobs do not have a master table anymore, cleanup the job while connected as the job owner. E.g.:

CONNECT USER1/tiger
SET serveroutput on
SET lines 100
DECLARE
h1 NUMBER;
BEGIN
h1 := DBMS_DATAPUMP.ATTACH('SYS_EXPORT_TABLE_01','USER1');
DBMS_DATAPUMP.STOP_JOB (h1);
END;
/


Note that after the call to the STOP_JOB procedure, it may take some time for the job to be removed. Query the view user_datapump_jobs to check
whether the job has been removed:

SELECT * FROM user_datapump_jobs;Step 7. Confirm that the job has been removed:

CONNECT / as sysdba
SET lines 200
COL owner_name FORMAT a10;
COL job_name FORMAT a20
COL state FORMAT a12
COL operation LIKE state
COL job_mode LIKE state

-- locate Data Pump jobs:

SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs
WHERE job_name NOT LIKE 'BIN$%'
ORDER BY 1,2;

OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED
---------- ------------------- --------- --------- ----------- --------
USER1 EXPDP_20051121 EXPORT SCHEMA EXECUTING 1
SYSTEM SYS_EXPORT_FULL_01 EXPORT FULL NOT RUNNING 0

-- locate Data Pump master tables:

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;

STATUS OBJECT_ID OBJECT_TYPE OWNER.OBJECT
------- ---------- ------------ -------------------------
VALID 85283 TABLE USER1.EXPDP_20051121
VALID 85162 TABLE SYSTEM.SYS_EXPORT_FULL_01

——————————————————————–
The drop of the master table itself, does not lead to any data dictionary corruption. If you keep the master table after the job completes (using the undocumented parameter: KEEP_MASTER=Y), then a drop of the master table afterwards, will not cause any corruption.