Monday, August 21, 2017

RDBMS -- a useful Datapump tip, Attaching to a running Job

Sometimes, during the database migrations especially, during the big-sized database migrations, we may need to change some of the configurations of our running datapump jobs.
While, stopping/cancelling the job and executing the datapump again, can be a solution for this, it can be costly in some environments where a full database export/import takes several hours.

In these kinds of situations, the datapump argument named attach comes to our help.
I see a need to write this, because we used it recently to increase the parallelism count of a running datapump export job and it performed well.

This attach feature is also useful when we start a job at one location and need to check on it at a later time from a different location.

Anyways, let's take a look at what we did for increasing the parallelism of our export job during an Exadata migration.

In the first glance; we predicted the optimal setting for the datapump parallelism as 8 but when we saw that the machine (in this case it was an Exadata x6-2 1/8) had plenty of I/O and CPU resources eventhough the 8 parallel expdp threads were running, we increased the parallelism to 24 and we did it using attach argument, without terminating and re-executing our datapump command.

It was as simple as follows;
  • We found our jobs using "select * from dba_datapump_jobs; "
  • We attached to our running job using "expdp usert/pass attach=job_name"
  • We increased the parallelism to 24 using "export> parallel=24"
It is pretty handy, isn't it? Actually, we can do a lot more using this attach argument.. Here is the list of the activities, that we can do when we attach a running datapump job. (along with their definitions)
  • ADD_FILE: Add additional dump files.
  • CONTINUE_CLIENT: Exit interactive mode and enter logging mode.
  • EXIT_CLIENT: Stop the export client session, but leave the job running.
  • FILESIZE: Redefine the default size to be used for any subsequent dump files.
  • HELP: Display a summary of available commands.
  • KILL_JOB: Detach all currently attached client sessions and terminate the current job.
  • PARALLEL: Increase or decrease the number of active worker processes for the current job. This command is valid only in the Enterprise Edition of Oracle Database 11g.
  • START_JOB: Restart a stopped job to which you are attached.
  • STATUS: Display detailed status for the current job and/or set status interval.
  • STOP_JOB:Stop the current job for later restart.

No comments :

Post a Comment