IMPDP, ORA-39083, ORA-02304 and the fix

 I was trying some PoC today with an impdp job involving sample schemas that I had in my lab database. The objective I was testing was to check on LONG column support for impdp via a network link (more of network link import in this link). 

I am aware LONG column is not supported in Oracle version 12.1 if we use network_link and is already documented in this official link but the same is mentioned as supported and as a new feature in the Oracle 12.2 official link here.

Well, testing makes us to be confident on what we are going to present to our clients, isn't it? :)

So in the due process of testing this, I just stumbled up on this error below. 

DB env: Oracle 12.2

Issue:

* Output truncated

[oracle@linux75-2 ~]$ impdp system REMAP_SCHEMA=pm:nwimp DIRECTORY=data_pump_dir NETWORK_LINK=nw_import_demo remap_tablespace=users:tt1 schemas=pm

Import: Release 12.2.0.1.0 - Production on Sun Jun 20 20:30:26 2021

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** REMAP_SCHEMA=pm:nwimp DIRECTORY=data_pump_dir NETWORK_LINK=nw_import_demo remap_tablespace=users:tt1 schemas=pm
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 18.81 MB
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"NWIMP" already exists

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
ORA-39083: Object type TYPE:"NWIMP"."ADHEADER_TYP" failed to create with error:
ORA-02304: invalid object identifier literal

Failing sql is:
CREATE EDITIONABLE TYPE "NWIMP"."ADHEADER_TYP"   OID '82A4AF6A4CCE656DE034080020E0EE3D'

  AS OBJECT
    ( header_name        VARCHAR2(256)
    , creation_date      DATE
    , header_text        VARCHAR2(1024)
    , logo               BLOB
    );

ORA-39083: Object type TYPE:"NWIMP"."TEXTDOC_TYP" failed to create with error:
ORA-02304: invalid object identifier literal
 ...
 ...
 ...

The IMPDP operation failed during the import of TYPE_SPEC and says invalid object identifier literal. 

This is a scenario which would occur when we try to duplicate a schema or duplicate objects and their dependent objects within the same database. What I'm trying here is importing a sample schema from source database via network_link which I already have in the target database into another schema and so in short, this also is sort of duplicating the schema.

When the types are exported, we also export the object_identifier (OID) of the types. Within the current architecture, the object-identifier needs to be unique in the database and the OID of the types already exist in target, the types can't be created and hence the error.

Solution: 

We can overcome this by precreating the type and tables in the target database under the schema nwimp and then import the schema with table_exists_action parameter set to append. 

or

Starting Oracle version 10.2, we have a parameter TRANSFORM which is used to alter object creation DDL for the objects being imported. So we will now use this parameter to tell Oracle not to perform OID checking when looking for an existing matching type on the target database and also to assign a new OID to the object. 

TRANSFORM=OID:N

* Output truncated

[oracle@linux75-2 ~]$ impdp system transform=oid:n REMAP_SCHEMA=pm:nwimp DIRECTORY=data_pump_dir NETWORK_LINK=nw_import_demo remap_tablespace=example:tt1 schemas=pm

Import: Release 12.2.0.1.0 - Production on Sun Jun 20 20:37:45 2021

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** transform=oid:n REMAP_SCHEMA=pm:nwimp DIRECTORY=data_pump_dir NETWORK_LINK=nw_import_demo remap_tablespace=example:tt1 schemas=pm
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 18.81 MB
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"NWIMP" already exists

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
ORA-31684: Object type TYPE:"NWIMP"."ADHEADER_TYP" already exists

ORA-31684: Object type TYPE:"NWIMP"."TEXTDOC_TYP" already exists

ORA-31684: Object type TYPE:"NWIMP"."TEXTDOC_TAB" already exists

Processing object type SCHEMA_EXPORT/TABLE/TABLE

. . imported "NWIMP"."PRINT_MEDIA"                            4 rows
. . imported "NWIMP"."TEXTDOCS_NESTEDTAB"                    12 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
...
...
...
 

Now, we can see the import says the objects are already existing and just continued with the import of table object and data without issues. 

By the way, I'm still pursuing my testing on long columns. That's all for today... :)

References: 

Data Pump Import (oracle.com)

Oracle Data Pump

DataPump Import Of Object Types Fails With Errors ORA-39083/ORA-39082 ORA-2304 Or ORA-39117 ORA-39779 (Doc ID 351519.1)

Happy importing...!!!