Skip to Main Content
  • Questions
  • DataPump crashed, now cant create new jobs

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Heiko.

Asked: July 27, 2020 - 3:13 pm UTC

Answered by: Connor McDonald - Last updated: July 31, 2020 - 2:39 am UTC

Category: Database Administration - Version: 12..1.0.2.0

Viewed 100+ times

You Asked

Hy Tom,

a sheduled task that worked for month crashed sudenly.
Its an EXPDP job transfering Data to an external SSD (USB3.0).

The Disk still runs.

The errors listet after a new test to export are like: (EXPDP SYSTEM/XXXX@....)
ORA-31626: now job exists
ORA-31638: Job SYS_EXPORT_SCHEMA_01 for User SYSTEM cannot be attached
ORA-06512: in "SYS.DBMS_SYS_ERROR", Line 95
......

I read some hints resulting in deleting all DataPump jobs (was too much I think).



and we said...

From MOS Doc ID 336014.1

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

%sqlplus /nolog

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
COL owner.object for a50

-- locate Data Pump jobs:

SELECT owner_name, job_name, rtrim(operation) "OPERATION",
       rtrim(job_mode) "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
---------- ------------------- --------- --------- ----------- --------
<SCHEMA>   EXPDP_20051121      EXPORT    SCHEMA    EXECUTING          1
<SCHEMA>   SYS_EXPORT_TABLE_01 EXPORT    TABLE     NOT RUNNING        0
<SCHEMA>   SYS_EXPORT_TABLE_02 EXPORT    TABLE     NOT RUNNING        0
SYSTEM     SYS_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. Identify orphan DataPump external tables. To do this, run the following as SYSDBA in SQL*Plus:

set linesize 200 trimspool on
set pagesize 2000
col owner form a30
col created form a25
col last_ddl_time form a25
col object_name form a30
col object_type form a25
select OWNER,OBJECT_NAME,OBJECT_TYPE, status,
to_char(CREATED,'dd-mon-yyyy hh24:mi:ss') created ,to_char(LAST_DDL_TIME , 'dd-mon-yyyy hh24:mi:ss') last_ddl_time
from dba_objects
where object_name like 'ET$%'
/

select owner, TABLE_NAME, DEFAULT_DIRECTORY_NAME, ACCESS_TYPE
from dba_external_tables
order by 1,2
/

Correlate the information from DBA_OBJECTS and DBA_EXTERNAL TABLES above to identify the temporary external tables that belong to the DataPump orphaned jobs.
Drop the temporary external tables that belong to the DataPump orphaned job. eg:

SQL> drop table system.&1 purge;
Enter value for 1: ET$00654E1E0001
old 1: drop table system.&1 purge
new 1: drop table system.ET$00654E1E0001 purge


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

-- locate Data Pump master tables:

COL owner.object FORMAT a50

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        <SCHEMA>.EXPDP_20051121
VALID        85215 TABLE        <SCHEMA>.SYS_EXPORT_TABLE_02
VALID        85162 TABLE        SYSTEM.SYS_EXPORT_FULL_01

select table_name, owner from dba_external_tables;


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

DROP TABLE <SCHEMA>.sys_export_table_02;

-- For systems with recycle bin additionally run:
purge dba_recyclebin;
Note:
=====
Following statement can be used to generate the drop table statement for the master table:

SELECT 'DROP TABLE '||o.owner||'.'||object_name||' PURGE;'
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$%';
 

NOTE:
In case the table name is mixed case, you can get errors on the drop, e.g.:
SQL> drop table SYSTEM.impdp_schema_TEST_10202014_0;
drop table SYSTEM.impdp_schema_TEST_10202014_0
                *
ERROR at line 1:
ORA-00942: table or view does not exist
  

Because the table has a mixed case, try using these statements with double quotes around the table name, for instance:
drop table SYSTEM."impdp_SCHEMA_TEST_04102015_1";
drop table SYSTEM."impdp_schema_TEST_10202014_0";
  

Step 7. 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 <USER>/<PASSWORD>

SET serveroutput on
SET lines 100
DECLARE
   h1 NUMBER;
BEGIN
   h1 := DBMS_DATAPUMP.ATTACH('SYS_EXPORT_TABLE_01','<SCHEMA>');
   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:

CONNECT <USER>/<PASSWORD>

SELECT * FROM user_datapump_jobs;


Step 8. 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 
COL owner.object for a50

-- locate Data Pump jobs: 

SELECT owner_name, job_name, rtrim(operation) "OPERATION",
       rtrim(job_mode) "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
---------- ------------------- --------- --------- ----------- --------
<SCHEMA>   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        <SCHEMA>.EXPDP_20051121
VALID        85162 TABLE        SYSTEM.SYS_EXPORT_FULL_01

and you rated our response

  (4 ratings)

Reviews

I hve to clarify

July 28, 2020 - 9:33 am UTC

Reviewer: Heiko Schröder from Ettlingen, Germany

I missed some parts of the errors - sorry

here the complete listing:
EXPDP system/*****@//localhost:1521/cosvega EXCLUDE=stat
istics  SCHEMAS=GSINZ_LWLMP_PLANUNG DUMPFILE=GSINZ_LWLMP_PLANUNG_DP.dmp LOGFILE=
GSINZ_LWLMP_PLANUNG_DP.Log


Export: Release 12.1.0.2.0 - Production on Di Jul 28 11:27:26 2020

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Angemeldet bei: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bi
t Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions
ORA-31626: Job ist nicht vorhanden
ORA-31638: Job SYS_EXPORT_SCHEMA_01 f³r Benutzer SYSTEM kann nicht zugeordnet we
rden
ORA-06512: in "SYS.DBMS_SYS_ERROR", Zeile 95
ORA-06512: in "SYS.KUPV$FT_INT", Zeile 429
ORA-39077: Zuordnung von Agent KUPC$A_1_112727297000000 an Queue "KUPC$C_1_20200
728112726" nicht m÷glich
ORA-06512: in "SYS.DBMS_SYS_ERROR", Zeile 95
ORA-06512: in "SYS.KUPC$QUE_INT", Zeile 254
ORA-06502: PL/SQL: numerischer oder Wertefehler: Zeichenfolgenpuffer zu klein


In step 1 of your answer I got NO lines at all :-(
Connor McDonald

Followup  

July 29, 2020 - 2:37 am UTC

Yes, but you might still have orphan tables

any more hints ?

July 29, 2020 - 2:02 pm UTC

Reviewer: Heiko Schröder from Ettlingen, Germany

Step 1: - no tables selected
Step 2: --
Step 3: --
Step 4: -no tables selected like 'ET§
- external Tables:

OWNER TABLE_NAME DEFAULT_DIRECTORY_NAME ACCESS
______________________________________________________
SYS OPATCH_XML_INV OPATCH_SCRIPT_DIR CLOB

Step 5: - no tables selected
Step 6: - no tables selected
Step 7: - no jobs at all
Step 8: - no tables selected

EXP is used as a temporarily woraround and works like a charm.

Is there something like "reinstall / repair" of the DataPump part ?
Connor McDonald

Followup  

July 30, 2020 - 12:28 am UTC

OK, can you run

select table_name
from user_tables
where table_name like '%EXP%'
order by 1

connected as SYSTEM

Result of last SQL

July 30, 2020 - 6:18 am UTC

Reviewer: Heiko Schröder from Ettlingen, Germany

TABLE_NAME
--------------------------------------------------------------------------------
AUDTAB$TBS$FOR_EXPORT_TBL
AW$EXPRESS
EXPACT$
EXPDEPACT$
EXPDEPOBJ$
EXPIMP_TTS_CT$
EXPPKGACT$
EXPPKGOBJ$
EXP_SERVICE$
FGA_LOG$FOR_EXPORT_TBL
FGR$_FILE_GROUP_EXPORT_INFO

TABLE_NAME
--------------------------------------------------------------------------------
INCEXP
KU$NOEXP_TAB
KU_NOEXP_TAB
NACL$_ACE_EXP_TBL
NACL$_HOST_EXP_TBL
NACL$_WALLET_EXP_TBL
NOEXP$


Thank you for your patience ;-)
Connor McDonald

Followup  

July 30, 2020 - 6:23 am UTC

That looks like you connected as SYS not SYSTEM

real results from SQL

July 30, 2020 - 7:00 am UTC

Reviewer: Heiko Schröder from Ettlingen, Germany

sorry - dont`SQL before the first coffee :-(

Same sql as system:

no rows selected
Connor McDonald

Followup  

July 31, 2020 - 2:39 am UTC

OK, I'm out of ideas :-)

Its time for you to talk to Support. Issues with the internal queues for DataPump sounds like some sort of corruption

More to Explore

Utilities

All of the database utilities are explained in the Utilities guide.