How to convert character set of Oracle R12.1.1 running on 11.0.6 Oracle database from US7ASCII to UTF8/AL32UTF8

Documents I have followed ->

Globalization Guide for Oracle Applications Release 12 [ID 393861.1]

Migrating an Applications Installation to a New Character Set (Note 124721.1)

Changing the Database Character Set or the Database National Character Set (Note 66320.1)

AL32UTF8 UTF8 (Unicode) Database Character Set Implications [ID 788156.1]

Migrating an Applications Installation to a New Character Set

Csscan output explained [ID 444701.1]

Installing and configuring Csscan in 10g and 11g (Database Character Set Scanner) [ID 745809.1]

***************************

First install the csscan utility in your database by following note 745809.1. In 11.0.6 Database you need to install patch 6460895. But, the patch will give some error if you try opatch. Install this patch manually by following the note 737155.1.

Then run csscan. Analyze the output by following note 444701.1.

We found that before converting to UTF8 it is better to convert the character from US7AASCII to WE8MSWIN1252.

In our case we run csscan again for from char US7ASCII to WE8MSWIN1252.

csscan FULL=Y FROMCHAR=US7ASCII TOCHAR=WE8MSWIN1252 LOG=dbcheck CAPTURE=N ARRAY=1000000 PROCESS=16

Then again run csscan for WE8MSWIN1252 to WE8MSWIN1252

Now we run the following commands after restarting the database in restricted mode->

$sqlplus / as sysdba

SQL> @?/rdbms/admin/csalter.plb

Check the character has been converted to WE8MSWIN1252 or not.

Then again we run csscan for WE8MSWIN1252 to UTF8.

csscan FULL=Y FROMCHAR=WE8MSWIN1252 TOCHAR=UTF8 LOG=dbcheck CAPTURE=Y ARRAY=1000000 PROCESS=16

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

[Application Data Conversion Summary]

Datatype Changeless Convertible Truncation Lossy

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

VARCHAR2 206,240,584 4,004,043 288 0

CHAR 67,266 0 0 0

LONG 104,395 0 0 0

CLOB 174,692 24,127 0 0

VARRAY 30,623 0 0 0

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

Total 206,617,560 4,028,170 288 0

Total in percentage 98.088% 1.912% 0.000% 0.000%

USER.TABLE Convertible Truncation Lossy

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

AP.AP_NOTES 90 0 0

APPLSYS.BISM_OBJECTS 4 0 0

APPLSYS.DR$FND_LOBS_CTX$I 4,001,867 288 0

................................................

................................................

Run the SQL given in 444701.1 under B.3) TRUNCATION data

APPLSYS.DR$FND_LOBS_CTX$I (TOKEN_TEXT) - VARCHAR2 - 128 Bytes

PL/SQL procedure successfully completed.

SQL> desc APPLSYS.DR$FND_LOBS_CTX$I;

Name Null? Type

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

TOKEN_TEXT NOT NULL VARCHAR2(64)

TOKEN_TYPE NOT NULL NUMBER(3)

TOKEN_FIRST NOT NULL NUMBER(10)

TOKEN_LAST NOT NULL NUMBER(10)

TOKEN_COUNT NOT NULL NUMBER(10)

TOKEN_INFO BLOB

SQL> alter table APPLSYS.DR$FND_LOBS_CTX$I modify (TOKEN_TEXT VARCHAR2(128));

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

Now run the following commands or you can change the NLS_LANG parameter in env file->

$ echo $NLS_LANG

American_America.US7ASCII

$ export NLS_LANG=American_America.WE8MSWIN1252

$ echo $NLS_LANG

American_America.WE8MSWIN1252

take the dump of the convertible data table which you are getting from csscan output.

$ exp file=exp_14_02_11.dmp parfile=expparfilefinal2.txt LOG=exp_14_02_11.log

during export I got the errors like this [I am only mentioning the error lines]->

EXP-00011: APPLSYS.DR$FND_LOBS_CTX$I does not exist

EXP-00010: CTXSYS is not a valid username

EXP-00010: MDSYS is not a valid username

EXP-00010: ORDSYS is not a valid username

Solution ->

1. Now, I read the doc 139388.1 for "EXP-00011: APPLSYS.DR$FND_LOBS_CTX$I does not exist".

Also, For APPLSYS.DR$FND_LOBS_CTX$I, you can recreate the index after csalter by

1) running the script aflobbld.sql ( $FND_TOP/sql)

2) Go to the $FND_TOP/sql

3) connect sqlplus apps/apps

run the following script @aflobbld.sql applsys apps You may ignore the lossy and truncation data message for APPLSYS.DR$FND_LOBS_CTX$I For indexes reported with invalid/incompatible data, you need to drop the indexes before conversion and recreate after conversion you can recreate it by aflobbld.sql script

2. The other three errors are noted in 228482.1. For this don't truncate the data in those tables.

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

Truncate all the tables listed in csscan USER.TABLE section except the tables in SYS schema and CTXSYS, MDSYS, ORDSYS schema. Please note that although you can skip truncating SYS schema tables but later on we have to do it as csalter is giving error.

You may feel problem during truncating APPLSYS.BISM_OBJECTS because of forein key constraint and triggers. For that keep a backup code for generating the forign key and trigger on this table. Then drop the triggers and foreign keys. Then truncate the table. After importing the table after running csalter, compile the trigger and create the foreign keys.

Now, run csscan again before running csalter.

csscan FULL=Y FROMCHAR=WE8MSWIN1252 TOCHAR=UTF8 LOG=dbcheck CAPTURE=Y ARRAY=1000000 PROCESS=16

Run csalter again ->

SQL> @?/rdbms/admin/csalter.plb

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

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 validity...

begin converting system objects

declare

*

ERROR at line 1:

ORA-22839: Direct updates on SYS_NC columns are disallowed

ORA-06512: at "SYS.CSM$MAIN", line 172

ORA-06512: at line 10

declare

*

ERROR at line 1:

ORA-01403: no data found

ORA-06512: at line 9

-------

Solution ->

In RDBMS code , a newly added check was introduced on SYS_NC* column

Hence before updating SYS_NC* column, Events 22838 should be set to turn off this check.

--) Set the below event

SQL>ALTER SYSTEM SET EVENTS '22838 TRACE NAME CONTEXT LEVEL 1,FOREVER';

--) Then execute the CSALTER.PLB script

This will allow the characterset conversion process to complete successfully.

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

After csalter import all the truncated tables from the dmp file using any of the following commands. Please check the whether data has come in the imported table or not ->

imp file=exptab170211.dmp ignore=y fromuser=APPLSYS touser=APPLSYS log=APPLsys.log TABLES=(BISM_OBJECTS) buffer=10000

or

imp file=exptab170211.dmp FULL=Y ignore=y log=APPLsys.log buffer=10000