Tuesday 30 July 2013

Identifying data pump jobs to be /Kill/Stop/Resume


Do a select from dba_datapump_jobs in sqlplus to get the job name:

> expdp system full=y

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_01   EXPORT     FULL       EXECUTING

Or when you use the JOB_NAME parameter when datapumping, you already identified the job with a name. You don’t need to look up afterwards…

expdp system full=y JOB_NAME=EXP_FULL

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


Killing or stopping a running datapump job:

The difference between Kill and Stop is simple to explain. When killing a job, you won’t be able to resume or start it again. Also logs and dumpfiles will be removed!
When exporting (or importing), press Ctrl-c to show the datapump prompt and type KILL_JOB orSTOP_JOB[=IMMEDIATE]. You will be prompted to confirm if you are sure…
Adding ‘=IMMEDIATE‘ to STOP_JOB will not finish currently running ‘sub-job’ and must be redone when starting it again.

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
[Ctrl-c]

Export> KILL_JOB

..or..

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

Resuming a stopped job:

Identify your job with SQL or you already knew it because you used ‘JOB_NAME=‘ ;)
SELECT owner_name, job_name, operation, job_mode, state
FROM dba_datapump_jobs;

OWNER_NAME JOB_NAME             OPERATION  JOB_MODE   STATE      
---------- -------------------- ---------- ---------- ------------
SYSTEM     EXP_FULL             EXPORT     FULL       NOT RUNNING
Now we can ATTACH to the job using it as a parameter to the expdp or impdp command, and a lot of gibberish is shown:

> expdp system ATTACH=EXP_FULL

Job: EXP_FULL
 Owner: SYSTEM
 Operation: EXPORT
 Creator Privs: TRUE
 GUID: A5441357B472DFEEE040007F0100692A
 Start Time: Thursday, 08 June, 2011 20:23:39
 Mode: FULL
 Instance: db1
 Max Parallelism: 1
 EXPORT Job Parameters:
 Parameter Name      Parameter Value:
 CLIENT_COMMAND        system/******** full=y JOB_NAME=EXP_FULL
 State: IDLING
 Bytes Processed: 0
 Current Parallelism: 1
 Job Error Count: 0
 Dump File: /u01/app/oracle/admin/db1/dpdump/expdat.dmp
 bytes written: 520,192

Worker 1 Status:
 Process Name: DW00
 State: UNDEFINED
(Re)start the job with START_JOB, use ‘=SKIP_CURRENT‘ if you want to skip the current job. To show progress again, type CONTINUE_CLIENT (Job will be restarted if idle).
Export> START_JOB[=SKIP_CURRENT]
Export> CONTINUE_CLIENT
Job EXP_FULL has been reopened at Thursday, 09 June, 2011 10:26
Restarting "SYSTEM"."EXP_FULL":  system/******** full=y JOB_NAME=EXP_FULL

Processing object type DATABASE_EXPORT/TABLESPACE

Processing object type DATABASE_EXPORT/PROFILE

Monday 29 July 2013

How to delete or remove non executing data pump jobs in oracle

 step 1- Identify which jobs are not in running state


SET lines 200

SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs
ORDER BY 1,2;

output:

OWNER_NAME       JOB_NAME       OPERATION
------------------------------ ------------------------------ ------------------------------------------------------------------------------------------------------------------------
JOB_MODE STATE ATTACHED_SESSIONS
------------------------------------------------------------------------------------------------------------------------ ------------------------------ -----------------
MANJIT       SYS_EXPORT_FULL_01       EXPORT
FULL NOT RUNNING 0

MANJIT       SYS_EXPORT_FULL_02       EXPORT
FULL NOT RUNNING 0

MANJIT       SYS_EXPORT_FULL_03       EXPORT
FULL NOT RUNNING 0


OWNER_NAME       JOB_NAME       OPERATION
------------------------------ ------------------------------ ------------------------------------------------------------------------------------------------------------------------
JOB_MODE STATE ATTACHED_SESSIONS
------------------------------------------------------------------------------------------------------------------------ ------------------------------ -----------------
MANJIT       SYS_EXPORT_FULL_04       EXPORT
FULL NOT RUNNING 0

Step 2- Identify the master tables which are created for these jobs

select 
   o.status, 
   o.object_id, 
   o.object_type, 
   o.owner||'.'||object_name "OWNER.OBJECT" 
from 
   dba_objects o, 
   dba_datapump_jobs j 
where 
   o.owner=j.owner_name 
and 
   o.object_name=j.job_name 
and 
   j.job_name not like 'BIN$%' 
order by 4, 2; 

output:

STATUS OBJECT_ID OBJECT_TYPE       OWNER.OBJECT
------- ---------- ------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------
VALID     123893 TABLE       MANJIT.SYS_EXPORT_FULL_01
VALID     123993 TABLE       MANJIT.SYS_EXPORT_FULL_02
VALID     124093 TABLE       MANJIT.SYS_EXPORT_FULL_03
VALID     124193 TABLE       MANJIT.SYS_EXPORT_FULL_04

Step -3 Drop these master tables

SQL> drop table        MANJIT.SYS_EXPORT_FULL_03;

Table dropped.

SQL> drop table        MANJIT.SYS_EXPORT_FULL_02;

Table dropped.

SQL> drop table        MANJIT.SYS_EXPORT_FULL_01;

Table dropped.
Important points

1. Datapump jobs that are not running doesn’t have any impact on currently executing ones.
2. When any datapump job (either export or import) is initiated, master and worker processes will be created.
3. When we terminate export datapump job, master and worker processes will get killed and it doesn’t lead to data courrption.

4. But when import datapump job is terminated, complete import might not have done as processes(master & worker)  will be killed.