Contact Us!!!

contact@unionsystechnologies.com

Friday 27 December 2013

ORA-12712: new character set must be a superset of old character set

Changing the database character set to (AL32)UTF8

You can't simply use "ALTER DATABASE CHARACTER SET" to go from WE8ISO8859P1 or
WE8ISO8859P15 or WE8MSWIN1252 to (AL32)UTF8 because (AL32)UTF8 is not a 
binary superset of any of these character sets. 

You will run into ORA-12712 or ORA-12710 because the code points for the 
"extended ASCII" characters are different between these 3 character sets 
and (AL32)UTF8. 

This note will describe a method of still using a 
"ALTER DATABASE CHARACTER SET" in a limited way.


Execute the following commands in sqlplus connected as "/ AS SYSDBA":

  1. SHUTDOWN IMMEDIATE;
  2. STARTUP MOUNT; 
  3. ALTER SYSTEM ENABLE RESTRICTED SESSION; 
  4. ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; 
  5. ALTER SYSTEM SET AQ_TM_PROCESSES=0; 
  6. ALTER DATABASE OPEN; 
  7. ALTER DATABASE CHARACTER SET WE8MSWIN1252;
  8. SHUTDOWN IMMEDIATE;
  9. STARTUP RESTRICT; 
  10. SHUTDOWN;
  11. STARTUP; 
Now of course your database is then WE8MSWIN1252, so
you need to replace <existing database character set> with WE8MSWIN1252 from now on....

  • Perform a backup of the database.
  • Check the backup.
  • Double-check the backup.
Then use the "alter database" command, this changes the current database 
character set definition WITHOUT changing the actual stored data.

Shutdown the listener and any application that connects locally to the database.
There should be only ONE connection the database during the WHOLE time and that's
the sqlplus session where you do the change.

Now From WE8MSWIN1252 TO AL32UTF8

Execute the following commands in sqlplus connected as "/ AS SYSDBA":

  1. SHUTDOWN IMMEDIATE;
  2. STARTUP MOUNT; 
  3. ALTER SYSTEM ENABLE RESTRICTED SESSION; 
  4. ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; 
  5. ALTER SYSTEM SET AQ_TM_PROCESSES=0; 
  6. ALTER DATABASE OPEN; 
  7. ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;
  8. SHUTDOWN IMMEDIATE;
  9. STARTUP; 
now we have done....Database into AL32UTF8