1 Reply Latest reply: Feb 17, 2013 5:58 AM by Srini Chavali-Oracle RSS

    Confusion in Migrating to AL32UTF8 database character set using CSSCAN

    987440
      Hi,

      I am trying to migrate the database character set from WE8MSWIN1252 to AL32UTF8.

      Operating System: Windows 7 64bit.
      Database: Oracle 11g

      Step 1) I install and run CSSCAN utility.

      scan.err file:

      Database Scan Individual Exception Report
      [Database Scan Parameters]
      
      Parameter                      Value                                           
      ------------------------------ ------------------------------------------------
      CSSCAN Version                 v2.1                                            
      Instance Name                  test                                            
      Database Version               11.2.0.1.0                                      
      Scan type                      Full database                                   
      Scan CHAR data?                YES                                             
      Database character set         WE8MSWIN1252                                    
      FROMCHAR                       WE8MSWIN1252                                    
      TOCHAR                         AL32UTF8                                        
      Scan NCHAR data?               NO                                              
      Array fetch buffer size        1024000                                         
      Number of processes            32                                              
      Capture convertible data?      NO                                              
      ------------------------------ ------------------------------------------------
      
      [Data Dictionary individual exceptions]
      
      
      [Application data individual exceptions]
      
      *scan.txt file*:
      
      Database Scan Summary Report
      
      Time Started  : 2013-02-15 22:30:21
      Time Completed: 2013-02-15 22:30:50
      
      Process ID         Time Started       Time Completed
      ---------- -------------------- --------------------
               1  2013-02-15 22:30:33  2013-02-15 22:30:49
      . . .
              32  2013-02-15 22:30:42  2013-02-15 22:30:49
      ---------- -------------------- --------------------
      
      [Database Size]
      
      Tablespace                           Used            Free           Total       Expansion
      ------------------------- --------------- --------------- --------------- ---------------
      SYSTEM                            678.00M           2.00M         680.00M           2.25M
      SYSAUX                            460.69M          29.31M         490.00M          13.28M
      UNDOTBS1                           24.13M          30.88M          55.00M            .00K
      TEMP                                 .00K            .00K            .00K            .00K
      USERS                               1.31M           3.69M           5.00M            .00K
      IIS_TABLES                          2.63M         197.38M         200.00M            .00K
      IIS_INDICES                         1.25M          98.75M         100.00M            .00K
      ------------------------- --------------- --------------- --------------- ---------------
      Total                           1,168.06M         361.94M       1,530.00M          15.53M
      
      The size of the largest CLOB is 1625114 bytes
      
      [Database Scan Parameters]
      
      Parameter                      Value                                           
      ------------------------------ ------------------------------------------------
      CSSCAN Version                 v2.1                                            
      Instance Name                  test                                            
      Database Version               11.2.0.1.0                                      
      Scan type                      Full database                                   
      Scan CHAR data?                YES                                             
      Database character set         WE8MSWIN1252                                    
      FROMCHAR                       WE8MSWIN1252                                    
      TOCHAR                         AL32UTF8                                        
      Scan NCHAR data?               NO                                              
      Array fetch buffer size        1024000                                         
      Number of processes            32                                              
      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                     3,339,700                0                0                0
      CHAR                             2,585                0                0                0
      LONG                           243,201                0                0                0
      CLOB                            63,415            1,205                0                0
      VARRAY                          42,147                0                0                0
      --------------------- ---------------- ---------------- ---------------- ----------------
      Total                        3,691,048            1,205                0                0
      Total in percentage             99.967%           0.033%           0.000%           0.000%
      
      The data dictionary can be safely migrated using the CSALTER script
      
      XML CSX Dictionary Tables:
      
      Datatype                    Changeless      Convertible       Truncation            Lossy
      --------------------- ---------------- ---------------- ---------------- ----------------
      VARCHAR2                           495                0                0                0
      CHAR                                 0                0                0                0
      LONG                                 0                0                0                0
      CLOB                                 0                0                0                0
      VARRAY                               0                0                0                0
      --------------------- ---------------- ---------------- ---------------- ----------------
      Total                              495                0                0                0
      Total in percentage            100.000%           0.000%           0.000%           0.000%
      
      [Application Data Conversion Summary]
      
      Datatype                    Changeless      Convertible       Truncation            Lossy
      --------------------- ---------------- ---------------- ---------------- ----------------
      VARCHAR2                     3,156,675                2                0                0
      CHAR                                48                0                0                0
      LONG                                 0                0                0                0
      CLOB                            22,299           10,893                0                0
      VARRAY                           6,913                0                0                0
      --------------------- ---------------- ---------------- ---------------- ----------------
      Total                        3,185,935           10,895                0                0
      Total in percentage             99.659%           0.341%           0.000%           0.000%
      
      [Distribution of Convertible, Truncated and Lossy Data by Table]
      
      Data Dictionary Tables:
      
      USER.TABLE                                              Convertible       Truncation            Lossy
      -------------------------------------------------- ---------------- ---------------- ----------------
      MDSYS.OPENLS_NODES                                               17                0                0
      MDSYS.SDO_COORD_OP_PARAM_VALS                                   200                0                0
      MDSYS.SDO_GEOR_XMLSCHEMA_TABLE                                    1                0                0
      MDSYS.SDO_STYLES_TABLE                                           78                0                0
      MDSYS.SDO_XML_SCHEMAS                                             3                0                0
      ORDDATA.ORDDCM_CT_PRED_OPRD                                      51                0                0
      ORDDATA.ORDDCM_DOCS                                               9                0                0
      ORDDATA.ORDDCM_MAPPING_DOCS                                       1                0                0
      SYS.METASTYLESHEET                                              178                0                0
      SYS.RULE$                                                         1                0                0
      SYS.SCHEDULER$_EVENT_LOG                                         15                0                0
      SYS.WRH$_SQL_PLAN                                               193                0                0
      SYS.WRH$_SQLTEXT                                                197                0                0
      SYS.WRI$_ADV_DIRECTIVE_META                                       5                0                0
      SYS.WRI$_ADV_OBJECTS                                             12                0                0
      SYS.WRI$_DBU_FEATURE_METADATA                                   188                0                0
      SYS.WRI$_DBU_FEATURE_USAGE                                        7                0                0
      SYS.WRI$_DBU_HWM_METADATA                                        20                0                0
      SYS.WRI$_REPT_FILES                                              27                0                0
      XDB.XDB$DXPTAB                                                    2                0                0
      -------------------------------------------------- ---------------- ---------------- ----------------
      
      XML CSX Dictionary Tables:
      
      USER.TABLE                                              Convertible       Truncation            Lossy
      -------------------------------------------------- ---------------- ---------------- ----------------
      -------------------------------------------------- ---------------- ---------------- ----------------
      
      Application Data:
      
      USER.TABLE                                              Convertible       Truncation            Lossy
      -------------------------------------------------- ---------------- ---------------- ----------------
      APEX_030200.WWV_FLOW_BANNER                                      10                0                0
      APEX_030200.WWV_FLOW_BUTTON_TEMPLATES                            12                0                0
      APEX_030200.WWV_FLOW_CUSTOM_AUTH_SETUPS                          19                0                0
      APEX_030200.WWV_FLOW_FLASH_CHART_SERIES                           5                0                0
      APEX_030200.WWV_FLOW_LIST_TEMPLATES                             298                0                0
      APEX_030200.WWV_FLOW_PAGE_GENERIC_ATTR                           44                0                0
      APEX_030200.WWV_FLOW_PAGE_PLUG_TEMPLATES                        254                0                0
      APEX_030200.WWV_FLOW_PAGE_PLUGS                               3,241                0                0
      APEX_030200.WWV_FLOW_PROCESSING                                  45                0                0
      APEX_030200.WWV_FLOW_ROW_TEMPLATES                               66                0                0
      APEX_030200.WWV_FLOW_SHORTCUTS                                   39                0                0
      APEX_030200.WWV_FLOW_STEP_PROCESSING                          2,239                0                0
      APEX_030200.WWV_FLOW_STEPS                                    1,797                0                0
      APEX_030200.WWV_FLOW_TEMPLATES                                  192                0                0
      APEX_030200.WWV_FLOW_WORKSHEETS                                  30                0                0
      SYSMAN.MGMT_HC_VENDOR_SW_SUMMARY                                  2                0                0
      SYSMAN.MGMT_IP_ELEM_DEFAULT_PARAMS                              130                0                0
      SYSMAN.MGMT_IP_REPORT_ELEM_PARAMS                             2,075                0                0
      SYSMAN.MGMT_IP_SQL_STATEMENTS                                    31                0                0
      SYSMAN.MGMT_JOB_OUTPUT                                            2                0                0
      SYSMAN.MGMT_SWLIB_ENTITY_DOCUMENTS                              364                0                0
      -------------------------------------------------- ---------------- ---------------- ----------------
      
      [Distribution of Convertible, Truncated and Lossy Data by Column]
      
      Data Dictionary Tables:
      
      USER.TABLE|COLUMN                                       Convertible       Truncation            Lossy
      -------------------------------------------------- ---------------- ---------------- ----------------
      MDSYS.OPENLS_NODES|SYS_NC00004$                                  17                0                0
      MDSYS.SDO_COORD_OP_PARAM_VALS|PARAM_VALUE_FILE                  200                0                0
      MDSYS.SDO_GEOR_XMLSCHEMA_TABLE|XMLSCHEMA                          1                0                0
      MDSYS.SDO_STYLES_TABLE|DEFINITION                                78                0                0
      MDSYS.SDO_XML_SCHEMAS|XMLSCHEMA                                   3                0                0
      ORDDATA.ORDDCM_CT_PRED_OPRD|SYS_NC00004$                         51                0                0
      ORDDATA.ORDDCM_DOCS|SYS_NC00005$                                  9                0                0
      ORDDATA.ORDDCM_MAPPING_DOCS|SYS_NC00007$                          1                0                0
      SYS.METASTYLESHEET|STYLESHEET                                   178                0                0
      SYS.RULE$|CONDITION                                               1                0                0
      SYS.SCHEDULER$_EVENT_LOG|ADDITIONAL_INFO                         15                0                0
      SYS.WRH$_SQL_PLAN|OTHER_XML                                     193                0                0
      SYS.WRH$_SQLTEXT|SQL_TEXT                                       197                0                0
      SYS.WRI$_ADV_DIRECTIVE_META|DATA                                  5                0                0
      SYS.WRI$_ADV_OBJECTS|ATTR4                                       12                0                0
      SYS.WRI$_DBU_FEATURE_METADATA|INST_CHK_LOGIC                     21                0                0
      SYS.WRI$_DBU_FEATURE_METADATA|USG_DET_LOGIC                     167                0                0
      SYS.WRI$_DBU_FEATURE_USAGE|FEATURE_INFO                           7                0                0
      SYS.WRI$_DBU_HWM_METADATA|LOGIC                                  20                0                0
      SYS.WRI$_REPT_FILES|SYS_NC00005$                                 27                0                0
      XDB.XDB$DXPTAB|SYS_NC00006$                                       2                0                0
      -------------------------------------------------- ---------------- ---------------- ----------------
      
      XML CSX Dictionary Tables:
      
      USER.TABLE|COLUMN                                       Convertible       Truncation            Lossy
      -------------------------------------------------- ---------------- ---------------- ----------------
      -------------------------------------------------- ---------------- ---------------- ----------------
      
      Application Data:
      
      USER.TABLE|COLUMN                                       Convertible       Truncation            Lossy
      -------------------------------------------------- ---------------- ---------------- ----------------
      APEX_030200.WWV_FLOW_BANNER|BANNER                               10                0                0
      APEX_030200.WWV_FLOW_BUTTON_TEMPLATES|TEMPLATE                   12                0                0
      APEX_030200.WWV_FLOW_CUSTOM_AUTH_SETUPS|AUTH_FUNC                 8                0                0
      APEX_030200.WWV_FLOW_CUSTOM_AUTH_SETUPS|PAGE_SENT                10                0                0
      APEX_030200.WWV_FLOW_CUSTOM_AUTH_SETUPS|POST_AUTH                 1                0                0
      APEX_030200.WWV_FLOW_FLASH_CHART_SERIES|SERIES_QU                 5                0                0
      APEX_030200.WWV_FLOW_LIST_TEMPLATES|ITEM_TEMPLATE                20                0                0
      APEX_030200.WWV_FLOW_LIST_TEMPLATES|ITEM_TEMPLATE                20                0                0
      APEX_030200.WWV_FLOW_LIST_TEMPLATES|LIST_TEMPLATE               105                0                0
      APEX_030200.WWV_FLOW_LIST_TEMPLATES|LIST_TEMPLATE               105                0                0
      APEX_030200.WWV_FLOW_LIST_TEMPLATES|SUB_LIST_ITEM                12                0                0
      APEX_030200.WWV_FLOW_LIST_TEMPLATES|SUB_LIST_ITEM                12                0                0
      APEX_030200.WWV_FLOW_LIST_TEMPLATES|SUB_TEMPLATE_                12                0                0
      APEX_030200.WWV_FLOW_LIST_TEMPLATES|SUB_TEMPLATE_                12                0                0
      APEX_030200.WWV_FLOW_PAGE_GENERIC_ATTR|ATTRIBUTE_                44                0                0
      APEX_030200.WWV_FLOW_PAGE_PLUG_TEMPLATES|TEMPLATE               166                0                0
      APEX_030200.WWV_FLOW_PAGE_PLUG_TEMPLATES|TEMPLATE                88                0                0
      APEX_030200.WWV_FLOW_PAGE_PLUGS|PLUG_SOURCE                   3,241                0                0
      APEX_030200.WWV_FLOW_PROCESSING|PROCESS_SQL_CLOB                 45                0                0
      APEX_030200.WWV_FLOW_ROW_TEMPLATES|ROW_TEMPLATE1                 54                0                0
      APEX_030200.WWV_FLOW_ROW_TEMPLATES|ROW_TEMPLATE2                 10                0                0
      APEX_030200.WWV_FLOW_ROW_TEMPLATES|ROW_TEMPLATE3                  2                0                0
      APEX_030200.WWV_FLOW_SHORTCUTS|SHORTCUT                          39                0                0
      APEX_030200.WWV_FLOW_STEP_PROCESSING|PROCESS_SQL_             2,239                0                0
      APEX_030200.WWV_FLOW_STEPS|HELP_TEXT                          1,514                0                0
      APEX_030200.WWV_FLOW_STEPS|HTML_PAGE_HEADER                     283                0                0
      APEX_030200.WWV_FLOW_TEMPLATES|BOX                               64                0                0
      APEX_030200.WWV_FLOW_TEMPLATES|FOOTER_TEMPLATE                   64                0                0
      APEX_030200.WWV_FLOW_TEMPLATES|HEADER_TEMPLATE                   64                0                0
      APEX_030200.WWV_FLOW_WORKSHEETS|SQL_QUERY                        30                0                0
      SYSMAN.MGMT_HC_VENDOR_SW_SUMMARY|NAME                             2                0                0
      SYSMAN.MGMT_IP_ELEM_DEFAULT_PARAMS|VALUE                        130                0                0
      SYSMAN.MGMT_IP_REPORT_ELEM_PARAMS|VALUE                       2,075                0                0
      SYSMAN.MGMT_IP_SQL_STATEMENTS|SQL_STATEMENT                      31                0                0
      SYSMAN.MGMT_JOB_OUTPUT|OUTPUT                                     2                0                0
      SYSMAN.MGMT_SWLIB_ENTITY_DOCUMENTS|VALUE                        364                0                0
      -------------------------------------------------- ---------------- ---------------- ----------------
      
      [Indexes to be Rebuilt]
      
      USER.INDEX on USER.TABLE(COLUMN)                                                         
      -----------------------------------------------------------------------------------------
      APEX_030200.WWV_FLOW_WORKSHEETS_UNQ_IDX on APEX_030200.WWV_FLOW_WORKSHEETS(SYS_NC00078$)
      APEX_030200.WWV_FLOW_WORKSHEETS_UNQ_IDX on APEX_030200.WWV_FLOW_WORKSHEETS(SYS_NC00079$)
      APEX_030200.WWV_FLOW_WORKSHEETS_UNQ_IDX on APEX_030200.WWV_FLOW_WORKSHEETS(SYS_NC00080$)
      APEX_030200.WWV_FLOW_WORKSHEETS_UNQ_IDX on APEX_030200.WWV_FLOW_WORKSHEETS(SYS_NC00081$)
      APEX_030200.WWV_FLOW_WS_UNQ_ALIAS_IDX on APEX_030200.WWV_FLOW_WORKSHEETS(SYS_NC00082$)
      APEX_030200.WWV_FLOW_WS_UNQ_ALIAS_IDX on APEX_030200.WWV_FLOW_WORKSHEETS(ALIAS)
      -----------------------------------------------------------------------------------------
      *Step 2) I run CSALTER in restrict mode.

      SQL> @D:\app\product\11.2.0\dbhome_1\RDBMS\ADMIN\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.


      Now, after this point how can I change the database character set?

      SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;

      This command appears to change the database character set. But I am not sure If this should be used.
      Please help.


      Any suggestion would be a great help.

      Thanks,
        • 1. Re: Confusion in Migrating to AL32UTF8 database character set using CSSCAN
          Srini Chavali-Oracle
          I have edited your post to use
           tags to make it more readable.
          
          Pl read thru Section A3 of this MOS Doc - 
          
          Solving Convertible or Lossy data in Data Dictionary objects reported by Csscan when changing the NLS_CHARACTERSET [ID 258904.1]
          
          You have convertible application data (APEX and SYSMAN) that will need to be handled before csalter will work.
          
          As noted in your earlier post, pl do not use this command
          
          {quote}
          SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;
          {quote}
          
          It is not documented/supported, and will corrupt your database beyond repair.
          
          HTH
          Srini