Oracle 10g - Changing Character sets

Database Version: Oracle 10g-11g
Problem:
ORA-03106: fatal two-task communication protocol error
on insert
Root cause:
Problem with wrong charactersets.
Database was created with:

NLS_CHARACTERSET AL32UTF8
NLS_NCHAR_CHARACTERSET UTF8

But required character sets are:

NLS_CHARACTERSET WE8ISO8859P1
NLS_NCHAR_CHARACTERSET AL16UTF16

*** To verify ***
sqlplus > SELECT * FROM NLS_DATABASE_PARAMMETERS

Solutions:


You basically have three options:
1. Drop the database and recreate with the right charactersets.
2. Drop the problematic schemas, change the charactersets, then reimport
3. Manually alter the charactersets as per Oracle's instructions
The option you choose  will depend on how severe is the problem.

In this cheatsheet, we dropped the problematic schemas, change the charactersets, then reimport.
The steps below are to change the charactersets. Note that you must change both  NLS_CHARACTERSET

and NLS_NCHAR_CHARACTERSET. Otherwise, it won't work.

Prepare:


1. Take a full backup
2. Take a backup copy of the spfile
3. Run csminst.sql
    from $ORACLE_HOME/rdbms/admin/csminst.sql

    You will be prompted for User CSMIG password CSMIG

4. Run a full database csscan from the command line:

    Make sure your library path points to the right library:

    export LD_LIBRARY_PATH=$ORACLE_HOME/lib

    csscan sys/syspassword as sysdba  full=y tochar=WE8ISO8859P1

Enter new database character set name: > WE8ISO8859P1

Enter array fetch buffer size: 1024000 >

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

Enumerating tables to scan...

 . process 1 scanning W2ADMIN.EXTERNAL_CLEANSED_SKU_HIST[AAAouzAATAAA++JAAA]

 . process 2 scanning W2ADMIN.EXTERNAL_CLEANSED_SKU_HIST[AAAouzAAQAAA9sJAAA   
Creating Database Scan Summary Report...

Creating Individual Exception Report...

Scanner terminated successfully.

Creating Database Scan Summary Report... 
Creating Individual Exception Report...

Database Scan Individual Exception Report

 [Database Scan Parameters]

Parameter                      Value

------------------------------ ------------------------------------------------

CSSCAN Version                 v2.1

Instance Name                  DEVDB2

Database Version               10.2.0.3.0

Scan type                      Full database

Scan CHAR data?                YES

Database character set         AL32UTF8

FROMCHAR                       AL32UTF8

TOCHAR                         WE8ISO8859P1

Scan NCHAR data?               NO

Array fetch buffer size        1024000

Number of processes            8

Capture convertible data?      NO

------------------------------ ------------------------------------------------

[Data Dictionary individual exceptions]

[Application data individual exceptions]

*** our DB is 300gb. Even    that, csscan still ran very fast.


Check the exception report from csscan. You must work on the exception report until there is no more exception before you can go on to the next step, thus, at this point, you should evaluate which strategy works for you.
In my case, since I had a lot of errors, I have decided to do the following steps
- Drop the schemas
- Rescan (rerun csscan)
- Alter the charactersets
- Recreate the schemas - by reimporting from production

Execute:

 *** You will need to change both default database characterset, or NLS characterset ***

sqlplus / as sysdba
STARTUP MOUNT exclusive RESTRICT
ALTER SYSTEM ENABLE RESTRICTED SESSION; 
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; 
ALTER SYSTEM SET AQ_TM_PROCESSES=0; 
ALTER DATABASE OPEN ;

SQL> @$ORACLE_HOME/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

6 rows in table SYS.SQL$TEXT are converted

3114 rows in table SYS.WRI$_ADV_ACTIONS are converted

17 rows in table SYS.WRI$_DBU_HWM_METADATA are converted

748 rows in table SYS.WRH$_SQLTEXT are converted

4 rows in table SYS.RULE$ are converted

6704 rows in table SYS.WRH$_SQL_PLAN are converted

617 rows in table SYSTEM.SYS_IMPORT_FULL_04 are converted

3529 rows in table SYS.FGA_LOG$ are converted

79 rows in table SYS.WRI$_DBU_FEATURE_METADATA are converted

17322 rows in table SYS.WRI$_ADV_OBJECTS are converted

80 rows in table SYS.METASTYLESHEET are converted

2323 rows in table SYS.WRI$_ADV_RATIONALE are converted

426 rows in table SYSTEM.SYS_IMPORT_FULL_01 are converted

2 rows in table SYS.EXTERNAL_TAB$ are converted

28 rows in table SYS.WRI$_DBU_FEATURE_USAGE are converted

PL/SQL procedure successfully completed.

update props$ 
set value$='AL16UTF16' 
where name = 'NLS_NCHAR_CHARACTERSET'; 
commit;
SHUTDOWN IMMEDIATE;

can now restart the database and verify if the characterset changes have been applied. Additionally, you need to revert the ‘job_queue_processes’ and ‘aq_tm_processes’ initialization parameters to their original values.

 You should be able to check:

SELECT * FROM NLS_DATABASE_PARAMETERS

Shouldbe:

NLS_CHARACTERSET      WE8ISO8859P1

NLS_NCHAR_CHARACTERSET     AL16UTF16

FOR RAC ONLY:

If the database is a RAC database, you must be in a single instance/single user mode before running 'csalter.plb':

1) shutdown one node and open up the DB in a single instance and single user mode:

2) set cluster_database=false

SQL> alter system set cluster_database=false scope=spfile;

System altered.

Sql> shutdown immediate

Sql> startup restrict

Sql> @csalter.plb