The case is we have a database with characterset WE8MSWIN1252, we need to migrate the schemas to different database which has different characterset.
What we did is we ran CSSCAN utility against the source database to check whether the characterset migration is possible or not.
step 1:
CSSCAN 'SYS/********@SourceDB as sysdba' user=schemaname fromchar=WE8MSWIN1252 TOCHAR=utf8 log=D:\CSS_SCAN_DETAILS\Schemaname_log
Step 2:
The above command will create three output files
1.Err output file.
2.Log file.
3.Text file
like Schemaname_log.err,schemaname_log.txt.schemaname_log.log
The text file contains the below output.
Database Scan Summary Report
Time Started : 2010-08-03 18:52:44
Time Completed: 2010-08-03 18:53:16
Process ID Time Started Time Completed
---------- -------------------- --------------------
1 2010-08-03 18:53:14 2010-08-03 18:53:15
---------- -------------------- --------------------
[Database Size]
Tablespace Used Free Total Expansion
------------------------- --------------- --------------- --------------- ---------------
SYSTEM 1,062.25M 7.75M 1,070.00M .00K
UNDOTBS1 17.94M 7,822.06M 7,840.00M .00K
SYSAUX 749.38M 50.63M 800.00M .00K
TEMP .00K .00K .00K .00K
USERS 2,392.50M 147.50M 2,540.00M .00K
EMP 28.88M 271.13M 300.00M .00K
EMPTEST 79.75M 220.25M 300.00M .00K
EMPPROD 78.00M 222.00M 300.00M .00K
------------------------- --------------- --------------- --------------- ---------------
Total 26,868.00M 19,685.06M 46,553.06M .00K
[Database Scan Parameters]
Parameter Value
------------------------------ ------------------------------------------------
CSSCAN Version v2.1
Instance Name dbdatabase
Database Version 10.2.0.4.0
Scan type User tables
User name TRAN
Scan CHAR data? YES
Database character set WE8MSWIN1252
FROMCHAR WE8MSWIN1252
TOCHAR utf8
Scan NCHAR data? NO
Array fetch buffer size 1024000
Number of processes 1
Capture convertible data? NO
------------------------------ ------------------------------------------------
[Scan Summary]
Some character type application data are not convertible to the new character set
[Data Dictionary Conversion Summary]
Datatype Changeless Convertible Truncation Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2 0 0 0 0
CHAR 0 0 0 0
LONG 0 0 0 0
CLOB 0 0 0 0
VARRAY 0 0 0 0
--------------------- ---------------- ---------------- ---------------- ----------------
Total 0 0 0 0
Total in percentage 0.000% 0.000% 0.000% 0.000%
[Application Data Conversion Summary]
Datatype Changeless Convertible Truncation Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2 4,499 29 2 0
CHAR 0 0 0 0
LONG 0 0 0 0
CLOB 0 0 0 0
VARRAY 0 0 0 0
--------------------- ---------------- ---------------- ---------------- ----------------
Total 4,499 29 2 0
Total in percentage 99.316% 0.640% 0.044% 0.000%
[Distribution of Convertible, Truncated and Lossy Data by Table]
USER.TABLE Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
TRAN.TBL_FILE_STORAGE 2 1 0
TRAN.TBL_TASK_DETAILS 3 0 0
TRAN.TBL_TASK_DOCUMENTS 1 1 0
TRAN.TBL_TRANSLATIONS 22 0 0
TRAN.TBL_USER_MASTER 1 0 0
-------------------------------------------------- ---------------- ---------------- ----------------
[Distribution of Convertible, Truncated and Lossy Data by Column]
USER.TABLECOLUMN Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
TRAN.TBL_FILE_STORAGEFIST_FILE_NAME 2 1 0
TRAN.TBL_TASK_DETAILSTADE_CUSTOMER_NAME 1 0 0
TRAN.TBL_TASK_DETAILSTADE_END_CUSTOMER_NA 1 0 0
TRAN.TBL_TASK_DETAILSTADE_INVOICE_SEND_TO 1 0 0
TRAN.TBL_TASK_DOCUMENTSTADO_SOURCE_FILENA 1 1 0
TRAN.TBL_TRANSLATIONSTRAN_VALUE 22 0 0
TRAN.TBL_USER_MASTERUSMA_PASSWORD 1 0 0
-------------------------------------------------- ---------------- ---------------- ----------------
[Indexes to be Rebuilt]
USER.INDEX on USER.TABLE(COLUMN)
-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
The error output file will contain the tables list that wich we need to work on when doing the migration like below
Database Scan Individual Exception Report
[Database Scan Parameters]
Parameter Value
------------------------------ ------------------------------------------------
CSSCAN Version v2.1
Instance Name dbdatabase
Database Version 10.2.0.4.0
Scan type User tables
User name TRAN
Scan CHAR data? YES
Database character set WE8MSWIN1252
FROMCHAR WE8MSWIN1252
TOCHAR utf8
Scan NCHAR data? NO
Array fetch buffer size 1024000
Number of processes 1
Capture convertible data? NO
------------------------------ ------------------------------------------------
[Data Dictionary individual exceptions]
[Application data individual exceptions]
User : TRAN
Table : TBL_FILE_STORAGE
Column: FIST_FILE_NAME
Type : VARCHAR2(50)
Number of Exceptions : 1
Max Post Conversion Data Size: 51
ROWID Exception Type Size Cell Data(first 30 bytes)
------------------ ------------------ ----- ------------------------------
AAAY62AAVAAAAHWAAU exceed column size 51 P3-FRA2028FR -Contrôle des DE
------------------ ------------------ ----- ------------------------------
User : TRAN
Table : TBL_TASK_DOCUMENTS
Column: TADO_SOURCE_FILENAME
Type : VARCHAR2(50)
Number of Exceptions : 1
Max Post Conversion Data Size: 51
ROWID Exception Type Size Cell Data(first 30 bytes)
------------------ ------------------ ----- ------------------------------
AAAY64AAVAAAAJuAAF exceed column size 51 P3-FRA2028FR -Contrôle des DE
------------------ ------------------ ----- ------------------------------
The logfile will contain the below output
Character Set Scanner v2.1 : Release 10.2.0.3.0 - Production on Tue Aug 3 18:52:43 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Enumerating tables to scan...
. process 1 scanning TRAN.TBL_EMAIL_LOG[AAAY6wAAVAAAAGhAAA]
. process 1 scanning TRAN.TBL_ROLE_ACCESS[AAAY5JAAVAAAAIZAAA]
. process 1 scanning TRAN.TBL_TASK_STATUS[AAAY5UAAVAAAAJ5AAA]
. process 1 scanning TRAN.TBL_SYSTEM_VARIABLES[AAAY5MAAVAAAAIxAAA]
. process 1 scanning TRAN.TBL_USER_MASTER[AAAY5gAAVAAAALZAAA]
. process 1 scanning TRAN.TBL_TRANSLATION_CODES[AAAY5aAAVAAAAKpAAA]
. process 1 scanning TRAN.TBL_TASK_DETAILS[AAAY5PAAVAAAAJJAAA]
. process 1 scanning TRAN.TBL_TASK_DOCUMENTS[AAAY64AAVAAAAJpAAA]
. process 1 scanning TRAN.TBL_TRANSLATIONS[AAAY5XAAVAAAAKRAAA]
. process 1 scanning TRAN.TBL_USER_ROLES[AAAY5nAAVAAAANZAAA]
. process 1 scanning TRAN.TBL_FORMAT_CODES[AAAZiFAAkAAAAAZAAA]
. process 1 scanning TRAN.TBL_LIST_DETAILS[AAAY5DAAVAAAAHpAAA]
. process 1 scanning TRAN.TBL_LIST_MASTER[AAAY5GAAVAAAAIBAAA]
. process 1 scanning TRAN.TBL_FILE_STORAGE[AAAY62AAVAAAAHRAAA]
Creating Database Scan Summary Report...
Creating Individual Exception Report...
Scanner terminated successfully.
From the above reports,the error file says we need to alter the column of the couple of the tables for the migration to be sucessfull.
So based on the report we altered the column size according to the report and did the migration sucessfully.
No comments:
Post a Comment