ORA-39097 ORA-39065 ORA-12805 During Export (expdp)

There are couple of reasons you may be facing below errors during export of database/schema/tables using datapump (expdp).

ORA-39097: Data Pump job encountered unexpected error -12805
ORA-39065: unexpected master process exception in DISPATCH
ORA-12805: parallel query server died unexpectedly

One of a common problem in RAC environment occurs when using a non-shared location and using parallelism. In this case you should simply use cluster=n parameter in your export command which will run export only on node local to expdp command.

However, I experienced this error even after parameters were correctly set and the job started failing all of sudden.

Above errors were accompanied with below messages in alertlog as well.

IPC Send timeout detected. Receiver ospid 22767 [oracle@********* (PPA6)]
Errors in file /u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL1_ppa6_22767.trc:

IPC Send timeout detected. Sender: ospid 43074 [oracle@********* (DM00)]
Receiver: inst 2 binc 2 ospid 99228

Upon investigation I observed below two sessions from SYS user. Since I was running export using SYS user, I filtered this with user SYS

Select a.inst_id, a.sid, a.serial#, a.username, substr(a.program,1,10) program, a.status, a.logon_time, substr(a.event,1,25) event, substr(machine,1,25) machine
from gv$session a
where a.username='SYS'
and a.sid <> (select sys_context('USERENV','SID') from dual)
order by a.logon_time;

  SID SERIAL# INST USER PROGRAM    STATUS   LOGON_TIME         SQL_TEXT                                 EVENT
----- ------- ---- ---- ---------- -------- ------------------ ---------------------------------------- -----------------
 1489   22849    1 SYS  ude@****** ACTIVE   21-APR-19 08:05:27 BEGIN          SYS.DBMS_DATAPUMP.GET_STA PL/SQL lock timer
 1489   22849    1 SYS  ude@****** ACTIVE   21-APR-19 08:05:27 SELECT operation, master_id, state, flag PL/SQL lock timer

For some reason (probable bug or hanged process) above processes caused failure in expdp with ORA-39065 and ORA-12805. The session was killed and export was able to complete without errors.

SQL> Alter system kill session '1489,22849' immediate;
System altered.

By the time I wrote this blog I had not seen a similar documented issues on Oracle support, hope this helps someone.

Thanks!
Anurag