RE: how to improve impdp

From: Patterson, Joel <jpatterson_at_entint.com>
Date: Tue, 9 Jul 2013 10:53:23 -0400
Message-ID: <C1117B1AA0340645894671E09A7891F715126CBBCD_at_EIHQEXVM2.ei.local>

For this scenario can you not just impdp the tables, then impdp a second time for everything except for the tables?

Joel Patterson
Database Administrator
904 928-2790

--
Joel Patterson
Sr. Database Administrator | Enterprise Integration
Phone: 904-928-2790 | Fax: 904-733-4916
http://www.entint.com/

http://www.entint.com/

http://www.facebook.com/pages/Enterprise-Integration/212351215444231 http://twitter.com/#!/entint http://www.linkedin.com/company/18276?trk=tyah http://www.youtube.com/user/ValueofIT

This message (and any associated files) is intended only for the use
of the addressee and may contain information that is confidential,
subject to copyright or constitutes a trade secret. If you are not the
intended recipient, you are hereby notified that any dissemination,
copying or distribution of this message, or files associated with this
message, is strictly prohibited. If you have received this message in
error, please notify us immediately by replying to the message and
deleting it from your computer. Messages sent to and from us may be
monitored. Any views or opinions presented are solely those of the
author and do not necessarily represent those of the company. [v.1.1]

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of rjamya
Sent: Tuesday, July 09, 2013 10:25 AM
To: Joan.Hsieh_at_tufts.edu
Cc: oracle_l
Subject: Re: how to improve impdp

You have only one dmp file so parallel isn't much use here. I bet most of your time is going in creating indexes and enabling constraints.
I'd expdp with
dumpfile=filename.%u.dmp parallel=5

and then use similar params for impdp. That will ONLY help your with table data import. Unless your tables have degree > 1 (in source db) constraints will be enabled in non-parallel mode. Unless your indexes have degree > 1 (in source db) they will be created by a single process, i.e. parallel will be used by 4 DW threads will be idling..

You might want to consider this if your source db schema doesn't change much (i have done this and it worked very well for me) , 1. expdp in parallel to multiple dumpfile and impdp excluding index/constraints (probably have to exclude stats as well).
2. impdp on dumpfiles extract one script for index, one for constraints and potentially one just for stats.
3. using sed, modify index script to create each index in parallel, then do a 'alter index' noparallel.
4. using sed modify constraints script to enable constraints with novalidate (if applicable), this should save you lot of time.
5. run index script using sqlplus then constraints script and then run stats script,

Raj



On Tue, Jul 9, 2013 at 10:14 AM, Hsieh, Joan <Joan.Hsieh_at_tufts.edu> wrote:



> Hi List,

> I'm trying to use data pump to export and import from the AIX database

> to the LINUX database. The application is peoplesoft Finacial.  The

> expdp take around 4 hours, the impdp take 7 hours which is a big

> concern on the weekend production cutover time.

>

> The parameter I used on the impdp as below, do you have any suggestion

> how to improve the impdp time?

>

> Thanks,

>

> Joan

>

> userid= /

> directory=dmpdir

> dumpfile=FMPROD_expdp.1300_070213.dmp

> PARALLEL=5

> logfile=impdp_FINPROD1_i07032013114001.log

>

> --

> http://www.freelists.org/webpage/oracle-l

>

>

>



--
http://www.freelists.org/webpage/oracle-l



--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jul 09 2013 - 16:53:23 CEST