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


2 comments :

Unknown said...

Hi, We are doing Character ser conversion, however we are getting same issue like you,i am confusing here

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

what are the objects need to drop..?,can you please conform me.

BJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE STATUS
--------------------------------------------------------- ---------------------
DR$FND_LOBS_CTX$I
TABLE VALID

DR$FND_LOBS_CTX$K
TABLE VALID

DR$FND_LOBS_CTX$R
TABLE VALID


OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE STATUS
--------------------------------------------------------- ---------------------
DR$FND_LOBS_CTX$N
TABLE VALID

DR$FND_LOBS_CTX$X
INDEX VALID

DR$FND_LOBS_CTX$I
SYNONYM VALID


OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE STATUS
--------------------------------------------------------- ---------------------
DR$FND_LOBS_CTX$K
SYNONYM VALID

DR$FND_LOBS_CTX$N
SYNONYM VALID

DR$FND_LOBS_CTX$R
SYNONYM VALID

Unknown said...

can you please let me know what are objects i need to drop,

APPLSYS.BISM_OBJECTS
APPLSYS.BISM_OBJECTS|SYS_NC00023$
APPLSYS.DR$FND_LOBS_CTX$I
APPLSYS.DR$FND_LOBS_CTX$I|TOKEN_TEXT
APPLSYS.FND_CONC_PROG_ANNOTATIONS
APPLSYS.FND_CONC_PROG_ANNOTATIONS|PROGRA
APPLSYS.FND_OAM_CONTEXT_FILES
APPLSYS.FND_OAM_CONTEXT_FILES|TEXT
APPLSYS.FND_OAM_DOC_LINK
APPLSYS.FND_OAM_DOC_LINK|DOC_LINK_INFO
APPS.FND_OAM_CONTEXT_FILES_1
APPS.FND_OAM_CONTEXT_FILES_1|TEXT
AZ.AZ_APIS
AZ.AZ_APIS|FILTERING_PARAMETERS
AZ.AZ_SELECTION_SET_ENTITIES_B
AZ.AZ_SELECTION_SET_ENTITIES_B|FILTERING_PARA
ECX.ECX_DTDS
ECX.ECX_DTDS|PAYLOAD
ECX.ECX_FILES
ECX.ECX_FILES|PAYLOAD
IBC.IBC_ATTRIBUTE_BUNDLES_DATA
IBC.IBC_ATTRIBUTE_BUNDLES|ATTRIBUTE_BUNDLE_
JTF.JTF_HEADER_DTD
JTF.JTF_HEADER_DTD|HEADER_DTD
JTF.JTF_MESSAGE_OBJECTS
JTF.JTF_MESSAGE_OBJECTS|BUS_OBJ_DTD
JTF.JTF_MESSAGE_OBJECTS|BUS_OBJ_SQL
JTF.JTY_TRANS_USG_PGM_SQL
JTF.JTY_TRANS_USG_PGM_SQL|BATCH_DEA_SQL
JTF.JTY_TRANS_USG_PGM_SQL|BATCH_INCR_SQL
JTF.JTY_TRANS_USG_PGM_SQL|BATCH_TOTAL_SQL
JTF.JTY_TRANS_USG_PGM_SQL|INCR_REASSIGN_SQL
JTF.JTY_TRANS_USG_PGM_SQL|REAL_TIME_INSERT
JTF.JTY_TRANS_USG_PGM_SQL|REAL_TIME_SQL
MDSYS.OPENLS_NODES
MDSYS.OPENLS_NODES|SYS_NC00004$
MDSYS.SDO_COORD_OP_PARAM_VALS
MDSYS.SDO_COORD_OP_PARAM_VALS|PARAM_VAL
MDSYS.SDO_GEOR_XMLSCHEMA_TABLE
MDSYS.SDO_GEOR_XMLSCHEMA_TABLE|XMLSCHEM
MDSYS.SDO_STYLES_TABLE
MDSYS.SDO_STYLES_TABLE|DEFINITION
MDSYS.SDO_XML_SCHEMAS
MDSYS.SDO_XML_SCHEMAS|XMLSCHEMA
ODM.ODM_PMML_DTD
ODM.ODM_PMML_DTD|DTD
OKC.OKC_REPORT_SQL_B
OKC.OKC_REPORT_SQL_B|SQL_TEXT
OKC.OKC_REPORT_SQL_TL
OKC.OKC_REPORT_SQL_TL|HELP_TEXT
OKC.OKC_REPORT_XSL_TL
OKC.OKC_REPORT_XSL_TL|HELP_TEXT
OKC.OKC_REPORT_XSL_TL|XSL_TEXT
ORDDATA.ORDDCM_CT_PRED_OPRD
ORDDATA.ORDDCM_CT_PRED_OPRD|SYS_NC00004$
ORDDATA.ORDDCM_DOCS
ORDDATA.ORDDCM_DOCS|SYS_NC00005$
ORDDATA.ORDDCM_MAPPING_DOCS
ORDDATA.ORDDCM_MAPPING_DOCS|SYS_NC00007$

XDB.XDB$DXPTAB
XDB.XDB$DXPTAB|SYS_NC00006$
XDP.XDP_PROC_BODY
XDP.XDP_PROC_BODY|PROC_BODY