This discussion is archived
7 Replies Latest reply: Feb 16, 2013 9:25 PM by 987440 RSS

Unable to migrate WE8ISO8859P1 to ALUTF8 *Unrecognized convertible date*

780435 Newbie
Currently Being Moderated
Hi

..have followed all steps in migration guide
create CSMIG
take backup (used datapump)
run cs scan
stopped db
startup restrict
run csalter.plb
the script fails with error:
Unrecognized convertible date found in scanner result


database: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

scan output:

scan.err file:

Database Scan Individual Exception Report


[Database Scan Parameters]

Parameter Value
------------------------------ ------------------------------------------------
CSSCAN Version v2.1
Instance Name itempl
Database Version 10.2.0.1.0
Scan type Full database
Scan CHAR data? YES
Database character set WE8ISO8859P1
FROMCHAR WE8ISO8859P1
TOCHAR AL32UTF8
Scan NCHAR data? NO
Array fetch buffer size 10240
Number of processes 3
Capture convertible data? NO
------------------------------ ------------------------------------------------

[Data Dictionary individual exceptions]


[Application data individual exceptions]



scan.txt:

Database Scan Summary Report

Time Started : 2010-06-20 23:48:40
Time Completed: 2010-06-20 23:50:33

Process ID Time Started Time Completed
---------- -------------------- --------------------
1 2010-06-20 23:48:41 2010-06-20 23:50:31
2 2010-06-20 23:48:41 2010-06-20 23:50:31
3 2010-06-20 23:48:43 2010-06-20 23:50:31
---------- -------------------- --------------------

[Database Size]

Tablespace Used Free Total Expansion
------------------------- --------------- --------------- --------------- ---------------
SYSTEM 488.38M 21.63M 510.00M 984.00K
UNDOTBS1 20.94M 9.06M 30.00M .00K
SYSAUX 349.44M 10.56M 360.00M 9.52M
TEMP .00K .00K .00K .00K
USERS 22.69M 1.06M 23.75M 10.00K
EXAMPLE 68.25M 31.75M 100.00M 3.00K
------------------------- --------------- --------------- --------------- ---------------
Total 949.69M 74.06M 1,023.75M 10.50M

The size of the largest CLOB is 1625114 bytes

[Database Scan Parameters]

Parameter Value
------------------------------ ------------------------------------------------
CSSCAN Version v2.1
Instance Name itempl
Database Version 10.2.0.1.0
Scan type Full database
Scan CHAR data? YES
Database character set WE8ISO8859P1
FROMCHAR WE8ISO8859P1
TOCHAR AL32UTF8
Scan NCHAR data? NO
Array fetch buffer size 10240
Number of processes 3
Capture convertible data? NO
------------------------------ ------------------------------------------------

[Scan Summary]

All character type data in the data dictionary are convertible to the new character set
All character type application data are convertible to the new character set

[Data Dictionary Conversion Summary]

Datatype Changeless Convertible Truncation Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2 2,983,218 0 0 0
CHAR 1,097 0 0 0
LONG 149,525 0 0 0
CLOB 31,468 2,071 0 0
VARRAY 22,366 0 0 0
--------------------- ---------------- ---------------- ---------------- ----------------
Total 3,187,674 2,071 0 0
Total in percentage 99.935% 0.065% 0.000% 0.000%

The data dictionary can be safely migrated using the CSALTER script

[Application Data Conversion Summary]

Datatype Changeless Convertible Truncation Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2 1,730,333 1,465 0 0
CHAR 59,586 0 0 0
LONG 0 0 0 0
CLOB 41 12 0 0
VARRAY 2,019 0 0 0
--------------------- ---------------- ---------------- ---------------- ----------------
Total 1,791,979 1,477 0 0
Total in percentage 99.918% 0.082% 0.000% 0.000%

[Distribution of Convertible, Truncated and Lossy Data by Table]

USER.TABLE Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
ITEMPLDB.EMP 1 0 0
MDSYS.SDO_COORD_OP_PARAM_VALS 200 0 0
MDSYS.SDO_GEOR_XMLSCHEMA_TABLE 1 0 0
MDSYS.SDO_STYLES_TABLE 78 0 0
MDSYS.SDO_XML_SCHEMAS 4 0 0
OE.WAREHOUSES 4 0 0
PM.PRINT_MEDIA 8 0 0
SYS.EXPIMP_TTS_CT$ 8 0 0
SYS.EXTERNAL_TAB$ 1 0 0
SYS.METASTYLESHEET 80 0 0
SYS.RULE$ 4 0 0
SYS.WRH$_SQLTEXT 607 0 0
SYS.WRH$_SQL_PLAN 532 0 0
SYS.WRI$_ADV_ACTIONS 22 0 0
SYS.WRI$_ADV_OBJECTS 418 0 0
SYS.WRI$_ADV_RATIONALE 7 0 0
SYS.WRI$_DBU_FEATURE_METADATA 90 0 0
SYS.WRI$_DBU_FEATURE_USAGE 2 0 0
SYS.WRI$_DBU_HWM_METADATA 17 0 0
SYSMAN.MGMT_HC_OS_COMPONENTS 8 0 0
SYSMAN.MGMT_HC_VENDOR_SW_COMPONENTS 8 0 0
-------------------------------------------------- ---------------- ---------------- ----------------

[Distribution of Convertible, Truncated and Lossy Data by Column]

USER.TABLE|COLUMN Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
ITEMPLDB.EMP|ENAME 1 0 0
MDSYS.SDO_COORD_OP_PARAM_VALS|PARAM_VALUE_FILE 200 0 0
MDSYS.SDO_GEOR_XMLSCHEMA_TABLE|XMLSCHEMA 1 0 0
MDSYS.SDO_STYLES_TABLE|DEFINITION 78 0 0
MDSYS.SDO_XML_SCHEMAS|XMLSCHEMA 4 0 0
OE.WAREHOUSES|SYS_NC00003$ 4 0 0
PM.PRINT_MEDIA|AD_FINALTEXT 4 0 0
PM.PRINT_MEDIA|AD_SOURCETEXT 4 0 0
SYS.EXPIMP_TTS_CT$|XMLINFO 8 0 0
SYS.EXTERNAL_TAB$|PARAM_CLOB 1 0 0
SYS.METASTYLESHEET|STYLESHEET 80 0 0
SYS.RULE$|CONDITION 4 0 0
SYS.WRH$_SQLTEXT|SQL_TEXT 607 0 0
SYS.WRH$_SQL_PLAN|OTHER_XML 532 0 0
SYS.WRI$_ADV_ACTIONS|ATTR5 11 0 0
SYS.WRI$_ADV_ACTIONS|ATTR6 11 0 0
SYS.WRI$_ADV_OBJECTS|ATTR4 418 0 0
SYS.WRI$_ADV_RATIONALE|ATTR5 7 0 0
SYS.WRI$_DBU_FEATURE_METADATA|INST_CHK_LOGIC 11 0 0
SYS.WRI$_DBU_FEATURE_METADATA|USG_DET_LOGIC 79 0 0
SYS.WRI$_DBU_FEATURE_USAGE|FEATURE_INFO 2 0 0
SYS.WRI$_DBU_HWM_METADATA|LOGIC 17 0 0
SYSMAN.MGMT_HC_OS_COMPONENTS|DESCRIPTION 8 0 0
SYSMAN.MGMT_HC_VENDOR_SW_COMPONENTS|DESCRIPTION 8 0 0
-------------------------------------------------- ---------------- ---------------- ----------------

[Indexes to be Rebuilt]

USER.INDEX on USER.TABLE(COLUMN)
-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------



******* need help..
  • 1. Re: Unable to migrate WE8ISO8859P1 to ALUTF8 *Unrecognized convertible date*
    Gunther Vermeir, Oracle Journeyer
    Currently Being Moderated
    That is normal

    To use Csalter the Csscan output needs to be

    * changeless for all CHAR VARCHAR2, and LONG data (Data Dictionary and Application Data)
    * changeless for all Application Data CLOB
    * changeless and/or convertible for all Data Dictionary CLOB

    And in order to run Csalter you need to see in the charcheck.txt file under [Scan Summary] this message:
    All character type application data remain the same in the new character set
    and under [Data Dictionary Conversion Summary] this message:
    The data dictionary can be safely migrated using the CSALTER script

    If you run Csalter without these conditions met then you will see messages like " Unrecognized convertible data found in scanner result " in the Csalter output.


    Kindly check these 2 notes when going to UTF8/AL32UTF8

    <Note:260192.1> Changing the NLS_CHARACTERSET to AL32UTF8 / UTF8 (Unicode)
    <Note:788156.1> AL32UTF8 / UTF8 (Unicode) Database Character Set Implications
  • 2. Re: Unable to migrate WE8ISO8859P1 to ALUTF8 *Unrecognized convertible date*
    780435 Newbie
    Currently Being Moderated
    Thanks for response!
    yes i've checked these in the scan messages:

    All character type data in the data dictionary are convertible to the new character set
    All character type application data are convertible to the new character set

    The data dictionary can be safely migrated using the CSALTER script


    ..were written to the log file. i've also posted the complete log for same reason. what could possibly the reason of failure be inspite of these messages?

    thanks
    Shwetank
  • 3. Re: Unable to migrate WE8ISO8859P1 to ALUTF8 *Unrecognized convertible date*
    Gunther Vermeir, Oracle Journeyer
    Currently Being Moderated
    Like said, to use Csalter you need

    All character type application data remain the same in the new character set
    not the message you have
    All character type application data are convertible to the new character set

    you can at this stage use full export/import,
    in order to use Csalter you need to export all Convertible Application data

    Kindly have a look at the note I refrenced.

    Gunther
  • 4. Re: Unable to migrate WE8ISO8859P1 to ALUTF8 *Unrecognized convertible date*
    Sergiusz Wolicki (Oracle) Expert
    Currently Being Moderated
    Convertible application data means that you will encounter no problems migrating with export/import, but you cannot use CSALTER. Changeless application data allows for CSALTER. Convertible data dictionary data is supported by CSALTER if it is in CLOB columns only. Otherwise, you need exp/imp as well.


    -- Sergiusz
  • 5. Re: Unable to migrate WE8ISO8859P1 to ALUTF8 *Unrecognized convertible date*
    987440 Newbie
    Currently Being Moderated
    Hi Sergiusz,

    I am also into a similar situation. CSALTER results into:

    . . .
    Checking data validity...
    Unrecognized convertible data found in scanner result

    PL/SQL procedure successfully completed.

    Checking or Converting phase did not finish successfully
    No database (national) character set will be altered
    CSALTER finished unsuccessfully.
    . . .


    After this I tried altering the database character set:

    SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;

    Database altered.


    Is this a correct way ?
    I was able to convert the database character set but I still curious to know if it is the right way to do it.

    Any suggestion would be a great help.

    Thanks,
  • 6. Re: Unable to migrate WE8ISO8859P1 to ALUTF8 *Unrecognized convertible date*
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    984437 wrote:
    Hi Sergiusz,

    I am also into a similar situation. CSALTER results into:
    Pl post details of OS and database versions, along with the parameters passed to the csalter utility.
    . . .
    Checking data validity...
    Unrecognized convertible data found in scanner result

    PL/SQL procedure successfully completed.

    Checking or Converting phase did not finish successfully
    No database (national) character set will be altered
    CSALTER finished unsuccessfully.
    . . .


    After this I tried altering the database character set:

    SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;

    Database altered.
    This command is undocumented/unsupported and should not be used - it will likely corrupt your database beyond repair - hopefully you have a good backup of the database that you can recover from.

    https://forums.oracle.com/forums/search.jspa?threadID=&q=INTERNAL_USE&objID=f50&dateRange=all&userID=&numResults=15

    >
    Is this a correct way ?
    I was able to convert the database character set but I still curious to know if it is the right way to do it.

    Any suggestion would be a great help.

    Thanks,
    Also, pl do not hijack an old post - pl post your issue in a new thread.

    HTH
    Srini
  • 7. Re: Unable to migrate WE8ISO8859P1 to ALUTF8 *Unrecognized convertible date*
    987440 Newbie
    Currently Being Moderated
    Hi Srini,

    My apologies for not posting my issue in a new thread.

    Now I have posted my issue in a new thread: Confusion in Migrating to AL32UTF8 database character set using CSSCAN

    Thanks for correcting me.


    Thanks.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points