Changing Database characterset using CSSCAN and CSALTER script

Using csscan to check compatibility and then change the character set as below

Step 1) Check the current character set of the target database.

SQL> Select value from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET';

VALUE
----------------------------------------
WE8MSWIN1252

Step 2)    Configure CSSCAN

Csscan is a tool that allows you to see the impact of a character set change. This is tool is available from 8i and above. To install the tool follow the steps below

cd $ORACLE_HOME/rdbms/admin
set oracle_sid=
sqlplus
SQL>conn / as sysdba
SQL>  spool csminst.log
SQL> START csminst.sql

Character set scanner creates a schema called CSMIG for maintaing its data in the database, and is created using the csminst.sql script.
The default tablespace for this user should be system you can change it.

alter user csmig default tablespace SYSTEM

Check file csminst.log for any errors after installing csscan.

Step 3) To check if csscan is working fine, run the command and check for the output as shown.

D:\Oracle10g\product\10.2.0\db_1\BIN>csscan TABLE=SYS.SQL_VERSION$ FROMCHAR=US7ASCII TOCHAR=US7ASCII LOG=d:\instchk CAPTURE=N P
ROCESS=1 ARRAY=1024000

Character Set Scanner v2.1 : Release 10.2.0.3.0 - Production on Tue Feb 8 14:47:15 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Username: sys as sysdba
Password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

Enumerating table to scan...
. process 1 scanning SYS.SQL_VERSION$[AAAAIDAABAAAA/xAAA]
Creating Database Scan Summary Report...
Creating Individual Exception Report...
Scanner terminated successfully.

D:\Oracle10g\product\10.2.0\db_1\BIN>

Once the csscan is completed, then csalter script has to be run, It will change the character set to the desired one used in csscan TOCHAR variable.

Some conditions has to be met before running the script csalter.

Csalter the Csscan output needs to be

* changeless for all CHAR VARCHAR2, and LONG data (Data Dictionary and user)
* changeless for all USER CLOB
* convertible and changeless for all Data Dictionary CLOB

in order to run Csalter you need to see in the instchk.txt (logfile which is created in the previous run) file under [Scan Summary] .All character type application data remain the same in the new character set
and under [Data Dictionary Conversion Summary]
The data dictionary can be safely migrated using the CSALTER script

Step 4) Run the csscan command as provided below.


    D:\Oracle10g\product\10.2.0\db_1\BIN>csscan FULL=Y FROMCHAR=WE8MSWIN1252 TOCHAR=utf8 ARRAY=1024000 LOG=d:\dbcharchng CAPTURE=Y
SUPPRESS=1000 PROCESS=4

. process 1 scanning SYSMAN.MGMT_DELTA_COMP_KEY_COLS[AAAMQiAADAAAGORAAA]
. process 2 scanning SYSMAN.MGMT_DELTA_IDS[AAAMP+AADAAAGJxAAA]
. process 3 scanning SYSMAN.MGMT_JOB_EXEC_LOCKS[AAAMS4AADAAAGgxAAA]
. process 1 scanning SYS.WRH$_SERVICE_WAIT_CLASS[AAAMjkAADAAABAhAAA]
. process 4 scanning SYSMAN.MGMT_HA_RAC_INTR_CONN[AAAMeDAADAAAHMRAAA]
. process 2 scanning SYS.WRH$_ROWCACHE_SUMMARY[AAAMhfAADAAABOpAAA]
. process 3 scanning SYS.WRH$_DLM_MISC[AAAMjMAADAAAA5RAAA]
. process 1 scanning SYS.WRH$_SQLSTAT[AAAMiWAADAAAAsJAAA]
. process 4 scanning SYS.WRH$_ACTIVE_SESSION_HISTORY[AAAMjUAADAAAA8RAAA]
. process 2 scanning SYS.WRH$_PARAMETER[AAAMjEAADAAAA1RAAA]
. process 3 scanning SYSTEM.LOGMNR_ATTRIBUTE$[AAABbdAADAAAAd5AAA]

Creating Database Scan Summary Report...

Creating Individual Exception Report...

Scanner terminated successfully.

Step 5). Once the CSSCAN has been run then you can check the logfile , text file and the error file which is generated while running the CSSCAN in the previous step. there has to be no errors in the files, if yes then you can proceed with the next step by running CSALTER script as below.

D:\Oracle10g\product\10.2.0\db_1\BIN>sqlplus

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Feb 8 15:31:44 2011

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Enter user-name: sys/sys as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> @D:\Oracle10g\product\10.2.0\db_1\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
79 rows in table SYS.WRI$_DBU_FEATURE_METADATA are converted
4 rows in table MDSYS.SDO_XML_SCHEMAS are converted
78 rows in table MDSYS.SDO_STYLES_TABLE are converted
80 rows in table SYS.METASTYLESHEET are converted
17 rows in table SYS.WRI$_DBU_HWM_METADATA are converted
2308 rows in table MDSYS.SDO_COORD_OP_PARAM_VALS are converted
4 rows in table SYS.RULE$ are converted
5 rows in table SYS.SCHEDULER$_EVENT_LOG are converted
1 row in table MDSYS.SDO_GEOR_XMLSCHEMA_TABLE is 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> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1289484 bytes
Variable Size              75498228 bytes
Database Buffers           83886080 bytes
Redo Buffers                7098368 bytes
Database mounted.
Database opened.

SQL>  select value from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET';

VALUE
--------------------------------------------------------------------------------
UTF8

SQL>

Note : please refer 2010 december post for Schema LEVEL characterset migration to another database using CSSCAN  and EXPORT/IMPORT utility.