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

On Feb 25, 1:26 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Feb 24, 5: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.
>
> That seems slow, have you done everything you can to speed it up?http://www.oracledba.co.uk/tips/import_speed.htmIn particular, using
> direct path for both exp and imp, and doing the imp with noarchivelog,
> then switching to archivelog and taking a backup can make a big
> difference.  Have you checked to see what is slowing it down?
> Sometimes the I/O is just overwhelmed, especially if you are dependent
> on some RAID-5 with write buffers that get saturated.
>
>
>
>
>
>
>
> > 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
>
> Aw, all we want are decent questions!  :-)
>
> jg
> --
> @home.com is bogus.
> What's in your database?  http://ap.google.com/article/ALeqM5ghPenZUJTE7BfSfgQbj6RX597DEAD8V019TG0- Hide quoted text -
>
> - Show quoted text -

Thank you both for the great suggestions. 1: I'm confident the USB drive is ok; brand new disk, just formatted and chkdsk /f'd with no problems. Not sure if its a Windows problem; I have seen similar 'dodgy' results when Windows tried to compress a file that ended up > 32Gb. No compression here though. 2: I tried the noarchivelog option on the destination; it made a slight difference.
3: The target disk is an EMC Clarion CX-310 configured as a Raid 5 disk so Raid delay could be a factor.
4: I don't control the source instances so CONSISTENT=Y and DIRECT=Y were both problematic due to Undo size and other factors. I actually went back to the original dump sent in by a customer some months ago making the Export moot for the first schema. I'm retrying Direct and consistent for the second schema.
5: Thank you Jg for the link to the best practices stuff on export/ import. I've actually been considering a variant on this idea, breaking up the export by tablesize then doing a series of imports much like you describe.
6: In watching the Import run the single gating issue appears to be that its single threaded. The HBA gets to about 35% busy and stays there. 35% busy, is right at the point in a single server queue where there is always something using the queue but little queueing(sp?). The processors on the target server are loafing. Received on Mon Feb 25 2008 - 15:15:15 CST