ORA-39346: Data Loss In Character Set Conversion During Import (IMPDP)

APPLIES TO:

Oracle Database - Standard Edition - Version 12.1.0.2 to 12.1.0.2 [Release 12.1]
Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information in this document applies to any platform.

SYMPTOMS

A datapump import, with the source database being 11.2.0.2 and the target 12.1.0.2, both with database character set AL32UTF8, reports:

ORA-39346: data loss in character set conversion for object DATABASE_EXPORT/SCHEMA/VIEW/VIEW
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY
ORA-39346: data loss in character set conversion for object DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_BODY
ORA-39346: data loss in character set conversion for object DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY

CAUSE

Invalid or corrupt characters are stored in the source database, usually in comments on objects.

The fix for Bug 13696958 added warning messages in 12.1 datapump import for characters that couldn't be converted so, while the characters may have been detected before 12.1, they wouldn't have been reported.

SOLUTION

Previously, a fix for Bug 16173664 was made available to report the affected object in the import log.
The fix was withdrawn and the Bug was superseded by unpublished Bug 21342624 - DATA LOSS DUE TO CHARACTER SET NOT SHOWING CORRECT SET OF OBJECTS DURING IMPORT.

- Apply interim patch 21342624 and run post-install step:

$ datapatch

- With the fix installed, redo the import, review the objects reported in the import log file and correct them. Look for and fix replacement characters (often an upside down '?' character) in the source environment.

The script below can be used to identify the rows containing non-printable characters:

set serveroutput on
declare
cursor c_comments is select owner,table_name,comments from dba_tab_comments where owner not in ('APEX_050100','APPQOSSYS','AUDSYS','CTXSYS','DBSNMP','FLOWS_FILES','MDSYS','OJVMSYS','ORDSYS','PERFSTAT','SYS','SYSTEM','WMSYS','XDB')
and comments is not null;
begin
for i in c_comments
loop
for j in 1..length(i.comments)
loop
if ascii(substr(i.comments,j,1))>128
then dbms_output.put_line(i.owner||'.'||i.table_name||' '||i.comments);
end if;
end loop;
end loop;
end;
/

set serveroutput on
declare
cursor c_comments is select owner,table_name,column_name,comments from dba_col_comments where owner not in ('APEX_050100','APPQOSSYS','AUDSYS','CTXSYS','DBSNMP','FLOWS_FILES','MDSYS','OJVMSYS','ORDSYS','PERFSTAT','SYS','SYSTEM','WMSYS','XDB')
and comments is not null;
begin
for i in c_comments
loop
for j in 1..length(i.comments)
loop
if ascii(substr(i.comments,j,1))>128
then dbms_output.put_line(i.owner||'.'||i.table_name||' '||i.column_name||' '||i.comments);
end if;
end loop;
end loop;
end;
/

set serveroutput on
declare
cursor c_text is select owner,name,text from dba_source where owner not in ('APEX_050100','APPQOSSYS','AUDSYS','CTXSYS','DBSNMP','FLOWS_FILES','MDSYS','OJVMSYS','ORDSYS','PERFSTAT','SYS','SYSTEM','WMSYS','XDB')
and type='PROCEDURE';
begin
for i in c_text
loop
for j in 1..length(i.text)
loop
if ascii(substr(i.text,j,1))>128
then dbms_output.put_line(i.owner||'.'||i.name||' '||i.text);
end if;
end loop;
end loop;
end;
/

Note:1/ With Patch 21342624 installed, impdp log will report the objects that are experiencing data loss during characterset conversion.
With the fix installed, the character data is checked even when the client and database character sets are the same, to prevent input of invalid character data.
The fix for Bug 21342624 does not stop the error messages or repair any bad data on the source database, they only allow identification of objects that have the bad data.

2/ To check for conflicting patches, 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.

- reference (Oracle Doc ID 1958604.1)