Steps to migrate the characterset of database

Steps to migrate the characterset of database



You can follow below steps to migrate database character set

Changing character to AL32UTF8 :

Take Full export of database before starting this activity.

1. SHUTDOWN IMMEDIATE;

2. Do a full backup

3. STARTUP MOUNT;

4. ALTER SYSTEM ENABLE RESTRICTED SESSION;

5. ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

6. ALTER SYSTEM SET IAQ_TM_PROCESSES=0;

7. ALTER DATABASE OPEN;

8. ALTER DATABASE CHARACTER SET <new_character_set_name>;
When i was doing it it errored out like below
SQL> ALTER DATABASE CHARACTER SET AL32UTF8;
ALTER DATABASE CHARACTER SET AL32UTF8
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set

We need to make sure New character set would be superset of old characterset , check below link for checking super set

http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/applocaledata.htm#sthref1958

I found work around after investigating for long time ,

ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;

The above command will skip the check of character set subset or superset.
9. SHUTDOWN IMMEDIATE;

10. STARTUP;
11.SQL> select value from NLS_DATABASE_PARAMETERS where parameter=’NLS_CHARACTERSET’;

VALUE
——————————————————————————–
AL32UTF8

Now character is successfully migrated.