Optimize Import Data Pump

Oracle Import Data Pump is no doubt much faster than deprecated import process.  However, it has some flaws that makes it slower.  

For example, if you import data_only for partitioned table, the impdp does serial import (see MOS ID 1327029.1).  

Also, when you have a schema with many indexes, it can take a while because impdp process creates one index at a time.  The index is created using parallelism you specify in the par file or at the command line.  But it does not create multiple indexes at the same time.

Here are the general steps I use to speed up the import process using impdp.

data_only import

1. Put database in NO ARCHIVELOG Mode.
2. Disable constraints (FK and PK)
3. Change all indexes in the schema(s) to unusable
4. Generate grants statements for sequences
5. Disable all triggers
6. Drop sequences
7. Import data using following options
1. content=data_only
2. TABLE_EXISTS_ACTION=TRUNCATE
3. parallel=4 (if your machine can handle more then increase this number)
8. Import sequences
1. content=metadata_only
2. INCLUDE=SEQUENCE
3. parallel=4
9. Apply the grants generated in step 2.
10. Rebuild indexes using parallel 20 in multiple sessions.
1. generate ALTER INDEX owner.index_name REBUILD PARALLEL 20; into a file
2. use Unix utility called split to split the file into 10 different files.
3. use nohup and & to background the sqlplus session to create index and start all 10 sessions.
11. Enable foreign and primary constraints
12. Change indexes back to NO PARALLEL or change it to whatever they were before.
13. Enable triggers
14. Update Stats
15. Put database back in ARCHIVELOG mode

Using the method mentioned above I was able to import about 511GB in 28 minuets for a database running in NO ARCHIVELOG mode.  Rebuilt 2,089 indexes in 40mins.  I created a korn shell script for the entire import process and it took about 1 hours and 20 minuets to complete.

Import everything

1. Put database in NO ARCHIVELOG Mode
2. Set the db_block_checksum parameter to false to improve import speed: ALTER SYSTEM SET db_block_checksum=FALSE SCOPE=BOTH;
3. Generate scripts or save info for following:
1. grants
2. parallelism for indexes
3. password
4. synonyms
5. DDL for foreign and primary constraints
6. Set index parallel to 20 and generate DDL for indexes into 10 files
4. Drop the schema if already exist
5. Import using following parameters
1. content=ALL
2. exclude=constraint
3. exclude=INDEX
6. Create indexes using nohup and & to background and start all 10 sessions.
7. Create constraints
8. Put indexes back to their original parallelism
9. Apply grants
10. Create synonyms
11. Set the passwords
12. Recompile all invalid objects
13. Update stats
14. Set the db_block_checksum parameter back to typical: ALTER SYSTEM SET db_block_checksum=TYPICAL SCOPE=BOTH;
15. Put database back in ARCHIVELOG Mod. 

I used the above steps to import about 2.75TB of data (multiple schemas) in about 14 hours.

I hope these steps help anyone who is looking to speed up import process.  I didn't put exact SQL and korn shell commands I use in my scripts.  Please contact me if you would like exact SQL and/or korn shell script commands.

If anyone does it any differently please let me know.  I am interested to see how other DBAs import large amount of data when Trasnportable Tablespace (TTS) is not an option.