Thursday, May 19, 2011

IMPDP Stuck with wait event - "wait for unread message on broadcast channel"

I was trying to run data import using impdp and the job was stuck with wait event "wait for unread message on broadcast channel".
This is an idle wait event as per oracle, that means oracle is waiting for something to happen for the session to proceed.

I googled and checked metalink but couldn't really find a solution and my job was stuck indefinitely.
From one of the forums online I figured out an old defunct job in the DB can cause new impdp sessions to be stuck.

Hence I queried dba_datapump_jobs and found out that I have an old job in "NOT RUNNING" state:

SQL> select * from dba_datapump_jobs;

OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
------------ ------------------------------ ------------ ------------ ------------------- ------------ ----------------- -----------------
SYSTEM NEW1 IMPORT TABLE EXECUTING 4 1 3
SYSTEM SYS_IMPORT_FULL_01 IMPORT FULL NOT RUNNING 0 0 0

Checked to find the underlying object:

SQL> SELECT a.object_id, a.object_type, a.owner, a.object_name, a.status
FROM dba_objects a, dba_datapump_jobs j
WHERE a.owner=j.owner_name AND a.object_name=j.job_name
and j.job_name='SYS_IMPORT_FULL_01';

OBJECT_ID OBJECT_TYPE OWNER OBJECT_NAME STATUS
------------ ------------------- -------------------- ---------------------- -------
984119 TABLE SYSTEM SYS_IMPORT_FULL_01 VALID

15:02:47 SQL> select owner,object_name,subobject_name, object_type,last_ddl_time from dba_objects where object_id=984119
/

OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE LAST_DDL_TIME
-------------------- ---------------------------------------- ------------------------------ ------------------- -------------------
SYSTEM SYS_IMPORT_FULL_01 TABLE 13-08-2010 19:59:50

Dropped the datapump job table:

15:02:59 SQL> drop table SYSTEM.SYS_IMPORT_FULL_01;

Table dropped.

And then my impdp job "NEW1" started progressing:

15:04:03 SQL> select * from dba_datapump_jobs;

OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
------------ ------------------------------ ------------ ------------ ------------------- ------------ ----------------- -----------------
SYSTEM NEW1 IMPORT TABLE EXECUTING 4 1 6

NOTE: Please make sure that the old job is NOT RUNNING.
Secondly, if its a known job you can also try to:
impdp username/password attach=JOB_NAME
and then at the IMPDP prompt do a kill_job. This should also clear the jobs status from dba_datapump_jobs.