R3load cancelled with ORA-29275 partial multibyte character during DB export

[Former Member]

Dear all,

I am trying to move my system from 10G on AIX to 11G on windows platform however  while exporting the DB with R3load I am getting ORA-29275 error for some non-standard tables.

If I ignore those table from .TSK files or truncate on source system I am able to migrate this table with oracle exp/imp utility without any problem.

However I would like to fix this issue and prefer to use R3load for migrating the db.

And also source DB character set is UTF8. I have posted below related cmd,log and STR files of one of those tables.

If there is anyone came across same problem before or anyone have an idea about it I am looking forward to your comment.

Thanks for your help.

Eren.

SAPAPPL1_145.cmd

tsk: "/export/tmp/sapinst_instdir/BS2008/ERP604/LM/COPY/ORA/EXP/CENTRAL/AS-ABAP/EXP/SAPAPPL1_145.TSK"

icf: "/export/export/ABAP/DATA/SAPAPPL1_145.STR"

dcf: "/export/export/ABAP/DB/DDLORA.TPL"

dat: "/export/export/ABAP/DATA/" bs=1k fs=1000M

dir: "/export/export/ABAP/DATA/SAPAPPL1_145.TOC"

SAPAPPL1_145.log

/usr/sap/TMP/SYS/exe/run/R3load: START OF LOG: 20150114213141

/usr/sap/TMP/SYS/exe/run/R3load: sccsid @(#) $Id: //bas/720_REL/src/R3ld/R3load/R3ldmain.c#21 $ SAP

/usr/sap/TMP/SYS/exe/run/R3load: version R7.20/V1.4 [UNICODE]

Compiled Oct 11 2014 05:18:16

/usr/sap/TMP/SYS/exe/run/R3load -e SAPAPPL1_145.cmd -datacodepage 4103 SAPAPPL1_145.log -stop_on_error

(DB) INFO: connected to DB

(DB) INFO: DbSlControl(DBSL_CMD_NLS_CHARACTERSET_GET): UTF16

(DB) INFO: Export without hintfile

(GSI) INFO: dbname   = "TMP20090425060421                                                                                         

                    "

(GSI) INFO: vname    = "ORACLE                          "

(GSI) INFO: hostname = "turtest                                                         "

(GSI) INFO: sysname  = "AIX"

(GSI) INFO: nodename = "turtest"

(GSI) INFO: release  = "3"

(GSI) INFO: version  = "5"

(GSI) INFO: machine  = "00F6D1134C00"

(GSI) INFO: instno   = "0020183188"

DbSl Trace: Error 29275 in stmt_fetch() from oci_fetch_stmt(), orpc=90767

DbSl Trace: ORA-29275 occurred at SQL stmt (parse error offset=0)

(EXP) ERROR: DbSlExeRead failed

  rc = 99, table "ZDSAP_A_ABNLK"

  (SQL error 29275)

  error message returned by DbSl:

ORA-29275: partial multibyte character

(DB) INFO: disconnected from DB

/usr/sap/TMP/SYS/exe/run/R3load: job finished with 1 error(s)

/usr/sap/TMP/SYS/exe/run/R3load: END OF LOG: 20150114213147

SAPAPPL1_145.STR

tab: ZDSAP_A_ABNLK

att: APPL1   7  ?N T all    ZDSAP_A_ABNLK~0                APPL1   7

fld: ABONE_KODU                     CHAR    16     0   0 not_null  1

fld: ABONE_TAKIP_KODU               CHAR    16     0   0 not_null  0

fld: TSLMT_KISI_KODU                CHAR    16     0   0 not_null  0

fld: FATURA_KISI_KODU               CHAR    16     0   0 not_null  0

fld: TSLMT_ADRES_KODU               CHAR    16     0   0 not_null  0

fld: FATURA_ADRES_KOD               CHAR    16     0   0 not_null  0

fld: MALZEME_KODU                   CHAR    18     0   0 not_null  0

fld: TESLIMAT_ADEDI                 INT2     2     0   0 not_null  0

fld: BASLANGIC_TARIHI               DATS     8     0   0 not_null  0

fld: BITIS_TARIHI                   DATS     8     0   0 not_null  0

fld: ABONELIK_DURUMU                CHAR     4     0   0 not_null  0

fld: ABONE_TIPI                     CHAR     4     0   0 not_null  0

fld: ODEME_TIPI                     CHAR     4     0   0 not_null  0

fld: ODEME_TURU                     CHAR     4     0   0 not_null  0

fld: AB_KAYIT_TARIHI                DATS     8     0   0 not_null  0

fld: ABONE_TURU                     CHAR     4     0   0 not_null  0

fld: PERIYOT                        CHAR     4     0   0 not_null  0

fld: FATURA_DURUMU                  CHAR     4     0   0 not_null  0

fld: BIRIM_FIYAT                    CURR     7    13   2 not_null  0

fld: KOMISYON_ORANI                  DEC     3     4   4 not_null  0

fld: AT_KODU                        CHAR    16     0   0 not_null  0

fld: ACIKLAMA                       CHAR   255     0   0 not_null  0

fld: OZEL_NOT                       CHAR   255     0   0 not_null  0

fld: ABONE_ONAY                     CHAR     4     0   0 not_null  0

fld: ABONE_PROMOSYON                CHAR     4     0   0 not_null  0

fld: KAYIT_DURUMU                   CHAR     1     0   0 not_null  0

fld: SON_ISLEM_ZAMANI               INT4     4     0   0 not_null  0

fld: PARSEL_KODU                    CHAR    16     0   0 not_null  0

fld: UZTLMS_BIT_TARIH               DATS     8     0   0 not_null  0

fld: ABONELIK_KANALI                CHAR     4     0   0 not_null  0

fld: TESLIM_KISI                    CHAR   100     0   0 not_null  0

fld: SAYI_ADEDI                     INT4     4     0   0 not_null  0

fld: KALAN_SAYI_ADEDI               INT4     4     0   0 not_null  0

fld: ILK_SAYI                       CHAR    10     0   0 not_null  0

fld: KAMPANYA_TIPI                  CHAR     4     0   0 not_null  0

fld: GSM_OPR                        CHAR     4     0   0 not_null  0

fld: GSM_NO                         CHAR    15     0   0 not_null  0

fld: ONAY_DURUMU                    CHAR     4     0   0 not_null  0

fld: STS_ELMN_KODU                  CHAR    16     0   0 not_null  0

[Former Member]

Hi,

I would recommend to check this oracle note:

AL32UTF8 / UTF8 (Unicode) Database Character Set Implications (Doc ID 788156.1)

additionally please past the result of this:

SQL> select value from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET';

If you receive errors like "SP2-0784: Invalid or incomplete character beginning 0xC4 returned" or "ORA-29275: partial multibyte character" or "ORA-600 [kole_t2u], [34]" than this means that you are storing data in a character datatype and the *data* is NOT using AL32UTF8 encoding.

Any character data type like CHAR, VARCHAR2 , LONG and CLOB expect the data to be in the encoding defined by the NLS_CHARACTERSET.
Storing data in  CHAR, VARCHAR2 , LONG and CLOB dataypes in an encoding that is not the NLS_CHARACTERSET is not supported.
Any
data using a encoding different from the NLS_CHARACTERSET should be considered BINARY and a BINARY data types like RAW or BLOB should be used to store and process this.

-------------------------------------------------------------

So looks like you storing in you Z table in some fields data using different encoding to NLS_CHARACTERSET

before you can export the table you need to correct entries or change a datatype

[Former Member]

Dear Stanislav,

Now I am lookig at Metalink note 788156.1.

I think you are right stored data is in different character set from the DB.

But I need to find out how to convert improper data. Is there any way to check table contents?

I have posted result of SELECT * FROM NLS_DATABASE_PARAMETERS below

NLS_NCHAR_CHARACTERSET-->UTF8

NLS_LANGUAGE-->AMERICAN

NLS_TERRITORY-->AMERICA

NLS_CURRENCY-->$

NLS_ISO_CURRENCY-->AMERICA

NLS_NUMERIC_CHARACTERS-->.,

NLS_CHARACTERSET-->UTF8

NLS_CALENDAR-->GREGORIAN

NLS_DATE_FORMAT-->DD-MON-RR

NLS_DATE_LANGUAGE-->AMERICAN

NLS_SORT-->BINARY

NLS_TIME_FORMAT-->HH.MI.SSXFF-->AM

NLS_TIMESTAMP_FORMAT-->DD-MON-RR-->HH.MI.SSXFF-->AM

NLS_TIME_TZ_FORMAT-->HH.MI.SSXFF-->AM-->TZR

NLS_TIMESTAMP_TZ_FORMAT-->DD-MON-RR-->HH.MI.SSXFF-->AM-->TZR

NLS_DUAL_CURRENCY-->$

NLS_COMP-->BINARY

NLS_LENGTH_SEMANTICS-->BYTE

NLS_NCHAR_CONV_EXCP-->FALSE

NLS_RDBMS_VERSION-->10.2.0.4.0

[Former Member]

I think you should do some sql tricks to identify  the column,rows causing our headache

• select col1 from tablename; 
• select col2 from tablename; 
• select col<n> from tablename;

the one giving you an error ORA-29275 will give us an answer which column(s) is with wrong characterset

the find out which row is causing an issue:

select col1,col2 from <tablename> rownum between <lowerbound> and <upperbound>; 

possible fix is as written above use convert:

CONVERT(COLUMN NAME,'NLS_CHARACTERSET','NLS_CHARACTERSET'); 

or by

1876577  - ORA-29275 error when loading data to BW system

[Former Member]

Dear Stanislav,

It does not work I have run select statement for each column of table but none of them gave me an error ?

I have open an incident to SAP as well

Thanks in advance.

Best regards,

Eren.

[Former Member]

And select * from sapsr3.<table name >?

Replace sapsr3 worth your schema name

[Former Member]

Dear Stanislav,

it works as well without any error related with ORA-29275.

I have opened a ticket to SAP. If they help me to solve this issue I share with everyone.

Have a good day...

Eren.

[former_member276031]

any feedback about the error ？ share it with me, I get stucked with it too.

[divyanshu_srivastava3]

Hi,

Have you tried converting the table to target character set ?

1st take the backup of these tables.

2nd convert them using 'select convert'

3rd start with DB export.

A similar issue is shared in below KBA, see if this helps.

1876577 - ORA-29275 error when loading data to BW system

Regards,

[Former Member]

Dear Srivastava,

Actually I found it this note but it is not cleat enough for me?

select dump ('a', 1010) from dual ;

Typ=96 Len=1 CharacterSet=UTF8: 97

But  I am not sure what the source and taget characterset value are. Bucase I am just trying to export table, target system not known by the R3load.

What is your opinion for this?

select convert ('Española', 'UTF8', 'UTF8') from dual;

Española