Is it supported to do a Transport Tablespace (TTS) Import with Data Pump on a tablespace with binary XML objects? Doc ID 1908140.1

APPLIES TO:

Enterprise Manager for Oracle Database - Version 10.1.0.2 to 12.1.0.8.0 [Release 10.1 to 12.1]
Oracle Database - Enterprise Edition - Version 10.1.0.2 to 12.2.0.1 [Release 10.1 to 12.2]
Oracle Database - Personal Edition - Version 10.1.0.2 to 12.2.0.1 [Release 10.1 to 12.2]
Oracle Database - Standard Edition - Version 10.1.0.2 to 12.2.0.1 [Release 10.1 to 12.2]
Information in this document applies to any platform.

GOAL

This document describes whether it is supported to do a Transport Tablespace (TTS) operation on a tablespace with binary XML objects.
 

SOLUTION

1. We (oracle) do not support Transport Tablespace Operations for binary XML for the following releases: 11.2.0.4 and 12.1.0.1/12.1.0.2.

- Starting with 12.2.0.1 there is almost complete support for tablespace and fully transportable mode of TTS.
However, in 12.2.0.1, when exporting and importing a large dataset when an XMLIndex is present on the source table, we recommend either applying a patch for unpublished BUG 23333562 - "XDB:CDB:TTS IMPORT TABLESPACE LOW PERFORMANCE WITH 9-11HRS TO COMPLETE" (fixed in 18.1), or use the workaround given be Development to exclude XMLIndex at import using DataPump parameter file. For example,  add EXCLUDE=INDEX:"LIKE 'HR_XMLTAB_IDX%' " in a parameter file and then use the parfile for to import.
This will avoid per-row index maintenance on the XML document during import when transformed to the destination encoding scheme. After excluding the indexes and when import is complete, the index can be created and rebuilt as a whole.

To check for conflicting patches when applying a patch for unpublished BUG 23333562, please use the MOS Patch Planner Tool. Please refer to Note 1317012.1 - How To Use MOS Patch Planner To Check And Request The Conflict Patches?
If no patch exists for your version, please contact Oracle Support for a backport request.

- For versions < 12.2, see the steps below:

2. If the Transport Tablespace Import Data Pump job indeed doesn't work, the following error should be seen during the import job:

ORA-39945: Token conflicting with existing tokens.

Workaround: use Export Data Pump (expdp) and Import Data Pump (impdp), i.e. non-transportable data Pump jobs, for the tables containing binary XML data.

3. Other possible errors during the Import Data Pump job:

...
Processing object type TRANSPORTABLE_EXPORT/PROCACT_INSTANCE
ORA-39083: Object type PROCACT_INSTANCE failed to create with error:
ORA-39945: Token conflicting with existing tokens.
Failing sql is:
BEGIN 
BEGIN
 xdb.dbms_csx_int.ImportMergeTokenTables;
END;
COMMIT; END; 
Processing object type TRANSPORTABLE_EXPORT/TABLE
ORA-39139: Data Pump does not support XMLType objects in version "SCOTT"."MY_XMLTAB".  will be skipped.
...
-- combined with following error in the alert logfile: --
ORA-00600: internal error code, arguments: [qmcxdGetQNameInfo2], [], [], [], [], [], [], [], [], [], [], []
-- or: --
ORA-39083: Object type TABLE:"SCOTT"."MY_XMLTAB" failed to create with error:
ORA-39945: Token conflicting with existing tokens.
-- or import aborts without error, while the alert logfile shows: --
ORA-07445: exception encountered: core dump [qmixdMarkAsSecondary()+55] [SIGSEGV] [ADDR:0x10] [PC:0x7339327] [Address not mapped to object] []

4. It is also possible that no errors are reported during the import job, but later in the target database, one of the following errors are reported:

ORA-00600: internal error code, arguments: [qmcxdsDecodeOpc6], [], [], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [QMCXDGETQNAMEINFO2], [], [], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [qmcxdEvtNextPPP3], [], [], [], [], [], [], [], [], [], [], []
OCI-21500: internal error code, arguments: [qmcxdEvtNextPPP3], [], [], [], [], [], [], []
ORA-21500: internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]

-- or when creating a csx table: --

ERROR at line 5:
ORA-00604: error occurred at recursive SQL level 1
ORA-31061: XDB error: dbms_xdbutil_int.get_tablespace_tab
ORA-06512: at "XDB.DBMS_XDBUTIL_INT", line 1013

5. When the source database is Oracle 11gR2 release 11.2.0.3 and the tablespace contains binary XML, ensure that you have installed the Patch 17260804 on the source database before starting the Export Data Pump jobs.

NOTE:
This consolidated Patch 17260804 is for using binary xml export as such.
Transportable tablespace operations for binary XML, are still not supported, as already stated.

References:
Bug 17260804 - CONSOLIDATED FIX FOR 16178854,16270726,14580295,16270061 &14490576 ON 11.2.0.3 (not a public bug)

Bug 14534333 - CDB: XDB: 33159 QUERY CSX TABLE AFTER IMPORT TTS FROM 11.2.0.3 TO PDB (not a public bug), fix superseded by fix for Bug 16178854
Bug 16178854 - FIX FOR BUG 14534333 NEEDS TO BE REDONE (not a public bug), fixed in 11.2.0.4, 12.1.0.1
Bug 16270726 - XDB 33159 TTS FROM 11.2.0.4.0: ORA-31061 CREATE CSX TABLE AFTER IMPORT (not a public bug), fixed in 11.2.0.4 and 12.1.0.2 and 12.2.0.1
Bug 14580295 - NLS:CDB:USE TRANSPORT TABLESPACE IMPDP XMLTYPE ORA-00600 QMCXDGETQNAMEINFO2 (not a public bug), fixed in 12.1.0.2
Bug 16270061 - XDB: 33159 TTS FROM 11.2.0.4.0 ORA-39945 IMPORT EMPTY CSX TABLE (not a public bug), fixed in 11.2.0.4 and 12.1.0.2 and 12.2.0.1
Bug 14490576 - CDB: XDB: 33159 IMPDP TTS ORA-07445 [QMIXDMARKASSECONDARY()+55] (not a public bug), fixed in 11.2.0.4 and 12.1.0.1

6. Other references:

Note 1903041.1 - Unable to perform TTS to export/import of tables with Binary XML data
Note 1454872.1 - Transportable Tablespace (TTS) Restrictions and Limitations: Details, Reference, and Version Where Applicable (Doc ID 1454872.1)
Note 1989198.1 - TTS changed XMLTYPE from CLOB to Binary, XML Tag Syntax are Changed

REFERENCES

NOTE:1989198.1 - TTS changed XMLTYPE from CLOB to Binary, XML Tag Syntax are Changed
NOTE:1454872.1 - Transportable Tablespace (TTS) Restrictions and Limitations: Details, Reference, and Version Where Applicable
NOTE:1903041.1 - Unable to perform TTS to export/import of tables with Binary XML data