Hi,
Recently we did full import of our Database from 10g to 11g. There are couple issues found during import.
1) The export activity took around 3 hrs to complete but Import took around 20hrs to complete.
2) Database size has grown large than it is expected.
I tried to debug the issue and find the solution and below are the details.
Issue1:
Tablespaces with partitioned objects have grown from
20GB to 100GB.
This is issue is due to new hidden parameter _partition_large_extents
introduced in 11gR2 specifically.
Solution:
Before start of import set the parameter
_partition_large_extents to FALSE
Issue2:
Import during TUP1:
full import of 10g database is done into
11g database as part of platform migration. This activity took around 20 hrs to
complete.
Identified Time consuming tasks:
1) We have partitoined table with around 4000+ partitions – 10 hrs – Due to
large number of partitions and many partitions have data in them.
2)
Table Statistics import – 6 hrs
3)
Index statistics - 2 hr
Solution:
To minimize the downtime, the full database import is split
and performed. Below is the complete set of activities tested with timing
details captured. This total activity has brought down the total time to 4 hrs.
Excluded partition tab;e and objects statistics
e.g.: Impdp full=y exclude=TABLE:<TABLE NAME>
2) Imp excluded objects into a different table Import excluded Partitioned object sepeartely with merge partition option
A separate table is created without any partitions using partitions=merge and remap_table options.
e.g.: impdp TABLES=<TABLE_NAME> REMAP_TABLE= SOURCENAME:TARGETNAME
Imported only metadata only of excluded source table along with partitions
e.g.: impdp CONTENT=METADATA_ONLY
#Import data from non partitioned table to partitioned table
This is estimated time and needs to be tested again.
