Data Pump: Interactive Command Mode

Restart Data Pump Jobs? Can we do that?  

You just started a data pump export or import job. The job, depending on the data size, will probably take a couple of hours to run. Then, it hits you: you forgot to set a parameter that would make the job faster, or maybe you forgot to encrypt the export dump files. What to do? Should you wait for the job to finish (this defeats the purpose, especially if you wanted to tune the data pump job performance), or do you stop the job, clear what has been imported so far, and then start the job afresh?

Well, data pump export and import jobs can be stopped and restarted. Using the interactive command mode, it is possible to interact with running data pump jobs, allowing you to; 

• change parameters

• change attributes

• monitor

• start/stop jobs and workers

The data pump export job can be restarted as soon as the 'Estimate' phase has been completed. The import job can be restarted using the control table with data pump workers tracking the state and status values. The 'State' values include; 

• R - Retrieved (objects were successfully exported)

• C - Current (objects were successfully imported)

• W - Written (objects are imported)

• U - Unknown (objects were being imported but didn't finish) 

Jobs can be restarted based on these values in the control table for the specific objects. For example, an object is deemed successfully imported when the state is R and status is C. On this basis, this object will be skipped if an Import restart is fired as it's already been successfully imported. Objects with value U are prime candidates for the restarted import job.

Several commands are available for both export and import jobs and can be invoked in the Interactive command mode. Some can be executed in both modes, and others not. These include but are not limited to; 

• parallel, available for both, and is used to change the parallelism of the current job. This increases almost immediately. 

• status, available for both and can be used to get the running job and worker status

• filesize, only usable with export, can be used to change the dump file size as well as specify a denominator.

• add_filename, only available for exports, adds an additional dump file or dump file pattern. 

• trace, available for both, and adds tracing, which can be used for troubleshooting problematic jobs. See MOS Note: 286496.1 

Jobs can be restarted based on these values in the control table for the specific objects. For example, an object is deemed successfully imported when the state is R and the status is C. On this basis, this object will be skipped if an Import restart is fired, as it's already been successfully imported. Objects with value U are prime candidates for the restarted import job.

You can get into the Interactive Command Mode using two options. These can be; 

• In a running data pump console, hitting (CTRL + C) or 

  • Attaching to a running job by;

    • expdp ... attach=<<job_name>>

    • impdp ... attach=<<job_name>>

Here we have a parameter file (export.par), used to start an export job. Please note we do not have the parallel parameter set.

You start the export job using the parameter file. 

Hit CTRL + C to break into the Interactive Command Mode

Type 'status' to view the status of the running job. Note that parallelism is 1 as no 'parallel' parameter was set in the export parameter file. 

Add the parallel parameter with a value (4) as shown below, and continue the job. 

Attach to the existing job in a new console, using status, check the parallelism degree again. 

Check the parallelism degree 

Note that the current parallelism is now 4 with 4 worker processes.  

Continue the job, let it complete. 

Long running data pump jobs need not be stopped for you to make needed changes where necessary. Just break into the Interactive Command Mode, make the needed changes and 'restart'. Hope this is helpful, happy restarting.