Unable to export char semantic 10.2 database

22 November 2005 at 10:00 CET | In Bugs and issues, Database, Oracle |

We’re in the middle of converting our 8.1.7.4 database to 10g release 2 (10.2). With this migration we’re also changing the character set of the database to AL32UTF8 and changing the database to char semantics. This gave us serious problems trying to export the database.

First a short introduction on the choice of character set and length semantics: Using Unicode as a character set will allow us to store any character and not just the western European characters we’re currently limited to. Unicode is a multi-byte character set which means that a single character could use more than a single byte. So what do you mean by VARCHAR2(10)? Is this 10 characters or 10 bytes? This used to be the same with our single byte character set, but these days are over. You can specify VARCHAR2(10 BYTE) or VARCHAR2(10 CHAR) if you want to be specific. However, all of our existing code doesn’t use this syntax. This is where the NLS_LENGTH_SEMANTICS parameter comes in. You can set this to BYTE or CHAR basically defining which one of the two is the default when not specifying it explicitly. A default installation will use BYTE semantics, but we decided to change it to CHAR. To me that is the more logical option.

So, we created a fresh database with NLS_LENGTH_SEMANTICS=CHAR. If you then run an export:

C:\>set ORACLE_SID=dev01

C:\>set NLS_LANG=AMERICAN_AMERICA.AL32UTF8

C:\>exp '/ as sysdba' full=y file=full.dmp

Export: Release 10.2.0.1.0 - Production on Tue Nov 22 09:48:06

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to: Personal Oracle Database 10g Release 10.2.0.1.0
With the Partitioning, OLAP and Data Mining options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR
character set

About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions
. exporting system procedural objects and actions
. exporting pre-schema procedural objects and actions
. exporting cluster definitions
EXP-00056: ORACLE error 932 encountered
ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR
EXP-00056: ORACLE error 932 encountered
ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR
EXP-00000: Export terminated unsuccessfully

Searching MetaLink made me find Note 339938.1 which describes a situation where a 10.2 full export fails with an ORA-932 when exporting the cluster definitions. I figured I found the issue and a solution. But the note describes that some SYS objects should be invalid whereas all my SYS objects are valid. It also describes that a SELECT on sys.ku$_xmlschema_view should fail. This one does fail on me:

SQL> select url, local, stripped_val from sys.ku$_xmlschema_view;
select url, local, stripped_val from sys.ku$_xmlschema_view
                   *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR

Now it was time to open a Service Request (the new name for a TAR) on MetaLink. In the end the support engineer came with the solution. It all boiled down to two pieces of advice:

  • only switch to char semantics after database creation (that has no impact on the “char semantics support”, any SYS objects cannot be CHAR and that is what happened here)
  • use a “startup migrate” when running any patch or catalog scripts

So I shouldn’t have set NLS_LENGTH_SEMANTICS=CHAR before creating the database. The second advice to always use STARTUP MIGRATE is also a good tip. This will force the system of doing anything in byte semantics.

Here are the steps to “repair” my database. First check that the NLS_LENGTH_SEMANTICS is indeed set to CHAR. Then shutdown the database and restart as “startup migrate” You can see that the length semantics are now BYTE:

C:\>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 22 10:00:46

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Personal Oracle Database 10g Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> column parameter format a20
SQL> column value format a5
SQL> select * from nls_session_parameters
  2  where PARAMETER='NLS_LENGTH_SEMANTICS';

PARAMETER            VALUE
-------------------- -----
NLS_LENGTH_SEMANTICS CHAR

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup migrate;
ORACLE instance started.

Total System Global Area  469762048 bytes
Fixed Size                  1249608 bytes
Variable Size             146804408 bytes
Database Buffers          314572800 bytes
Redo Buffers                7135232 bytes
Database mounted.
Database opened.
SQL> select * from nls_session_parameters
  2  where PARAMETER='NLS_LENGTH_SEMANTICS';

PARAMETER            VALUE
-------------------- -----
NLS_LENGTH_SEMANTICS BYTE

Now drop the XML DB stuff. After that exit SQL*Plus. There is some bug that screws up your session. This means you’ll have to close SQL*Plus at the end:

SQL> @?\RDBMS\ADMIN\catnoqm.sql

PL/SQL procedure successfully completed.

User dropped.

PL/SQL procedure successfully completed.

Table dropped.

SQL> exit
ERROR:
ORA-00600: internal error code, arguments: [729], [256],
[space leak], [], [], [], [], []

Disconnected from Personal Oracle Database 10g Release 10.2.0.1.0
With the Partitioning, OLAP and Data Mining options
(with complications)

There is a bug that says you have to restart the database after running catnoqm. Be sure to start it with “startup migrate” again:

C:\>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 22 10:05:17

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Personal Oracle Database 10g Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup migrate;
ORACLE instance started.

Total System Global Area  469762048 bytes
Fixed Size                  1249608 bytes
Variable Size             146804408 bytes
Database Buffers          314572800 bytes
Redo Buffers                7135232 bytes
Database mounted.
Database opened.
SQL> column parameter format a20
SQL> column value format a5
SQL> select * from nls_session_parameters
  2  where PARAMETER='NLS_LENGTH_SEMANTICS';

PARAMETER            VALUE
-------------------- -----
NLS_LENGTH_SEMANTICS BYTE

Now run the CATQM.SQL script which requires three arguments (initial password and two tablespaces):

SQL> @?\RDBMS\ADMIN\catqm.sql change_on_install SYSAUX TEMP

Table created.

Table created.

[[ lot of succesfull statements removed for readability ]]

Package dropped.

Package dropped.

Function dropped.

Commit complete.

Now shutdown and start the database normally. You can check that the length semantics are back to its original (CHAR) and the query from sys.ku$_xmlschema_view now works fine:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  469762048 bytes
Fixed Size                  1249608 bytes
Variable Size             150998712 bytes
Database Buffers          310378496 bytes
Redo Buffers                7135232 bytes
Database mounted.
Database opened.
SQL> column parameter format a20
SQL> column value format a5
SQL> select * from nls_session_parameters
  2  where PARAMETER='NLS_LENGTH_SEMANTICS';

PARAMETER            VALUE
-------------------- -----
NLS_LENGTH_SEMANTICS CHAR

SQL> select url, local, stripped_val
  2  from sys.ku$_xmlschema_view;

URL
----------------------------------------------------------------
     LOCAL
----------
STRIPPED_VAL
----------------------------------------------------------------
http://xmlns.oracle.com/xdb/XDBSchema.xsd
         0
<schema xmlns="http://www.w3.org/2001/XMLSchema" xmlns:xdb="
http://xmlns.oracle.

http://xmlns.oracle.com/xdb/XDBResource.xsd
         0

[[ lot of log removed for readability ]]
13 rows selected.

If you now retry the export it will succeed:

C:\>set ORACLE_SID=dev01

C:\>set NLS_LANG=AMERICAN_AMERICA.AL32UTF8

C:\>exp '/ as sysdba' full=y file=full.dmp

Export: Release 10.2.0.1.0 - Production on Tue Nov 22 10:23:09

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to: Personal Oracle Database 10g Release 10.2.0.1.0
With the Partitioning, OLAP and Data Mining options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR
character set

About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions
. exporting system procedural objects and actions
. exporting pre-schema procedural objects and actions
. exporting cluster definitions
. about to export SYSTEM's tables via Conventional Path ...
. . exporting table               DEF$_AQCALL     0 rows exported
. . exporting table              DEF$_AQERROR     0 rows exported
. . exporting table             DEF$_CALLDEST     0 rows exported
. . exporting table          DEF$_DEFAULTDEST     0 rows exported
. . exporting table          DEF$_DESTINATION     0 rows exported
. . exporting table                DEF$_ERROR     0 rows exported
. . exporting table                  DEF$_LOB     0 rows exported
. . exporting table               DEF$_ORIGIN     0 rows exported
. . exporting table           DEF$_PROPAGATOR     0 rows exported
. . exporting table  DEF$_PUSHED_TRANSACTIONS     0 rows exported

[[ rest removed for readability ]]

The lessons learned from this experience:

  • Switch to NLS_LENGTH_SEMANTICS=CHAR after creating the initial database
  • Always use “startup migrate” when running catalog scripts

Also see MetaLink Note 144808.1 for more information and limits of using BYTE and CHAR semantics. This case will be added to this Note.

Update 17-dec-2005: Oracle filed bug 4886376 to have this problem fixed in a future release. The bug description also indicates that AL32UTF8 will be the default character set for a version 11.x database.

41 Comments

TrackBack URI

  1. Thank you for sharing this insight.

    Comment by Martin — 22 November 2005 #

  2. Thanks for this explanation of this problem !

    I project an installation and migration towards 10gR2, it’s a great help.

    Thanks a lot

    Henri

    Comment by Henri318 — 25 April 2006 #

  3. I have applied patch set 10.1.0.5.0.
    My previous relase was 10.1.0.3.0.

    Now I have a exp issue as below:

    Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
    With the Partitioning, OLAP and Data Mining options
    Export done in US7ASCII character set and AL16UTF16 NCHAR character set
    server uses AL32UTF8 character set (possible charset conversion)

    About to export specified users …
    . exporting pre-schema procedural objects and actions
    . exporting foreign function library names for user WITS
    . exporting PUBLIC type synonyms
    . exporting private type synonyms
    . exporting object type definitions for user WITS
    About to export WITS’s objects …
    . exporting database links
    . exporting sequence numbers
    . exporting cluster definitions
    EXP-00056: ORACLE error 932 encountered
    ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR
    EXP-00000: Export terminated unsuccessfully

    Comment by Dawar — 3 May 2006 #

  4. Dawar,

    It looks like the same problem, but I cannot guarantee it. We never used 10.1. We upgraded straight to 10.2 from 8.1.7.

    Just follow the steps described in this posting and see if you have the same prerequisites (database running CHAR mode and not able to select from sys.ku$_xmlschema_view). If that’s the case, I guess you’re safe to follow the same steps to solve the issue. All you’re doing is recreating stuff that’s already there, but this time in BYTE mode.

    If you’re not sure, just contact Oracle Support and have them figure it out. You can also point them to this posting.

    Comment by Wilfred — 4 May 2006 #

  5. I’ve been reading your blog for a while and I have a question for you, can I take it that you have a version of Forms 10g in production against an Oracle 8.1.7.4 database ?

    If so, I’m intrigued to know what issues you faced.

    Many thanks.

    Comment by Rasheed — 31 May 2006 #

  6. Hi Rasheed,

    By now we upgraded our database from 8.1.7.4 to 10.2.0.1, so we no longer run 8i. During that database upgrade we also upgrade Forms from 9.0.4 to 10.1.2.

    We have been running database 8i with Forms 10g (9.0.4) for over a year without any problems.

    According to Oracle Metalink Forms 9.0.4 was a supported configuration running against 8.1.7.4 database when the database was still supported.

    We never ran Forms 10.1.2 against a 8.1.7.4 database in production. During testing the 9.0.4 to 10.1.2 migration we did some tests running against a 8.1.7.4 database and didn’t find any problems. However, be aware that Forms 10.1.2 with database 8.1.7.4 has never been a certified combination.

    Comment by Wilfred — 1 June 2006 #

  7. Thanks a lot . This was very usefull. I faced the problem when doing exports using 10.2.0.1 upgraded to 10.2.0.2 but same problem persists then I ran the two scripts in database mounted in migrate stage and my exports works.

    cheers!

    Comment by Amit — 20 June 2006 #

  8. Worked with a 10g XE version, too. Thanks a lot.

    Comment by Zento — 21 June 2006 #

  9. We have two databases on a Sun server running SunOS. One db is 8.1.7 and the other is 9.2 We have a 10g(version 9.0.4) app server for Oracle Discoverer reports from these 2 databases.

    We procured new HP servers running SLES 9.3 and want to upgrade our dbs to 10gR2. Could you please list out the important steps to follow and any special considerations. I am planning to use imp/exp as we cannot upgarde the databases to 10g on our existing Sun server due to space constraints. This is my first major upgrade, so I want to make sure everything will work fine.

    Any suggestions are greatly appreciated. Thanks, Kishore.

    Comment by kishore — 20 July 2006 #

  10. Kishore,

    There’s so much to think of when upgrading the database. Oracle has great documentation on it. Take the time to read it at.

    Comment by Wilfred — 20 July 2006 #

  11. Hi, I’m new reader in your Blog, I was wondering what migration method you used for DB , was it direct upgrade with the Assistant or installing a new 10g Database and using exp/imp? and why?

    Right now we are in the middle of a migration project planning and looking for the best migration method for our DB, Oracle seems to recommed using “Database Upgrade Assistant” , but I’m not sure about it..

    Thanks in advance.. and sorry if you talked about this subject before in some entry of the Blog…

    John.

    Comment by Juan — 18 October 2006 #

  12. John,

    We decided to create a clean 10.2 database and use exp/imp. That’s because I didn’t want to keep possible old 8i stuff in my new database. Besides that, I think the upgrade assistance “destroys” your 8i instance and we wanted the upgrade process to be reproducible. We ran it dozens of times as testing before we did the actual migration.

    Another complexity was that we migrated to new hardware. We came from 32-bit HP-UX and went to 64-bit HP-UX. As far as I can remember, you had to run the 8i and 10g instance on the same box for the Migration Assistant. We didn’t want to install the 8i stuff on the new 64-bit machine and I think we couldn’t install the 10g stuff on the old 32-bit machine.

    We also changed our tablespace architecture and we were more comfortable with creating a brand new database and importing the data.

    Comment by Wilfred — 18 October 2006 #

  13. Wilfred,

    Very helpful your answer… and fast! :)

    Thanks for sharing your experience…

    J.

    Comment by Juan — 18 October 2006 #

  14. Really very helfull information,Thanks for sharing the knowledge and experience.

    Comment by senthil — 31 October 2006 #

  15. Hi,

    I have a problem with CLOB in forms 10g.

    I can’t copy (dbms_lob.copy)a filed clob in another fiels clob.

    Thanks a lot.

    Comment by Karim — 19 January 2007 #

  16. Excellent explaination !!!

    Thanks for sharing your knowledge.

    Comment by Karam — 19 January 2007 #

  17. Sorry Karim, I have no experience with the use of CLOB/BLOB in Oracle Forms.
    Since DBMS_LOB is a database package, I’m not sure if and how you can use it in Oracle Forms.

    Comment by wilfred — 20 January 2007 #

  18. Hi wilfred,

    I can use the package (dbms_lob) but when it exceeds 32767 it generates me the erruer ora-06502.

    I think that it is a bug oracle.

    Thanks a lot.

    Comment by Karim — 22 January 2007 #

  19. I have created a 10g on solaris 10, 64-bit. And I have imported my 9.2, 64-bit, database into this new 10g. When I created my DB, I did not set the character set. Many packages are invalid and compilation does not make them valid either.
    My export gives me error too:
    . exporting cluster definitions
    EXP-00056: ORACLE error 932 encountered
    ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR
    EXP-00000: Export terminated unsuccessfully

    When I run select from sys.ku$_xmlschema_view it gives me an error. I do not think I have installed XDB and right now I do not need that either.
    I know I don’t have to pay attention to the Error
    932 but I usually dump my DB:s to have quick access to the data in the case of data loss. And I am worried about the invalid objects in the DB.
    Thanks for your help.
    Misa

    Comment by Misa — 15 April 2007 #

  20. It is lovely to have a forum for consultation.
    Please keep this forum alive.
    Misa

    Comment by Misa — 15 April 2007 #

  21. Hi,
    Dear All,
    Thanks Alot, This is very usefull.
    I am facing the same problem that you have mentioned above, the parameter NLS_LENGHT_SYMENTICS is already set to BYTE.
    I want to ask that why there is need to drop and recreate the XDB stuff?
    Will the solution work in my case? i don’t want to change the NLS_LENGTH_SYMENTICS parameter, i just want to export a user, but i can’t due to ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR.
    Thanks, i am looking forward.

    Comment by Vohra — 9 May 2007 #

  22. There can probably a whole bunch of reasons why you can get the ORA-00932 error. Out problem was caused by an invalid view sys.ku$_xmlschema_view that raises the ORA-00932 error.

    This was caused by having the database in CHAR mode while installing XDB. Apparently all the SYS objects should be installed as CHAR objects as well as the XDB stuff. Unfortunately the XDB installation scripts do not specify CHAR/BYTE and rely on the database being in BYTE mode. So, you would have to set your database to BYTE mode when installing XDB and only switch to CHAR mode afterwards.

    If you’re problem is not caused by this same invalid view due to CHAR/BYTE switching, then you’re facing a different problem and I advice you to browse metalink.oracle.com or open a service request with Oracle Support.

    Comment by wilfred — 9 May 2007 #

  23. thanks for this post really helped and solved my issue.

    Comment by varay — 4 June 2007 #

  24. Thanks ~…

    gOOd days~~

    [blueSKY]

    Comment by Jedy Yoon — 3 July 2007 #

  25. Thanks so much for taking the time to post this detailed fix. Oracle tech support pointed me to Note: 396454.1 but the instructions there left me in a bind, as they did not tell you that you needed to shutdown and startup migrate in between running the scripts! Thanks again, it really saved me.

    Comment by j.f. — 16 August 2007 #

  26. Thanks … was very helpful

    Shabani

    Comment by Shabani — 27 August 2007 #

  27. Hi,

    Thanks a lot. It was very helpful.

    Comment by rajan — 30 August 2007 #

  28. Hi..

    Thanks a a lot… very short and sweet description…. solve the problem in just few mins. for which I was suffring since long…

    Comment by Harsh — 12 October 2007 #

  29. But still I have one problem in my export backup… when I tried to take export backup through oracle EM.. When I tries to take backup of few schemas backup generates on system perfectly without any error but in OEM it show.. job still running since more then 10 hours… and when I tries to stop that… it never stop.. and it’s status changed so.. stop pending… and that job moves in Problem from Running in Main OEM page… Can u tell me what could be the problem ???

    Comment by Harsh — 12 October 2007 #

  30. Many thanks!
    This saved me many hours of banging my head against the wall… :-)

    Comment by Tero — 14 November 2007 #

  31. Many thanks, this saved me a lot of time!

    Comment by darkblaster — 19 November 2007 #

  32. Hi,

    Thanks a ton for your information, the same ora error 932 got fixed in 10.1.0.5 version also.

    Comment by Sripad — 14 February 2008 #

  33. Very precise article about the error and its solution. Thanks a lot for posting such nice, clear article.

    Regards
    Pavan

    Comment by Pavan — 27 February 2008 #

  34. Excellent article! This addressed our issues precisely and saved me a lot of time. Thanks for publishing this.

    Regards,

    Dustin

    Comment by Dustin — 18 June 2008 #

  35. Excellent Stuff!

    Saved my night :-)

    Comment by Sabalesh Mahajan — 7 August 2008 #

  36. Super, man! Big thanks
    It saved my day. Your procedure has done marvels.
    The “normal” procedure suggests to use commands such as: alter system set nls_length_semantics=byte scope=both; (and so on) But it did not work at all for me. Whereas your procedure did. So I guess that using “startup migrate” is a good option here.

    Comment by Stef — 26 August 2008 #

  37. Thanks alot ,

    The NLS_LENGHT_SYMATICS didnt changed to CHAR after running the catnoqm.sql then catqm.sql scripts, but the export completed successfully.

    Comment by Ahmad Abed — 14 October 2008 #

  38. Thanks

    kızlık zarı

    Comment by kızlık zarı — 7 December 2008 #

  39. Solved my problem thanks very inforamtive and detailed soloution

    Comment by Rashid — 17 December 2008 #

  40. Hi All,

    I am facing the same problem when I taking the Oracle DB 10.2.0.3.0 backup by using exp command.

    The below is the error message:
    . exporting sequence numbers
    . exporting cluster definitions
    EXP-00056: ORACLE error 932 encountered
    ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR
    EXP-00000: Export terminated unsuccessfully

    Could you please help me ASAP.

    Comment by Pragash — 12 June 2009 #

  41. Thanks ATON mann…amazing stuff…i came to know abt ur blogs for the first time…and its amazing stuff :) class apart. Thanks…saved me hours of hard work.

    Comment by Anuj Sukhija — 17 June 2009 #

Sorry, the comment form is closed at this time.

Powered by WordPress with Pool theme design by Borja Fernandez.
Entries and comments feeds.