Add new comment

As we move on to 12.2 and 19c we're seeing more instances where simple schema export/import migrations are missing objects on the target database.

If you do object counts after a simple schema migration... Or see something like this pop up in your impdp logs:

Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
SCHEMA.PK_CONSTRAINT_NAME : sqlerrm = ORA-20000: Unable to set values for index PK_INDEX_NAME: does not exist or insufficient privileges

The missing object and import error is happening because Oracle is invoking a 'smart optimization. It decided to skip creating a Unique index from the source database and instead substitute a functionally equivalent non-unique index with the same column list. It is possible for the substituted index to have the same columns but in a different order. So the net effect is that you are missing a Unique index on the target side but functionally your schema will mostly interact the same with applications. 3

Two concerns with allowing Oracle to “be smart” and allowing the optimization. The net effect is two indexes have been collapsed into one. Functionality should be fine, App will still work, here are two situations I can see arising:

1) Upgrade work. The DDL on the source database was supplied by the app developer. It is now different since the impdp optimization kicked in. If they decide to alter the DDL in the future they may get unexpected errors.
Example: If they decide to do drop or alter that additional index. In the old DDL they could drop it with no error. In the new target version they’ll get an error trying to drop now that it’s used by a PK constraint. You’ll get an ORA-02429: cannot drop index used for enforcement of unique/primary key
I’m more concerned about this case – interruptions to processing upgrade DDL scripts since that can bite us at a critical time in the future (during upgrade efforts that are already stressful).
2) Performance. The additional non-unique index was probably originally added for a reason.
Sure the columns are the same, just in a different order but if any query optimization depended on an index leading column for a limiting condition then that’s out the window.
Less concerned about this one.

So if these are your concerns how can you avoid the issue? Oracle Support document 1455492.1 suggests to perform the import in 2 passes. First pass with exclude=indexes and second one with include=indexes parameter. It worked and brought back my missing non-unique indexes but also created an extra index with system generated name.

-Dave