How to convert character on oracle R12.1.1

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
--------------------- ---------------- ---------------- ---------------- 
HAR2 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

No comments:

SHRD0014: GLLEZL - process exiting with failure

  SYMPTOMS Journal Import completes with the following error: Error ------ ORA-01653 : unable to extend table GL.GL_IMPORT_REFERENCES ORA-01...