0

All imports/exports talked about here are using datapump, the 10.1.5 system is running on Windows Server 2003 and the 11.2 system is running on Linux.

I have an 11gig datapump dumpfile from a 10.1.5 database that I need to import into a 11.2.0 database and it's taking about 6 hours - much longer than I would expect. Once the data is imported, further exports/imports of the same schema on 11.2 run much faster - typically in about 25 minutes.

The import takes about between 5h30m and 6h30m hours on a reasonably high powered server. We've tried on a few different machines running Centos and Oracle Linux, with raided disks and with SSD's and nothing seems to have much impact on the time. (I've also tried various datapump options like parallel, or direct path with no impact). The logs look normal with no errors presented - it's just extremely slow.

The slowness occurs during the actual table data loading, rather than the index or constraint creation at the end.

Once I have the data imported, I can export it in about 25 minutes, and import it back in about the same and the file sizes produced by these exports are about the same size as the original but just load 5 times quicker.

The most likely explanation I can think of for this is that the datapump format has changed between 10 and 11 and there's some sort of conversion going on when I'm doing the initial import.

Does anyone have any ideas on what's going on? Or how I can further investigate what's happening to cause this?

Import command:

impdp xxxx/yyyy@zzz directory=backups dumpfile=PROD_AUG2015.DMP   EXCLUDE=TRIGGER,CONSTRAINT,GRANT

Thanks!

(Mostly irrelevant background info - this is for a time sensitive upgrade for a client on a remote island with fairly crappy internet. The data is coming from a system we have restricted access to and going into our own database. Due to the restricted access to their current production system it is a major exercise to make changes in the source database so trying to avoid that if possible)

  • Are you running exactly the same preparation process before the 10g import and the 11g one? (e.g. dropping/purging existing tables/tablespaces/schemas) – Mat Oct 19 '15 at 11:20
  • In order to use parallel you need to either specify all of the dmp files that are going to be used, or use the %U tag to number the dmp files. You should try using parallel of at least 4, if not more. You need the expdp to get done with parallel that is equal to what you use on the import. You should probably also consider dropping indexes and constraints before the import and then recreate the indexes and constraints after the import in parallel. – Gandolf989 Oct 19 '15 at 14:04
  • If you objects had small initial extents, You can try to create the metadata first with large initial extent sizes and then use ASSM configured on 11g. Create the size of the initial extents on the metadata for large tables/create tables first. Then understand which tables are taking the longest time and then focus on just those table object metadata. You can use parallelism but you have to export it out in parallel first with the %U so you create multiple files and then import it in in parallel. If you plan to do both methods I explained, make sure you import with DATA_ONLY content. – Data Flux Oct 19 '15 at 14:12
  • Mat: Yeah, I load into a new empty schema each time (using remap_schema). I have tried a few without the remap_schema to see if it had any impact on timing - it didn't – Dave Smylie Oct 19 '15 at 20:32
  • Gandolf989: Thanks. I misunderstood the parallel option and didn't realise it required multiple separate dump files. I will generate these and let you know. – Dave Smylie Oct 19 '15 at 20:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.