RE: how to improve impdp

I have tested the dumpfile=%u.dmp and parallel=7 , it cut down one hour to export already. I have never used SQLFILE option, I'll test it out. Can I use exclude=indexes, statistics exclude the indexes and statistics for imp?

Thanks,

Joan

-----Original Message-----
From: Bobby Curtis [mailto:curtisbl_at_gmail.com] Sent: Tuesday, July 09, 2013 2:12 PM
To: Hsieh, Joan
Cc: oracle_l
Subject: Re: how to improve impdp

Joan,

Not sure if you have your answer yet. Typically, imports with IMPDP are slow not so much from the data being imported, but the DDL commands to create indexes. Even with a parallel of 5, indexes will be created in serial since data pump uses the metadata of the object to create it. I've see quite a few positive recommendations in the email string; which all are good approaches. I think the approach you are looking for is going to be determined by what your business needs are.

In the interim. If you are going to rely on dump files, I would recommend setting the following:

DUMPFILE="file_name_%U.dmp"
PARALLEL=<some number>

This way you will end up with a number of files based on the degree of parallel you want.

On import, you will need to specify the same parameters in your par file to import the data.

To address the serial issue of the indexes. You can use the SQLFILE option to dump all the DDL for indexes to a sql file to run after the import. During the import, just exclude the indexes, then run the SQL file later when done.

Hope this helps.

Thanks
Bobby Curtis
curtisbl_at_gmail.com
http://dbasolved.com

On 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