Wikipedia

Search results

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)

16 comments:

  1. The applicants get an electronically sharable testament on effectively finishing the course and clearing an online test. ExcelR Data Science Courses

    ReplyDelete
  2. I just got to this amazing site not long ago. I was actually captured with the piece of resources you have got here. Big thumbs up for making such wonderful blog page!

    data science course

    ReplyDelete
  3. Awesome blog. I enjoyed reading your articles. This is truly a great read for me. I have bookmarked it and I am looking forward to reading new articles. Keep up the good work!

    Correlation vs Covariance

    ReplyDelete
  4. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Correlation vs Covariance
    Simple linear regression

    ReplyDelete
  5. I just got to this amazing site not long ago. I was actually captured with the piece of resources you have got here. Big thumbs up for making such wonderful blog page!

    Simple Linear Regression

    Correlation vs Covariance

    ReplyDelete
  6. Amazing Article ! I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Simple Linear Regression
    Correlation vs covariance
    data science interview questions
    KNN Algorithm

    ReplyDelete
  7. This is a wonderful article, Given so much info in it, These type of articles keeps the users interest in the website, and keep on sharing more ... good luck.

    Simple Linear Regression

    Correlation vs covariance

    KNN Algorithm

    Logistic Regression explained

    ReplyDelete
  8. Really nice and interesting post. I was looking for this kind of information and enjoyed reading this one. Keep posting. Thanks for sharing.

    data science interview questions

    ReplyDelete
  9. A Computer Scientist figures out how to plan programming frameworks and gains top to bottom learning of the hypothesis of calculationdata scientist courses

    ReplyDelete
  10. I am looking for and I love to post a comment that "The content of your post is awesome" Great work!

    Simple Linear Regression

    Correlation vs Covariance

    ReplyDelete
  11. Thank you for sharing wonderful information with us.Really useful for everyonedata scientist courses

    ReplyDelete
  12. Going to graduate school was a positive decision for me. I enjoyed the coursework, the presentations, the fellow students, and the professors. And since my company reimbursed 100% of the tuition, the only cost that I had to pay on my own was for books and supplies. Otherwise, I received a free master’s degree. All that I had to invest was my time. data scientist course in surat

    ReplyDelete
  13. A good blog always comes-up with new and exciting information and while reading I have feel that this blog is really have all those quality that qualify a blog to be a one. data science course in surat

    ReplyDelete
  14. Such a helpful article. Interesting to peruse this article.I might want to thank you for the endeavors you had made for composing this wonderful article.
    data scientist training in hyderabad

    ReplyDelete
  15. It would help if you thought that the data scientists are the highest-paid employees in a company.data science course in kochi

    ReplyDelete