Re: User Experiences with Data Pump Versus Legacy EXP/IMP

On Feb 24, 8:08 pm, EdLong <rdhm..._at_prodigy.net> wrote:
> 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.

A couple of thoughts. I have had some very nice results using impdp with a link between the databases and parallel. Of course you need good network bandwidth betwen the systems and some way to make sure your schema that you are cloning isn't changing in the process.

Using a usb drive and/or firewire drive to expdp out then impdp in also has worked for me. If you are having "read errors" against the drive doing the import well that's a problem and I would first guess that it is something dodgy with the drive. Maybe windows has problems reading files of this size is another possibility?

You could do some testing of the usb out and in on systems that are not running windows to rule out that as a factor. Received on Mon Feb 25 2008 - 06:39:30 CST