I thought there may be some incompatible conversion from WE8MSWIN1252 to UTF8, so I created a fresh database with CS =US7ASCII , but I failed to convert it . Here is what I did in 11.2.0.2 database. I remember I have no such problem in 10.2 .
1. Check the character set.
SYS@TTS> select * from nls_database_parameters;
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET US7ASCII
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 11.2.0.2.0
NLS_CSMIG_SCHEMA_VERSION 5
21 rows selected.
2. Install the cssan and no problem with test run.
orarac2poc:TTS:/u01/app/oracle/product/11.2.0/db_2/rdbms/admin> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Wed Mar 20 15:05:44 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SYS@TTS> @csminst
User created.
Grant succeeded.
Grant succeeded.
...
.
View created.
Grant succeeded.
Grant succeeded.
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
orarac2poc:TTS:/home/oracle/ops/exp/csscan> time csscan TABLE='(SYS.SQL_VERSION$)' FROMCHAR=US7ASCII TOCHAR=UTF8 LOG=instchkc CAPTURE=N PROCESS=1 ARRAY=1024000
Character Set Scanner v2.2 : Release 11.2.0.2.0 - Production on Thu Mar 21 09:53:42 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Enumerating table to scan...
. process 1 scanning SYS.SQL_VERSION$[AAAACVAABAAAASgAAA]
Creating Database Scan Summary Report...
Creating Individual Exception Report...
Scanner terminated successfully.
real 0m10.196s
user 0m0.071s
sys 0m0.028s
3. The test run report. Important for later comarison.
[Scan Summary]
All character type application data remain the same in the new character set
[Data Dictionary Conversion Summary]
Data Dictionary Tables:
Datatype Changeless Convertible Truncation Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2 4 0 0 0
CHAR 0 0 0 0
LONG 0 0 0 0
CLOB 0 0 0 0
VARRAY 0 0 0 0
--------------------- ---------------- ---------------- ---------------- ----------------
Total 4 0 0 0
Total in percentage 100.000% 0.000% 0.000% 0.000%
4. The FULL run scan.
rarac2poc:TTS:/home/oracle/ops/exp/csscan> time csscan FULL=Y FROMCHAR=US7ASCII TOCHAR=UTF8 LOG=dbcheck CAPTURE=N ARRAY=1000000 PROCESS=2
Character Set Scanner v2.2 : Release 11.2.0.2.0 - Production on Thu Mar 21 09:55:34 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Enumerating tables to scan...
. process 1 scanning SYS.SOURCE$[AAAADgAABAAAAXgAAA]
. process 2 scanning SYS.TYPE$[AAAAHvAABAAAA0IAAA]
. process 2 scanning SYS.ATTRIBUTE$[AAAAHvAABAAAA0IAAA]
. process 2 scanning SYS.PARAMETER$[AAAAHvAABAAAA0IAAA]
. process 2 scanning SYS.METHOD$[AAAAHvAABAAAA0IAAA]
. process 2 scanning SYS.TAB$[AAAAACAABAAAACQAAA]
...
. process 1 scanning EXFSYS.RLM$RULESETPRIVS
. process 2 scanning EXFSYS.RLM$RSPRIMEVENTS
. process 1 scanning EXFSYS.RLM$PRIMEVTTYPEMAP
. process 2 scanning EXFSYS.RLM$COLLGRPBYSPEC
. process 1 scanning EXFSYS.RLM$EQUALSPEC
. process 2 scanning EXFSYS.RLM$ORDERCLSALS
. process 2 scanning EXFSYS.RLM$DMLEVTTRIGS
Creating Database Scan Summary Report...
Creating Individual Exception Report...
Scanner terminated successfully.
real 1m3.600s
user 0m12.710s
sys 0m1.855s
4. The full run report.
orarac2poc:TTS:/home/oracle/ops/exp/csscan> more dbcheck.txt
Database Scan Summary Report
Time Started : 2013-03-21 09:55:39
Time Completed: 2013-03-21 09:56:36
Process ID Time Started Time Completed
---------- -------------------- --------------------
1 2013-03-21 09:55:53 2013-03-21 09:56:35
2 2013-03-21 09:55:53 2013-03-21 09:56:35
---------- -------------------- --------------------
[Database Size]
Tablespace Used Free Total Expansion
------------------------- --------------- --------------- --------------- ---------------
SYSTEM 573.44M 126.56M 700.00M 2.41M
SYSAUX 358.75M 241.25M 600.00M 11.67M
UNDOTBS1 20.25M 349.75M 370.00M .00K
TEMP .00K .00K .00K .00K
USERS 1,024.00K 4.00M 5.00M .00K
------------------------- --------------- --------------- --------------- ---------------
Total 953.44M 721.56M 1,675.00M 14.07M
The size of the largest CLOB is 1625114 bytes
[Database Scan Parameters]
Parameter Value
------------------------------ ------------------------------------------------
CSSCAN Version v2.1
Instance Name TTS
Database Version 11.2.0.2.0
Scan type Full database
Scan CHAR data? YES
Database character set US7ASCII
FROMCHAR US7ASCII
TOCHAR UTF8
Scan NCHAR data? NO
Array fetch buffer size 1000000
Number of processes 2
Capture convertible data? NO
------------------------------ ------------------------------------------------
[Scan Summary]
All character type data in the data dictionary are convertible to the new character set
All character type application data are convertible to the new character set
[Data Dictionary Conversion Summary]
Data Dictionary Tables:
Datatype Changeless Convertible Truncation Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2 2,544,999 0 0 0
CHAR 154 0 0 0
LONG 187,100 0 0 0
CLOB 49,072 1,403 0 0
VARRAY 41,492 0 0 0
--------------------- ---------------- ---------------- ---------------- ----------------
Total 2,822,817 1,403 0 0
Total in percentage 99.950% 0.050% 0.000% 0.000%
The data dictionary can be safely migrated using the CSALTER script
5. The summary says "are convertible to the new character set", and only CLOB of Data Dictionary Tables are belong to Convertible column. so I proceed with csalter.plb to change characterset from US7ASCII to UTF8, but failed.
orarac2poc:TTS:/u01/app/oracle/product/11.2.0/db_2/rdbms/admin> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu Mar 21 16:48:32 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SYS@TTS> @csalter.plb
0 rows created.
Function created.
Function created.
Procedure created.
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...
Unrecognized convertible data found in scanner result
PL/SQL procedure successfully completed.
Checking or Converting phase did not finish successfully
No database (national) character set will be altered
CSALTER finished unsuccessfully.
PL/SQL procedure successfully completed.
0 rows deleted.
Function dropped.
Function dropped.
Procedure dropped.
SYS@TTS> exit
6. According to below info excepted from "Changing the NLS_CHARACTERSET to AL32UTF8 / UTF8 (Unicode) [ID 260192.1]" , it seems to me that all "Convertible" Data Dictionary CLOB can be ignored.
8.b) For 10g and up the Csscan output needs to be
* "Changeless" for all CHAR VARCHAR2, and LONG data (Data Dictionary and User/Application data )
* "Changeless" for all User/Application data CLOB
* "Changeless" and/or "Convertible" for all Data Dictionary CLOB
7. Further checking into "Csscan Output Explained [ID 444701.1]", I don't the bold message in my report. Hence I am a bit confusing and stop here.
D.3) (10g and 11g) To use Csalter.
To use Csalter the Csscan output needs to be
* changeless for all CHAR VARCHAR2, and LONG data (Data Dictionary and Application Data)
* changeless for all Application Data CLOB
* changeless and/or convertible for all Data Dictionary CLOB
And in order to run Csalter you need to see in the charcheck.txt file under [Scan Summary] this message:
All character type application data remain the same in the new character set
and under [Data Dictionary Conversion Summary] this message:
The data dictionary can be safely migrated using the CSALTER script
Am I right to say that in 11.2 , it is unlikely change UTF8 subset to UTF8 character set with cssan/csalter.plb ?
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment