How We Used the Oracle CSSCAN Utility and CSALTER Script to Migrate the Database Character Set?

As per the requirement from the Application Vendor, we had to change the database character set on one of the existing test databases.

CSSCAN Utility and CSALTER Script:

Database character set migration has two stages: data scanning (csscan) and data conversion (csalter.plb).

The CSSCAN Utility is used to denitrify possible database character set conversion problems and truncation of data. Data scanning identifies the amount of effort required to migrate data into the new character encoding scheme before changing the database character set. This information helps to determine the best approach for converting the database character set.

The CSALTER script is part of the Database Character Set Scanner utility. The CSALTER script is the most straightforward way to migrate a character set, but it can be used only if all of the schema data is a strict subset of the new character set. The new character set is a strict superset of the current character set if:

Each and every character in the current character set is available in the new character set.

Each and every character in the current character set has the same code point value in the new character set.

Here are the simple steps to migrate the default Database Character Set:

Database Version: Oracle 10g Database R2 (10.2.0.3)
Source Character Set: AL32UTF8
Target Character Set: WE8ISO8859P1

Shut down the database, using either a SHUTDOWN IMMEDIATE or a SHUTDOWN NORMAL statement.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Take a full database backup:

Sometimes, incorrect data conversion can lead to data corruption, so perform a full backup of the database before attempting to migrate the data to a new character set. The database can be reconstructed later using the backup taken before this action, if something goes wrong.

Note: We have taken the cold backup of the database and also performed the full database export using Datapump utility (expdp/impdp). It’s up to you, how and what type of full backup you prefer for your database i.e. Cold/Hot Backup/RMAN. So, you should recreate the database using this backup when the conversion fails.

Start up the database:

SQL> startup;
ORACLE instance started.

Total System Global Area 2097152000 bytes
Fixed Size 2074120 bytes
Variable Size 771754488 bytes
Database Buffers 1308622848 bytes
Redo Buffers 14700544 bytes
Database mounted.
Database opened.

Run the Database Character Set Scanner (CSSCAN) utility.

This CSSCAN Utility executable is under the $ORACLE_HOME/bin directory.

$ csscan \"sys/password@mydb as sysdba\" full=y
Character Set Scanner v2.1 : Release 10.2.0.3.0 - Production on Sat Sep 13 15:58:28 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Current database character set is AL32UTF8.
Enter new database character set name: > WE8ISO8859P1
Enter array fetch buffer size: 1024000 >
Enter number of scan processes to utilize(1..32): 1 > 32
Enumerating tables to scan...
. process 1 scanning SYS.SOURCE$[AAAABIAABAAAAIRAAA]
. process 2 scanning SYS.SOURCE$[AAAABIAABAAAHCJAAA]
. process 3 scanning SYS.ATTRIBUTE$[AAAAC1AABAAAAURAAA]
…………………………………………………………………………….
…………………………………………………………………………….
…………………………………………………………………………….

Creating Database Scan Summary Report...
Creating Individual Exception Report...
Scanner terminated successfully.
$

For any help on CSSCAN utility i.e. keyword, prompt, and description, use the following command.

$ csscan help=y

Run the CSALTER script.

Once the Database Character Set Scanning has completed successfully, the database must be opened in restricted mode, because no normal user should allow to access the database during this task is being performed. So you can run the CSALTER script as the SYS user. The location of the CSALTER Script is "$ORACLE_HOME/rdbms/admin/csalter.plb".

Shut Down the Database

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Start Up the Database in Restricted Mode.

SQL> startup restrict;
ORACLE instance started.

Total System Global Area 2097152000 bytes
Fixed Size 2074120 bytes
Variable Size 771754488 bytes
Database Buffers 1308622848 bytes
Redo Buffers 14700544 bytes
Database mounted.
Database opened.

Run the csalter.plb script

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
12 rows in table SYS.WRI$_ADV_RATIONALE are converted
1 row in table MDSYS.SDO_GEOR_XMLSCHEMA_TABLE is converted
80 rows in table SYS.METASTYLESHEET are converted
17 rows in table SYS.WRI$_DBU_HWM_METADATA are converted
79 rows in table SYS.WRI$_DBU_FEATURE_METADATA are converted
4 rows in table SYS.RULE$ are converted
78 rows in table MDSYS.SDO_STYLES_TABLE are converted
10576 rows in table SYS.WRH$_SQL_PLAN are converted
21 rows in table SYS.WRI$_DBU_FEATURE_USAGE are converted
19 rows in table SYS.WRI$_ADV_ACTIONS are converted
4 rows in table MDSYS.SDO_XML_SCHEMAS are converted
2308 rows in table MDSYS.SDO_COORD_OP_PARAM_VALS are converted
578 rows in table SYS.WRI$_ADV_OBJECTS are converted
789 rows in table SYS.WRH$_SQLTEXT 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.

Shut Down the Database

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Start Up the Database

SQL> startup;
ORACLE instance started.

Total System Global Area 2097152000 bytes
Fixed Size 2074120 bytes
Variable Size 771754488 bytes
Database Buffers 1308622848 bytes
Redo Buffers 14700544 bytes
Database mounted.
Database opened.
SQL>

Make sure the changes:

SQL> SELECT * FROM nls_database_parameters;

References:

Character Set Migration
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14225/ch11charsetmig.htm

Character Set Scanner Utilities
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14225/ch12scanner.htm

Oracle Metalink Notes on Database Character Set Migration

Note: 225912.1 - Subject: Changing the Database Character Set - a short Overview
Note: 119164.1 - Subject: Changing Database Character Set - Valid Superset Definitions
Note: 66320.1 -Subject: Changing the Database Character Set or the Database National Character Set
Note: 123670.1 - Subject: Use Scanner Utility before Altering the Database Character Set
Note: 225938.1 - Subject: Database Character Set Health check.

Conclusion: Here I have only explained the steps I followed to convert the database character set, which is specific to our single instance database. It means that this article doesn’t consist of any other information about Database Character Set, as these details are available and discussed thoroughly in the Oracle Documentations and Oracle Metalink. If the database is a multi instance database i.e. RAC, and has other options enabled in place. I would strongly recommend to follows the Oracle Documentations and Oracle Metalink Notes. Moreover, this scenario, explained here, is specific to our requirement, and need not be the same with others. When you are not sure or in doubt, then do *NOT* do this for other character set combinations without logging a tar with Oracle Support for verification.

Regards,
Sabdar Syed,
http://sabdarsyed.blogspot.com/