Oracle Data Pump expdp impdp Use JOB_NAME Option to Stop_job, Attach, Kill_job and Continue_client interactively

One of the main advantage of datapump is you can suspend the running export or import job and it can be resume if needed. Suppose if your server load is high when you started the export job, you can suspend the job and later you can resume the job once the server load comes down. One more feature is you can suspend the job from one client machine and can be resume from different client.

See one example below
Once you press ^C on the expdp window it will come to interactive mode with Export> prompt. In that prompt you can give commands to stop_job or kill_job

$ expdp scott/tiger schemas=scott directory=exp_dir dumpfile=exp_schema.dmp logfile=exp_schema.log job_name=expschema

Export: Release 11.1.0.7.0 - 64bit Production on Tuesday, 02 August, 2011 22:17:58

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."EXPSCHEMA":  scott/******** schemas=scott directory=exp_dir dumpfile=exp_schema.dmp logfile=exp_schema.log job_name=expschema
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
^C
Export> stop_job=immediate
Are you sure you wish to stop this job ([yes]/no): yes

oracle@prod(4113) prod9 /home/oracle/scott

You can use dba_datapump_jobs view to get the details of the datapump jobs.

$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.7.0 - Production on Tue Aug 2 22:19:43 2011

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


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

SQL> select OWNER_NAME,JOB_NAME,OPERATION,JOB_MODE,STATE from dba_datapump_jobs;

OWNER_NAME                     JOB_NAME
------------------------------ ------------------------------
OPERATION                      JOB_MODE
------------------------------ ------------------------------
STATE
------------------------------
SCOTT                         EXPSCHEMA
EXPORT                         SCHEMA
NOT RUNNING

Using the below mentioned command you can resume the job. Once you fire the below command in the prompt, expdp will load the job details and come export> prompt. You have to give continue_client command to resume the job.

$ expdp scott/tiger attach=EXPSCHEMA

Export: Release 11.1.0.7.0 - 64bit Production on Tuesday, 02 August, 2011 22:25:09

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

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

Job: EXPSCHEMA
  Owner: SCOTT
  Operation: EXPORT
  Creator Privs: TRUE
  GUID: A993FD0301520998E04400144F9F5BAA
  Start Time: Tuesday, 02 August, 2011 22:25:12
  Mode: SCHEMA
  Instance: prod9
  Max Parallelism: 1
  EXPORT Job Parameters:
  Parameter Name      Parameter Value:
     CLIENT_COMMAND        scott/******** schemas=scott directory=exp_dir dumpfile=exp_schema.dmp logfile=exp_schema.log job_name=expschema
  State: IDLING
  Bytes Processed: 0
  Current Parallelism: 1
  Job Error Count: 0
  Dump File: /home/oracle/scott/exp_schema.dmp
    bytes written: 4,096

Worker 1 Status:
  Process Name: DW01
  State: UNDEFINED

Export> continue_client
Job EXPSCHEMA has been reopened at Tuesday, 02 August, 2011 22:25
Restarting "SCOTT"."EXPSCHEMA":  scott/******** schemas=scott directory=exp_dir dumpfile=exp_schema.dmp logfile=exp_schema.log job_name=expschema
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
^C
Export> kill_job
Are you sure you wish to stop this job ([yes]/no): yes

Once you kill the export job it will remove the details from the dba_datapump_jobs.

$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.7.0 - Production on Tue Aug 2 22:26:46 2011

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


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

SQL> select OWNER_NAME,JOB_NAME,OPERATION,JOB_MODE,STATE from dba_datapump_jobs;

DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google