This discussion is archived
6 Replies Latest reply: Feb 12, 2013 7:35 AM by 860897 RSS

Character set Conversion (US7ASCII to AL32UTF8) -- ORA-31011 problem

860897 Newbie
Currently Being Moderated
Hello,

We've run into some problems as part of our character set conversion from US7ASCII to AL32UTF8. The latest problem is that we have a query that works in US7ASCII, but after converting to AL32UTF8 it no longer works and generates an ORA-31011 error. This is very concerning to us as this error indicates an XML parsing problem and we are doing no XML whatsoever in our DB. We do not have XML columns (nor even CLOBs or BLOBs) nor XML tables and it's not XMLDB.

For reference, we're running 11.2.0.2.0 over Solaris.

Has anyone seen this kind of problem before?

If need be, I'll find a way to post table definitions. However, it's safe to assume that we are only using DATE, VARCHAR2 and NUMBER column types in these tables. All of the tables are local to the DB.


Thanks
  • 1. Re: Character set Conversion (US7ASCII to AL32UTF8) -- ORA-31011 problem
    sb92075 Guru
    Currently Being Moderated
    user2601851 wrote:
    Hello,

    We've run into some problems as part of our character set conversion from US7ASCII to AL32UTF8. The latest problem is that we have a query that works in US7ASCII, but after converting to AL32UTF8 it no longer works and generates an ORA-31011 error. This is very concerning to us as this error indicates an XML parsing problem and we are doing no XML whatsoever in our DB. We do not have XML columns (nor even CLOBs or BLOBs) nor XML tables and it's not XMLDB.

    For reference, we're running 11.2.0.2.0 over Solaris.

    Has anyone seen this kind of problem before?

    If need be, I'll find a way to post table definitions. However, it's safe to assume that we are only using DATE, VARCHAR2 and NUMBER column types in these tables. All of the tables are local to the DB.


    Thanks
    my car has a problem
    tell me how to make my car.

    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 2. Re: Character set Conversion (US7ASCII to AL32UTF8) -- ORA-31011 problem
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Pl provide details on how exactly you converted the database characterset from US7ASCII to AL32UTF8, along with the complete error message you get.

    Queries Sporadically Fail with ORA-31011, ORA-19202, LPX-00210 After Character Set Migration to AL32UTF8 [ID 1313386.1]

    HTH
    Srini
  • 3. Re: Character set Conversion (US7ASCII to AL32UTF8) -- ORA-31011 problem
    860897 Newbie
    Currently Being Moderated
    We converted using the database using scripts I developed. I'm not quite sure how we converted is relevant, other than saying that we did not use the Oracle conversion utility (not csscan, but the GUI Java tool).

    A summary:

    1) We replaced the lossy characters by parsing a csscan output file
    2) After re-scanning with csscan and coming up clean, our DBA converted the database to AL32UTF8 (changed the parameter file, changing the character set, switched the semantics to char, etc).
    3) Final step was changing existing tables to use char semantics by changing the table schema for VARCHAR2 columns

    Any specific steps I cannot easily answer, I worked with a DBA at our company to do this work. I handled the character replacement / DDL changes and the DBA ran csscan & performed the database config changes.

    Our actual error message:

    ORA-31011: XML parsing failed
    ORA-19202: Error occurred in XML processing
    LPX-00210: expected '<' instead of '´┐ŻError at line 1
    31011. 00000 - "XML parsing failed"
    *Cause:    XML parser returned an error while trying to parse the document.
    *Action:   Check if the document to be parsed is valid.
    Error at Line: 24 Column: 15


    This seems to match the the document ID referenced below. I will ask our DBA to pull it up and review it.

    Please advise if more information is needed from my end.
  • 4. Re: Character set Conversion (US7ASCII to AL32UTF8) -- ORA-31011 problem
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Pl elaborate on what exactly you mean by "(changed the parameter file, changing the character set, switched the semantics to char, etc)"

    AFAIK, the only two supported methods of changing the characterset are using the csalter utility or the DMU tool. Why would you not use a supported method ? Any other methods may leave your database corrupted beyond repair.

    If the MOS Doc is not relevant/helpful, hopefully you have a good backup of the database taken before the "conversion" - you will likely need this backup to re-do the conversion in a supported manner.

    HTH
    Srini
  • 5. Re: Character set Conversion (US7ASCII to AL32UTF8) -- ORA-31011 problem
    860897 Newbie
    Currently Being Moderated
    Perhaps our DBA used the csalter tool to do all of the changes. However, from looking over various whitepapers on converting, csalter will not fix lossy or truncatable rows, and will not proceed with the conversion until they're handled, so either you fix those data issues yourself (which is what we did re: custom scripting) or you use the DMU tool.

    The MOS Doc was very helpful and exactly relevant and sorted out this particular issue. We have already performed the conversion but had to roll back due to other reasons (have an SR opened for that). This XML issue came up when one customer realized they had never tested a particular query and notified us after we rolled back the first time.

    Thanks for your help!

    Edited by: user2601851 on Feb 12, 2013 7:33 AM
  • 6. Re: Character set Conversion (US7ASCII to AL32UTF8) -- ORA-31011 problem
    860897 Newbie
    Currently Being Moderated
    MOS reference was exactly what we needed.

Legend

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