8 Replies Latest reply: Jan 23, 2013 6:43 PM by Simon_DBA RSS

    EXPDP --> IMPDP speed

    R. Royal
      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
          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
            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
              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 .
                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
                  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 .
                    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
                      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
                        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.