Wednesday 24 April 2013

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.

No comments:

Post a Comment