Changing the Database Character Set to AL32UTF8 / UTF8

1-Installing and configuring CSSCAN in 10g and 11g

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

2-Take full export of database as well other backups (RMAN or Hot)
(Do not to use expdp )

3-Invalid objects.

SQL> Select owner, object_name, object_type, status from dba_objects where status =’INVALID’;

4-Orphaned Datapump master tables (10g and up)

SELECT o.status, o.object_id, o.object_type,
o.owner’.’object_name “OWNER.OBJECT”
FROM dba_objects o, dba_datapump_jobs j
WHERE o.owner=j.owner_name AND o.object_name=j.job_name
AND j.job_name NOT LIKE ‘BIN$%’ ORDER BY 4,2;

5-Drop default schemas

For example : The ‘HR’, ‘OE’, ‘SH’, ‘PM’, ‘IX’, ‘BI’ and ‘SCOTT’ users are by default sample schema’s.

6-Check the Source database for “Lossy” (invalid code points in the source character set).
Note: Always run Csscan connecting with a ‘sysdba’ connection/user,do not use “system” or “cmsig” user.

csscan FULL=Y FROMCHAR=current_charaterset TOCHAR=target_charaterset LOG=log_file_name CAPTURE=N ARRAY=1000000 PROCESS=2
csscan FULL=Y FROMCHAR=WE8ISO8859P1 TOCHAR=AL32UTF8 LOG=dbcheck CAPTURE=N ARRAY=1000000 PROCESS=2

This will create 3 files :

dbcheck.out a log of the output of csscan
dbcheck.txt a Database Scan Summary Report
dbcheck.err contains the rowid’s of the Lossy rows reported in dbcheck.txt (if any).

If all the data in the database is stored correctly at the moment then there should only be “Changeless” data reported in dbcheck.txt.
If this is the case please go to point 5).

7-Check for “Convertible” and “Truncation” data when going to AL32UTF8

csscan FULL=Y TOCHAR=AL32UTF8 LOG=toutf8 CAPTURE=Y SUPPRESS=1000 ARRAY=1000000 PROCESS=2

This will create 3 files :
toutf8.out a log of the output of csscan
toutf8.txt the Database Scan Summary Report
toutf8.err contains the rowid’s of the Convertible and Lossy rows reported in toutf8.txt

8-Objects in the recyclebin

Sqlplus>PURGE DBA_RECYCLEBIN;

9-Steps needed to use Alter Database Character Set / Csalter:

For 10g and up:

a. Export all the “Convertible” data (tables) ( no tables belong to sys schemas) ( NLS_LANG is set to the current database character set)
b. Truncate the exported tables of point a
c. Run csscan again with the syntax of point 5) to verify you only have “convertible” CLOB in the data dictionary and all other data is “changeless”.
d. If this is now correct then proceed to step 10), otherwise do the same again for the rows you’ve missed out.
e. Import the exported data again.

10-Running Csalter/Alter Database Character Set

For 10g and up

shutdown
startup restrict
SPOOL Nswitch.log
@@?/rdbms/admin/csalter.plb
— Csalter will aks confirmation – do not copy paste the whole actions on one time
— sample Csalter output:
— 3 rows 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.

— Procedure dropped.

shutdown
startup

11-Check the character set

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

12 – Reload the data pump packages after a change to AL32UTF8 in 10g and up.

If you use 10g or up then the datapump packages need to be reloaded after a conversion to AL32UTF8. In order to do this run the following scripts from $ORACLE_HOME/rdbms/admin in sqlplus connected as “/ AS SYSDBA”:

For 10.2.X and higher:
catnodp.sql
catdph.sql
catdpb.sql

For 10.1.X:
catnodp.sql
catdp.sql

13- Possible error

—————
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…
Unrecognized convertible date found in scanner result
PL/SQL procedure successfully completed.
Checking or Converting phrase did not finish successfully
No database (national) character set will be altered
CSALTER finished unsuccessfully.

PL/SQL procedure successfully completed.
4 rows deleted.

—————

Check the log file toutf8.err ‘s “[Application data individual exceptions]”
Export all the tables coming under above session and truncate them

Re-run CSSAN command and check again “Application data individual exceptions”
csscan FULL=Y TOCHAR=AL32UTF8 LOG=toutf8 CAPTURE=Y SUPPRESS=1000 ARRAY=1000000 PROCESS=2

=================More info================

Note:745809.1 Installing and configuring CSSCAN in 10g and 11g
Note:225912.1 Changing the Database Character Set – a short overview
Note:260192.1 Changing the NLS_CHARACTERSET to AL32UTF8 / UTF8 (Unicode)

Note:258904.1 Convertible data in data dictionary: Workarounds when changing character set

Advertisements

About deepakguptadba

10 years of experience in Database Administrator for Oracle Database 10g//9i/8i/7.x with total 15 years’ comprehensive experience in IT , Pharma / Biotech , Manufacturing, Construction , Auto & Electronics.
This entry was posted in Changing the Database Character Set to AL32UTF8. Bookmark the permalink.

One Response to Changing the Database Character Set to AL32UTF8 / UTF8

  1. Maria says:

    Hi Deepak, Thanks a LOT for the topic. That is exactly what I will be doing in June. The post extremely helpful!!Maria Gurenich

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s