This content has been marked as final. Show 6 replies
user2601851 wrote:my car has a problem
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 22.214.171.124.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.
tell me how to make my car.
How do I ask a question on the forums?
SQL and PL/SQL FAQ
Pl provide details on how exactly you converted the database characterset from US7ASCII to AL32UTF8, along with the complete error message you get.1 person found this helpful
Queries Sporadically Fail with ORA-31011, ORA-19202, LPX-00210 After Character Set Migration to AL32UTF8 [ID 1313386.1]
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).
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.
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.
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
MOS reference was exactly what we needed.