DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Saturday, January 14, 2017

How  to  stop the datapump running job and how to reattach / resume same job:-



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

The ability to detach from and reattach to long-running jobs without affecting the job itself. This allows DBAs and other operations personnel to monitor jobs from multiple locations. The Data Pump Export and Import utilities can be attached to only one job at a time.


To check the master control process table details.

$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.4.0  - Production on
Copyright (c) 1982, 2008, Oracle.  All rights reserved.

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

Sql> Select * from dba_Datapump_job;

no rows selected

] $ expdp dumpfile=full1.dmp logfile=user.log directory=dp full=y
Username: system/xxxxxxx

=================================================

Sql prompt and check the master control process:

$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.4.0  - Production on
Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.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
---------------------- --------------------------------------------- ---------------------
EXPORT          SYS_EXPORT_FULL_02     SYSTEM            FULL         EXECUTING

Sql>  select SID,SERIAL# ,OPNAME,SOFAR,TOTALWORK,to_char(START_TIME,'dd-mon-yy hh:mi') Start_time,to_char(LAST_UPDATE_TIME,'dd-mon-yy hh:mi') Last_updated,time_remaining from  gv$session_longops where SOFAR<TOTALWORK;

=================================================

To stop the data pump job:-

] $ ] $ expdp dumpfile=full1.dmp logfile=user.log directory=dp full=y
Username: system/xxxxxxx

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE


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


Press CTRL+C on export window.
Export> stop_job
Prompt: - Y

Note:- STOP_JOB interactive command, the master table is retained for use in restarting the job.

=================================================

Go to sqlplus window and check the master control process
Sql> SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs;

OWNER_NAME      JOB_NAME                OPERATION   JOB_MODE      STATE
---------------------- --------------------------------------------- ---------------------
EXPORT          SYS_EXPORT_FULL_02     SYSTEM            FULL     NOT RUNNING

=================================================

To reattach / resume same data pump job:-

] $ expdp system/xxxxxx attach=SYS_EXPORT_FULL_02

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.

Export> start_job
Export> continue_client

Export: Release 11.2.0.4.0 - 64bit Production on
Copyright (c) 2003, 2007, Oracle.  All rights reserved.

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

=================================================

SQLPLUS prompt

Sql> SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs;

OWNER_NAME      JOB_NAME                OPERATION   JOB_MODE       STATE
---------------------- --------------------------------------------- ---------------------
EXPORT          SYS_EXPORT_FULL_02     SYSTEM            FULL        EXECUTING

No comments: