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:
Post a Comment