This article presents a simple example of migrating the default character set of a database using the CSSCAN and CSALTER character set scanner utilities provided by Oracle. The basic steps involved in character set conversion are listed below:

SHUTDOWN IMMEDIATE
BACKUP
STARTUP
CSSCAN
SHUTDOWN IMMEDIATE
STARTUP RESTRICT
CSALTER
SHUTDOWN IMMEDIATE
STARTUP

Before migerating the chanracter set its really very important that we backup the database completly. In case if some issue occurs while migeration or some file gets corrupted, then we can restore the database back.

Once backup is done, start the instance and begin scanning.

Before we migerate to a new character set we have to scan the database for determining
the language and character set for unknown file text. With each text, the character set detection engine sets up a series of probabilities, each probability corresponding to a language and character set pair. The most statistically probable pair identifies the dominant language and character set.

We can scan the database using CSSCAN utility. From the command prompt

===============================================================
bash-2.05$ csscan system/manager full=y tochar=al32utf8 ARRAY=10240 PROCESS=3

ld.so.1: csscan: fatal: libclntsh.so.10.1: open failed: No such file or directory
Killed
===============================================================

The above error is because LD_LIBRARY_PATH is not set. Set the LD_LIBRARY_PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

===============================================================
bash-2.05$ csscan system/manager full=y tochar=al32utf8 ARRAY=10240 PROCESS=3
Character Set Scanner v2.1 : Release 10.2.0.0.0 – Production on Sun May 27 10:52:53 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
CSS-00107: Character set migration utility schema not installed

Scanner terminated unsuccessfully.
===============================================================
The above error is due to

CSS-00107 Character set migration utility schem not installed
Cause: CSM$VERSION table not found in the database.
Action: Run CSMINST.SQL on the database.

===============================================================
bash-2.05$ csscan system/manager full=y
Character Set Scanner v2.1 : Release 10.2.0.0.0 – Production on Sun May 27 11:13:46 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

Current database character set is US7ASCII.

Enter new database character set name: > al32utf8

Enter array fetch buffer size: 1024000 >

Enter number of scan processes to utilize(1..32): 1 > 3

Enumerating tables to scan…

. process 1 scanning SYS.SOURCE$[AAAABIAABAAAAIRAAA]
. process 2 scanning SYS.TYPE$[AAAAC1AABAAAAURAAA]
. process 2 scanning SYS.METHOD$[AAAAC1AABAAAAURAAA]
. process 3 scanning SYS.ATTRIBUTE$[AAAAC1AABAAAAURAAA]
. process 2 scanning SYS.PARAMETER$[AAAAC1AABAAAAURAAA]

…
…
…

. process 2 scanning SYS.LOGMNR_BUILDLOG[AAABXcAABAAADN5AAA]
. process 3 scanning SYSTEM.LOGSTDBY$APPLY_MILESTONE[AAABw/AADAAAAkJAAA]
. process 1 scanning SYSTEM.REPCAT$_CONFLICT[AAAB3IAABAAAEVhAAA]
. process 2 scanning SYS.DBMS_UPG_LOG$[AAACGVAABAAAFHRAAA]
. process 3 scanning SYS.WRH$_DATAFILE[AAACLIAADAAAAs5AAA]
. process 2 scanning SYS.WRH$_MTTR_TARGET_ADVICE[AAACORAADAAABBJAAA]
. process 1 scanning SYS.WRH$_STREAMS_CAPTURE[AAACOkAADAAABDBAAA]
. process 3 scanning DBSNMP.MGMT_CAPTURE_SQL[AAACWHAADAAABSZAAA]
. process 2 scanning SYS.WRH$_ACTIVE_SESSION_HISTORY[AAACVHAADAAABB5AAA]
. process 1 scanning SYSTEM.LOGMNRC_GTCS[AAABajAADAAAAZxAAA]
. process 3 scanning SYS.WRH$_DB_CACHE_ADVICE[AAACubAADAAABjhAAA]

Creating Database Scan Summary Report…

Creating Individual Exception Report…

Scanner terminated successfully.

===============================================================

Once the scaning is completed shutdown the database and start in restrict mode.

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

Total System Global Area 1073741824 bytes
Fixed Size 1984184 bytes
Variable Size 750786888 bytes
Database Buffers 314572800 bytes
Redo Buffers 6397952 bytes
Database mounted.
Database opened.
SQL>

SQL> @@?/rdbms/admin/csalter.plb

0 rows created.
Function created.
Function created.
Procedure created.

This script will update the content of the Oracle Data Dictionary.
Please ensure you have a full backup before initiating this procedure.
Would you like to proceed (Y/N)?Y
old 6: if (UPPER(‘&conf’) <> ‘Y’) then
new 6: if (UPPER(‘Y’) <> ‘Y’) then
Checking data validility…
begin converting system objects
80 rows in table SYS.METASTYLESHEET are converted
4 rows in table SYS.RULE$ are converted
3369 rows in table SYS.WRH$_SQL_PLAN are converted
408 rows in table SYS.WRH$_SQLTEXT are converted
79 rows in table SYS.WRI$_DBU_FEATURE_METADATA are converted
17 rows in table SYS.WRI$_DBU_HWM_METADATA are converted
21 rows in table SYS.WRI$_DBU_FEATURE_USAGE are converted

PL/SQL procedure successfully completed.

Alter the database character set…
CSALTER operation completed, please restart database

PL/SQL procedure successfully completed.
0 rows deleted.
Function dropped.
Function dropped.
Procedure dropped.

SQL>
===============================================================

If there are possible conversion problems, the process will report the problem and clean itself up without performing the conversion. Here are a couple of the messages I got when trying this process.

# When I tried to convert WE8MSWIN1252 -> AL32UTF8.
Checking data validility…
Unrecognized convertible date found in scanner result

# When I tried to run the CSALTER script without a SHUTDOWN-STARTUP RESTRICT.
Checking data validility…
Sorry only one session is allowed to run this script

Once the conversion is complete, you must restart the instance

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

Total System Global Area 1073741824 bytes
Fixed Size 1984184 bytes
Variable Size 750786888 bytes
Database Buffers 314572800 bytes
Redo Buffers 6397952 bytes
Database mounted.
Database opened.
SQL>
===============================================================

For more information on this utility, please check the oracle documentation link.