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

On Feb 25, 1:15 pm, EdLong <rdhm..._at_prodigy.net> wrote:
> 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.htmInparticular, 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/ALeqM5ghPenZUJTE7BfSfgQbj6RX597DEAD8V019...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.

Sounds like you may be simply hitting your hardware limits then, though if noarchivelog doesn't make much difference, I'm not sure what is going on (unless your arcs go to a different device or controller?). You might try "roll-your-own-parallelization" by simply starting up a couple of imps to different tablespaces and see what your HBA and clock time say about that. I would expect your business would stay the same and the queues would grow, but I really don't know anything about Clarion, it may have some smartypants adaptiveness that you need to convince it to use, or may be much worse. If the latter, I would suspect the results mixadba posted wouldn't apply to your situation.

DIRECT should be faster.

And now for something completely different: http://asktom.oracle.com/tkyte/flat/

jg

--
@home.com is bogus.
Download Jonathan Lewis's its_the_data in rtf format.  Open it with
the notepad editor.  Smoothly scroll left and right until you see
square white blocks on either side, then moderately quickly scroll
left and right between those areas.  See the Pong?