This discussion is archived
8 Replies Latest reply: Jan 23, 2013 4:43 PM by Simon_DBA RSS

EXPDP --> IMPDP speed

R. Royal Newbie
Currently Being Moderated
Hi,
Oracle version: 10g
O.S: Windows 2003

I've 2 oracle instance: ORCL1 (schema TOM) on machine1 and ORCL2 (schema SAM) on machine2
I'd like to import the schema TOM into schema SAM.
TOM schema size is 300 GB

I exported TOM with following parfile:
SCHEMAS=TOM
DIRECTORY=TOM_DIR
DUMPFILE=exp_tom_%U.dmp
LOGFILE=exp_tom.log 
filesize=30GB
JOB_NAME=EXP_JOB
and created about 10 dump files in more of 30 hours

Now I need import into schema SAM, but I'd like to import faster

Which parameter can I add at following imp parfile to speed up the import?
schemas=TOM
remap_schema=TOM:SAM
logfile=imp_sam.log
directory=SAM_DIR
DUMPFILE=exp_tom_%U.dmp
exclude=statistics
job_name=IMP_JOB
Thanks in advance!
  • 1. Re: EXPDP --> IMPDP speed
    TSharma-Oracle Guru
    Currently Being Moderated
    IMPORT:

    Create an indexfile so that you can create indexes AFTER you have imported data. Do this by setting INDEXFILE to a filename and then import. No data will be imported but a file containing index definitions will be created. You must edit this file afterwards and supply the passwords for the schemas on all CONNECT statements.

    Place the file to be imported on a separate physical disk from the oracle data files
    Increase DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior to 9i) considerably in the init$SID.ora file
    Set the LOG_BUFFER to a big value and restart oracle.
    Stop redo log archiving if it is running (ALTER DATABASE NOARCHIVELOG;)
    Create a BIG tablespace with a BIG rollback segment inside. Set all other rollback segments offline (except the SYSTEM rollback segment of course). The rollback segment must be as big as your biggest table (I think?)

    Use COMMIT=N in the import parameter file if you can afford it
    Use STATISTICS=NONE in the import parameter file to avoid time consuming to import the statistics
    Remember to run the indexfile previously created
  • 2. Re: EXPDP --> IMPDP speed
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    TSharma wrote:
    IMPORT:

    Create an indexfile so that you can create indexes AFTER you have imported data. Do this by setting INDEXFILE to a filename and then import. No data will be imported but a file containing index definitions will be created. You must edit this file afterwards and supply the passwords for the schemas on all CONNECT statements.

    Place the file to be imported on a separate physical disk from the oracle data files
    Increase DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior to 9i) considerably in the init$SID.ora file
    Set the LOG_BUFFER to a big value and restart oracle.
    Stop redo log archiving if it is running (ALTER DATABASE NOARCHIVELOG;)
    Create a BIG tablespace with a BIG rollback segment inside. Set all other rollback segments offline (except the SYSTEM rollback segment of course). The rollback segment must be as big as your biggest table (I think?)

    Use COMMIT=N in the import parameter file if you can afford it
    Use STATISTICS=NONE in the import parameter file to avoid time consuming to import the statistics
    Remember to run the indexfile previously created
    Pl post references when you quote them -

    http://tamimdba.wordpress.com/tag/import-imp/
    http://www.dbahq.com/OT_ExpImpTips.html

    Srini
  • 3. Re: EXPDP --> IMPDP speed
    TSharma-Oracle Guru
    Currently Being Moderated
    I had these saved on my desktop long time back. I just copied these from there. It served the same purpose.
  • 4. Re: EXPDP --> IMPDP speed
    Richard Harrison . Expert
    Currently Being Moderated
    Hi,
    The biggest factor in impede/expdp speed is the parallel parameter. Try a value of 4 maybe for it and see how well it runs. Setting is dependent on the number of CPUs. Not sure if its available in standard edition though and I'm not sure what version you are actually using?

    Cheers,
    Harry
  • 5. Re: EXPDP --> IMPDP speed
    R. Royal Newbie
    Currently Being Moderated
    my oracle version: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0

    in impdp I used FILESIZE parameter. Can I use PARALLEL parameter for expdp?
    schemas=TOM
    remap_schema=TOM:SAM
    logfile=imp_sam.log
    directory=SAM_DIR
    DUMPFILE=exp_tom_%U.dmp
    exclude=statistics
    job_name=IMP_JOB
    PARALLEL=4
  • 6. Re: EXPDP --> IMPDP speed
    Richard Harrison . Expert
    Currently Being Moderated
    Yes.

    It will load multiple tables at the same time and also build indexes in parallel.

    Doesn't matter if you have 1 file or 10 files it can still be parallel. Some will just be more efficient than others.

    Cheers,
    Harry
  • 7. Re: EXPDP --> IMPDP speed
    Dean Gagne Expert
    Currently Being Moderated
    Import should always take more then export. At export time, all doing is reading the oracle dictionary. At import time, you need to create objects. This will take more time. To speed up import, you can lok at parallel but this will use more CPU resources. You could also look into transportable tablespaces. This can reduce both export and import by a lot.

    Dean
  • 8. Re: EXPDP --> IMPDP speed
    Simon_DBA Journeyer
    Currently Being Moderated
    As already suggested, use PARALLEL. Usually up to 2x the number of CPUs. I'd actually start with 8 or 16 and see how it goes. And also use the FILESIZE parameter.

    Import also with PARALLEL set and using the same value. Or faster yet, don't export, just import through the NETWORK_LINK option and still using PARALLEL.

    Build indexes yourself separately via a SQL script. Even with the PARALLEL import parameter, data pump is not going to start building the first index until all of the tables are loaded. By building indexes yourself outside of data pump you can start building the indexes on the first table as soon as it's loaded and so on and not have to wait for subsequent tables. Build indexes with "NOLOGGING PARALLEL x" and reset the indexes parallel value after building via an "ALTER INDEX ...".

    Also add constraints separately yourself via a SQL script and using "NOVALIDATE".

    If you do all of that it will be much faster than just a regular data pump export and import.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points