oracle数据库字符集characterset迁移及变更之csscan及csalter.plb系列三
背景
本文主要熟悉下如何用csscan以及csalter脚本变更数据库字符集，进一步理解如何调整数据库字符集的知识。之前的相关文章链接：
oracle10g_csscan_更变数据库字符集及国家字符集的工具测试
http://blog.itpub.net/9240380/viewspace-614070/
csscan: error while loading shared libraries: libclntsh.so.10.1
http://blog.itpub.net/9240380/viewspace-766685/
分析思路
结论
1,csscan结合csalter.plb可以调整数据库的数据库字符集2,运行完csscan后，会在当前目录产生3个文件：
[oracle@seconary admin]$ ll -lrht scan*
-rw-r--r-- 1 oracle oinstall 9.2K Nov 30 01:45 scan.txt ---需要关注的文件
-rw-r--r-- 1 oracle oinstall 87K Nov 30 01:45 scan.out ---这个就是类似LOG之类的文件
-rw-r--r-- 1 oracle oinstall 5.0K Nov 30 01:45 scan.err --需要关注的文件
具体关于这几个文件，请见下述测试
只要把上述2个关注文件里面的错误解决完毕，方可继续运行csalter.plb,否则会报如下错误
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.
3，csscan的用户和exp同理，也分为数据库级，用户级，表级，为了方便运行，可以采用命令行模式运行：
csscan userid=/'sys/system as sysdba/' full=y tochar=AL32UTF8 array=1024000 process=10
4,具体分析思路为：
[oracle@seconary admin]$ more scan.txt
Data Dictionary Tables:
USER.TABLE|COLUMN Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------
XML CSX Dictionary Tables:
USER.TABLE|COLUMN Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------
Application Data:
USER.TABLE|COLUMN Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------
确认上述文件中列Convertible的内容为0或空，否则在运行csalter.plb会上述第2提到的错误
因为这步我没有理解到位，消耗了1个小时左右的时间
4，后来是查阅MOS文章：oracle_更改数据库字符集 (NLS_CHARACTERSET) ，受到启发，用上述第4提示的涉及Convertible的表或相关数据，把这些数据EXP出来
然后再删除这些数据，接着重新运行csscan,直到cscsan.txt中的Convertible为空，最后再运行csalter.plb，就成功了，然后导入EXP的数据
不过记得一定要重启数据库
这步可以说是我认为最有价值的地方
5，再引申一下，其实从csalter.plb运行输出日志也会有对应的提示，如下：
Checking data validity...
Unrecognized convertible data found in scanner result ---看到没 convertible，Unrecognized，这里就很重要
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.
所以说一定要深入理解输出日志的报错，太重要了，太重要了
6,关于csscan运行产生的3个日志，理解还不够，还要进一步理解，结合实战案例，解决问题
分析思路
测试
SQL> select parameter,value from nls_database_parameters where parameter='NLS_CHARACTERSET';
PARAMETER VALUE
------------------------------ --------------------------------------------------
NLS_CHARACTERSET ZHS16GBK
SQL> show user
USER is "SCOTT"
SQL> select a,dump(a) from t_charset where rownum=1;
A DUMP(A)
---------- --------------------------------------------------
我们 Typ=96 Len=10: 230,136,145,228,187,172,32,32,32,32
---查看csscan用法
[oracle@seconary admin]$ csscan -help
Character Set Scanner v2.2 : Release 11.2.0.1.0 - Production on Mon Nov 30 01:32:25 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. 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 AS SYSDBA/"
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 AS SYSDBA/" 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 --获取数据的ARRAY大小
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.
[oracle@seconary admin]$
---提示CSS-00107: Character set migration utility schema not installed好像一个什么用户示例数据没安装
[oracle@seconary admin]$ csscan userid=/'sys/system as sysdba/' full=y
Character Set Scanner v2.2 : Release 11.2.0.1.0 - Production on Mon Nov 30 01:37:02 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
CSS-00107: Character set migration utility schema not installed
Scanner terminated unsuccessfully.
根据我之前的文章：
http://blog.itpub.net/9240380/viewspace-614070/
可解决上述的问题
[oracle@seconary admin]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 30 01:39:05 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @$ORACLE_HOME/rdbms/admin/csminst.sql
中间内容略
View created.
View created.
View created.
Grant succeeded.
Grant succeeded.
--重新运行CSSCAN即可
[oracle@seconary admin]$ csscan userid=/'sys/system as sysdba/' full=y
Character Set Scanner v2.2 : Release 11.2.0.1.0 - Production on Mon Nov 30 01:40:19 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Current database character set is ZHS16GBK.
Enter new database character set name: > AL32UTF8 --输入目标字符集
Enter array fetch buffer size: 1024000 > 可见与csscan命令行的参数可以对应起来，这里是交互式方式，道理同exp
Enter number of scan processes to utilize(1..64): 1 > 10
Enumerating tables to scan...
. process 1 scanning SYS.SOURCE$[AAAADfAABAAAAXYAAA] ---可见会扫描所有用户的表
. process 7 scanning SYS.SOURCE$[AAAADfAABAAAR+AAAA]
. process 3 scanning SYS.PARAMETER$[AAAAH3AABAAAAzgAAA]
. process 3 scanning SYS.OBJ$[AAAAASAABAAAADwAAA]
. process 7 scanning APEX_030200.WWV_FLOW_CALS[AAARPaAACAAAKHgAAA]
. process 2 scanning SCOTT.EMP[AAAR3xAAEAAAACQAAA]
. process 10 scanning SCOTT.T_VARCHAR[AAAR7TAAEAAAACoAAA] --这个就是我们的业务表
. process 8 scanning SYS.FILE$[AAAAARAABAAAADoAAA]
. process 1 scanning SYS.PROPS$[AAAABiAABAAAAMgAAA]
. process 4 scanning SYS.TRIGGERJAVAS$[AAAACmAABAAAAUoAAA]
. process 6 scanning SYS.PENDING_TRANS$[AAAAEHAABAAAAdQAAA]
. process 5 scanning SYS.MAP_FILE_EXTENT$[AAAAERAABAAAAeIA
Creating Database Scan Summary Report...
Creating Individual Exception Report...
Scanner terminated successfully.
--CSSCAN会在当前目录生成相关日志
[oracle@seconary admin]$ ll -lrht scan*
-rw-r--r-- 1 oracle oinstall 9.2K Nov 30 01:45 scan.txt
-rw-r--r-- 1 oracle oinstall 87K Nov 30 01:45 scan.out
-rw-r--r-- 1 oracle oinstall 5.0K Nov 30 01:45 scan.err
---可见scan.txt是一个扫描后形成的报告，包括扫描进程消耗的进程以及开始及结束时间，以及每个表空间迁移后空间变化的情况，以及扫描定制的参数，即针对于CSSCAN
以及各种字典表及应用表，基于不同数据类型分组的，数据迁移的情况，这个非常细分
[oracle@seconary admin]$ more scan.txt
Database Scan Summary Report
Time Started : 2015-11-30 01:41:47
Time Completed: 2015-11-30 01:45:41
Process ID Time Started Time Completed
---------- -------------------- --------------------
1 2015-11-30 01:42:32 2015-11-30 01:45:36
2 2015-11-30 01:42:36 2015-11-30 01:45:36
3 2015-11-30 01:42:34 2015-11-30 01:45:36
4 2015-11-30 01:42:36 2015-11-30 01:45:36
5 2015-11-30 01:42:35 2015-11-30 01:45:36
6 2015-11-30 01:42:35 2015-11-30 01:45:36
7 2015-11-30 01:42:34 2015-11-30 01:45:36
8 2015-11-30 01:42:34 2015-11-30 01:45:36
9 2015-11-30 01:42:34 2015-11-30 01:45:36
10 2015-11-30 01:42:36 2015-11-30 01:45:36
---------- -------------------- --------------------
[Database Size]
Tablespace Used Free Total Expansion
------------------------- --------------- --------------- --------------- ---------------
SYSTEM 670.81M 9.19M 680.00M .00K
SYSAUX 461.63M 28.38M 490.00M .00K
UNDOTBS1 5.81M 69.19M 75.00M .00K
TEMP .00K .00K .00K .00K
USERS 21.63M 2.13M 23.75M 3.00M
------------------------- --------------- --------------- --------------- ---------------
Total 1,159.88M 108.88M 1,268.75M 3.00M
[Database Scan Parameters]
Parameter Value
------------------------------ ------------------------------------------------
CSSCAN Version v2.1
Instance Name charzhs
Database Version 11.2.0.1.0
Scan type Full database
Scan CHAR data? YES
Database character set ZHS16GBK
FROMCHAR ZHS16GBK
TOCHAR AL32UTF8
Scan NCHAR data? NO
Array fetch buffer size 1024000
Number of processes 10
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,281,790 34 0 0
CHAR 2,594 0 0 0
LONG 239,383 0 0 0
VARRAY 42,147 0 0 0
--------------------- ---------------- ---------------- ---------------- ----------------
Total 3,565,914 34 0 0
Total in percentage 99.999% 0.001% 0.000% 0.000%
The data dictionary can not 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
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,010,086 1 0 0
CHAR 47 1,048,577 0 0
LONG 0 0 0 0
VARRAY 6,912 0 0 0
--------------------- ---------------- ---------------- ---------------- ----------------
Total 3,017,045 1,048,578 0 0
Total in percentage 74.209% 25.791% 0.000% 0.000%
[Distribution of Convertible, Truncated and Lossy Data by Table]
Data Dictionary Tables:
USER.TABLE Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
SYS.AUD$ 5 0 0
SYS.USER$ 1 0 0
SYS.WRH$_SQLSTAT 28 0 0
-------------------------------------------------- ---------------- ---------------- ----------------
XML CSX Dictionary Tables:
USER.TABLE Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------
Application Data:
USER.TABLE Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
SCOTT.T_CHARSET 524,289 0 0
SCOTT.T_CHARSET_BAK 524,288 0 0
SCOTT.T_VARCHAR 1 0 0
-------------------------------------------------- ---------------- ---------------- ----------------
[Distribution of Convertible, Truncated and Lossy Data by Column]
Data Dictionary Tables:
USER.TABLE|COLUMN Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
SYS.AUD$|USERID 5 0 0
SYS.USER$|NAME 1 0 0
SYS.WRH$_SQLSTAT|PARSING_SCHEMA_NAME 28 0 0
-------------------------------------------------- ---------------- ---------------- ----------------
XML CSX Dictionary Tables:
USER.TABLE|COLUMN Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------
Application Data:
USER.TABLE|COLUMN Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
SCOTT.T_CHARSET|A 524,289 0 0
SCOTT.T_CHARSET_BAK|A 524,288 0 0
SCOTT.T_VARCHAR|A 1 0 0
-------------------------------------------------- ---------------- ---------------- ----------------
[Indexes to be Rebuilt]
USER.INDEX on USER.TABLE(COLUMN)
-----------------------------------------------------------------------------------------
SYS.I_USER1 on SYS.USER$(NAME)
-----------------------------------------------------------------------------------------
[oracle@seconary admin]
--scan.err记录迁移失败一些记录信息，全与我之前测试创建的包含中文字符的数据库用户名相关
[oracle@seconary admin]$ more scan.err
Database Scan Individual Exception Report
[Database Scan Parameters]
Parameter Value
------------------------------ ------------------------------------------------
CSSCAN Version v2.1
Instance Name charzhs
Database Version 11.2.0.1.0
Scan type Full database
Scan CHAR data? YES
Database character set ZHS16GBK
FROMCHAR ZHS16GBK
TOCHAR AL32UTF8
Scan NCHAR data? NO
Array fetch buffer size 1024000
Number of processes 10
Capture convertible data? NO
------------------------------ ------------------------------------------------
[Data Dictionary individual exceptions]
User : SYS
Table : WRH$_SQLSTAT
Column: PARSING_SCHEMA_NAME
Type : VARCHAR2(30)
Number of Exceptions : 0
Max Post Conversion Data Size: 11
ROWID Exception Type Size Cell Data(first 30 bytes)
------------------ ------------------ ----- ------------------------------
AAAR5hAACAAAOXpAAU convertible ZXY翟z
AAAR5hAACAAAOXpAAW convertible ZXY翟z
AAAR5hAACAAAOXpAAY convertible ZXY翟z
AAAR5hAACAAAOXqAAA convertible ZXY翟z
AAAR5hAACAAAOXqAAB convertible ZXY翟z
AAAR5hAACAAAOXqAAD convertible ZXY翟z
AAAR5hAACAAAOXqAAE convertible ZXY翟z
AAAR5hAACAAAOXqAAI convertible ZXY翟z
AAAR5hAACAAAOXqAAN convertible ZXY翟z
AAAR5hAACAAAOXqAAS convertible ZXY翟z
AAAR5hAACAAAOXqAAT convertible ZXY翟z
AAAR5hAACAAAOXqAAU convertible ZXY翟z
AAAR5hAACAAAOXqAAa convertible ZXY翟z
AAAR5hAACAAAOXqAAc convertible ZXY翟z
AAAR5hAACAAAOXrAAD convertible ZXY翟z
AAAR5hAACAAAOXrAAE convertible ZXY翟z
AAAR5hAACAAAOXrAAI convertible ZXY翟z
AAAR5hAACAAAOXrAAL convertible ZXY翟z
AAAR5hAACAAAOXrAAN convertible ZXY翟z
AAAR5hAACAAAOXrAAQ convertible ZXY翟z
AAAR5hAACAAAOXrAAR convertible ZXY翟z
AAAR5hAACAAAOXrAAS convertible ZXY翟z
AAAR5hAACAAAOXsAAF convertible ZXY翟z
AAAR5hAACAAAOXsAAL convertible ZXY翟z
AAAR5hAACAAAOXsAAN convertible ZXY翟z
AAAR5hAACAAAOXsAAP convertible ZXY翟z
AAAR5hAACAAAOXsAAR convertible ZXY翟z
AAAR5hAACAAAOXsAAW convertible ZXY翟z
------------------ ------------------ ----- ------------------------------
User : SYS
Table : USER$
Column: NAME
Type : VARCHAR2(30)
Number of Exceptions : 0
Max Post Conversion Data Size: 27
ROWID Exception Type Size Cell Data(first 30 bytes)
------------------ ------------------ ----- ------------------------------
AAAAAKAABAAAADVAAE convertible ZXY翟z
------------------ ------------------ ----- ------------------------------
User : SYS
Table : AUD$
Column: USERID
Type : VARCHAR2(30)
Number of Exceptions : 0
Max Post Conversion Data Size: 9
ROWID Exception Type Size Cell Data(first 30 bytes)
------------------ ------------------ ----- ------------------------------
AAAAF/AABAAAAj5AAk convertible ZXY翟z
AAAAF/AABAAAAj5AAl convertible ZXY翟z
AAAAF/AABAAAAj5AAm convertible ZXY翟z
AAAAF/AABAAAAj5AAn convertible ZXY翟z
AAAAF/AABAAAAj5AAo convertible ZXY翟z
------------------ ------------------ ----- ------------------------------
[Application data individual exceptions]
[oracle@seconary admin]$
---scan.out就是真正的CSSCAN的输出日志了
[oracle@seconary admin]$ more scan.out
Character Set Scanner v2.2 : Release 11.2.0.1.0 - Production on Mon Nov 30 01:40:19 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Current database character set is ZHS16GBK.
Enter new database character set name: >
Enter array fetch buffer size: 1024000 >
Enter number of scan processes to utilize(1..64): 1 >
Enumerating tables to scan...
. process 1 scanning SYS.SOURCE$[AAAADfAABAAAAXYAAA]
. process 7 scanning SYS.SOURCE$[AAAADfAABAAAR+AAAA]
. process 3 scanning SYS.PARAMETER$[AAAAH3AABAAAAzgAAA]
. process 8 scanning SYS.METHOD$[AAAAH3AABAAAAzgAAA]
. process 9 scanning SYS.ATTRIBUTE$[AAAAH3AABAAAAzgAAA]
. process 6 scanning SYS.TYPE$[AAAAH3AABAAAAzgAAA]
. process 5 scanning SYS.LIBRARY$[AAAAACAABAAAACQAAA]
ORA-30967: operation directly on the Path Table is disallowed
CSS-00144: failed to scan table XDB.PRIN_PT
ORA-30967: operation directly on the Path Table is disallowed
CSS-00144: failed to scan table XDB.SC_PT
---可见ora-30967与域索引相关，我们不研究这个，这是ORACLE一种技术
[oracle@seconary admin]$ oerr ora 30967
30967, 00000, "operation directly on the Path Table is disallowed"
// *Cause: illegal operation on secondary objects of a domain index
// *Action: Try appropriate operation on the domain index.
注意：到这里其实数据库的字符集没有调整为al32utf8,好了，我们继续操作
[oracle@seconary admin]$ pwd
/oracle/product/11.2.0/db_1/rdbms/admin
[oracle@seconary admin]$ ll csal*
-rw-r--r-- 1 oracle oinstall 6431 Aug 3 2009 csalter.plb
[oracle@seconary admin]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 30 01:59:20 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @@csalter.plb
0 rows created.
Function created.
Function created.
Procedure created.
This script will update the content of the Oracle Data Dictionary. 这里很重要，ORACLE提示会更新数据字典的内容，所在操作之前一定要备份数据库，不然万一失败就玩大了
Please ensure you have a full backup before initiating this procedure.
Would you like to proceed (Y/N)?Y
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.
---可见重启库仍然无效，源字符集zhs16gbk并未转化指定的al32utf8
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size 2213896 bytes
Variable Size 956303352 bytes
Database Buffers 687865856 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL> select parameter,value from nls_database_parameters where parameter='NLS_CHARACTERSET';
PARAMETER
------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_CHARACTERSET
ZHS16GBK
基于上述CSSCAN中错误提示，解决包含中文字符的用户
SQL> select username from dba_users;
USERNAME
------------------------------
SYS
SYSTEM
ZXY翟z
SCOTT
OUTLN
CSMIG
MGMT_VIEW
FLOWS_FILES
MDSYS
ORDSYS
EXFSYS
USERNAME
------------------------------
DBSNMP
WMSYS
APPQOSSYS
APEX_030200
OWBSYS_AUDIT
ORDDATA
CTXSYS
ANONYMOUS
SYSMAN
XDB
ORDPLUGINS
USERNAME
------------------------------
OWBSYS
SI_INFORMTN_SCHEMA
OLAPSYS
ORACLE_OCM
XS$NULL
MDDATA
DIP
APEX_PUBLIC_USER
SPATIAL_CSW_ADMIN_USR
SPATIAL_WFS_ADMIN_USR
32 rows selected.
SQL> drop user ZXY翟z cascade;
User dropped.
[oracle@seconary admin]$ pwd
/oracle/admin
[oracle@seconary admin]$ ll scan*
-rw-r--r-- 1 oracle oinstall 5043 Nov 30 01:45 scan.err
-rw-r--r-- 1 oracle oinstall 89077 Nov 30 01:45 scan.out
-rw-r--r-- 1 oracle oinstall 9340 Nov 30 01:45 scan.txt
[oracle@seconary admin]$ rm -rf scan*
---发现还是有残留的坏数据，想办法移除这些坏数据
[oracle@seconary admin]$ more scan.err
Database Scan Individual Exception Report
[Database Scan Parameters]
Parameter Value
------------------------------ ------------------------------------------------
CSSCAN Version v2.1
Instance Name charzhs
Database Version 11.2.0.1.0
Scan type Full database
Scan CHAR data? YES
Database character set ZHS16GBK
FROMCHAR ZHS16GBK
TOCHAR AL32UTF8
Scan NCHAR data? NO
Array fetch buffer size 1024000
Number of processes 10
Capture convertible data? NO
------------------------------ ------------------------------------------------
[Data Dictionary individual exceptions]
User : SYS
Table : WRH$_SQLSTAT
Column: PARSING_SCHEMA_NAME ---提示你是哪个表的哪个列有问题
Type : VARCHAR2(30)
Number of Exceptions : 0
Max Post Conversion Data Size: 11
ROWID Exception Type Size Cell Data(first 30 bytes)
------------------ ------------------ ----- ------------------------------
AAAR5hAACAAAOXpAAU convertible ZXY翟z
AAAR5hAACAAAOXpAAW convertible ZXY翟z
AAAR5hAACAAAOXpAAY convertible ZXY翟z
AAAR5hAACAAAOXqAAA convertible ZXY翟z
AAAR5hAACAAAOXqAAB convertible ZXY翟z
AAAR5hAACAAAOXqAAD convertible ZXY翟z
AAAR5hAACAAAOXqAAE convertible ZXY翟z
AAAR5hAACAAAOXqAAI convertible ZXY翟z
AAAR5hAACAAAOXqAAN convertible ZXY翟z
AAAR5hAACAAAOXqAAS convertible ZXY翟z
AAAR5hAACAAAOXqAAT convertible ZXY翟z
AAAR5hAACAAAOXqAAU convertible ZXY翟z
AAAR5hAACAAAOXqAAa convertible ZXY翟z
AAAR5hAACAAAOXqAAc convertible ZXY翟z
AAAR5hAACAAAOXrAAD convertible ZXY翟z
AAAR5hAACAAAOXrAAE convertible ZXY翟z
AAAR5hAACAAAOXrAAI convertible ZXY翟z
AAAR5hAACAAAOXrAAL convertible ZXY翟z
AAAR5hAACAAAOXrAAN convertible ZXY翟z
AAAR5hAACAAAOXrAAQ convertible ZXY翟z
AAAR5hAACAAAOXrAAR convertible ZXY翟z
AAAR5hAACAAAOXrAAS convertible ZXY翟z
AAAR5hAACAAAOXsAAF convertible ZXY翟z
AAAR5hAACAAAOXsAAL convertible ZXY翟z
AAAR5hAACAAAOXsAAN convertible ZXY翟z
AAAR5hAACAAAOXsAAP convertible ZXY翟z
AAAR5hAACAAAOXsAAR convertible ZXY翟z
AAAR5hAACAAAOXsAAW convertible ZXY翟z
------------------ ------------------ ----- ------------------------------
User : SYS
Table : AUD$
Column: USERID ---这个列有问题
Type : VARCHAR2(30)
Number of Exceptions : 0
Max Post Conversion Data Size: 9
ROWID Exception Type Size Cell Data(first 30 bytes)
------------------ ------------------ ----- ------------------------------
AAAAF/AABAAAAj5AAk convertible ZXY翟z
AAAAF/AABAAAAj5AAl convertible ZXY翟z
AAAAF/AABAAAAj5AAm convertible ZXY翟z
AAAAF/AABAAAAj5AAn convertible ZXY翟z
AAAAF/AABAAAAj5AAo convertible ZXY翟z
------------------ ------------------ ----- ------------------------------
[Application data individual exceptions]
[oracle@seconary admin]$
其实也可以解决更新此列，不过这是底层表，和其它表的关联非常复杂，不能简单的UPDATE，当然DELETE也不是一种非常好的方法
SQL> select userid from aud$ where rowid in ('AAAAF/AABAAAAj5AAk','AAAAF/AABAAAAj5AAl','AAAAF/AABAAAAj5AAm','AAAAF/AABAAAAj5AAn','AAAAF/AABAAAAj5AAo');
USERID
------------------------------
ZXY翟z
ZXY翟z
ZXY翟z
ZXY翟z
ZXY翟z
SQL> delete from aud$ where rowid in ('AAAAF/AABAAAAj5AAk','AAAAF/AABAAAAj5AAl','AAAAF/AABAAAAj5AAm','AAAAF/AABAAAAj5AAn','AAAAF/AABAAAAj5AAo');
5 rows deleted.
SQL> commit;
Commit complete.
SQL> select userid from aud$ where rowid in ('AAAAF/AABAAAAj5AAk','AAAAF/AABAAAAj5AAl','AAAAF/AABAAAAj5AAm','AAAAF/AABAAAAj5AAn','AAAAF/AABAAAAj5AAo');
no rows selected
SQL> delete from wrh$_sqlstat where rowid in
2 ('AAAR5hAACAAAOXpAAU',
3 'AAAR5hAACAAAOXpAAW',
4 'AAAR5hAACAAAOXpAAY',
5 'AAAR5hAACAAAOXqAAA',
6 'AAAR5hAACAAAOXqAAB',
7 'AAAR5hAACAAAOXqAAD',
8 'AAAR5hAACAAAOXqAAE',
9 'AAAR5hAACAAAOXqAAI',
10 'AAAR5hAACAAAOXqAAN',
11 'AAAR5hAACAAAOXqAAS',
12 'AAAR5hAACAAAOXqAAT',
13 'AAAR5hAACAAAOXqAAU',
14 'AAAR5hAACAAAOXqAAa',
15 'AAAR5hAACAAAOXqAAc',
16 'AAAR5hAACAAAOXrAAD',
17 'AAAR5hAACAAAOXrAAE',
'AAAR5hAACAAAOXrAAI',
19 'AAAR5hAACAAAOXrAAL',
20 'AAAR5hAACAAAOXrAAN',
'AAAR5hAACAAAOXrAAQ',
22 'AAAR5hAACAAAOXrAAR',
23 'AAAR5hAACAAAOXrAAS',
24 'AAAR5hAACAAAOXsAAF',
25 'AAAR5hAACAAAOXsAAL',
26 'AAAR5hAACAAAOXsAAN',
27 'AAAR5hAACAAAOXsAAP',
28 'AAAR5hAACAAAOXsAAR',
29 'AAAR5hAACAAAOXsAAW');
28 rows deleted.
SQL> commit;
Commit complete.
[oracle@seconary admin]$ rm -rf scan*
[oracle@seconary admin]$
---这下没有错误了
[oracle@seconary admin]$ more scan.err
Database Scan Individual Exception Report
[Database Scan Parameters]
Parameter Value
------------------------------ ------------------------------------------------
CSSCAN Version v2.1
Instance Name charzhs
Database Version 11.2.0.1.0
Scan type Full database
Scan CHAR data? YES
Database character set ZHS16GBK
FROMCHAR ZHS16GBK
TOCHAR AL32UTF8
Scan NCHAR data? NO
Array fetch buffer size 1024000
Number of processes 10
Capture convertible data? NO
------------------------------ ------------------------------------------------
[Data Dictionary individual exceptions]
[Application data individual exceptions]
还是没有转换到指定的数据库字符集
SQL> @@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.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size 2213896 bytes
Variable Size 922748920 bytes
Database Buffers 721420288 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL> select parameter,value from nls_database_parameters where parameter='NLS_CHARACTERSET';
PARAMETER
------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_CHARACTERSET
ZHS16GBK
SQL>
SQL> alter system enable restricted session;
System altered.
还是不成功
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.
--多运行几次上述的CSSCAN命令（调整交互式为命令行式，方便多次运行）,看来不是多次运行CSSCAN可以解决的
[oracle@seconary admin]$ csscan userid=/'sys/system as sysdba/' full=y tochar=AL32UTF8 array=1024000 process=10
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.
换个思路，分析下 @@csalter.plb脚本内容，也没有看到有价值的内容，因为全是加密的代码
继续换个思路，基于MOS一篇文章：oracle_更改数据库字符集 (NLS_CHARACTERSET)
找到哪些数据可能会在迁移时产生转换，即字符集转换，把这些数据导出来，然后删除这些表或数据，接着执行CSALTER脚本
[oracle@seconary admin]$ more scan.txt
USER.TABLE Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------
Application Data:
USER.TABLE Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ---------------- ---可知是哪些表可能会发生数据转换（基于字符集转换）
SCOTT.T_CHARSET 524,289 0 0
SCOTT.T_CHARSET_BAK 524,288 0 0
SCOTT.T_VARCHAR 1 0 0
-------------------------------------------------- ---------------- ---------------- ----------------
[oracle@seconary admin]$ exp userid=scott/system owner=scott file=exp_scott_1.dmp
Export: Release 11.2.0.1.0 - Production on Mon Nov 30 03:38:10 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table DEPT 4 rows exported
. . exporting table EMP 14 rows exported
. . exporting table SALGRADE 5 rows exported
. . exporting table T_CHARSET 524289 rows exported
. . exporting table T_CHARSET_BAK 524288 rows exported
. . exporting table T_CLOB 1 rows exported
. . exporting table T_VARCHAR 1 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
[oracle@seconary admin]$
SQL> drop user scott cascade;
User dropped.
[oracle@seconary admin]$ cd $ORACLE_BASE/admin
[oracle@seconary admin]$ ll
total 28944
drwxr-x--- 5 oracle oinstall 4096 Nov 29 18:58 charzhs
-rw-r--r-- 1 oracle oinstall 16384 Nov 30 00:32 exp_charzhs_clob.dmp
-rw-r--r-- 1 oracle oinstall 7356416 Nov 29 22:01 exp_charzhs.dmp
-rw-r--r-- 1 oracle oinstall 16384 Nov 29 23:12 exp_charzhs_varchar.dmp
-rw-r--r-- 1 oracle oinstall 14696448 Nov 30 03:39 exp_scott_1.dmp
-rw-r--r-- 1 oracle oinstall 16384 Nov 30 00:56 exp_user1.dmp
-rw-r--r-- 1 oracle oinstall 7356416 Nov 29 21:53 exp_utf8char.dmp
drwxr-x--- 5 oracle oinstall 4096 Apr 17 2014 guowang
drwxr-x--- 5 oracle oinstall 4096 Sep 11 00:44 newb
drwxr-x--- 5 oracle oinstall 4096 Nov 28 16:45 onetime
-rw-r--r-- 1 oracle oinstall 1357 Nov 30 03:16 scan.err
-rw-r--r-- 1 oracle oinstall 88905 Nov 30 03:16 scan.out
-rw-r--r-- 1 oracle oinstall 8693 Nov 30 03:16 scan.txt
drwxr-xr-x 3 oracle oinstall 4096 Aug 28 2013 second
drwxr-x--- 5 oracle oinstall 4096 Nov 29 18:35 utf8char
[oracle@seconary admin]$ rm -rf scan*
[oracle@seconary admin]$
重新运行csscan
[oracle@seconary admin]$ csscan userid=/'sys/system as sysdba/' full=y tochar=AL32UTF8 array=1024000 process=10
直到scan.txt中Convertible对应的值为非0
[oracle@seconary admin]$ more scan.txt
Data Dictionary Tables:
USER.TABLE|COLUMN Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------
XML CSX Dictionary Tables:
USER.TABLE|COLUMN Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------
Application Data:
USER.TABLE|COLUMN Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------
[Indexes to be Rebuilt]
这下运行csalter.plb成功了
[oracle@seconary admin]$ cd /oracle/product/11.2.0/db_1/rdbms/admin
[oracle@seconary admin]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 30 03:47:31 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @@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...
begin converting system objects
PL/SQL procedure successfully completed.
Alter the database character set...
CSALTER operation completed, please restart database ---CSALTER完后，一定要重启库，引申：命令运行过程中的输出信息日志一定要仔细理解，这个非常重要，如果理解不对路，可能会消耗大多无谓到时间到其它分析思路上去
PL/SQL procedure successfully completed.
0 rows deleted.
Function dropped.
Function dropped.
Procedure dropped.
SQL>
SQL> col parameter for a30
SQL> col value for a30
SQL> select parameter,value from nls_database_parameters where parameter='NLS_CHARACTERSET';
PARAMETER VALUE
------------------------------ ------------------------------
NLS_CHARACTERSET AL32UTF8
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size 2213896 bytes
Variable Size 939526136 bytes
Database Buffers 704643072 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL> select parameter,value from nls_database_parameters where parameter='NLS_CHARACTERSET';
PARAMETER VALUE
------------------------------ ------------------------------
NLS_CHARACTERSET AL32UTF8
SQL> create user scott identified by system account unlock;
User created.
SQL> grant resource,connect to scott;
Grant succeeded.
报如下错误
[oracle@seconary admin]$ imp userid=system/system fromuser=scott touser=scott file=exp_scott_1.dmp rows=n
Column 1 我们
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
SQL> desc t_charset;
Name Null? Type
----------------------------------------- -------- ----------------------------
A CHAR(10)
基于前文：oracle数据库字符集characterset迁移及变更系列二
http://blog.itpub.net/9240380/viewspace-1850079/
调整参数blank_trimming=true，不过要重启库方可生效，即移除列尾部的空格字符
SQL> show parameter blank_trimming
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
blank_trimming boolean
FALSE
SQL> alter system set blank_trimming=true scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size 2213896 bytes
Variable Size 939526136 bytes
Database Buffers 704643072 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
[oracle@seconary admin]$ imp userid=system/system fromuser=scott touser=scott file=exp_scott_1.dmp ignore=y
Import: Release 11.2.0.1.0 - Production on Mon Nov 30 04:09:02 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
Warning: the objects were exported by SCOTT, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing SCOTT's objects into SCOTT
. . importing table "DEPT"
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT.PK_DEPT) violated
中间略
Column 3 CLERK
Column 4 7698
Column 5 03-DEC-1981:00:00:00
Column 6 950
Column 7
Column 8 30
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT.PK_EMP) violated --报这个错是正常的，因为在这前已经导入了EMP表的记录，所以可以忽略
Column 1 7902
Column 2 FORD
Column 3 ANALYST
Column 4 7566
Column 5 03-DEC-1981:00:00:00
Column 6 3000
Column 7
Column 8 20
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT.PK_EMP) violated
Column 1 7934
Column 2 MILLER
Column 3 CLERK
Column 4 7782
Column 5 23-JAN-1982:00:00:00
Column 6 1300
Column 7
Column 8 10 0 rows imported
. . importing table "SALGRADE" 5 rows imported
. . importing table "T_CHARSET" 524289 rows imported
. . importing table "T_CHARSET_BAK" 524288 rows imported
. . importing table "T_CLOB" 1 rows imported
. . importing table "T_VARCHAR" 1 rows imported
About to enable constraints...
Import terminated successfully with warnings.
[oracle@seconary admin]$
SQL> select a,dump(a) from t_charset where rownum<=10;
A DUMP(A)
-------------------- ------------------------------------------------------------------------------------------
我们 Typ=96 Len=10: 233,142,180,230,136,156,230,187,145,32
我们 Typ=96 Len=10: 233,142,180,230,136,156,230,187,145,32
我们 Typ=96 Len=10: 233,142,180,230,136,156,230,187,145,32
我们 Typ=96 Len=10: 233,142,180,230,136,156,230,187,145,32
我们 Typ=96 Len=10: 233,142,180,230,136,156,230,187,145,32
我们 Typ=96 Len=10: 233,142,180,230,136,156,230,187,145,32
我们 Typ=96 Len=10: 233,142,180,230,136,156,230,187,145,32
我们 Typ=96 Len=10: 233,142,180,230,136,156,230,187,145,32
我们 Typ=96 Len=10: 233,142,180,230,136,156,230,187,145,32
我们 Typ=96 Len=10: 233,142,180,230,136,156,230,187,145,32
10 rows selected.
正文到此结束
热门推荐
相关文章
近期评论
-
文章和留言都翻到11页了 没有OOM
-
-
朋友，翻页到11页，及以后，会出现OOM，无法访问
-
-
搞个gitee的项目
-
-
版本号是多少，你可以下载哪个代码仓库，jdk选1.8 直接跑就行
-
-
-
Loading...
![[HBLOG]公众号](https://www.liuhaihua.cn/img/qrcode_gzh.jpg)
