故障排查22 分钟阅读
真相大白！DataPump 导入频繁失败的幕后真凶竟然是它。。。
最近遇到一个问题，有一套 Oracle 11GR2 数据库，使用数据泵 impdp 进行大数据量导入时遇到异常问题，worker 进程反复被终止并报错 ORA-00028: your session has been killed，经过深入分析，终于找到问题原因。
2025年9月5日阅读—点赞—收藏—
database
内容阅读
在知识库中专注阅读，并随时返回相关工具与课程
前言
本章目标：掌握本章核心知识点 前置要求：完成前序章节学习 预计时长：60 分钟
最近遇到一个问题，有一套 Oracle 11GR2 数据库，使用数据泵 impdp 进行大数据量导入时遇到异常问题，worker 进程反复被终止并报错 ORA-00028: your session has been killed，经过深入分析，终于找到问题原因。
本文记录完整的分析排查过程，供后续参考。
问题现象
数据泵导入日志显示 worker 进程异常终止，具体错误如下：
1 Starting "SYS"."SYS_IMPORT_FULL_01": sys/******** AS SYSDBA directory=data_pump_dir dumpfile=lucifer_20250828171754.dmp2 logfile=lucifer_imp_20250902.log table_exists_action=replace34 ORA-39014: One or more workers have prematurely exited.5 ORA-39029: worker 1 with process name "DW00" prematurely terminated6 ORA-31671: Worker process DW00 had an unhandled exception.7 ORA-00028: your session has been killed8 ORA-06512: at "SYS.KUPD$DATA_INT", line 589 ORA-06512: at "SYS.KUPD$DATA", line 343410 ORA-06512: at "SYS.KUPW$WORKER", line 1637011 ORA-06512: at "SYS.KUPW$WORKER", line 454912 ORA-06512: at "SYS.KUPW$WORKER", line 1046413 ORA-06512: at "SYS.KUPW$WORKER", line 182414 ORA-06512: at line 2Alert 日志显示 worker 进程被异常终止：
1DW01 terminating with fatal err=28, pid=26, wid=2, job LUCIFER.SYS_IMPORT_FULL_022opidrv aborting process DW01 ospid (58023) as a result of ORA-28ORA-00028 错误通常表示会话被外部程序终止，这种情况在数据泵导入中较为罕见。在 MOS 中也未找到相关解决方案：
需要进行详细的故障排查。
问题分析
Trace 分析
参考 MOS 文档：How to Trace Data Pump Using a Logon Trigger (Doc ID 1991279.1)，创建 10046 跟踪触发器来分析数据泵的 DM 和 DW 进程：
1SQL> conn / as sysdba2SQL> CREATE OR REPLACE TRIGGER sys.set_dp_trace3AFTER LOGON ON DATABASE4DECLARE5 v_program v$session.program%TYPE;6BEGIN7 SELECT substr(program, -5, 2) INTO v_program8 FROM v$session WHERE sid = (SELECT DISTINCT sid FROM v$mystat);910 IF v_program = 'DW' or v_program= 'DM' THEN11 EXECUTE IMMEDIATE 'alter session set tracefile_identifier = '||'DPTRC';12 EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';13 EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';14 EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';15 END IF;16END;17/执行数据泵导入并启用跟踪：
1## 创建 parfile for LUCIFER 导入2cat<<-\EOF>/home/oracle/LUCIFER_IMP.par3dumpfile=LUCIFER_EXP%U.DMP4directory=DATA_PUMP_DIR5logfile=LUCIFER_IMP.log6parallel=87table_exists_action=replace8METRICS=Y9TRACE=48030010EOF1112## 执行导入13impdp lucifer/oracle parfile=/home/oracle/LUCIFER_IMP.parTrace 参数也可以根据以下值进行配置：
1Trace DM DW ORA Lines2 level trc trc trc in3 (hex) file file file trace Purpose4------- ---- ---- ---- ------ -----------------------------------------------5 10300 x x x SHDW: To trace the Shadow process (API) (expdp/impdp)6 20300 x x x KUPV: To trace Fixed table7 40300 x x x 'div' To trace Process services8 80300 x KUPM: To trace Master Control Process (MCP) (DM)9 100300 x x KUPF: To trace File Manager10 200300 x x x KUPC: To trace Queue services11 400300 x KUPW: To trace Worker process(es) (DW)12 800300 x KUPD: To trace Data Package131000300 x META: To trace Metadata Package14------- 'Bit AND'151FF0300 x x x 'all' To trace all components (full tracing)常用跟踪值：
- TRACE=1FF0300 - 全组件跟踪
- TRACE=480300 - 核心组件跟踪（推荐用于调试）
- TRACE=10300 - 仅 Shadow process 跟踪
数据泵导入运行了一段时间后还是报错，跟踪文件显示 worker 进程在 Streams AQ: enqueue blocked on low memory 等待事件上停滞：
1WAIT #140098774867664: nam='Streams AQ: enqueue blocked on low memory' ela= 3000069 p1=0 p2=0 p3=0 obj#=408355 tim=17569897680583792KUPM:20:42:48.058: ORA-39029: worker 5 with process name "DW04" prematurely terminated3KUPM:20:42:48.059: ORA-31671: Worker process DW04 had an unhandled exception.4ORA-00028: your session has been killed猜测是内存不足导致 DataPump worker 进程被杀。
系统资源检查
检查数据库内存配置：
1SQL> SELECT name, value/1024/1024 as MB FROM v$parameter2WHERE name IN ('memory_target','memory_max_target','sga_target','pga_aggregate_target'); 34NAME MB5-------------------------------------------------------------------------------- ----------6sga_target 143687memory_target 08memory_max_target 09pga_aggregate_target 4784检查内存使用情况：
1SQL> SELECT pool, name, bytes/1024/1024 as MB FROM v$sgastat2WHERE pool IS NOT NULL AND bytes > 104857603ORDER BY bytes DESC;45-- 结果显示: streams pool 64MB，free memory 63.98MB，几乎完全空闲6POOL NAME MB7------------ -------------------------- ----------8......9streams pool free memory 63.982009910......11streams pool KGH: NO ACCESS 31.99221812......检查 Streams 池状态：
1SQL> SELECT component, current_size/1024/1024 as current_mb,2 min_size/1024/1024 as min_mb,3 max_size/1024/1024 as max_mb4FROM v$memory_dynamic_components5WHERE component LIKE '%stream%' OR component LIKE '%queue%';67COMPONENT CURRENT_MB MIN_MB MAX_MB8---------------------------------------------------------------- ---------- ---------- ----------9streams pool 64 0 64通过以上信息可以发现：streams pool free memory 63.9820099 几乎全部空闲，系统资源充足，排除内存不足的可能性。
外部进程终止
考虑可能的外部终止原因：
- OOM Killer；
- 系统服务限制；
- 监控程序干预；
- 数据库内部调度任务；
资源管理限制
后来怀疑可能是资源管理器限制，检查资源限制情况：
1-- 检查资源管理器是否启用2SQL> SELECT name, value FROM v$parameter WHERE name = 'resource_manager_plan';34NAME VALUE5------------------------------ ------------------------------6resource_manager_plan78-- 检查资源限制设置9SQL> SELECT resource_name, current_utilization, max_utilization, limit_value10FROM v$resource_limit11WHERE limit_value != 'UNLIMITED';1213RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE14------------------------------ ------------------- --------------- ----------------------------------------15processes 44 63 300016sessions 65 84 452817enqueue_locks 98 127 5278018enqueue_resources 93 128 UNLIMITED19ges_procs 0 0 020ges_ress 0 0 UNLIMITED21ges_locks 0 0 UNLIMITED22ges_cache_ress 0 0 UNLIMITED23ges_reg_msgs 0 0 UNLIMITED24ges_big_msgs 0 0 UNLIMITED25ges_rsv_msgs 0 0 026gcs_resources 0 0 UNLIMITED27gcs_shadows 0 0 UNLIMITED28smartio_overhead_memory 0 0 UNLIMITED29smartio_buffer_memory 0 0 UNLIMITED30smartio_metadata_memory 0 0 UNLIMITED31smartio_sessions 0 0 UNLIMITED32dml_locks 9 38 UNLIMITED33temporary_table_locks 0 101 UNLIMITED34transactions 14 15 UNLIMITED35branches 1 1 UNLIMITED36cmtcallbk 4 13 UNLIMITED37max_rollback_segments 12 12 6553538sort_segment_locks 16 22 UNLIMITED39k2q_locks 0 0 UNLIMITED40max_shared_servers 1 1 UNLIMITED41parallel_max_servers 0 16 3600查看 profile 限制：
1SQL> select username,profile from dba_users where username='LUCIFER';23USERNAME PROFILE4------------------------------ ------------------------------5LUCIFER DEFAULT67SQL> select * from dba_profiles;89PROFILE RESOURCE_NAME RESOURCE LIMIT10------------------------------ -------------------------------- -------- ----------------------------------------11DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED12DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED13DEFAULT CPU_PER_SESSION KERNEL UNLIMITED14DEFAULT CPU_PER_CALL KERNEL UNLIMITED15DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED16DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED17DEFAULT IDLE_TIME KERNEL UNLIMITED18DEFAULT CONNECT_TIME KERNEL UNLIMITED19DEFAULT PRIVATE_SGA KERNEL UNLIMITED20DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED21DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED22DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED23DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED24DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL25DEFAULT PASSWORD_LOCK_TIME PASSWORD 126DEFAULT PASSWORD_GRACE_TIME PASSWORD 7没有任何资源限制问题。
检查数据库JOB
之前其实一直没往这方面想，一直怀疑是 Oracle 的 BUG 导致，有没有可能进程真是被什么 job 给 kill 了？
检查数据库调度任务：
1SQL> SELECT job, what, last_date, last_sec, next_date, next_sec, broken2FROM dba_jobs3WHERE what LIKE '%kill%' OR what LIKE '%terminate%';45 JOB WHAT LAST_DATE LAST_SEC NEXT_DATE NEXT_SEC B6---------- ---------------------------------------------------------------------------------------------------- ------------------- -------------------------------- ------------------- -------------------------------- -7 24 proc_killsession; 2025/09/05 13:19:20 13:19:20 4000/01/01 00:00:00 00:00:00 N89-- 检查调度器任务10SQL> SELECT job_name, job_action, enabled, state, last_start_date, next_run_date11FROM dba_scheduler_jobs12WHERE job_action LIKE '%kill%' OR job_action LIKE '%terminate%';1314no rows selected发现有一个 Job 每分钟执行一次 proc_killsession 存储过程：
- JOB 24 每分钟执行一次 (13:11:20 -> 13:12:20)；
- 最后执行时间: 2025/09/05 13:11:20；
- 作业状态: N (未 broken，正在正常运行)；
这很可能就是杀死 DataPump 进程的罪魁祸首！立即查看 proc_killsession 存储过程的代码：
1SQL> SELECT text2FROM dba_source3WHERE name = 'PROC_KILLSESSION'4ORDER BY owner, line;56TEXT7------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------8PROCEDURE proc_killsession9AS10v_sid NUMBER := 0;11v_serial NUMBER := 0;12v_sql VARCHAR2(2000);13v_record_time VARCHAR2(19) := to_char(sysdate,'yyyy-mm-dd hh24:mi:ss');14v_object_name VARCHAR2(30);15CURSOR block_session_list IS16 SELECT s1.sid, s1.serial#, ao.object_name17 FROM v$session s1, v$session s2, all_objects ao18 WHERE s1.sid = s2.final_blocking_session19 AND s2.blocking_session IS NOT NULL20 AND s2.blocking_session_status = 'VALID'21 AND s2.state = 'WAITING'22 AND s2.row_wait_obj# = ao.object_id23 AND s2.last_call_et > 60;24BEGIN25 INSERT INTO block_event26 SELECT v_record_time record_time,27 to_char(s1.logon_time, 'yyyy-mm-dd hh24:mi:ss') logon_time,28 nvl(to_char(t.start_date, 'yyyy-mm-dd hh24:mi:ss'),'~') start_time,29 s1.username||'@'||s1.machine||'('||s1.client_info||') '||s1.program||' '||s1.action30 ||' (sid='||s1.sid||' serial#='||s1.serial#||' sql_id='||s1.sql_id||' | prev_sql_id='||s1.prev_sql_id||')' blocker,31 s2.username||'@'||s2.machine||'('||s2.client_info||') '||s2.program||' '||s2.action32 ||' (sid='||s2.sid||' serial#='||s2.serial#||' sql_id='||s2.sql_id||')' blocked,33 q1.sql_text blocking_text, q2.sql_text blocked_text,34 l1.lmode lock_mode, l2.request request_mode, l1.ctime CTIME1,l2.ctime CTIME2,35 s1.event blocking_event,s2.event blocked_event,ao.owner||'.'||ao.object_name object_name,36 DBMS_ROWID.rowid_create(1, ao.data_object_id, s2.row_wait_file#, s2.row_wait_block#, s2.row_wait_row#) BLOCKED_ROWID,37 s2.final_blocking_session final_blocking_session38 FROM v$lock l1, v$session s1, v$lock l2, v$session s2, v$locked_object lo, all_objects ao, v$transaction T, v$sql q1, v$sql q239 WHERE s1.sid=l1.sid AND s2.sid=l2.sid40 AND s2.last_call_et > 6041 AND l1.block=1 AND l2.request>042 AND l1.id1=l2.id1 AND l1.id2=l2.id243 AND s1.sid=lo.session_id44 AND lo.object_id=ao.object_id45 AND hextoraw(s1.saddr) = hextoraw(t.ses_addr(+))46 AND q2.sql_id = s2.sql_id47-- AND ao.owner='LUCIFER'48 AND q1.sql_id IN (s1.sql_id,s1.prev_sql_id);49 COMMIT;50 OPEN block_session_list;51 FETCH block_session_list INTO v_sid,v_serial,v_object_name;52 LOOP53 EXIT WHEN block_session_list%notfound;54 v_sql := 'insert into block_sql select :v_record_time,sql_id,hash_value,sql_fulltext,first_load_time,last_load_time,last_active_time from v$sql55 where upper(sql_text) like ''% '||v_object_name||' %'' AND command_type=6 AND last_active_time > sysdate-10/1440';56 EXECUTE IMMEDIATE v_sql using v_record_time;57 v_sql := 'alter system kill session '''||v_sid||','||v_serial||'''';58 --DBMS_OUTPUT.PUT_LINE(v_sql);59 EXECUTE IMMEDIATE v_sql;60 FETCH block_session_list INTO v_sid,v_serial,v_object_name;61 END LOOP;62 CLOSE block_session_list;63 COMMIT;64 EXCEPTION65 WHEN OTHERS THEN DBMS_OUTPUT.put_line('Error NO:'||SQLCODE);66END proc_killsession;分析了一下这个存储过程的主要功能：
- 监控阻塞会话：查找造成阻塞的会话（final_blocking_session）
- 时间条件：s2.last_call_et > 60 - 阻塞超过 60 秒的会话
- 记录详情：将阻塞信息插入 block_event 表
- 自动终止：
alter system kill session- 自动杀死阻塞会话
该存储过程自动识别并终止阻塞时间超过 60 秒的会话。
验证分析结果：
1-- 检查block_event表中的记录，看是否有Data Pump相关的记录2SQL> SELECT * FROM block_event3WHERE record_time >= '2025-09-05 06:30:00'4 AND record_time <= '2025-09-05 06:40:00'5 AND (blocker LIKE '%DW%' OR blocked LIKE '%DW%');67RECORD_TIME LOGON_TIME START_TIME BLOCKER BLOCKED BLOCKING_TEXT BLOCKED_TEXT LOCK_MODE REQUEST_MODE CTIME1 CTIME2 BLOCKING_EVENT BLOCKED_EVENT OBJECT_NAME BLOCKED_ROWID FINAL_BLOCKING_SESSION8------------------- ------------------- ------------------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------- ------------ ---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ------------------------------------------------------------- ------------------ ----------------------92025-09-05 06:33:58 2025-09-04 21:36:02 2025-09-05 06:28:39 IIH@dbtest() oracle@dbtest (DW IIH@dbtest() oracle@dbtest (J0 BEGIN SYS.KUPW$WORKER.MAIN('SYS_IMPORT_FULL_05', 'IIH', 4719360); END; DELETE FROM BL_CG_IP WHERE DT_SRV > TO_CHAR(SYSDATE,'YYYY-MM-DD') AND ID_SRV = '1001L51000000136P7RS 6 3 319 120 Datapump dump file I/O enq: TM - contention IIH.BL_CG_IP AABmyqAAAAAAAAAAAA 310 00) SYS_IMPORT_FULL_05 (sid=3 02) (sid=2832 serial#=1109 sq '11 serial#=1 sql_id=aa1j28hu26qat l_id=4uwxvrr9q4af3)12 | prev_sql_id=1at2aksnpg563)1314......15......1617-- 检查是否有IIH相关的对象被阻塞18SQL> SELECT * FROM block_event19WHERE record_time >= '2025-09-05 06:30:00'20 AND object_name LIKE 'IIH.%';2122RECORD_TIME LOGON_TIME START_TIME BLOCKER BLOCKED BLOCKING_TEXT BLOCKED_TEXT LOCK_MODE REQUEST_MODE CTIME1 CTIME2 BLOCKING_EVENT BLOCKED_EVENT OBJECT_NAME BLOCKED_ROWID FINAL_BLOCKING_SESSION23------------------- ------------------- ------------------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------- ------------ ---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ------------------------------------------------------------- ------------------ ----------------------242025-09-05 06:33:58 2025-09-04 21:36:02 2025-09-05 06:28:39 IIH@dbtest() oracle@dbtest (DW IIH@dbtest() oracle@dbtest (J0 BEGIN SYS.KUPW$WORKER.MAIN('SYS_IMPORT_FULL_05', 'IIH', 4719360); END; DELETE FROM BL_CG_IP WHERE DT_SRV > TO_CHAR(SYSDATE,'YYYY-MM-DD') AND ID_SRV = '1001L51000000136P7RS 6 3 319 120 Datapump dump file I/O enq: TM - contention IIH.BL_CG_IP AABmyqAAAAAAAAAAAA 325 00) SYS_IMPORT_FULL_05 (sid=3 02) (sid=2832 serial#=1109 sq '26 serial#=1 sql_id=aa1j28hu26qat l_id=4uwxvrr9q4af3)27 | prev_sql_id=1at2aksnpg563)2829......30......记录显示：DataPump worker 进程在导入 LUCIFER.Bx_xx_xP 表时被标记为阻塞会话，随后被 proc_killsession 自动终止。
到这里为止，问题总算弄清楚了：
- DataPump worker 进程在导入过程中持有表级锁；
- 其他会话(Job 进程)尝试访问相同对象被阻塞超过 60 秒；
- proc_killsession 将 DataPump 进程误识别为恶意阻塞会话并自动终止；
- 导致
ORA-00028: your session has been killed错误；
不是系统资源问题，不是内存不足，而是自动阻塞会话清理 Job 程序！这也太坑了！
解决方案
临时解决方案
禁用 Job 运行：
1SQL> BEGIN2 DBMS_JOB.BROKEN(24, TRUE);3END;4/重新执行数据泵导入：
1impdp 'userid="/ as sysdba"' parfile=/home/oracle/LUCIFER_IMP.par等待一段时间后，数据成功导入：
导入成功完成后重新启用 Job，并且删除 10046 触发器：
1SQL> BEGIN2 DBMS_JOB.BROKEN(24, FALSE);3END;4/56SQL> DROP TRIGGER SYS.SET_DP_TRACE;永久解决方案
优化 proc_killsession 存储过程，排除 DataPump 进程：
1-- 在游标定义中添加排除条件2CURSOR block_session_list IS3 SELECT s1.sid, s1.serial#, ao.object_name4 FROM v$session s1, v$session s2, all_objects ao5 WHERE s1.sid = s2.final_blocking_session6 AND s2.blocking_session IS NOT NULL7 AND s2.blocking_session_status = 'VALID'8 AND s2.state = 'WAITING'9 AND s2.row_wait_obj# = ao.object_id10 AND s2.last_call_et > 6011 -- 排除DataPump相关进程12 AND s1.program NOT LIKE '%DW%'13 AND s1.program NOT LIKE '%MCP%'14 AND s1.module NOT LIKE 'Data Pump%';15-- 其余逻辑保持不变此案例充分说明了数据库故障排查中"现象与根因分离"的典型特征。表面上看是 DataPump 进程异常终止的技术问题，实质上是人为管理策略设计不当导致的运维事故。
总结
在数据库运维的道路上，技术问题往往有标准的解决方案，但人为因素导致的问题却更加复杂和隐蔽。这个案例提醒我们，在追求自动化和效率的同时，必须始终保持对系 统安全性和稳定性的敬畏之心。
正如那句老话："工具是把双刃剑"。我们创造的每一个自动化脚本，都可能在某个不经意的时刻，以我们意想不到的方式影响系统运行。因此，谨慎设计、充分测试、持续 监控，应该成为每一位数据库管理员的基本准则。
希望这个案例能够为大家提供有价值的参考，让我们在数据库运维的路上少走弯路，多一些从容与自信。