Re: how to improve impdp
Date: Tue, 9 Jul 2013 10:25:12 -0400
Message-ID: <CAGurbTP-GSbN=FzRCy2aNt1vkReHy7WLu_xLcR5WakHceMeOkA_at_mail.gmail.com>
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-lReceived on Tue Jul 09 2013 - 16:25:12 CEST