Changing the Database Character Set

Changing the Database Character Set

Introduction
============
This article gives a overview of methods to change the database ch aracter set .
This is the NLS_CHARACTERSET in NLS_DATABASE_PARAMETERS.
Use this note to get a basic understanding of the methods, then use the in-depth
notes at the end of this note for specific guidance on your conversion.

For all questions regarding the NLS_NCHAR_CHARACTERSET read first:
Note 276914.1 The National Character Set in Oracle 9i and 10g

The database character set
==========================
The character set of a database defines how characters are stored in the
database. Therefore you are limited to storing just the characters defined in
that character set.
If you change character sets there is a possibility that characters that you
currently use are not defined in the new character set and therefore you could
‘corrupt’ your data. You should always check this by using the Character Set
Scanner (csscan) or by following Note 225938.1 Database Character Set Healthcheck
before making any changes to your character set.

Please do NOT skip this!

Choosing a new database character set
=====================================
Oracle believes that for the majority of customers a unicode character set
is the best choice.
If you choose any other character set then please be advised of the following:
Note 306411.1 Character Set Consolidation for Oracle Database 11

Changing the database character set
===================================
There are 2 basic ways of changing the character set and a third ‘combined’ way:

1. Using the “ALTER DATABASE CHARACTER SET” command
This is not always possible because this does not change the actual code points
of the stored data. So this method can only be used if the data that is
currently stored in the database is defined under the same code points in the
new character set.
This is documented in
Note 66320.1 Changing the Database Character Set or the Database National Character Set
and can be used for these combinations:Note 119164.1 Changing Database Character Set – Valid Superset Definitions

2. Using Export/Import
This will always work, you simply export the current database, then create a new
database with the new character set and import the data into that database. Of
course the characters that you were storing will still have to be defined in the
new character set for this to work!
See Note 227332.1 NLS considerations in Import/Export – Frequently Asked Questions

3. Using a combination of ALTER DATABASE CHARACTER SET and export/import
In some cases method 1 does not work because csscan tells you that some data
needs to be converted to the new character set, and method 2 will simply take
too much time. In those cases it is usualy possible to use a combination of the
2 methods:
a) Export the data from the tables that need to be converted
b) Truncate or drop those tables.
c) Run csscan again to confirm that all data is now ready to be moved to the new
character set directly and if that is the case change the character set of the
database using the ALTER DATABASE CHARACTERSET command (method 1).
d) Now that the character set has changed we can simply import the data
exported in step (a). The import will convert that data so that it gets stored
in the correct way for this character set.

These specific notes can guide you through some often used conversions, they
show how to use the the above mentioned “combined method” in practice:

Note 257722.1 Changing WE8ISO8859P1 to WE8ISO8859P15 with ALTER DATABASE CHARACTER SETNote 263119.1 Changing EE8ISO8859P2 to EE8MSWIN1250 with ALTER DATABASE CHARACTER SETNote 260022.1 Changing AR8ISO8859P6 to AR8MSWIN1256 with ALTER DATABASE CHARACTER SETNote 261871.1 Changing EL8ISO8859P7 to EL8MSWIN1253 with ALTER DATABASE CHARACTER SETNote 266309.1 Changing WE8ISO8859P9 to WE8ISO8859P1/WE8MSWIN1252 with ALTER DATABASE CHARACTER SETNote 246008.1 Changing WE8ISO8859P15 to WE8MSWIN1252 with ALTER DATABASE CHARACTER SET

Note 261639.1 Changing WE8MSWIN1252 to WE8ISO8859P15 with ALTER DATABASE CHARACTER SETNote 273281.1 Changing WE8ISO8859P15 TO WE8ISO8859P1 with ALTER DATABASE CHARACTER SET

Note 265859.1 Changing WE8DEC to WE8ISO8859P1/WE8MSWIN1252 with ALTER DATABASE CHARACTERSET

Note 260192.1 Changing WE8ISO8859P1/WE8ISO8859P15 or WE8MSWIN1252 to UTF8 with ALTER DATABASE CHARACTER SETNote 234381.1 Changing AL24UTFFSS to UTF8 – AL32UTF8 with ALTER DATABASE CHARACTER SET

Changing AL32UTF8 to UTF8 with ALTER DATABASE CHARACTER SET is documented in:
Note 237593.1 Problems connecting to AL32UTF8 databases from older versions (8i and lower)

Do *NOT* do this for other characterset combinations without logging a tar for verification.

Further reading
===============
A much more in-depth overview of these procudures can be found in:
Note 257736.1 Changing the Database Character Set – an extended overview

There are some additional considerations when you change the character set of
an Oracle Applications database, please see the following note for a complete
overview of those:
Note 124721.1 Migrating an Applications Installation to a New Character Set

Note that display problems are most likly *NOT* resolved by start changing the
database characterset. Instead start here:

Note 158577.1 NLS_LANG Explained (How does Client-Server Character Conversion Work?)Note 179133.1 The correct NLS_LANG in a Windows EnvironmentNote 264157.1 How to set correctly NLS_LANG on Unix EnvironmentsNote 229786.1 NLS_LANG and webservers explained.

Before you worrie about the change of the NATIONAL CHARACTERSET when going
to 9i read first:
Note 276914.1 The National Character Set in Oracle 9i and 10g

For further NLS / Globalization information you may start here:

Note 150091.1 Globalization Technology (NLS) Library indexNote 60134.1 Globalization (NLS) – Frequently Asked Questions

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

Character Set Migration using CSSCAN and CSALTER

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.

Backup
CLUSTER_DATABASE=FALSE (*RAC Only*)
SHUTDOWN IMMEDIATE
STARTUP
CSSCAN
SHUTDOWN IMMEDIATE
STARTUP RESTRICT
CSALTER
CLUSTER_DATABASE=TRUE (*RAC Only*)
SHUTDOWN IMMEDIATE
STARTUP
Performing a backup before starting the character set conversion is very important. If the conversion fails part way through, you must restore from a backup before re-attempting the conversion.

With the backup complete, the instance must be restarted. In the case of RAC databases, the CLUSTER_DATABASE parameter should be set to FALSE, then all RAC instances stoped and only one restarted.

ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=SPFILE; -- RAC Only
SHUTDOWN IMMEDIATE;
STARTUP;
Once the database is open, the CSSCAN utility is run. This example command below would perform a scan of the whole database.

CSSCAN "sys/password@db10g AS SYSDBA" FULL=Y
If the character set migration utility schema is not installed on your database, you will get the following error.

C:\>CSSCAN "sys/password@db10g AS SYSDBA" FULL=Y

Character Set Scanner v2.1 : Release 10.2.0.0.0 - Production on Tue Dec 5 06:54:23 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
CSS-00107: Character set migration utility schema not installed

Scanner terminated unsuccessfully.

C:\>
The character set migration utility schema is installed by running the "$ORACLE_HOME/rdbms/admin/csminst.sql" 
script in SQL*Plus as the SYS user. Once the schema is present, the character set scanner should work normally.

In the following example, the current database character set is "WE8MSWIN1252", which we will convert to "WE8ISO8859P15" using 32 scan processes.

C:\>CSSCAN "sys/password@db10g AS SYSDBA" FULL=Y

Character Set Scanner v2.1 : Release 10.2.0.0.0 - Production on Tue Dec 5 07:00:36 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

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

Current database character set is WE8MSWIN1252.

Enter new database character set name: > WE8ISO8859P15

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 14 scanning SYS.WRH$_SERVICE_WAIT_CLASS[AAANZRAADAAAJKBAAA]
. process 22 scanning SYS.WRH$_WAITSTAT[AAANUxAADAAAHzJAAA]

Creating Database Scan Summary Report...

Creating Individual Exception Report...

Scanner terminated successfully.

C:\>
Once the scan has completed successfully, the database should be opened in restricted mode so
 you can run the "$ORACLE_HOME/rdbms/admin/csalter.plb" script as the SYS user.
 The example below shows a successful conversion.

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP RESTRICT
ORACLE instance started.

Total System Global Area  289406976 bytes
Fixed Size                  1248600 bytes
Variable Size              83886760 bytes
Database Buffers          201326592 bytes
Redo Buffers                2945024 bytes
Database mounted.
Database opened.
SQL> @@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

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. In the case of RAC databases, the CLUSTER_DATABASE parameter should be set back to TRUE.

ALTER SYSTEM SET CLUSTER_DATABASE=TRUE SCOPE=SPFILE; -- RAC Only
SHUTDOWN IMMEDIATE;
STARTUP;

"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""

C) You can change from AL16UTF16 to UTF8 (or inverse) following these steps: 
1. Make sure the parallel_server parameter in INIT.ORA is set to false or it is not set at all. 
If you are using RAC see 
[NOTE:221646.1] Changing the Character Set for a RAC Database Fails with an ORA-12720 Error 
2. Execute the following commands in sqlplus connected as "/ AS SYSDBA": 
SPOOL Nswitch.log 
SHUTDOWN IMMEDIATE; 
STARTUP MOUNT; 
ALTER SYSTEM ENABLE RESTRICTED SESSION; 
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; 
ALTER SYSTEM SET AQ_TM_PROCESSES=0; 
ALTER DATABASE OPEN; 
@RDBMS/ADMIN/N_SWITCH.SQL 
SHUTDOWN IMMEDIATE; 
3. Restore the parallel_server parameter in INIT.ORA, if necessary. 

4. STARTUP;