ORA-06512: at “SYS.KUPV$FT” – impdp failing

This time, impdp failing to create a job for import and failing with following error:-

ORA-06512: at "SYS.KUPV$FT",

In many cases you sometimes stop data pump job or in case of an abnormal end of the Data Pump job (we call it the orphaned job) or using undocumented parameter KEEP_MASTER=Y, the master table remain in the database.

So lets start digging out for orphaned jobs

Script to check Orphaned Pump Jobs

SET lines 140
COL owner_name FORMAT a10;
COL job_name FORMAT a20
COL state FORMAT a12
COL operation LIKE owner_name
COL job_mode LIKE owner_name
SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs;

By running above script if any rows returned in “not running “ mode that means you will be having orphaned jobs. Typically orphaned jobs state will be “Not Running” state and but in my case some are even in Executing mode which they shouldn’t

As I got the following jobs running at my nose.

DBABATCH SYS_EXPORT_FULL_22 Executing

DBABATCH SYS_EXPORT_FULL_22 Not Running
DBABATCH SYS_EXPORT_FULL_22 Not Running
DBABATCH SYS_EXPORT_FULL_22 Not Running
DBABATCH SYS_EXPORT_FULL_22 Not Running
DBABATCH SYS_EXPORT_FULL_22 Not Running

Verify that any data pump sessions are running in the database

SQL> select OWNER_NAME,JOB_NAME,SESSION_TYPE from dba_datapump_sessions;
OWNER_NAME JOB_NAME SESSION_TYPE
------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ ------------------------------------------
DBABATCH SYS_EXPORT_FULL_22 DBMS_DATAPUMP
DBABATCH SYS_EXPORT_FULL_22 MASTER
DBABATCH SYS_EXPORT_FULL_22 WORKER
DBABATCH SYS_EXPORT_FULL_22 WORKER
DBABATCH SYS_EXPORT_FULL_22 WORKER
DBABATCH SYS_EXPORT_FULL_22 WORKER

Now i got confirmed that some jobs running (whoa!!! am doing a import where there is full export running in database, not at all possible, when logically thinking)

Lets clean up this mess (orphaned jobs)

Fire the below Dynamic script

SQL> SELECT 'drop table ULTIMUS.'||job_name||';' FROM dba_datapump_jobs t;

This may give you result something like this

drop table DBABATCH.SYS_EXPORT_FULL_22;

Once you dropped the table also ensure drop from Recycle bin as well; (My bad i got lot of)

PURGE table DBABATCH.SYS_EXPORT_FULL_22;
PURGE table DBABATCH.BIN$ymzgo9ZcHjXgRAAUTyu65A==$0;
PURGE table DBABATCH.BIN$ymzgo9Z/HjXgRAAUTyu65A==$0;
PURGE table DBABATCH.BIN$ymzgo9atHjXgRAAUTyu65A==$0;
PURGE table DBABATCH.BIN$ymzgo9Z1HjXgRAAUTyu65A==$0;
PURGE table DBABATCH.BIN$ymzgo9ajHjXgRAAUTyu65A==$0;
PURGE table DBABATCH.BIN$ymzgo9ayHjXgRAAUTyu65A==$0;
PURGE table DBABATCH.BIN$ymzgo9ZXHjXgRAAUTyu65A==$0;
PURGE table DBABATCH.BIN$ymzgo9ZSHjXgRAAUTyu65A==$0;
PURGE table DBABATCH.BIN$ymzgo9ZNHjXgRAAUTyu65A==$0;
PURGE table DBABATCH.BIN$ymzgo9aoHjXgRAAUTyu65A==$0;
PURGE table DBABATCH.BIN$ymzgo9ZhHjXgRAAUTyu65A==$0;
PURGE table DBABATCH.BIN$ymzgo9Z6HjXgRAAUTyu65A==$0;
PURGE table DBABATCH.BIN$ymzgo9aUHjXgRAAUTyu65A==$0;
PURGE table DBABATCH.SYS_EXPORT_SCHEMA_01;
PURGE table DBABATCH.BIN$ymzgo9aJHjXgRAAUTyu65A==$0;
PURGE table DBABATCH.BIN$ymzgo9aeHjXgRAAUTyu65A==$0;
PURGE table DBABATCH.BIN$ymzgo9ZrHjXgRAAUTyu65A==$0;
PURGE table DBABATCH.BIN$ymzgo9aZHjXgRAAUTyu65A==$0;
PURGE table DBABATCH.BIN$ymzgo9aEHjXgRAAUTyu65A==$0;
PURGE table DBABATCH.BIN$ymzgo9ZwHjXgRAAUTyu65A==$0;
PURGE table DBABATCH.BIN$ymzgo9ZmHjXgRAAUTyu65A==$0;
PURGE table DBABATCH.BIN$ymzgo9aOHjXgRAAUTyu65A==$0;

Some times you will get like , no table exists (it happens to me for one master table)

ORA-31626: job does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.KUPV$FT", line 438
ORA-31638: cannot attach to job SYS_EXPORT_FULL_22 for user DBABATCH
ORA-31632: master table "DBABATCH.SYS_EXPORT_FULL_22" not found, invalid, or inaccessible
ORA-00942: table or view does not exist

Check that any process at OS side, I have got few, killed those

/export/home/oracle#> ps -eaf | grep dw
oracle 29147 1 0 Aug 05 ? 21:11 ora_dw02_ecmp1
oracle 23896 23808 0 01:10:24 pts/3 0:00 grep dw
oracle 29152 1 0 Aug 05 ? 21:42 ora_dw04_ecmp1
oracle 29149 1 0 Aug 05 ? 21:15 ora_dw03_ecmp1
oracle 28765 1 0 Aug 05 ? 21:40 ora_dw01_ecmp1

And reran the import, this time it sailed towards end.

Happy reading!!!!