About Me

My photo
Bangalore, India
I am an Oracle Certified Professional working in SAP Labs. Everything written on my blog has been tested on my local environment, Please test before implementing or running in production. You can contact me at amit.rath0708@gmail.com.

Saturday, June 29, 2013

How to kill, cancel, restart, stop data pump jobs

Datapump jobs (expdp, impdp) can be stopped, killed or resumed from the database level. Killing or stopping a datapump job from Oslevel does not kill or stop it, we have to do it from database . PFB :-

 Exporting a Full database backup :-

C:\Users\NewAdmin>expdp system/orcl directory=datapump dumpfile=expdp_full_29062013.dmp logfile=expdp_full_29062013.log compression=all full=y &

 Export: Release 11.2.0.1.0 - Production on Sat Jun 29 19:46:41 2013

 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/******** directory=datapump dumpfile=expdp_full_29062013.dmp logfile=expdp_full_29062013.log compression=all full=y
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

Stop a datapump job :-

Datapump jobs can be stopped in two ways, either press CTRL + C you will get EXPDP prompt or attach to a already running job and after that write STOP_JOB

If datapump job is not running in background then once you press CTRL+C you will get EXPDP prompt . PFB :-

Export> STOP_JOB
Are you sure you wish to stop this job ([yes]/no): y

Datapump job is stopped.

if datapump is running in background then you have to attach a datapump job, find out the datapump job name by using below query :-

SQL> SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs;
OWNER_NAME                     JOB_NAME                       OPERATION                           JOB_MODE                  STATE
------------------------------ ------------------------------ ----------------------------------- ------------------------- -----------------------------
SYSTEM                         SYS_EXPORT_FULL_02             EXPORT                              FULL                      EXECUTING

Now attach to a running job:-

C:\Users\NewAdmin>expdp system/orcl attach=SYS_EXPORT_FULL_02

Export: Release 11.2.0.1.0 - Production on Sat Jun 29 20:01:45 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Job: SYS_EXPORT_FULL_02
  Owner: SYSTEM
  Operation: EXPORT
  Creator Privs: TRUE
  GUID: 42902560C22D46BDA6924724126F59FD
  Start Time: Saturday, 29 June, 2013 20:01:08
  Mode: FULL
  Instance: orcl
  Max Parallelism: 1
  EXPORT Job Parameters:
  Parameter Name      Parameter Value:
     CLIENT_COMMAND        system/******** directory=datapump dumpfile=expdp_full_29062013_1.dmp logfile=expdp_full_29062013_1.log compression=all full=y
     COMPRESSION           ALL
  State: EXECUTING
  Bytes Processed: 0
  Current Parallelism: 1
  Job Error Count: 0
  Dump File: C:\DATAPUMP\EXPDP_FULL_29062013_1.DMP
    bytes written: 4,096

Worker 1 Status:
  Process Name: DW00
  State: EXECUTING

Export> STOP_JOB
Are you sure you wish to stop this job ([yes]/no): y

you will get a message like this :-

Job "SYSTEM"."SYS_EXPORT_FULL_02" stopped by user request at 20:02:01

SQL> SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs;
OWNER_NAME                     JOB_NAME                       OPERATION                           JOB_MODE                  STATE
------------------------------ ------------------------------ ----------------------------------- ------------------------- ------------------------------
SYSTEM                         SYS_EXPORT_FULL_02             EXPORT                              FULL                      NOT RUNNING

Restart a Stopped datapump Job :-

Attach to a stopped datapump job :-

C:\Users\NewAdmin>expdp system/orcl attach=SYS_EXPORT_FULL_02

Export: Release 11.2.0.1.0 - Production on Sat Jun 29 20:08:17 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Job: SYS_EXPORT_FULL_02
  Owner: SYSTEM
  Operation: EXPORT
  Creator Privs: TRUE
  GUID: 42902560C22D46BDA6924724126F59FD
  Start Time: Saturday, 29 June, 2013 20:08:20
  Mode: FULL
  Instance: orcl
  Max Parallelism: 1
  EXPORT Job Parameters:
  Parameter Name      Parameter Value:
     CLIENT_COMMAND        system/******** directory=datapump dumpfile=expdp_full_29062013_1.dmp logfile=expdp_full_29062013_1.log compression=all full=y
     COMPRESSION           ALL
  State: IDLING
  Bytes Processed: 0
  Current Parallelism: 1
  Job Error Count: 0
  Dump File: C:\datapump\expdp_full_29062013_1.dmp
    bytes written: 4,096

Worker 1 Status:
  Process Name: DW00
  State: UNDEFINED

Export> START_JOB

Export> CONTINUE_CLIENT
Job SYS_EXPORT_FULL_02 has been reopened at Saturday, 29 June, 2013 20:08
Restarting "SYSTEM"."SYS_EXPORT_FULL_02":  system/******** directory=datapump dumpfile=expdp_full_29062013_1.dmp logfile=expdp_full_29062013_1.log compression=all full=y
Processing object type DATABASE_EXPORT/TABLESPACE

Job is again restarted.

Kill a datapump job :-

Attach to a running datapump job :-

C:\Users\NewAdmin>expdp system/orcl attach=SYS_EXPORT_FULL_01

Export: Release 11.2.0.1.0 - Production on Sat Jun 29 20:12:55 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Job: SYS_EXPORT_FULL_01
  Owner: SYSTEM
  Operation: EXPORT
  Creator Privs: TRUE
  GUID: 452263D9B00047A9BCD6E97DE83878F4
  Start Time: Saturday, 29 June, 2013 20:12:57
  Mode: FULL
  Instance: orcl
  Max Parallelism: 1
  EXPORT Job Parameters:
  Parameter Name      Parameter Value:
     CLIENT_COMMAND        system/******** directory=datapump dumpfile=expdp_full_29062013.dmp logfile=expdp_full_29062013.log compression=all full=y
     COMPRESSION           ALL
  State: IDLING
  Bytes Processed: 0
  Current Parallelism: 1
  Job Error Count: 0
  Dump File: C:\datapump\expdp_full_29062013.dmp
    bytes written: 5,570,560

Worker 1 Status:
  Process Name: DW00
  State: RUNNING

Export> KILL_JOB
Are you sure you wish to stop this job ([yes]/no): y

DIfference between KILLING and STOPPING a datapump job is that, once you killed a datapump job you cannot able to restart it . But if we stop a datapump job we can restart it. Killing a datapump job will delete the dump and log files also.

I hope this article helped you.

Regards,
Amit Rath
Posted by at
Labels: ,

5 comments:

Subscribe to: Post Comments (Atom)