在oracle 10.2.0.4上使用字符集扫描工具Csscan(二)

二、运行Csscan

1、查看csscan相关参数

HQ-CICUTESTDB-01:cicutedb>csscan help=y

Character Set Scanner v2.1 : Release 10.2.0.3.0 - Production on Tue Jun 17 03:26:22 2014

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

You can let Scanner prompt you for parameters by entering the CSSCAN        
command followed by your username/password:                                 
                                                                            
  Example: CSSCAN SYSTEM/MANAGER                                            
                                                                            
Or, you can control how Scanner runs by entering the CSSCAN command         
followed by various parameters. To specify parameters, you use keywords:    
                                                                            
  Example: CSSCAN SYSTEM/MANAGER FULL=y TOCHAR=utf8 ARRAY=1024000 PROCESS=3  
                                                                            
Keyword    Default Prompt Description                                       
---------- ------- ------ ------------------------------------------------- 
USERID             yes    username/password                                 
FULL       N       yes    scan entire database                              
USER               yes    owner of tables to be scanned                     
TABLE              yes    list of tables to scan                            
COLUMN             yes    list of columns to scan                            
EXCLUDE                   list of tables to exclude from scan               
TOCHAR             yes    new database character set name                   
FROMCHAR                  current database character set name               
TONCHAR                   new national character set name                   
FROMNCHAR                 current national character set name               
ARRAY      1024000 yes    size of array fetch buffer                        
PROCESS    1       yes    number of concurrent scan process                 
MAXBLOCKS                 split table if block size exceed MAXBLOCKS        
CAPTURE    N              capture convertible data                          
SUPPRESS                  maximum number of exceptions logged for each table
FEEDBACK                  report progress every N rows                      
BOUNDARIES                list of column size boundaries for summary report 
LASTRPT    N              generate report of the last database scan         
LOG        scan           base file name of report files                    
PARFILE                   parameter file name                               
PRESERVE   N              preserve existing scan results                    
LCSD       N       no     enable language and character set detection       
LCSDDATA   LOSSY   no     define the scope of the detection                 
HELP       N              show help screen (this screen)                    
QUERY      N              select clause to scan subset of tables or columns 
---------- ------- ------ ------------------------------------------------- 
Scanner terminated successfully.

2、运行csscan

[oracle@HQ-CICUTESTDB-01 ~]$ csscan userid="'"sys/oracle as sysdba"'" full=y fromchar=AL32UTF8 tochar=UTF8 log=cssan_check process=4 array=1024000

。。。省略
. process 1 scanning SYS.WRH$_ACTIVE_SESSION_HISTORY[AAAlypAADAAABPBAAA]
. process 2 scanning SYS.WRH$_SERVICE_WAIT_CLASS[AAAlu4AADAAAIKRAAA]
. process 3 scanning SYS.WRH$_SGASTAT[AAAltDAADAAAIRBAAA]
. process 4 scanning SYS.WRH$_ACTIVE_SESSION_HISTORY[AAAlq2AADAAAMxxAAA]

Creating Database Scan Summary Report...

Creating Individual Exception Report...

Scanner terminated successfully.

执行完毕后,会在当前目录下生成以参数log指定命名的三个文件,分别是:cssan_check.err  cssan_check.out  cssan_check.txt

二、Csscan结果分析

1、.out文件
cssan_check.out - Scan log -- provides a complete list of all scanned items     cssan的运行日志,只是记录的cssan的执行过程

2、.txt文件
cssan_check.txt - Database Scan Summary -- Summary of scan results     扫描结果概要
下面是一个TOCHAR=AL32UTF8的.txt文件样例:

HQ-CICUTESTDB-01:cicutedb>more cssan_check.txt 
Database Scan Summary Report
Time Started  : 2014-06-17 04:03:30
Time Completed: 2014-06-17 04:06:53

Process ID         Time Started       Time Completed
---------- -------------------- --------------------
         1  2014-06-17 04:03:32  2014-06-17 04:06:51
         2  2014-06-17 04:03:32  2014-06-17 04:06:51
         3  2014-06-17 04:03:32  2014-06-17 04:06:51
         4  2014-06-17 04:03:32  2014-06-17 04:06:51
---------- -------------------- --------------------

[Database Size]
Tablespace                           Used            Free           Total       Expansion
------------------------- --------------- --------------- --------------- ---------------
SYSTEM                            598.19M         401.81M       1,000.00M           1.00K
UNDOTBS1                          489.00M       3,511.00M       4,000.00M            .00K
SYSAUX                            408.13M         391.88M         800.00M            .00K
。。。
TBLSPACE_CLAIM                    127.63M       1,872.38M       2,000.00M            .00K
TBLSPACE_CSMIG                      9.19M          90.81M         100.00M            .00K
------------------------- --------------- --------------- --------------- ---------------
Total                          23,331.38M      44,568.63M      67,900.00M           1.00K

The size of the largest CLOB is 1625114 bytes

[Database Scan Parameters]
Parameter                      Value                                           
------------------------------ ------------------------------------------------
CSSCAN Version                 v2.1                                            
Instance Name                  cicutedb                                        
Database Version               10.2.0.4.0                                      
Scan type                      Full database                                   
Scan CHAR data?                YES                                             
Database character set         AL32UTF8                                        
FROMCHAR                       AL32UTF8                                        
TOCHAR                         UTF8                                            
Scan NCHAR data?               NO                                              
Array fetch buffer size        1024000                                         
Number of processes            4                                               
Capture convertible data?      NO                                              
------------------------------ ------------------------------------------------

[Scan Summary]
Some character type data in the data dictionary are not convertible to the new character set
Some character type application data are not convertible to the new character set

[Data Dictionary Conversion Summary]
Datatype                    Changeless      Convertible       Truncation            Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2                     4,864,310                0                0               58
CHAR                             1,104                0                0                0
LONG                           232,188                0                0                0
CLOB                            39,382                0                0                0
VARRAY                          22,432                0                0                0
--------------------- ---------------- ---------------- ---------------- ----------------
Total                        5,159,416                0                0               58
Total in percentage             99.999%           0.000%           0.000%           0.001%

The data dictionary can not be safely migrated using the CSALTER script

[Application Data Conversion Summary]
Datatype                    Changeless      Convertible       Truncation            Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2                 1,714,396,614                0                0           73,068
CHAR                           154,597                0                0                0
LONG                                 0                0                0                0
CLOB                            41,562                0                0                0
VARRAY                           1,575                0                0                0
--------------------- ---------------- ---------------- ---------------- ----------------
Total                    1,714,594,348                0                0           73,068
Total in percentage             99.996%           0.000%           0.000%           0.004%

[Distribution of Convertible, Truncated and Lossy Data by Table]
USER.TABLE                                              Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
SYS.SOURCE$                                                       0                0               5
SYS.WRH$_SQLSTAT                                                  0                0               53
UKPRODRES.GCADJUSTMENTCHARGE                                      0                0               10
UKPRODRES.GCADJUSTMENTFEE                                         0                0               34
UKPRODRES.GCADJUSTMENTITEM                                        0                0               32
。。。。。。

[Distribution of Convertible, Truncated and Lossy Data by Column]
USER.TABLE|COLUMN                                       Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
SYS.SOURCE$|SOURCE                                                0                0               5
SYS.WRH$_SQLSTAT|ACTION                                           0                0               53
UKPRODRES.GCADJUSTMENTCHARGE|PAYEE                                0                0               10
UKPRODRES.GCADJUSTMENTFEE|PAYEE                                   0                0               34
。。。。。。

[Indexes to be Rebuilt]
USER.INDEX on USER.TABLE(COLUMN)                                                         
-----------------------------------------------------------------------------------------
UKPRODRES.IDX_GCREGISTPOLICY_INSUREDNAME on UKPRODRES.GCREGISTPOLICY(INSUREDNAME)
UKPRODRES.IND_GSCLIMAIN_CLIENTCNAME on UKPRODRES.GSCLIENTMAIN(CLIENTCNAME)
。。。。。。
UKPRODRES.IND_GUPROPOSALMAIN_INSUREDNAME on UKPRODRES.GUPROPOSALMAIN(INSUREDNAME)
-----------------------------------------------------------------------------------------


The .txt file shows:

  •   Time Started / Time Completed: duration of the Csscan run. Csscan will do a fetch of all character data, so running time is in most cases at least the time to do a full export.
  •   [Database Size]:  the size of the data within the database, the Expansion column (if applicable) gives an estimation on how much more place you need in the current tablespace when going to the new characterset. The Tablespace Expansion for tablespace X is calculated as the grand total of the differences between the byte length of a string converted to the target character set and the original byte length  of this string over all strings scanned in tables in X. The distribution of values in blocks, PCTFREE, free extents, etc., are not taken into account.
  •   [Database Scan Parameters]: the parameters used to run Csscan
  •   [Scan Summary]: which gives you directly an idea if you can do a full exp/imp, use Csalter or "Alter Database Character Set" as described in point D)
  •   [Data Dictionary Conversion Summary]: gives an overview of the amount of Changeless, Convertible, Truncation or Lossy data there is in the Data Dictionary.
  •   [Application Data Conversion Summary]: gives an overview of the amount of Changeless, Convertible, Truncation or Lossy data there is in User data.
  •   [Distribution of Convertible Data per Table]: gives a breakdown on table basis.
  •   [Distribution of Convertible Data per Column]: gives a breakdown on column basis.
  •   [Indexes to be Rebuilt]: gives which indexes are going to be affected by convertible data. The name of the section is bit misleading. When using full export/import there nothing to do on those indexes. When using Cslater/alter database characterset together with a partial export/import it depends on the amount of 'convertible' data in the underlying columns. If only a few rows in the underlying columns are 'convertible' then there is nothing to do (the indexes do not need to be rebuild as such). But if you have a lot of 'convertible' data in underlying columns it might be a good idea to drop and recreate them after the import, simply for performance reasons. The only exception is an index on a CHAR/NCHAR column that you need to adapt for "truncation". In that case all key values of a CHAR/NCHAR index key have to be padded with blanks to the new length and it may be more efficient to drop and recreate the index.
  •   [Truncation Due To Character Semantics]: (not often seen) This can be seen if you use Char Semantics in the current database.The Truncation Due to Character Semantics section identifies the number of data cells that would be truncated if they were converted to the target character set (for example, by the SQL CONVERT function or another inline conversion process) before the database character set is updated with the Csalter script. If the data conversion occurs after the database character set is changed (= you use export/import for convertible data), then this section can be ignored.
     从上述的.txt样例中可以分析:数据字典数据和用户数据都有数据会在转换后丢失, 数据字典数据会丢失58行,占比0.001% 用户数据会丢失73068行 占比 0.004%

3、.err文件
cssan_check.err - Individual Exceptions -- Excerpted view of scan exceptions.     

 CAPTURE=Y or CAPTURE=N参数对.err文件有很大的影响:
CAPTURE=N:.err文件只会记录会lossy或truncation的行。
CAPTURE=Y:.err文件会记录会lossy或truncation和可转换的行。同时这会增加csmig 下的表空间使用,特别是可转换的行的量特别大时。SUPPRESS参数可以限制.err文件的大小,SUPPRESS=1000最多只会记录一个表的的1000行。

数据丢失或被截断的详细记录,包括涉及的表及列和rowid,样例如下:
HQ-CICUTESTDB-01:cicutedb>more cssan_check.err 
Database Scan Individual Exception Report

[Database Scan Parameters]

Parameter                      Value                                           
------------------------------ ------------------------------------------------
CSSCAN Version                 v2.1                                            
Instance Name                  cicutedb                                        
Database Version               10.2.0.4.0                                      
Scan type                      Full database                                   
Scan CHAR data?                YES                                             
Database character set         AL32UTF8                                        
FROMCHAR                       AL32UTF8                                        
TOCHAR                         UTF8                                            
Scan NCHAR data?               NO                                              
Array fetch buffer size        1024000                                         
Number of processes            4                                               
Capture convertible data?      NO                                              
------------------------------ ------------------------------------------------


[Data Dictionary individual exceptions]
User  : SYS
Table : SOURCE$
Column: SOURCE
Type  : VARCHAR2(4000)
Number of Exceptions         : 5         
Max Post Conversion Data Size: 4000      


ROWID              Exception Type      Size Cell Data(first 30 bytes)     
------------------ ------------------ ----- ------------------------------
AAAABIAABAAAR2VAB+ lossy conversion         raise_application_error(-20002
AAAABIAABAAAR2VAB7 lossy conversion         raise_application_error(-20001
AAAABIAABAAAR2VACO lossy conversion         raise_application_error(-20003
AAAABIAABAAAR2VACb lossy conversion         raise_application_error(-20003
AAAABIAABAAAR2VACv lossy conversion         raise_application_error(-20004
------------------ ------------------ ----- ------------------------------


User  : SYS
Table : WRH$_SQLSTAT
Column: ACTION
Type  : VARCHAR2(64)
Number of Exceptions         : 53        
Max Post Conversion Data Size: 39        


ROWID              Exception Type      Size Cell Data(first 30 bytes)     
------------------ ------------------ ----- ------------------------------
AAAlrOAADAAAIU+AAF lossy conversion         SQL 窗口 - 新建               
AAAlrOAADAAAJXNAAS lossy conversion         SQL 窗口 - 新建               
。。。。。。                  
AAAlvJAADAAAJ0wAAN lossy conversion         SQL 窗口 - 新建               
------------------ ------------------ ----- ------------------------------


[Application data individual exceptions]
User  : UKPRODRES
Table : GPREMINDERHISTORY
Column: RECEIVERNAME
Type  : VARCHAR2(1000)
Number of Exceptions         : 1         
Max Post Conversion Data Size: 71        
。。。。。。

In the .err this is listed:

  •  [Database Scan Parameters]: the used Csscan parameters.
  •  [Data Dictionary individual exceptions]: exceptions (LOSSY,TRUNCATION and CONVERTIBLE) for Data Dictionary objects.
  •  [Application data individual exceptions]: exceptions (LOSSY,TRUNCATION and CONVERTIBLE if CAPTURE=Y) for User objects.

For each affected column it lists:

  •  User : user name 
  •  Table : table name
  •  Column: column name
  •  Type : data type of the column and the defined column length
  •  Number of Exceptions : the number of rows in this column that are lossy, convertible or truncation.
  •  Max Post Conversion Data Size: the maximum size of the data in this column after conversion to the new characterset.
一般情况下,根据[Scan Summary]的结果可以采用下面的转换方法:

D.1) (any Oracle version) To use a (full) exp/imp into a database with the new characterset.

To use a (full) export/import into a database with the new characterset all data needs to be "changeless and convertible".

In order to use a full export/import without any data loss you need to see in the charcheck.txt file under [Scan Summary] this message::

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

You cannot use Csalter (10g and up) or "Alter Database Character Set" (8i/9i) if you see this as [Scan Summary].

D.2) (8i/9i only) To use "Alter Database Character Set".

To use "Alter Database Character Set" the Csscan output needs to be changeless for all CHAR VARCHAR2, CLOB and LONG data (Data Dictionary and Application Data).
In order to use "Alter Database Character Set" you need to see in the charcheck.txt file under [Scan Summary] this message::

All character type data in the data dictionary remain the same in the new character set
All character type application data remain the same in the new character set

A 'clean' Csscan run must have been completed prior to running "Alter Database Character Set". A 'clean' scan means that there is no convertible, truncation or lossy data in the database.

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


参考oracle support文档:Csscan Output Explained (文档 ID 444701.1)


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25465866/viewspace-1184874/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/25465866/viewspace-1184874/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值