In my case I did an export of a large 12c 126.96.36.199 version test database that is using the application JD Edwards Enterprise One version 9.2 . The size of the database is about 2.6TB. The hardware is an Oracle engineered system the ODA X5-2. The database compute node has 1 single CPU socket with 24 cores.
I used the following export data pump parameters in my par file.
I was requested by my client to refresh the two schemas I exported PRODCTL and PRODDTA. I simply dropped and recreated the users from the user creation DDL scripts I had saved prior. This method is the cleanest way I know to do a refresh of a full schema which is the process I normally use.
I then proceeded to create the import parameter file as follows:
userid='/ as sysdba'
userid='/ as sysdba'
- Increased the redo log size from 1G to 3GB and created 5 groups instead of 3. This was done to reduce the log file sync waits.
- Increased the
PGA_AGGREGATE_LIMITfrom 2GB to 24GB to help reduce overall sorting operations when building the indexes.
- Increased the PGA_AGGREGATE_TARGET from 2GB to 12GB to help reduce overall sorting operations when building the indexes.
- Increased the SGA_TARGET from 4GB to 12GB to help reduce the I/O physical reads from disk.
I have an additional update that can be used to help optimize the process even further. I was able to optimize the constraint creation process in parallel as well and shave off about by another 7 hours! So now the entire import process went from about 32.25 hours down to about 4 hours of duration this is about a 88% decrease change in improvement now! I like that.
Here is what you need to do to optimize the constraint creation process.
- Separate the file index_constraint_script.sql into two files one for the indexes and one for the constraints.
- Enable a parallel degree on all tables in the tables you need to import into such as a degree of 12.
- Then again split the constraints script into two files again.
- The first of the constraint file should only have the following command to create the constraint with the novalidate clause. This will create the constraint instantly, an example is below.
- Enable parallel DDL in the same session prior to running the following step.
- The second constraint file should have the following syntax. This will allow the constraint to be created in parallel by checking for the parallel degree on the table/index.
Patch 22273229: IMPDP DOES NOT USE PARALLEL LOAD FOR PRIMARY KEY