expdp导出卡着不动的解决案例

现象描述，在执行expdp导出时，出现如下信息后，就一直卡着不动，不报错也不中断，一直卡。
Export: Release 10.2.0.5.0 - 64bit Production on Friday, 06 May, 2016 12:49:29

 

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

 

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

一直卡在此处，不报错也不中断。

因为错误现象中并没有出现异常错误，告警日志及其他集群日志中也未查看到异常信息，就是一直卡死不动，长时间也不会自动中断，所以怀疑是dump进程夯死导致的。

在sql环境下查看dump工作执行情况：

SQL> select * from dba_datapump_jobs;

 

OWNER_NAME        JOB_NAME

------------------------------ ------------------------------

OPERATION

------------------------------------------------------------

JOB_MODE

------------------------------------------------------------

STATE    DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS

------------------------------ ---------- ----------------- -----------------

GSAIC_PUB        SYS_EXPORT_SCHEMA_02

EXPORT

SCHEMA

NOT RUNNING 0   0     0

 

 

OWNER_NAME        JOB_NAME

------------------------------ ------------------------------

OPERATION

------------------------------------------------------------

JOB_MODE

------------------------------------------------------------

STATE    DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS

------------------------------ ---------- ----------------- -----------------

AN_REP        SYS_EXPORT_SCHEMA_01

EXPORT

SCHEMA

NOT RUNNING 0   0     0

 

 

OWNER_NAME        JOB_NAME

------------------------------ ------------------------------

OPERATION

------------------------------------------------------------

JOB_MODE

------------------------------------------------------------

STATE    DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS

------------------------------ ---------- ----------------- -----------------

GSAIC_PUB        SYS_EXPORT_SCHEMA_01

EXPORT

SCHEMA

NOT RUNNING 0   0     0

可以看到有一些没有运行的job，但是资源还是没有释放。在oracle用户下尝试通过以下方式结束任务：

[oracle@gsdb1 ~]$ expdp xxx/xxx  attach=SYS_EXPORT_SCHEMA_02

 

Export: Release 10.2.0.5.0 - 64bit Production on Friday, 06 May, 2016 12:55:41

 

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

 

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

ORA-39002: invalid operation

ORA-39000: bad dump file specification

ORA-31640: unable to open dump file "/gsnfs/gsdbbackup/gsaic_pub_160302.dmp" for read

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

 

可以看到这些任务都已经成了僵尸任务，所以直接在数据库中通过以下方式释放：

SQL> drop table GSAIC_PUB.SYS_EXPORT_SCHEMA_02 purge;

 

Table dropped.

SQL> drop table GSAIC_PUB.SYS_EXPORT_SCHEMA_01 purge;

 

Table dropped.

 

SQL> drop table AN_REP.SYS_EXPORT_SCHEMA_01 purge;

 

Table dropped.

释放之后，可以看见卡着不动的expdp任务立马就开始向下继续执行，因为卡着的会话窗口一直没有关闭，所以能第一时间看见。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31403259/viewspace-2140788/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31403259/viewspace-2140788/