Datapump Errors and Solutions

   

SCOPE

The article covers common & complex error occurs during the Datapump Export and Import operations

ERROR #1  -- import

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT",line 798
ORA-31635: unable to establish job resource synchronization 

Solution :

Identify the blocking sessions, kill them. Clean up the datapump table by droping it and flush the cache

SQL> SELECT DECODE(request,0,'Holder: ','Waiter: ')||vl.sid sess, status,
     id1, id2, lmode, request, vl.type
     FROM v$LOCK vl, v$session vs
     WHERE (id1, id2, vl.type) IN
     (SELECT id1, id2, type FROM v$LOCK WHERE request>0)
     and vl.sid = vs.sid
     ORDER BY id1, request ;

SQL> 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$%';

SQL> alter system flush shared_pool ;

SQL> alter system flush buffer_cache ;

ERROR #2  -- import

ORA-39029: worker 1 with process name "DW00" prematurely terminated
ORA-31671: Worker process DW00 had an unhandled exception.
ORA-04063: package body "SYS.KUPW$WORKER" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.KUPW$WORKER"

Solution :

SQL> shutdown immediate
SQL> startup migrate
SQL> @?/rdbms/admin/catproc.sql
SQL> @?/rdbms/admin/utlrp.sql

ERROR #3 -- Export

Export Job running longer, Export status shows as 'executing' and there aren't any errors.
Wait event in v$session shows ‘wait for unread message on broadcast channel’ 
Database alert log has no unusual errors, tablespaces has sufficient space. 

Solution :

1. If you have login trigger in database, you may his such wait event.
OR
2. Check if the statistics are up to date.

SQL> select v.status, v.sid,v.serial#,io.block_changes,event
     from v$sess_io io, v$session v
     where io.sid = v.sid
     and v.saddr in (
     select saddr
     from dba_datapump_sessions) order by sid;  

STATUS          SID    SERIAL# BLOCK_CHANGES EVENT
-------- ---------- ---------- ------------- ----------------------------------------------------------------
ACTIVE           57      64809         31034 wait for unread message on broadcast channel
ACTIVE          151      57617         33373 wait for unread message on broadcast channel
ACTIVE          257      37529         28172 wait for unread message on broadcast channel
ACTIVE          350      35645         40829 wait for unread message on broadcast channel
ACTIVE          450      26735          9978 wait for unread message on broadcast channel
ACTIVE          545      24425        200127 PX Deq: Execute Reply
ACTIVE          600      49413         44215 wait for unread message on broadcast channel

7 rows selected.

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

SQL> EXEC DBMS_STATS.GATHER_SYSTEM_STATS;
SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS (null)
select count(*) from dba_objects where owner='&USER';

ERROR #4 -- EXPORT

ORA-31693: Table data object "SCOTT"."EMPLOYEE_TABLE" failed to load/unload and is being skipped due to error:
ORA-31617: unable to open dump file "/shared/mount_pt1/export_dir/SCOTT_SCHEMA_03.dmp" for write
ORA-19505: failed to identify file "/shared/mount_pt1/export_dir/SCOTT_SCHEMA_03.dmp"
ORA-27037: unable to obtain file status

Solution :

The database from where we are expoting could be a RAC configured instance and not standalone
Use parameter    CLUSTER=N  in expdp command

Do post your comments or suggestions, which are greatly appreciated.

-- Nikhil

Found this post interesting? Subscribe us  😊😉

Read more 

Datapump Scenarios

Datapump Architecture and Master Table

Datapump Frequently Asked scenarios

Datapump gets better in 11g

Get a good idea and stay with it. Dog it, and work at it until it’s done right.” 
                                                                                                               —Walt Disney