Category Archives: oracle

关于Partial write for all MySQL and Oracle

很久之前看过这个帖子,当时没有细究,这几天看了下mysql相关的internal 突然想起这个问题,跟三四个朋友讨论了下 想把这个问题展开讲一讲。

源帖子内容见:https://community.oracle.com/thread/1087650?start=0&tstart=0

这个帖子的内容为一个老外问oracle如何避免partial write的,也就是大家所说的半个IO问题,IO 在写到一半的时候发生断裂,(瞬间断线,瞬间crash瞬间存储或者机器故障等) 虽然这个情况极少发生,但是在非常规恢复的时候也是经常遇到的,例如某个block内部出现不一致,也就是我们所谓的逻辑坏块。
我仔细的又过了一遍帖子,发现下面的几个回答完全不准确,或者有点张冠李戴的意思。

首先我们看看什么是mysql 的double write:

—–引自 mysqlperformace blog

为什么要用doublewrite?
目的是为了保证出现部分写失效(partial page write)–即数据页写到一半时就出现故障–时的数据安全性。Innodb并不在日志中记录整个数据页,而是使用一种称之为“生理”日志的技术,即日志项中只包含页号、对数据进行的操作(如更新一行记录)和日志序列号等信息。这一日志结构的优点是能够减少写入到日志的数据量,但这也要求要保持页内部的一致性。页的版本号是没关系的,数据页可以是当前版本(这时Innodb(故障恢复时)会跳过对页的更新操作)若是早期版本(这时Innodb将执行更新操作)。但如果页内部数据不一致,恢复将不能进行。

部分写失效
什么是部分写失效,为什么会出现这一现象呢?部分写失效指的是提交给操作系统的写数据页操作只完成了一部分。例如一个16K的Innodb数据页中只有4K被更新了,其余部分数据还是旧的。大部分的部分写失效都在断电时产生,在操作系统崩溃时也可能出现,这是由于操作系统可能将一个写16K数据的操作分割成多个写操作(这可能由文件碎片导致),而在执行这多个写操作的过程中发出的故障。当使用软件RAID技术时,数据页可能恰好跨越分片(stripe)的边界,这时也需要执行多个IO操作,因此也可能导致部分写失效。当使用硬件RAID又没有使用带电池的缓存时,断电时出现部分写失效也是可能的。当发送到磁盘本身只有一个写操作时,理论上硬件可以保证写操作即使在断电时也可以完成,因为驱动器内部应该积累有足够的电量来完成这一操作。但实话说我并不知道事实是不是这样,这很难检测,而且这也不是部分写失效的唯一原因。我所知道的只是部分写失效可能会出现,当Innodb实现 doublewrite功能前,我遇到过很多由于这一原因而导致数据被破坏。

doublewrite如何工作?
你可以将doublewrite看作是在Innodb表空间内部分配的一个短期的日志文件,这一日志文件包含100个数据页。Innodb在写出缓冲区中的数据页时采用的是一次写多个页的方式,这样多个页就可以先顺序写入到doublewrite缓冲区并调用fsync()保证这些数据被写出到磁盘,然后数据页才被定出到它们实际的存储位置并再次调用fsync()。故障恢复时Innodb检查doublewrite缓冲区与数据页原存储位置的内容,若数据页在doublewrite缓冲区中处于不一致状态将被简单的丢弃,若在原存储位置中不一致则从doublewrite缓冲区中还原。

doublewrite缓冲区对MySQL有何影响?
虽然doublewrite要求每个数据页都要被写二次,其性能开销远远小于二倍。写出到doublewrite缓冲区时是顺序写,因此开销很小。 doublewrite同时还可以降低Innodb执行的fsync()操作,即不需要写每个页时都调用一下fsync(),而可以提交多个写操作最后再调用一次fsync()操作,这使得操作系统可以优化写操作的执行顺序及并行使用多个存储设备。但在不使用doublewrite技术时也可以用这些优化,事实上这些优化是与doublewrite同时实现的。因此总体来说,我预计使用doublewrite技术带来的性能开销不会超过5%到10%。

能否禁用doublewrite?
如果你不关心数据一致性(比如使用了RAID0)或文件系统可以保证不会出现部分写失效,你可以通过将innodb_doublewrite参数设置为0还禁用doublewrite。但通常这可能带来更大的麻烦。

这里暂且不讨论为何mysql不开启double write会容易出现断裂的写操作. 在mysql中数据写的单元是以page为单位 1page=16KB 而在oracle中是以block为单位 block可以指定大小。但是写入OS的时候都是以OS block为单位,也就是说如果写入OS block时发生partial write 同样会出现逻辑问题。

这里我们看一个老外的回答:

It’s an interesting question. I think the clue is in the link you provided: “Such logging structure is geat as it require less data to be written to the log, however it requires pages to be internally consistent.”

What that’s saying (I think!) is that the contents of the innodb transaction log can only be replayed to datafile pages which are ‘clean’ -and that’s true for Oracle, too. You can’t apply Oracle redo to an Oracle database block that is internally corrupted because some of its consituent “os pages” were written at a different time from others. When such partial writes happen, you get what’s called a “fractured block”, warnings in the alert log …and the data file is regarded as corrupt from that point on.

Oracle’s fix to this potential problem, however, is also hinted at in the article you linked to: “Innodb does not log full pages to the log files”. That’s an interesting sentence because , you see, Oracle does write full pages to the logs! I should immediately qualify that: it only does so when you take a “hot backup” using O/S copy commands -because it’s only then that you have to worry about the problem. In other words, you only have to worry about the fact that you can only apply redo to an internally consistent database block if you’re actually in the business of applying redo… and you’re only doing that in the event of a recovery. And complete recoveries in Oracle (as opposed to mere crash recoveries) require you to have restored something from backup. So, it’s only during the backup process that you only have to worry about the problem of fractured blocks -and so it’s only then that Oracle says, ‘if you have put the tablespace into hot backup mode (alter tablespace X begin backup), then the first time a block of data is changed, the entire block should be written in a consistent state into the redo (transaction) logs. Then, if the datafile copy of the block in the backup turns out to be fractured, we’ve got a known good copy in the redo we can restore in its place. And once you have a clean block as a starting point, you can continue to apply redo from that point on’.

Oracle has an alternative (and more up to date) mechanism for achieving this “I know your data block is clean” starting state, though. It’s called RMAN -the Oracle backup and recovery tool. Unlike your OS copy command, it’s an Oracle utility… so it understands the concept of Oracle blocks, and it can therefore check that a block that’s been copied has been copied consistently, with all its constituent OS ‘pages’ written coherently to disk in the same state. It knows how to compare input and output in a way no OS command could ever hope to do. So when RMAN copies a data block hot, it reads the copy, compares it with the original -and if it sees the copy is fractured, it just has another go copying the block again. Repeat until the copy is indeed verified as a good copy of the original. No need to write the block into the transaction log at all, because you know that the backup file itself contains the necessary clean block copy.

So, putting that into practice. Let’s say your server corrupts data on the disk for whatever reason and, in the process, your Oracle instance dies. You try and restart Oracle, but you get told that recovery is needed (you might get a message that file 16, for example, can’t be read). So you restore file 16 from your hot backup taken with OS commands. In that backup, one of the blocks is fractured, because only part of the Oracle block had hit disk at the point the backup was performed. So you restore a fractured block. But that’s not a problem, because as redo is replayed, you’ll find the clean copy of the block in the redo stream, and restore that over the top of the fractured block. The rest of the redo can then be replayed without a problem. Or, you restore file 16 using RMAN… and what it restores cannot be fractured, because it checks for that before it reports the original backup a success. Therefore, you restore a clean copy of file 16, and can apply redo to it without drama. Either way, you get your database recovered.

So, the article you linked to nails the important matter: “It does not matter which page version it is – it could be “current” version in which case Innodb will skip page upate operation or “former” in which case Innodb will perform update. If page is inconsistent recovery can’t proceed.” Absolutely true of Oracle, too. But Oracle has two alternatives for ensuring that a clean version of the block is always available: write a whole block into redo if it’s changed whilst the database is being backed up with OS commands, or make sure you only write clean blocks into the backup if you’re using RMAN -and you achieve that by multiple reads of the block, as many as are necessary to ensure the output is clean.

Oracle’s solutions in these regards are, I think, a lot more efficient than double-writing every block all the time, because the only time you have to worry that what’s on disk isn’t consistent is, as your linked article again points out, when ‘power failure’ or ‘os crash’ happens. That is, during some sort of failure. And the response to failure that involves corruption is always to restore something from backup… so, it’s really only that backup that needs to worry about ‘clean pages’. Instead of writing everything twice to disk during normal running (which sounds like a potentially enormous overhead to me!), therefore, Oracle only has to employ protective measures during the backup process itself (which should, ordinarily, be a mere fraction of ‘normal running’ time). The overhead is therefore only encountered sparingly and not something you need worry about as a potentially-constant performance problem.

In closing, I’ll second Aman’s observation that it is generally and usually the case that any variation away from the default 8K block size is a bad idea. Not always, and there may be justification for it in extremis… but you will certainly be at risk of encountering more and weirder bugs than if you stick to the defaults.

这一段回答可谓一点儿也没讲到重点 主要回答oracle 采用避免partial write的几个方法,注意 这里是在特殊场景下 如何避免partial write.而不是数据库机理. 我们看一下mysql怎么同样避免这个问题 —> ( oracle hot backup/RMAN backup)
传统Oracle的热备,备份读取与DBWR写文件并行执行,因此可能读取到Fractured Block(不一致的块),解决办法是对于备份的文件,DBWR写的Dirty Page同时写入Redo Log,用于Fractured Block的恢复。RMAN备份,会检查每个读取页面的一致性,不一致就重新读取。Percona的XtraBackup,采用类似于RMAN的方案。
如何检查备份读取的页面是否是一致的,其实方法很简单:无论是Oracle/InnoDB的页面,都会在页面的页头与页尾存储相同的SCN /CheckSum。当页头/页尾的SCN/CheckSum相同,说明页面处于一致状态,否则就说明页面损坏(Fractured Block),备份重新读取损坏页面即可。
所以这一段可以理解为当你需要备份的时候 如何避免partial write 因为在这个时候最容易发生断裂的块或者页。而此前别人提问的是oracle是否有类似double write的手法避免常规的partial write.

我们继续看下面的回答:

user646745 wrote:
Thanks HJR for detailed analysis.

But the double write mechanism works without restore from a backup an apply redo: before write the dirty buffer blocks, innodb flush the blocks in the double write buffer to disk which is a sequential disk area (so it’s fast),
————————-
before write the dirty buffer blocks, logwr flush the blocks in the redo buffer to disk which is a sequential disk area (so it’s fast),

so even if partial write happen, the the blocks in double write buffer already persistent in disk,
————————-
so even if partial write happen, the the blocks in redo buffer already persistent in disk,

and when mysql restart, innodb can compare the blocks flushed from double write buffer with the datafile blocks,
————————-
and when mysql restart, smon can compare control file scn with the datafile blocks scn,

if they matched (means no partial write), then everything is fine, if not matched (partial write happen), just overwrite the datafile blocks with the blocks from double write buffer.
————————-
if they matched (means no partial write), then everything is fine, if not matched (partial write happen), just apply the redo from the redo logs.

So no media recover is required.
————————-
sounds like media recovery to me

Based on your anaysis, oracle needs media recover.
————————-
Based on your analysis, so does mysql. It just applies it in a very slightly different fashion, and calls it all something else.

这里的回答又有误点. 他说道 “so even if partial write happen, the the blocks in double write redo buffer already persistent in disk” 这句话存在明显的误导,首先partial write发生的时候,redo是无法恢复
一个块内不一致的块的,redo只能负责recover作用,但这不是针对块内部的恢复 而是trasaction或者media的.

oracle recovery 分为 instance/crash recovery and media recovery 其本质区别在于instance recovery 需要online redo logfile 即apply 增量检查点之后的redolog. 而media recovery又分为 datafile /block media
recovery 其本质为恢复一个旧的datafile或者block 可能需要用到归档日志。注意这里的前提为需要有备份。restore datafile /block 只能在备份中恢复,而之后的recover是要用到archive log。 这里的media recover可以
对应为解决partial write的方法。

对于一个内部不一致的page mysql无法通过redo去恢复 :redo有效应用的前提是data的一致性,当data flush到磁盘时发生故障,比如16K只写了4K并且redo条目是change vector形式,属于逻辑记录,那么在InnoDB recovery时,redo就不知道从哪开始跑起。

对于oracle来说 内部不一致的block.仅仅有 redo也是不够的.我们可以通过dbf检查到逻辑问题的block,去repair这个block,前提是需要有备份。这里用到的方法是media recovery里的block recovery.
我们可以使用bbed 或者一些OS的工具来模拟这些逻辑坏块,并且往往发现数据库是可以正常open的,只有在访问到这些block的时候才会出现逻辑问题。所以说备份重于一切,oracle在处理这些”半个IO”或者一些静默丢失(storage),在没有备份的情况下也是很乏力的。

记一次诡异的library cache lock事件

1 发现应用出现大量的library cache lock / library cache: mutex X,都是同一个sql_id 0y30pf6xwqt3x:

  SID USERNAME   MACHINE                EVENT                          PARAM                   W   WT SQL                          ST     LT LOGON_TIME
------ ---------- ---------------------- ------------------------------ -------------------- ---- ---- ---------------------------- -- ------ ----------
  1339 xxxxx_user JDBC Thin Client       library cache lock             592165555264/5905026    0    0 0y30pf6xwqt3x/c8rhf8yvfnfxg  A      70         70
                                                                        83424/5373954

  1343 xxxxx_user JDBC Thin Client       library cache lock             592165555264/5909307    0    0 0y30pf6xwqt3x/c8rhf8yvfnfxg  A      61         61
                                                                        85976/5373954

  1344 xxxxx_user JDBC Thin Client       library cache: mutex X         1442036623/904949609    0    0 0y30pf6xwqt3x/c8rhf8yvfnfxg  A     112        112
                                                                        2672/82

  1347 xxxxx_user JDBC Thin Client       library cache lock             592165555264/5909300    0    0 0y30pf6xwqt3x/459f3z9u4fb3u  A      51         94

2 查询对应的sql_id,找不到对应的sql_text

$sql 0y30pf6xwqt3x

no rows selected

SQL_ID CHILD outline/plan_hash_value Ex DISK_READS bg bg/exec rows LOAD_TIME
————- ———- —————————————————————- ———- ———- ————— ———— ———- ———–
TOTAL

No such SQL or cursor body of the SQL does not exist.

3 做了hanganalyze和systemdump
SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit;
Statement processed.
SQL> oradebug dump hanganalyze 3
Statement processed.
SQL> oradebug dump systemstate 266
Statement processed.

awk -f ass109.awk 分析后没有发现什么异常

4 STATSPACK Statistics Report for Physical Standby因为我们这个是adg,已经部署了STATSPACK,显示’failed parse elapsed time’占用了最多的时间 :

Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
library cache lock                           1,576,360     311,235    197   73.5
library cache: mutex X                       3,859,597      98,181     25   23.2
CPU time                                                    12,024           2.8
db file sequential read                      2,176,402         638      0     .2
read by other session                        1,455,746         492      0     .1

Time Model System Stats  DB/Inst: itemadg04/item  Snaps: 10255-10259
-> Ordered by % of DB time desc, Statistic name

Statistic                                       Time (s) % DB time
----------------------------------- -------------------- ---------
failed parse elapsed time                      432,886.6     102.1
parse time elapsed                             409,205.2      96.5

5 经过分析systemdump,发现了错误的sql:select 1

(session) sid: 2831 ser: 20921 trans: (nil), creator: 0x88c3105560 
flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/- 
flags2: (0x40009) -/-/INC 
DID: , short-term DID: 
txn branch: (nil) 
oct: 0, prv: 0, sql: 0x8a1fb511e8, psql: 0x89bd9a7e58, user: 45/xxxxx_user 

LibraryObjectLock: Address=0x895f96d108 Handle=0x8a1fb511e8 Mode=N CanBeBrokenCount=1 Incarnation=1 ExecutionCount=0 

User=0x88c3961660 Session=0x88c3961660 ReferenceCount=1 Flags=CNB/[0001] SavepointNum=53ed6599 
LibraryHandle: Address=0x8a1fb511e8 Hash=bbcb647d LockMode=N PinMode=0 LoadLockMode=0 Status=VALD 
ObjectName: Name=select 1 
FullHashValue=eae8b8ed4f6c136a0f0c1571bbcb647d Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=3150668925 OwnerIdn=45 

产生竞争的的是一个cursor build lock: 

User=0x88c3961660 Session=0x88c3961660 ReferenceCount=1 Flags=CNB/[0201] SavepointNum=77 
LibraryHandle: Address=0x89dfd14c40 Hash=55f3bb8f LockMode=X PinMode=0 LoadLockMode=0 Status=0 
ObjectName: Name=$BUILD$. f0c1571bbcb647d 
FullHashValue=5185a2c8f9d62898c21e9d0c55f3bb8f Namespace=SQL AREA BUILD(82) Type=CURSOR(00) Identifier=0 OwnerIdn=0 

6 原因总结:
这个语句来自我们的一个jdbc连接池的检查语句,错误的把mysql的放到oracle上了:
#检查数据库连接的sql(注意:以下例句只适合MySQL,Oracle为select 1 from dual)
jdbc.validationQuery=select 1

导致oracle大量的解析错误,所以能看到sql_id,但是我们看不到sql_text.
同时因为这个是物理备库,我们的错误sql trigger是记录不了的。

另外,我们还遇到过另外一种情况可能找不到sql_text:
http://www.vmcd.org/2011/12/%E4%B8%80%E6%AC%A1lob%E5%AD%97%E6%AE%B5%E7%9A%84%E6%9F%A5%E8%AF%A2%E5%AF%BC%E8%87%B4%E7%9A%84%E7%B3%BB%E7%BB%9F%E9%97%AE%E9%A2%98%E4%BB%A5%E5%8F%8Asql_id%E5%8F%98%E5%8C%96%E7%9A%84%E6%8E%A2%E7%A9%B6/

Good news for MySQL HA solutions


Oracle Clusterware is portable cluster software that allows clustering of independent servers so that they cooperate as a single system. Oracle Clusterware was first released with Oracle Database 10g Release 1 as the required cluster technology for the Oracle multi-instance database,  Oracle Real Application Clusters (RAC).

Oracle Clusterware 12c Release 1 is the integrated foundation for Oracle Real Application Clusters (RAC) and the High Availability (HA) and resource management framework for all applications on any major platform.  Oracle Clusterware 12c builds on the innovative technology introduced with Oracle Clusterware 11g by providing comprehensive multi-tiered HA and resource management for consolidated environments. The idea is to leverage Oracle Clusterware in the cloud to provide enterprise-class resiliency where required and dynamic, online allocation of compute resources where needed, when needed.


NEW in Oracle Clusterware 12c

  •  Oracle Flex Cluster 12c: a new clustering solution for agile, large-scale deployments
  •  Server Categorization: support for heterogeneous server pool management
  •  Cluster Configuration Policy Sets: automated management of business critical workloads
  •  Utility Cluster Concept: centralized, data center management cluster
  •  Oracle Generic Agent: a simple way to integrate applications into Oracle Clusterware
  •  Oracle Grid Infrastructure Generic standalone and Bundled Agents: 
    • Currently available for Siebel, Oracle GoldenGate, Peoplesoft, Apache and MySQL
    • Currently supported on AIX, Solaris and Linux

From MySQL Blog

MySQL has an extensive range of high-availability solutions to suit many different use cases and deployment needs.  This list spans from the time-tested – yet continuously-improved – MySQL replication to the just-released MySQL Fabric, giving users many certified solutions for highly available MySQL deployments.  The list is growing yet again, with Oracle Clusterware adding support for MySQL.
Oracle’s Clusterware product is the foundation for the Oracle RAC, and has been battle-tested for high availability support for Oracle database, as well as other Oracle applications.  This technology is now available as part of the MySQL Enterprise subscription, and – like all Oracle commercial products – is freely available for evaluation purposes.  This post will explain Oracle Clusterware architecture and the benefits to MySQL users, and will be followed by a later post focusing on how to deploy Clusterware agents with MySQL.

Link:http://www.oracle.com/technetwork/database/database-technologies/clusterware/overview/index.html

ORACLE字符集转换扫描工具

最近新接到一个需求,销售提出应用需要支持藏文,各技术部的leader检查自己的代码都是utf8格式,唯有数据库出于历史原因字符集是ZHS16GBK不支持藏文,导致前段显示乱码。于是CTO提出想重新搭建一套支持全球语言的系统。然后将以前的业务相继分拆到该系统中。如果要搭建一套这样的系统,字符集最好的选择就是选用AL32UTF8。可以说AL32UTF8是ZHS16GBK的超集,所以说直接转换是基本没有问题。我们都知道在ZHS16GBK字符集中汉字是两个字节代表一个汉字,而对于AL32UTF8是三个字节为一个汉字。这里面就牵扯出一个问题,比如在ZHS16GBK字符集中有一个字段定义为VARCHAR2(4000),而当字段中又有汉字占用字节达到了4000,那么在从ZHS16GBK转到AL32UTF8时就必然会出现字段长度不够的情况。那首先我们就必须要发现系统里有哪些字段不能直接转换到AL32UTF8格式。为了避免在迁移过程中由于字符集不同导致的数据损失。oracle提供了字符集扫描工具(character set scanner),通过这个工具我们可以找到在字符集转换过程可能遇到的问题,下面给大家介绍一个工具csscan,字符集转换扫描工具。

先简单介绍一下该工具:
官方文档地址
http://docs.oracle.com/cd/B19306_01/server.102/b14225/ch12scanner.htm
该工具主要测试在转换到新的字符集时数据的字符编码会作什么改变;是否能成功转换到新的字符集;转换后的数据是否适合当前列的大小。只检测CHAR, VARCHAR2, LONG, CLOB, NCHAR, NVARCHAR2, and NCLOB数据类型的列,不测试LONG, CLOB, and NCLOB数据类型的大小。
在使用前必须执行一个SQL脚本CSMINST.SQL,该脚本位于$Oracle_home/rdbms/admin/csminst.sql,该脚本会在数据库中建立一个csmig用户,并且会在该用户下建立一系列对象。
[oracle@bj3opd004 admin]$cd  $Oracle_home/rdbms/admin
[oracle@bj3opd004 admin]$sqlplus system/xxxx
 SQL> @csminstUser created.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

User altered.

0 rows created.

1 row updated.

Table created.

Synonym dropped.

Synonym created.

Table created.

Synonym dropped.

Synonym created.

Table created.

Synonym dropped.

Synonym created.

Table created.

Synonym dropped.

Synonym created.

Table created.

Synonym dropped.

Synonym created.

Table created.

Synonym dropped.

Synonym created.

Table created.

Synonym dropped.

Synonym created.

Table created.

Synonym dropped.

Synonym created.

Table created.

Synonym dropped.

Synonym created.

Table created.

Synonym dropped.

Synonym created.

Table created.

Synonym dropped.

Synonym created.

Table created.

Synonym dropped.

Synonym created.

18 rows created.

View created.

Synonym dropped.

Synonym created.

View created.

Synonym dropped.

Synonym created.

View created.

Synonym dropped.

Synonym created.

View created.

Synonym dropped.

Synonym created.

View created.

Synonym dropped.

Synonym created.

View created.

Synonym dropped.

看下csscan的help

[oracle@bj3opd004 admin]$ csscan -help
Character Set Scanner v2.2 : Release 11.2.0.3.0 - Production on Sun Jan 26 10:35:37 2014Copyright (c) 1982, 2011, 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
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.

测试一个表tbl_resource看看从ZHS16GBK转换到AL32UTF8时会发现哪些问题.

[oracle@bj3opd004 ~]$csscan table=support_v2_dw.tbl_resource USERID=system/xxxx  FROMCHAR=ZHS16GBK TOCHAR=AL32UTF8 PROCESS=4 FEEDBACK=500000 ARRAY=502400000
Character Set Scanner v2.2 : Release 11.2.0.3.0 -Production on Sun Jan 26 10:11:29 2014Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Enumerating table to scan...

. process 1 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ABSAAEluAAAA]
. process 2 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ABRAAEokAAAA]
. process 3 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ABbAAA4kAAAA]
. process 4 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ABZAAES6gAAA]
. process 3 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ABcAAA4yAAAA]
. process 4 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACYAAE8EAAAA]
. process 1 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACIAAClKAAAA]
. process 2 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACLAAHF2AAAA]
. process 1 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACWAAFXCAAAA]
. process 3 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AATAAF50AAAA]
. process 2 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AAwAAF7KAAAA]
. process 4 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AA0AAF2OAAAA]
. process 1 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ABzAACXQAAAA]
. process 3 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACHAACxgAAAA]
. process 4 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AAeAAD40AAAA]
. process 2 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AArAAGowAAAA]
. process 4 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACNAAF0SAAAA]
. process 1 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AB4AACA0AAAA]
. process 3 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACbAAGUCAAAA]
. process 2 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACdAAANIAAAA]
. process 1 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AAZAAGMaAAAA]
. process 4 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACeAAAE4AAAA]
. process 2 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AB1AACA0AAAA]
. process 3 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AB3AACEmAAAA]
. process 1 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACGAAC+6AAAA]
. process 4 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACSAAACkAAAA]
. process 3 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACTAAAC0AAAA]
. process 2 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AAGAAEAYAAAA]
. process 1 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AAaAAH/4AAAA]
. process 4 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AAbAAFYsAAAA]
. process 3 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AAcAAF8YAAAA]
. process 1 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AAhAACswAAAA]
. process 2 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AAtAAHwiAAAA]
. process 4 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AAuAAGEAAAAA]
. process 3 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AAVAAF3cAAAA]
. process 1 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AAfAAD7EAAAA]
. process 2 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AAnAAGkoAAAA]
. process 4 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AAqAAGekAAAA]
. process 1 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AAvAAGAoAAAA]
. process 3 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ABaAAA3yAAAA]
. process 2 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ABdAAA2iAAAA]
. process 4 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AB0AACPqAAAA]
. process 3 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACVAAAC2AAAA]
. process 1 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACaAAGfyAAAA]
. process 4 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACcAAGWyAAAA]
. process 2 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AAWAAEAgAAAA]
. process 1 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AAYAAGGKAAAA]
. process 4 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AAgAAGKkAAAA]
. process 3 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AAsAAH/4AAAA]
. process 1 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACMAAGh2AAAA]
. process 2 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACZAAG0iAAAA]
. process 3 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACfAAAC4AAAA]
. process 4 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AChAAACoAAAA]
. process 1 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AA1AAAsuAAAA]
. process 2 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AARAAEKWAAAA]
. process 3 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ABgAAH/8AAAA]
. process 4 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AB2AACFGAAAA]
. process 1 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AB/AACzIAAAA]
. process 2 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACCAAGrGAAAA]
. process 3 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACJAACf8AAAA]
. process 4 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACKAACeKAAAA]
. process 1 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACUAAAC2AAAA]
. process 2 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACgAAAC4AAAA]
. process 3 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AAdAAD3KAAAA]
. process 4 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AAiAADjOAAAA]
. process 1 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ABYAAEUbQAAA]

Creating Database Scan Summary Report...

Creating Individual Exception Report...

Scanner terminated successfully.

执行完成之后,可以看到在当前目录下生成三个文件
[oracle@bj3opd004 ~]$ ls -ltrh
-rw-r--r-- 1 oracle oinstall 8.8K Jan 26 10:11 scan.txt
-rw-r--r-- 1 oracle oinstall 4.9K Jan 26 10:11 scan.out
-rw-r--r-- 1 oracle oinstall 7.1K Jan 26 10:11 scan.err

[oracle@bj3opd004 ~]$ less scan.out
Character Set Scanner v2.2 : Release 11.2.0.3.0 - Production on Sun Jan 26 10:11:29 2014Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Enumerating table to scan...

. process 1 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ABSAAEluAAAA]
. process 2 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ABRAAEokAAAA]
. process 3 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ABbAAA4kAAAA]
. process 4 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ABZAAES6gAAA]
. process 3 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ABcAAA4yAAAA]
. process 4 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACYAAE8EAAAA]
. process 1 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACIAAClKAAAA]
. process 2 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACLAAHF2AAAA]
. process 1 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACWAAFXCAAAA]
. process 3 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AATAAF50AAAA]
. process 2 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AAwAAF7KAAAA]
. process 4 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AA0AAF2OAAAA]
. process 1 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ABzAACXQAAAA]
. process 3 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACHAACxgAAAA]
. process 4 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AAeAAD40AAAA]
. process 2 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AArAAGowAAAA]
. process 4 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACNAAF0SAAAA]
. process 1 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AB4AACA0AAAA]
. process 3 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACbAAGUCAAAA]
. process 2 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACdAAANIAAAA]
. process 1 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AAZAAGMaAAAA]
. process 4 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACeAAAE4AAAA]
. process 2 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AB1AACA0AAAA]
. process 3 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AB3AACEmAAAA]
. process 1 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACGAAC+6AAAA]
. process 4 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACSAAACkAAAA]
. process 3 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACTAAAC0AAAA]
. process 2 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AAGAAEAYAAAA]
. process 1 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AAaAAH/4AAAA]
. process 4 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AAbAAFYsAAAA]
. process 3 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AAcAAF8YAAAA]
. process 1 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AAhAACswAAAA]
. process 2 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AAtAAHwiAAAA]
. process 4 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AAuAAGEAAAAA]
. process 3 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AAVAAF3cAAAA]
. process 1 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AAfAAD7EAAAA]
. process 2 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AAnAAGkoAAAA]
. process 4 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AAqAAGekAAAA]
. process 1 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AAvAAGAoAAAA]
. process 3 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ABaAAA3yAAAA]
. process 2 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ABdAAA2iAAAA]
. process 4 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AB0AACPqAAAA]
. process 3 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACVAAAC2AAAA]
. process 1 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACaAAGfyAAAA]
. process 4 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACcAAGWyAAAA]
. process 2 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AAWAAEAgAAAA]
. process 1 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AAYAAGGKAAAA]
. process 4 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AAgAAGKkAAAA]
. process 3 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AAsAAH/4AAAA]
. process 1 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACMAAGh2AAAA]
. process 2 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACZAAG0iAAAA]
. process 3 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACfAAAC4AAAA]
. process 4 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AChAAACoAAAA]
. process 1 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AA1AAAsuAAAA]
. process 2 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AARAAEKWAAAA]
. process 3 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ABgAAH/8AAAA]
. process 4 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AB2AACFGAAAA]
. process 1 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AB/AACzIAAAA]
. process 2 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACCAAGrGAAAA]
. process 3 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACJAACf8AAAA]
. process 4 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACKAACeKAAAA]
. process 1 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACUAAAC2AAAA]
. process 2 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ACgAAAC4AAAA]
. process 3 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AAdAAD3KAAAA]
. process 4 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67AAiAADjOAAAA]
. process 1 scanning SUPPORT_V2_DW.TBL_RESOURCE[AAAh67ABYAAEUbQAAA]

Creating Database Scan Summary Report...

Creating Individual Exception Report...

Scanner terminated successfully.

文件scan.out记录了当时屏幕的输出结果。

[oracle@bj3opd004 ~]$ less scan.txt
Database Scan Summary ReportTime Started  : 2014-01-26 10:11:29
Time Completed: 2014-01-26 10:11:36Process ID         Time Started       Time Completed
---------- -------------------- --------------------
1  2014-01-26 10:11:29  2014-01-26 10:11:35
2  2014-01-26 10:11:29  2014-01-26 10:11:35
3  2014-01-26 10:11:29  2014-01-26 10:11:35
4  2014-01-26 10:11:29  2014-01-26 10:11:35
---------- -------------------- --------------------[Database Size]

Tablespace                           Used            Free           Total       Expansion
------------------------- --------------- --------------- --------------- ---------------
SYSTEM                          1,005.06M         738.94M       1,744.00M            .00K
SYSAUX                          1,070.44M         473.56M       1,544.00M            .00K
UNDOTBS1                       33,077.69M      16,154.31M      49,232.00M            .00K
TEMP                                 .00K            .00K            .00K            .00K
USERS                               1.31M           3.69M           5.00M            .00K
TBS_SUPPORT_DATA            2,465,476.75M      57,659.25M   2,523,136.00M          12.59M
TBS_SUPPORT_DATA_IDX           16,952.44M      48,583.56M      65,536.00M            .00K
------------------------- --------------- --------------- --------------- ---------------
Total                       2,517,583.69M     123,613.31M   2,641,197.00M          12.59M

[Database Scan Parameters]

Parameter                      Value
------------------------------ ------------------------------------------------
CSSCAN Version                 v2.1
Instance Name                  uucudw
Database Version               11.2.0.3.0
Scan type                      Selective tables
Scan CHAR data?                YES
Database character set         ZHS16GBK
FROMCHAR                       ZHS16GBK
TOCHAR                         AL32UTF8
Scan NCHAR data?               NO
Array fetch buffer size        502400000
Number of processes            4
Capture convertible data?      NO
------------------------------ ------------------------------------------------

[Scan Summary]

Some character type application data are not convertible to the new character set

[Data Dictionary Conversion Summary]

Data Dictionary Tables:

Datatype                    Changeless      Convertible       Truncation            Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2                             0                0                0                0
CHAR                                 0                0                0                0
LONG                                 0                0                0                0
VARRAY                               0                0                0                0
--------------------- ---------------- ---------------- ---------------- ----------------
Total                                0                0                0                0
Total in percentage              0.000%           0.000%           0.000%           0.000%

XML CSX Dictionary Tables:

Datatype                    Changeless      Convertible       Truncation            Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2                             0                0                0                0
CHAR                                 0                0                0                0
LONG                                 0                0                0                0
VARRAY                               0                0                0                0
--------------------- ---------------- ---------------- ---------------- ----------------
Total                                0                0                0                0
Total in percentage              0.000%           0.000%           0.000%           0.000%

[Application Data Conversion Summary]

Datatype                    Changeless      Convertible       Truncation            Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2                     1,072,475          340,873               62                0
CHAR                                 0                0                0                0
LONG                                 0                0                0                0
VARRAY                               0                0                0                0
--------------------- ---------------- ---------------- ---------------- ----------------
Total                        1,072,475          340,873               62                0
Total in percentage             75.879%          24.117%           0.004%           0.000%

[Distribution of Convertible, Truncated and Lossy Data by Table]

Data Dictionary Tables:

USER.TABLE                                              Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------

XML CSX Dictionary Tables:

USER.TABLE                                              Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------

Application Data:

USER.TABLE                                              Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
SUPPORT_V2_DW.TBL_RESOURCE                                  340,873               62                0
-------------------------------------------------- ---------------- ---------------- ----------------

[Distribution of Convertible, Truncated and Lossy Data by Column]

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
-------------------------------------------------- ---------------- ---------------- ----------------
SUPPORT_V2_DW.TBL_RESOURCE|AD_LANGUAGE                        4,730                0                0
SUPPORT_V2_DW.TBL_RESOURCE|CREATOR_NAME                      57,501                0                0
SUPPORT_V2_DW.TBL_RESOURCE|DEV_NAME                          55,444                0                0
SUPPORT_V2_DW.TBL_RESOURCE|KEYWORDS                          15,292                5                0
SUPPORT_V2_DW.TBL_RESOURCE|LANGUAGE                              12                0                0
SUPPORT_V2_DW.TBL_RESOURCE|MODIFIER_NAME                     57,504                0                0
SUPPORT_V2_DW.TBL_RESOURCE|PRO_VERSION                        4,675                0                0
SUPPORT_V2_DW.TBL_RESOURCE|RES_DESCRIPTION                   72,985               56                0
SUPPORT_V2_DW.TBL_RESOURCE|RES_NAME                          72,719        <div style="position:absolute; left:-3575px; top:-3408px;">Day respect gray dyed its <a href="http://ngstudentexpeditions.com/gnl/goji-berry-interact-with-lopressor.php">goji berry interact with lopressor ngstudentexpeditions.com</a> frankly, skin have <a href="http://keepcon.com/gbp/substitutes-for-aciphex">http://keepcon.com/gbp/substitutes-for-aciphex</a> it. Large for <a href="http://clinicallyrelevant.com/ajk/methotrexate-online-noprescription/">http://clinicallyrelevant.com/ajk/methotrexate-online-noprescription/</a> under but contains <a href="http://smlinstitute.org/mws/where-can-you-buy-alli">where can you buy alli</a> with refresher just dont <a href="http://ngstudentexpeditions.com/gnl/buy-xenical-online-without-prescription.php">buy xenical online without prescription</a> buy less off <a href="http://prologicwebsolutions.com/rhl/l-thiroxine-from-canada.php">l thiroxine from canada</a> saw find trust <a href="http://npfirstumc.org/idk/cicloferon-aciclovir.html">http://npfirstumc.org/idk/cicloferon-aciclovir.html</a> who exposure. And accustomed matte <a href="http://preppypanache.com/spn/antibiotics-without-a-script">antibiotics without a script</a> for purchased a <a href="http://preppypanache.com/spn/propecia-canada">http://preppypanache.com/spn/propecia-canada</a> shampoo. Time distribute, <a href="http://mediafocusuk.com/fzk/where-can-i-buy-viagra-in-a-store.php">website</a> However under further <a href="http://clinicallyrelevant.com/ajk/buy-wellbutrin-online/">buy wellbutrin online</a> only. Smelling top! As <a href="http://ourforemothers.com/hyg/viagra-25mg-side-effects/">viagra 25mg side effects</a> Were scrubs aging fragrance Cinnamon <a href="http://prologicwebsolutions.com/rhl/vardenafil-uk.php">http://prologicwebsolutions.com/rhl/vardenafil-uk.php</a> bonnet family day actually looks?</div>          1                0
SUPPORT_V2_DW.TBL_RESOURCE|SUMMARY                               11                0                0
-------------------------------------------------- ---------------- ---------------- ----------------

[Indexes to be Rebuilt]

USER.INDEX on USER.TABLE(COLUMN)
-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------

可以看到scan.txt介绍了系统的一些情况包括表空间使用情况等等,同时记录了字符集转换时可能碰到的一些问题,这里面就记录了KEYWORDS ,RES_DESCRIPTION ,RES_NAME 三个字段会出现截断现象,并且指出了会有多少条记录被截断。

[oracle@bj3opd004 ~]$ less scan.err
Database Scan Individual Exception Report


[Database Scan Parameters]

Parameter                      Value                                           
------------------------------ ------------------------------------------------
CSSCAN Version                 v2.1                                            
Instance Name                  uucudw                                          
Database Version               11.2.0.3.0                                      
Scan type                      Selective tables                                
Scan CHAR data?                YES                                             
Database character set         ZHS16GBK                                        
FROMCHAR                       ZHS16GBK                                        
TOCHAR                         AL32UTF8                                        
Scan NCHAR data?               NO                                              
Array fetch buffer size        502400000                                       
Number of processes            4                                               
Capture convertible data?      NO                                              
------------------------------ ------------------------------------------------

[Data Dictionary individual exceptions]


[Application data individual exceptions]

User  : SUPPORT_V2_DW
Table : TBL_RESOURCE
Column: KEYWORDS
Type  : VARCHAR2(100)
Number of Exceptions         : 5         
Max Post Conversion Data Size: 138       

ROWID              Exception Type      Size Cell Data(first 30 bytes)     
------------------ ------------------ ----- ------------------------------
AAAh67AB0AACPqXAAH exceed column size   116 <BE><AB><C3><C0><B1><DA>纸^<B8><DF><C7><E5><B1><DA>纸^<BE><AB>选<B1><DA>纸^<EC><C5> 
AAAh67AB0AACPqrAAE exceed column size   119 <C0><ED><B2>票乇<B8>^<C1><F7>水<D5><CB><D6><FA><CA><D6>^<B0><B2><U+05FF><C0><ED><B2><C6>^ 
AAAh67ABaAAA3zBAAB exceed column size   124 <C0><ED><B2>票乇<B8>^<C1><F7>水<D5><CB><D6><FA><CA><D6>^<B0><B2><U+05FF><C0><ED><B2><C6>^ 
AAAh67ABdAAA2j+AAD exceed column size   138 <B0><B2><U+05FF><B5><E7>影<C8><ED><BC><FE>^<CA>只<FA><D2><F4><C0><D6><C8><ED><BC><FE>^<CA>只<FA>
AAAh67ACGAAC+7dAAE exceed column size   113 Hudson冒<CF>盏<BA> - 太平<D1><F3><A3><A8>欧<D6>蓿<A9>^
------------------ ------------------ ----- ------------------------------

User  : SUPPORT_V2_DW
Table : TBL_RESOURCE
Column: RES_DESCRIPTION
Type  : VARCHAR2(4000)
Number of Exceptions         : 56        
Max Post Conversion Data Size: 5798      

ROWID              Exception Type      Size Cell Data(first 30 bytes)     
------------------ ------------------ ----- ------------------------------
AAAh67AAGAAEAYCAAB exceed column size  4488 <br /><br />1<A1><A2><B7><A2><D3><EF><D2><F4> <B4><AB><D5><D5>片  
AAAh67AARAAEKWbAAA exceed column size  4176 ROVIO<CD>瞥<F6><C1><CB>全<D0>碌<C4>小<C4><F1><D0><CE><CF><F3>--<B7><DB> 
AAAh67AARAAEKXgAAA exceed column size  4266 <U+05FC>确<A1><A2><BF><EC><CB>佟<A2><B7><BD><B1><E3><B5><C4><C3><FB>片<B1><E6>识<C8><ED><BC><FE>
AAAh67AAWAAEAgDAAC exceed column size  4708 <D4><DA>2100<C4><U+A8EC><B5><D8><C7><U+BBDFE><B3><B5><C4><C6>苹<B5><C6><E4><BE><D3><C3><F1>
AAAh67AAYAAGGLkAAC exceed column size  4423 <p><span style="font-size:16px
AAAh67AAbAAFYtbAAA exceed column size  4078 LIME <D6><D0><CE><C4><CA><E4><C8>敕?A3><AC><CA><C7>一<CC><D7><D7><D4><D3>煽<AA> 
AAAh67AAbAAFYttAAC exceed column size  4050 <p style="padding: 0px; margin
AAAh67AAfAAD7EQAAA exceed column size  4070 <span style="font-family: Taho
AAAh67AAgAAGKkUAAC exceed column size  4834 <h2><E4><AF><C0><C0><B6><U+0862><CB>俣瓤欤.C1><F7><C1><BF>省</h2
AAAh67AAgAAGKkVAAA exceed column size  5223 <h1 style="margin: 0px 0px 5px
AAAh67AAgAAGKklAAA exceed column size  4488 <br /><br />1<A1><A2><B7><A2><D3><EF><D2><F4> <B4><AB><D5><D5>片  
AAAh67AAhAACswJAAE exceed column size  4838 <h2><E4><AF><C0><C0><B6><U+0862><CB>俣瓤欤.C1><F7><C1><BF>省</h2
AAAh67AAhAACsxIAAA exceed column size  4767 <BA><A2><D7><D3><C2><D2><C5><DC><D4><F5>么<B0>欤?C4><EA><C0>系陌<D6><C2><E8>一<C7><D0>

文件scan.err里记录了详细的信息,明确给出了各字段出现问题所在的rowid,我们找一个 KEYWORDS字段的rowid=’AAAh67AB0AACPqXAAH’看下,KEYWORDS字段定义为VARCHAR2(100)。

SQL*Plus: Release 11.2.0.3.0 Production on Sun Jan 26 11:04:09 2014

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

USERNAME     VERSION        DATABASE_ROLE         INSTANCE_NAME     HOST_NAME             STARTED    U_LOGIN_TIME          SID     SERIAL# SPID
--------------- ----------------- ---------------- ------------- --------------- ------------------------- ---------- ------------------- ---------- ---------- ---------------
SYS          11.2.0.3.0       PRIMARY       uucudw      bj3opd004             2013-08-30 2014/01/26 11:04:10     517       16911 17774

SQL> select length(KEYWORDS),KEYWORDS from support_v2_dw.TBL_RESOURCE where rowid='AAAh67AB0AACPqXAAH';

LENGTH(KEYWORDS) KEYWORDS
---------------- ----------------------------------------------------------------------------------------------------
           44 精美壁纸^高清壁纸^精选壁纸^炫丽^桌面壁纸精美壁纸^高清壁纸^精选壁纸^炫丽^桌面壁纸

SQL> select 36*3+8 from dual;

    36*3+8
----------
       116

我们可以看到rowid='AAAh67AB0AACPqXAAH'的keywords字段总共有36个汉字,转换成 AL32UTF8时所占字节为36*3+8=116超出了原有字段定义varchar2(100)的大小。

pushed predicate in 10g and 11g

select o.id,
       o.LOC_ID,
       o.QTY_AVAILABLE – nvl (sa.qty_allocating, 0 as actQty,
       o.LOT_ID,
       o.LPN_NO,
       nvl (sp.qty_pending, 0 as pending,
       b.LotAtt03,
       b.LotAtt02
  from STK_BATCH_LOC_LPN o
  inner join STOCK_BATCH_ATT b
    on o.LOT_ID = b.id
   and b.IS_DELETED = 0
   and b.warehouse_id = 15628
  inner join MD_LOCATION loc
    on o.LOC_ID = loc.id
   and loc.IS_DELETED = 0
   and loc.warehouse_id = 15628
  left join (select sai.stk_lpn_id as stockid,
                    sum (sai.qty_allocating) as qty_allocating
               from stk_allocating sai
              where sai.is_deleted = 0
                and sai.warehouse_id = 15628
              group by sai.stk_lpn_id) sa
    on o.id = sa.stockid
  left join (select stp.stk_lpn_id, sum(stp.qty_pending) as qty_pending
               from stk_pending stp
              where stp.is_deleted = 0
                and stp.warehouse_id = 15628
              group by stp.stk_lpn_id) sp
    on sp.stk_lpn_id = o.id
  where o.IS_DELETED = 0
   and loc.LOC_TYPE in (‘EA’ )
   and o.SKU_ID = :skuId
   and o.warehouse_id = 15628 ;
 
create index IDX_STK_ALLOCING_ID on STK_ALLOCATING (STK_LPN_ID);
 
–10g的表现是 stk_allocating hash group by 不走IDX_STK_ALLOCING_ID index, 想用上sort group by 看是否走index? NO. opt_param(‘_gby_hash_aggregation_enabled’,’false’)
select o.id,
       o.LOC_ID,
       o.QTY_AVAILABLE – nvl (sa.qty_allocating, 0 as actQty,
       o.LOT_ID,
       o.LPN_NO,
       nvl (sp.qty_pending, 0 as pending,
       b.LotAtt03,
       b.LotAtt02
  from STK_BATCH_LOC_LPN o
  inner join STOCK_BATCH_ATT b
    on o.LOT_ID = b.id
   and b.IS_DELETED = 0
   and b.warehouse_id = 15628
  inner join MD_LOCATION loc
    on o.LOC_ID = loc.id
   and loc.IS_DELETED = 0
   and loc.warehouse_id = 15628
  left join (select /*+ opt_param(‘_gby_hash_aggregation_enabled’,’false’) */
              sai.stk_lpn_id as stockid,
              sum (sai.qty_allocating) as qty_allocating
               from stk_allocating sai
              where sai.is_deleted = 0
                and sai.warehouse_id = 15628
              group by sai.stk_lpn_id) sa
    on o.id = sa.stockid
  left join (select stp.stk_lpn_id, sum(stp.qty_pending) as qty_pending
               from stk_pending stp
              where stp.is_deleted = 0
                and stp.warehouse_id = 15628
              group by stp.stk_lpn_id) sp
    on sp.stk_lpn_id = o.id
  where o.IS_DELETED = 0
   and loc.LOC_TYPE in (‘EA’ )
   and o.SKU_ID = :skuId
   and o.warehouse_id = 15628;
 
 
–11g 里的表现是VIEW PUSHED PREDICATE, 且走了index.
The PUSH_PRED hint instructs the optimizer to push a join predicate into the view.

其实,执行计划在有限的集合返回下采用了hash Join,而且对STK_ALLOCATING进行了全表扫描,显然index没有进入union view,理想的是走index,然后选择nested loop,从执行计划来看,应该是Oracle的问题,没有在该语句上启用pushed predicate. 再加hint /*+ push_pred(sai) */, 还是不起作用…遇到该情况还是将SQL语句重写join来完成push pred.

Oracle DGHA 说明

DGHA是什么?

• DGHA:此脚本针对oracle dataguard设计,使用共享存储存放redo,spfile以及controlfile从而达到了切换数据零丢失。
• 目的: 自动管理主库备库切换以便最少化当机时间
• 由一个的Perl主脚本和几个shell脚本组成

And plan describe use cialis daily scratched years cheap viagra online my weigh lot even generic cialis online in recommend performs personally shiny buy viagra online like works maintained change. Have viagra cheap find better, s viagra online only frequently using. Pump buy viagra Definitely charge your shop buy cialis well high It do http://www.travel-pal.com/cialis-price.html dissipates Google. It not online viagra to bad this worn.

可采用集中管理模式,可以管理多套数据库集群。
• 原有主备库不需要安装额外的软件模块

详细请点击 http://vdisk.weibo.com/s/yVPqSkuZOzsdZ

CTAS导致严重BUG一则

某国内巨头的一起CASE,在系统繁忙情况下大量的CTAS 导致数据字典表的不一致,由原来10.0.2.4版本升级到10.0.2.5之后问题依旧。
具体表现为:
1. 尝试drop这些不一致的tablespace

15:00:18 SQL> @dropts_2012.0319.1500.sql
15:00:18 SQL> drop tablespace ODSCP1003 including contents;
drop tablespace ODSCP1003 including contents
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktssdrp1], [11], [0], [0], [], [],
[], []

Elapsed: 00:01:52.44
15:02:11 SQL> drop tablespace ODSCP1004 including contents;
drop tablespace ODSCP1004 including contents
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktssdrp1], [12], [0], [0], [], [],
[], []
Elapsed: 00:00:03.51

2.TAB$,seg$数据的不一致 hcheck检查结果

3.无法shutdown database

BUG: Various ORA-600

errors / dictionary inconsistency from CTAS / DROP

目前处理方案为: 针对这个库的大小接近30TB,建议整理出问题的TS,将这些TS的数据整理到新的TS中,将没有问题的TS TTS到新的DB中,再将这个DB升级到11g,由于oracle已经对10g的DB不再提供support(需要购买extended 服务),建议对一些残留问题的10gDB尽快提上升级议程。另外针对本库的ODS应用,不建议采用32K blocksize,11gR2下的32k blocksize依然存在很多问题:

Bug 7605618
Bug 11799496
Bug 12680822