Oracle 数据泵异常JOB状态DEFINING处理

Oracle 10.2.0.5
数据泵IMPDP导入时hang住:

前台输出以及日志输出:

Import: Release 10.2.0.5.0 - 64bit Production on Thursday, 14 January, 2021 14:02:50

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, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_03" successfully loaded/unloaded

查看数据泵JOB状态:

SQL> select job_name,state from dba_datapump_jobs;

JOB_NAME                       STATE
------------------------------ ------------------------------
SYS_IMPORT_SCHEMA_03           DEFINING
SYS_IMPORT_FULL_01             NOT RUNNING
SYS_EXPORT_SCHEMA_02           NOT RUNNING
SYS_EXPORT_SCHEMA_01           NOT RUNNING
SYS_IMPORT_SCHEMA_01           NOT RUNNING
SYS_IMPORT_SCHEMA_02           NOT RUNNING

尝试停止该JOB SYS_IMPORT_SCHEMA_03

$ expdp \'/ as sysdba\'  attach=SYS_IMPORT_SCHEMA_03
Export: Release 10.2.0.5.0 - 64bit Production on Thursday, 14 January, 2021 15:26:33
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, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.KUPV$FT", line 435
ORA-31638: cannot attach to job SYS_IMPORT_SCHEMA_03 for user SYS
ORA-31632: master table "SYS.SYS_IMPORT_SCHEMA_03" not found, invalid, or inaccessible
ORA-00942: table or view does not exist
无法停止.

根据MOS1626201.1提供SQL查询JOB master主表:

SQL> SELECT o.status, o.object_id, o.object_type,
  2  o.owner||'.'||object_name "OWNER.OBJECT"
  3  FROM dba_objects o, dba_datapump_jobs j
  4  WHERE o.owner=j.owner_name AND o.object_name=j.job_name
  5  AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;
STATUS   OBJECT_ID OBJECT_TYPE OWNER.OBJECT
---------------------------------------------------------------------------
VALID       617965 TABLE  HB.SYS_EXPORT_SCHEMA_01
VALID       617968 TABLE  HB.SYS_EXPORT_SCHEMA_02
VALID        91021 TABLE  SYSTEM.SYS_IMPORT_FULL_01
VALID        86818 TABLE  SYSTEM.SYS_IMPORT_SCHEMA_01
VALID       395030 TABLE  SYSTEM.SYS_IMPORT_SCHEMA_02
VALID       633461 TABLE  SYSTEM.SYS_IMPORT_SCHEMA_03

干掉这个主表:

SQL> drop table SYSTEM.SYS_IMPORT_SCHEMA_03 purge;
drop table SYSTEM.SYS_IMPORT_SCHEMA_03 purge
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

此时表可能被锁住或占用.查询会话占用该job情况:

SQL> select JOB_NAME,ATTACHED_SESSIONS,DATAPUMP_SESSIONS from dba_datapump_jobs;
JOB_NAME                       ATTACHED_SESSIONS DATAPUMP_SESSIONS
------------------------------ ----------------- -----------------
SYS_IMPORT_SCHEMA_03                           0                 2
SYS_IMPORT_FULL_01                             0                 0
SYS_EXPORT_SCHEMA_02                           0                 0
SYS_EXPORT_SCHEMA_01                           0                 0
SYS_IMPORT_SCHEMA_01                           0                 0
SYS_IMPORT_SCHEMA_02                           0                 0

通过vlock以及vlocked_object判断状态表是否有锁.

SQL> select object_id,session_id,locked_mode from v$locked_object where object_id=633461;
 OBJECT_ID SESSION_ID LOCKED_MODE
---------- ---------- -----------
    633461       1575           3

定位会话:

SQL> select sid,serial# from v$session where sid=1575;
       SID    SERIAL#
---------- ----------
      1575      42458

杀会话:

SQL> alter system kill session '1575,42458,@1' immediate;
alter system kill session '1575,42458,@1' immediate
*
ERROR at line 1:
ORA-00026: missing or invalid session ID
//锁资源未释放

操作系统层面杀锁:

SQL> select a.spid,b.sid,b.serial#,b.username 
from v$process a,v$session b where a.addr=b.paddr and b.status='KILLED';

SPID                SID    SERIAL# USERNAME
------------ ---------- ---------- ------------------------------
28508560           1575      42458 SYSTEM

资源成功释放:

SQL> select a.spid,b.sid,b.serial#,b.username 
from v$process a,v$session b 
where a.addr=b.paddr and b.status='KILLED';
no rows selected

SQL> select sid,serial# from v$session where sid=1575;
no rows selected

SQL> select object_id,session_id,locked_mode from v$locked_object where object_id=633461;
no rows selected

再次查询该JOB状态:

SQL> select job_name,state,owner_name from dba_datapump_jobs;
JOB_NAME                       STATE                          OWNER_NAME
------------------------------ ------------------------------ ------------------------------
SYS_IMPORT_FULL_01             NOT RUNNING                    SYSTEM
SYS_EXPORT_SCHEMA_02           NOT RUNNING                    HB
SYS_IMPORT_SCHEMA_03           NOT RUNNING                    SYSTEM
SYS_EXPORT_SCHEMA_01           NOT RUNNING                    HB
SYS_IMPORT_SCHEMA_01           NOT RUNNING                    SYSTEM
SYS_IMPORT_SCHEMA_02           NOT RUNNING                    SYSTEM
//变为NOT RUNNING

再次执行导入成功!