ORA-39365 Error Reported by DataPump Import (IMPDP) When SUPPLEMENTAL_LOG_DATA_MIN Is Set To YES (文档

APPLIES TO:

Oracle Database - Enterprise Edition - Version 12.1.0.1 and later
Information in this document applies to any platform.

GOAL

The following error message appears for the tables that have a column with datatype SDO_GEOMETRY:

ORA-39365: supplemental log data could not be generated for .<table_name>

when running a DataPump Import job in a 12c database with SUPPLEMENTAL_LOG_DATA_MIN set to "Y".

The impdp job runs without errors when the parameter SUPPLEMENTAL_LOG_DATA_MIN is set to "N".

Why does this parameter influence the impdp behavior?

SOLUTION

   The issue from unpublished Bug 16040580 : LGSB:SUPPLEMENTAL LOGGING CAUSES ORA-932 W/ IMPDP OF ADT TABLES, is observed with DB Supplemental logging ON and Direct-path loads of tables involving ADT Columns.
During DataPump import, supplemental log data could not be generated for tables of user-defined types or those with columns of such types. This issue has been investigated by Development in Bug 16040580, fixed starting with 12.1.0.1.

    When impdp based Direct-Loads are performed including any ADT type columns (including Multimedia/Spatial types), Supplemental logging is disabled for such columns and XML-Redo generation is disabled for the whole table,
causing SQL Apply (on the Logical standby) to abort.

The fix of Bug 16040580 disables generation of XML-Redo for the whole table if it has at least one ADT type column (including Multimedia/Spatial types).

In all Oracle releases which have the fix for Bug 16040580 included, DataPump will generate warnings/errors saying the supplemental logging information has not been generated for tables with an ADT column if supplemental logging is enabled.
Hence, a new error is introduced by this fix to report the warning : ORA-39365: Supplemental log data could not be generated for [table_name]

$ oerr ora-39365
39365, 00000, "supplemental log data could not be generated for %s"
// *Cause: The instance was unable to generate supplemental log data when  Data Pump was importing the table. Supplemental logging is not
// supported for Data Pump import of tables containing columns of user-defined types.
// *Action: No action is required for replication using Oracle GoldenGate.
// For logical standby users, perform a separate import of this table into the standby database.