User Experiences with Data Pump Versus Legacy EXP/IMP

From: EdLong <rdhm99a_at_prodigy.net>
Date: Sun, 24 Feb 2008 17:08:31 -0800 (PST)
Message-ID: <9f19f266-860f-43f1-bbaf-462bbee6e445@q33g2000hsh.googlegroups.com>


Hi everyone.
We are 10.2.0.3 running on Windows 2003. I have to move about a terabyte of Oracle data from one disk array to another; its split amongst 5 schemas or so. I'd like to solicit some user experiences with DataPump. You may infer that we have not had the best experience so far.

I started with the largest subset, a single schema of about 200gb. Using traditional exp, sneaker net, import, this took about 24 hours to move. Of this, nearly 20 hours was the import. Target machine consists of 4 3ghz engines with beaucoup memory and little if any competing workload.

I've tried several different cuts at DataPump but found it somewhat unsatisfactory.

It appears that at least in our shop at this level, The target directory for the expdp and the source directory for the impdp cannot be either a mapped drive or a USB drive. I could not find any documentation for these apparent limitations.

The expdp says I can't open and write to the log file if its a map drive. The EXPDP appears to work when pointed at a USB drive. The impdp fails on a USB drive with a 'hard' i/o error after an hour or two of processing. I tried the latter at a parallel level of 4 and 2; I didn't try 1.

What leads me to risk the wrath of Achilles and query this group is that a highly informal survey of genuine Oracle guri's in the neighborhood (think video thievery in the NFL) revealed a similar frustration with Data Pump. I also read the most excellent "Oracle Automatic Storage Management" book (Vengurlekar, Vallath, Long (no relation)). In the intro, they list a number of supported disk hardware interfaces. Guess which two don't make the cut, USB and mapped drives aka NFS. Even if Oracle 10g won't run with tablespaces on a USB drive, it should be able to reliably read and write to it as an 'external' file or table.

Before anyone mentions transportable tablespaces, I also tested that option. Much like the fellow in the recent thread, I found that in our shop at this maintenance level, we got odd results. I didn't take it to his level of doing an endian to endian conversion where source and target were Intel. I'm very willing to try again though since 24 hours for 200 Gb. is pretty gauche!

Thanks for taking the time to read this note and for considering our problem. Received on Sun Feb 24 2008 - 19:08:31 CST

Original text of this message