Posts

Figuring Out What EXPDP and IMPDP are Doing Right Now

Starting Oracle 10g, the old export/import tools were replaced by a newer, more sophisticated tool: the data pump.
This new tool had some advantages over the older tool – it was able to run in parallel, it has a better interface and it can be ran from PL/SQL code easily. It can even be restarted or resumed if needed. On the con side, it uses the database DIRECTORY object which means the file we’re exporting to or importing from must reside on the server.

The problem start when we try to diagnose what is going on when there is a performance issue. Since the process we use for exporting might be forked into multiple processes, we can’t really know what is going on behind the scenes.

So, how can we resolve that?

Read more →

/2 Comments/by /Estimated Reading Time: 4 Minutes
waterpump

How to manage lost Datapump jobs

I’ve got a customer who runs export scripts for his most important tables using Oracle data pump export. This is a pretty good solution to backup his important tables using dump file for fast recovery of specific tables (this is in no way a proper database backup) or for transferring it to his development environments. The problem is that the tables which we export are very large and the export process might take a very long time to complete (at least a few hours).

Sometimes, we find ourselves in the need to peek into the export process and see what is going on – and figure out what it is doing: is it stack, what table it is exporting or how much time is left till the process completes. Generally, since our scripts are running at the background (using timed cron) and there isn’t really an output we can look at (except the log files). The problem is that the output which goes into the log file is usually not enough to answer all the questions the customer is asking.

In another scenario, we’re running an export (full, for example) and that takes a very long time but then our connection to the server is closed for some reason. It can be a server policy or it can be that we did it by mistake but my most common reason is just loosing the network connection between my client and the server.

In this post we will look at the solution given to us by the datapump processes and understand how the fact that we’re using data pump jobs, actually makes those issues very simple to solve.

Read more →

/4 Comments/by /Estimated Reading Time: 7 Minutes