前言
本文主要介绍了关于expdp任务异常处理的相关内容,分享出来供大家参考学习,下面话不多说了,来一起看看详细的介绍吧
环境:AIX 6.1 + Oracle 10.2.0.4
现象:在XTTS迁移测试阶段,遇到执行几个expdp的导出任务,迟迟没有返回任何信息,对应日志无任何输出,查看任务状态:
SQL>
set lines 300
col OWNER_NAME for a10
col OPERATION for a15
col JOB_MODE for a20
col STATE for a15
select * from dba_datapump_jobs;
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
---------- ------------------------------ --------------- -------------------- --------------- ---------- ----------------- -----------------
SYS SYS_EXPORT_TRANSPORTABLE_01 EXPORT TRANSPORTABLE DEFINING 1 0 1
SYS SYS_EXPORT_TRANSPORTABLE_02 EXPORT TRANSPORTABLE DEFINING 1 1 2
SYS SYS_EXPORT_TRANSPORTABLE_03 EXPORT TRANSPORTABLE DEFINING 1 1 2
SYS SYS_EXPORT_SCHEMA_01 EXPORT SCHEMA DEFINING 1 1 2
SYS SYS_EXPORT_TRANSPORTABLE_04 EXPORT TRANSPORTABLE DEFINING 1 1 2
SYS SYS_EXPORT_SCHEMA_02 EXPORT SCHEMA DEFINING 1 1 2
6 rows selected.
可以看到所有的expdp导出任务的STATE都停留在DEFINING状态。
1.牛刀小试清异常
先强制杀掉后台执行的所有expdp任务:
ps -ef|grep expdp|grep -v grep|awk '{print $2}'|xargs kill -9
然后尝试删除这些表(其实应该在not running状态下删除)
select 'drop table '||OWNER_NAME||'.'||JOB_NAME||' purge;' from dba_datapump_jobs where STATE='NOT RUNNING';
drop table sys.SYS_EXPORT_TRANSPORTABLE_01 purge;
..
可这样是没有作用的,查询结果不变。
甚至尝试正常shutdown immediate停止数据库,也无法成功,告警日志看到有活动调用:
Thu Nov 1 15:14:24 2018
Active call for process 4522064 user 'oracle' program 'oracle@localhost (DM00)'