数据泵impdp导入卡住，等待事件wait for unread message on broadcast channel
Tags： impdpOraclewait for unread message on broadcast channel卡住等待事件
现象
Oracle 11.2.0.4 impdp导入数据，卡了很久不动，也不报错。
先说明我的问题吧：
我的环境是因为导入中包含了一个大的表，且该表包含CLOB字段，而CLOB占用了40G的空间，又因为是11.2.0.4的环境对CLOB导出导入有bug，所以特别慢。具体可以参考：https://www.xmmup.com/oracle-11-2-0-4-expdpdaochuhanclobziduanbasicfiledebiaochaojimandewenti.html
原因
一般造成该问题的原因有两种：出现可能是假死或者导入对象为大对象CLOB，造成表象为卡顿的现象。
判断是否彻底卡住了，可以通过如下几种办法：
1、查询表空间大小是否有变化，参考：https://www.xmmup.com/oracle-11gchaxunbiaokongjiandaxiao.html
2、通过ATTACH，进去后输入status命令进行查看：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | impdp \'/ AS SYSDBA\' ATTACH=SYS_IMPORT_FULL_01 -- 例如 Worker 1 Status: Process Name: DW00 State: EXECUTING Object Schema: ABCLOGY Object Name: WORKFLOW_ABCLOG Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA Completed Objects: 1 Completed Rows: 22,368,372 Completed Bytes: 12,104,771,848 Percent Done: 69 Worker Parallelism: 1 |
3、查询v$session_longops视图获取进度：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | SET LINE 9999 PAGESIZE 9999 col username format a10 col OSUSER format a10 col target format a20 col opname format a30 col WAIT_CLASS format a20 col wait_event format a30 col message format a80 col SQL_TEXT format a100 col sofar_TOTALWORK format a20 col session_info format a15 col progress format a8 SELECT a.USERNAME, (SELECT upper(nb.OSUSER) FROM v$session nb WHERE nb.SID = a.sid) OSUSER, (SELECT nb.sid || ',' || nb.SERIAL# || ',' || pr.SPID FROM v$process pr, v$session nb WHERE nb.PADDR = pr.ADDR and nb.sid = a.SID and nb.SERIAL# = a.SERIAL#) session_info, a.opname, to_char(a.START_TIME, 'YYYY-MM-DD HH24:MI:SS') start_time, round(a.SOFAR * 100 / a.TOTALWORK, 2) || '%' AS progress, a.TIME_REMAINING TIME_REMAINING, a.elapsed_seconds elapsed_seconds, message message, (SELECT nb.EVENT FROM V$session_Wait nb WHERE nb.SID = a.SID) wait_event, (SELECT nb.STATUS FROM v$session nb WHERE nb.SID = a.SID) STATUS FROM v$session_longops a WHERE a.time_remaining <> 0 ORDER BY status, a.TIME_REMAINING DESC, a.SQL_ID, a.sid; |
解决
WAITEVENT: "wait for unread message on broadcast channel" Reference Note (文档 ID 170464.1)
The Oracle process is waiting for a message on a broadcast channel. This is normally an idle wait - the process is waiting for an AQ message on a subscribed queue.
This event is classed as an "idle" wait so should be ignored when looking at systemwide timings. See <<61998.1>> for more information about "IDLE" waits.
该等待事件是由于oracle进程在请求message时出现等待，该等待是空闲(idle)等待，并且可以忽略
总结
1、首先需要弄懂是不是真的卡住了，还是导入非常慢。若是11.2.0.4的版本，表含有大的CLOB字段，则做导出操作会非常慢，这是一个bug，参考：https://www.xmmup.com/ruhetigaoshujubengdecaozuoxingneng.html#11204han_you_da_declob_zi_duan
2、别忘记检查告警日志，查看是否某个表空间满了或是闪回恢复区满了等问题。
