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.
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.
No comments:
Post a Comment