Export/Import Validation Gotchas

Export/Import Validation Gotchas

When using Datapump, Original Export/Import or some other logical backup/restore mechanism, we need to validate the migrated databases to ensure all objects have been copied and that they are in the correct state (Valid/Invalid, Enabled/Disabled, etc).

The export and import logs ought to provide enough information for that validation; if we export/import one schema and both export and import have completed without errors then the migration has probably succeeded. However, the log files dont present information in a particularly helpful format – for example if an object is imported but wont compile the log file will contain some pretty ugly looking messages. And even if they are error-free, it can be awkward doing a comparison of an export and import log to identify issues.

So it is normal to present the required information in a standard format using predefined reports based on simple SQL queries running against the data dictionaries of the source and target databases.

But beware: I recently met issues in a couple of migrations that appeared to show discrepencies between the source and target databases. In both cases they turned out to be due to Bugs…

Bug# 9935857 – LOB Indexes are missing from DBA_OBJECTS


  • Source and target databases were queried by doing a count per object_type from the DBA_OBJECTS view.
  • Source Database – 11.1
  • Source Database Index count for the migrated schema : 629
  • Target Database  – 11.2
  • Target Database index count for the migrated schema: 648

Root Cause and Workaround:

I noticed straightaway that the difference in the number of indexes was 19, which happened to be how many LOB objects belonged to that schema. I poked around on MOS and found the bug, which was causing LOB indexes to be discounted when querying the DBA_OBJECTS view. The workaround was to count all indexes via the DBA_INDEXES view and to UNION join that to the count of DBA_OBJECTS…

SELECT … FROM dba_objects WHERE object_type <> ‘INDEX’…


SELECT … FROM dba_indexes…

References: Document 1582869.1 refers

Bug# 5523375 – expdp does not export disabled PK Constraints


  • Source Database – 11.1
  • Target Database  – 11.2
  • Two DISABLED PK constraints in a schema in the source database were missing from the target database after a datapump import.
  • No related errors in the export or import log files

Root Cause and Workarounds:

The root cause was found to be bug# 5523375, which is believed to affect releases prior to 11gR2. the bug is on the export side and the documented workaround is be to enable the PK before export, then disable it afterwards. The simpler solution is just to prepare a script to recreate the PK in its DISABLED state following import. Of course, if the PK is DISABLED then it is probably redundant, but in the migration project we cannot make that judgement and it is important not to get dragged into remediating application design issues if that isnt in the remit of our project.


My methodology requires that production migrations are rehearsed wherever possible and these kinds of issues underline the value of doing that – better to have time to research and explain these discrepancies and prepare a workaround following a rehearsal than to try and investigate and troubleshoot it in the middle of a live migration, with all of the stress and time limitations that suggests.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s