MANAGE TERMINATED OR LOST DATAPUMP IMPDP JOB
Error from import log:
...
ORA-39029: worker 8 with process name "DW01" prematurely terminated
ORA-31671: Worker process DW01 had an unhandled exception.
ORA-01114: IO error writing block to file (block # )
ORA-01114: IO error writing block to file 1039 (block # 896640)
ORA-15055: unable to connect to ASM instance
ORA-15055: unable to connect to ASM instance
ORA-00603: ORACLE server session terminated by fatal error
ORA-27504: IPC error creating OSD context
ORA-27300: OS system dependent operation:sendmsg failed with status: 105
ORA-27301: OS failure message: No buffer space available
ORA-27302: failure occurred at: sskgxpsnd2
ORA-03135: connection lost contact
UDI-31626: operation generated ORACLE error 31626
ORA-31626: job does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4756
ORA-39076: cannot delete job SYS_IMPORT_FULL_01 for user sys
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_DATAPUMP", line 2114
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4683
ORA-06512: at "SYS.DBMS_DATAPUMP", line 6137
ORA-06512: at line 1
-- when you attach the job, you can see below status also.
Resuming the impdp process:
$ impdp sys/********@DEMODB attach=SYS_IMPORT_FULL_01
...
State: UNDEFINED
...
Import>
-- find your job details from database
SELECT owner_name, job_name, operation, job_mode, state
FROM dba_datapump_jobs
where JOB_NAME='SYS_IMPORT_FULL_01';
output:
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
---------- ------------------------------ ------------ --------------- -------------------- ---------- ----------------- -----------------
sys SYS_IMPORT_FULL_01 IMPORT FULL NOT RUNNING 0 0 ##########
Solution/ Fix:
Restart the job using CONTINUE_CLIENT after attachinig the job. It ll restart the job where it was failed.
e.g.,
Import> CONTINUE_CLIENT
Job SYS_IMPORT_FULL_01 has been reopened at Sun Apr 14 22:59:09 2024
Restarting "sys"."SYS_IMPORT_FULL_01": sys/********@DEMODB parfile=impdp_HCM_DEV.par
-- Verify the status of the job
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE
------------ ------------------------------ ---------------------- ---------------------- ----------------------
sys SYS_IMPORT_FULL_01 IMPORT FULL EXECUTING
-- from alert log it is confirmed, job restarted again.
2024-04-14T22:59:10.761862-05:00
Thread 1 advanced to log sequence 134694 (LGWR switch), current SCN: 21163224110
Current log# 2 seq# 134694 mem# 0: +RECO/DEMODB/ONLINELOG/group_2.337.1053949991
2024-04-14T22:59:37.902800-05:00
DEMODB(3):
DEMODB(3):DW00 started with pid=116, OS id=15549, wid=1, job sys.SYS_IMPORT_FULL_01
2024-04-14T22:59:40.229363-05:00
DEMODB(3):
DEMODB(3):DW01 started with pid=117, OS id=15580, wid=2, job sys.SYS_IMPORT_FULL_01
2024-04-14T22:59:40.432641-05:00
DEMODB(3):
DEMODB(3):DW02 started with pid=123, OS id=15593, wid=3, job sys.SYS_IMPORT_FULL_01
