admin – 第91页 – Database SOS

恢复被rm意外删除数据文件

一.模拟数据文件删除

[oracle@node1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat Dec 31 22:00:52 2011
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, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
--所有数据文件
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/ora11g/system01.dbf
/opt/oracle/oradata/ora11g/sysaux01.dbf
/opt/oracle/oradata/ora11g/undotbs01.dbf
/opt/oracle/oradata/ora11g/users01.dbf
/opt/oracle/oradata/ora11g/example01.dbf
--删除example01.dbf数据文件
SQL> !rm /opt/oracle/oradata/ora11g/example01.dbf
SQL> !ls -l /opt/oracle/oradata/ora11g/example01.dbf
ls: /opt/oracle/oradata/ora11g/example01.dbf: 没有那个文件或目录
--因为数据文件被删除，创建表失败
SQL> create table t_xifenfei tablespace example
  2  as select * from dba_tables;
as select * from dba_tables
                 *
ERROR at line 2:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/opt/oracle/oradata/ora11g/example01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

二.找回数据文件

--查找dbw进程spid
[oracle@node1 ~]$ ps -ef|grep dbw|grep -v grep
oracle   18387     1  0 Dec22 ?        00:00:12 ora_dbw0_ora11g
--查看该进程所有文件句柄
[oracle@node1 ~]$ ll /proc/18387/fd
总计 0
lr-x------ 1 oracle oinstall 64 12-31 22:03 0 -> /dev/null
l-wx------ 1 oracle oinstall 64 12-31 22:03 1 -> /dev/null
lr-x------ 1 oracle oinstall 64 12-31 22:03 10 -> /dev/zero
lr-x------ 1 oracle oinstall 64 12-31 22:03 11 -> /dev/zero
lr-x------ 1 oracle oinstall 64 12-31 22:03 12 -> /opt/oracle/product/11.2.0/db_1/rdbms/mesg/orazhs.msb
lrwx------ 1 oracle oinstall 64 12-31 22:03 13 -> /opt/oracle/product/11.2.0/db_1/dbs/hc_ora11g.dat
lr-x------ 1 oracle oinstall 64 12-31 22:03 14 -> /proc/18387/fd
lr-x------ 1 oracle oinstall 64 12-31 22:03 15 -> /dev/zero
lr-x------ 1 oracle oinstall 64 12-31 22:03 16 -> /opt/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb
lrwx------ 1 oracle oinstall 64 12-31 22:03 17 -> /opt/oracle/product/11.2.0/db_1/dbs/hc_ora11g.dat
lrwx------ 1 oracle oinstall 64 12-31 22:03 18 -> /opt/oracle/product/11.2.0/db_1/dbs/lkORA11G
lr-x------ 1 oracle oinstall 64 12-31 22:03 19 -> /opt/oracle/product/11.2.0/db_1/rdbms/mesg/orazhs.msb
l-wx------ 1 oracle oinstall 64 12-31 22:03 2 -> /dev/null
lr-x------ 1 oracle oinstall 64 12-31 22:03 20 -> /opt/oracle/product/11.2.0/db_1/rdbms/mesg/oraus.msb
lrwx------ 1 oracle oinstall 64 12-31 22:03 21 -> socket:[441562]
lrwx------ 1 oracle oinstall 64 12-31 22:03 256 -> /opt/oracle/oradata/ora11g/control01.ctl
lrwx------ 1 oracle oinstall 64 12-31 22:03 257 -> /opt/oracle/oradata/ora11g/system01.dbf
lrwx------ 1 oracle oinstall 64 12-31 22:03 258 -> /opt/oracle/oradata/ora11g/sysaux01.dbf
lrwx------ 1 oracle oinstall 64 12-31 22:03 259 -> /opt/oracle/oradata/ora11g/undotbs01.dbf
lrwx------ 1 oracle oinstall 64 12-31 22:03 260 -> /opt/oracle/oradata/ora11g/users01.dbf
lrwx------ 1 oracle oinstall 64 12-31 22:03 261 -> /opt/oracle/oradata/ora11g/example01.dbf (deleted)
lrwx------ 1 oracle oinstall 64 12-31 22:03 262 -> /opt/oracle/oradata/ora11g/temp01.dbf
lr-x------ 1 oracle oinstall 64 12-31 22:03 3 -> /dev/null
lr-x------ 1 oracle oinstall 64 12-31 22:03 4 -> /dev/null
lrwx------ 1 oracle oinstall 64 12-31 22:03 5 -> /opt/oracle/product/11.2.0/db_1/dbs/hc_ora11g.dat
lr-x------ 1 oracle oinstall 64 12-31 22:03 6 -> /dev/null
lr-x------ 1 oracle oinstall 64 12-31 22:03 7 -> /dev/null
lr-x------ 1 oracle oinstall 64 12-31 22:03 8 -> /dev/null
lr-x------ 1 oracle oinstall 64 12-31 22:03 9 -> /dev/null
--通过句柄恢复数据文件[被删除数据文件会被标示(deleted)]
[oracle@node1 ~]$ cp /proc/18387/fd/261 /opt/oracle/oradata/ora11g/example01.dbf
--确认该数据文件已经恢复成功
[oracle@node1 ~]$ ll /opt/oracle/oradata/ora11g/example01.dbf
-rw-r----- 1 oracle oinstall 362422272 12-31 22:05 /opt/oracle/oradata/ora11g/example01.dbf

三.数据文件online

SQL> alter database datafile 5 offline;
Database altered.
SQL> recover datafile 5;
Media recovery complete.
SQL> alter database datafile 5 online;
Database altered.
SQL> create table t_xifenfei tablespace example
  2  as select * from dba_tables;
Table created.

四.补充说明
在意外使用os命令删除掉数据文件时，千万不要慌张重启数据库或者操作系统，可以通过dbwn进程相关句柄找回数据文件

在UltraEdit中定位数据文件内容

一、定位数据块
1.bbed查看数据块

--第一个数据块
BBED> set block 1
        BLOCK#          1
BBED> dump count 16
 File: /opt/oracle/oradata/chf/example01.dbf (0)
 Block: 1                Offsets:    0 to   15           Dba:0x00000000
------------------------------------------------------------------------
 0ba20000 01004001 00000000 00000104
 <32 bytes per line>
BBED> dump count 128
 File: /opt/oracle/oradata/chf/example01.dbf (0)
 Block: 1                Offsets:    0 to  127           Dba:0x00000000
------------------------------------------------------------------------
 0ba20000 01004001 00000000 00000104 387a0000 00000000 0003200b 74684acd
 43484600 00000000 7d4a0000 00320000 00200000 05000300 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 d28a0900 00000000 1ab5d72c f7b4d72c c5320900 00000000 00000000
 <32 bytes per line>
--第二个数据块
BBED> set block 2
        BLOCK#          2
BBED> dump count 128
 File: /opt/oracle/oradata/chf/example01.dbf (0)
 Block: 2                Offsets:    0 to  127           Dba:0x00000000
------------------------------------------------------------------------
 1da20000 02004001 08dee400 00000204 f6b80000 05000000 08000000 00320000
 09000000 50000000 feff3f00 07000000 00320000 7f000000 fa010000 07dee400
 00000000 00000000 00000000 00000000 b1220000 08000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 <32 bytes per line>
--第100个数据块
BBED> set block 100
        BLOCK#          100
BBED> dump count 128
 File: /opt/oracle/oradata/chf/example01.dbf (0)
 Block: 100              Offsets:    0 to  127           Dba:0x00000000
------------------------------------------------------------------------
 06a20000 64004001 343f0900 00000104 a38d0000 02002000 e1c90000 343f0900
 0000e81f 021f3200 61004001 00000000 00000000 00000000 00000000 00000000
 00000000 05000900 2c010000 52008000 c8005900 00800000 a3350900 00000000
 00000000 00008001 00000000 6b00fa00 5e1a6419 00000000 00000000 00000000
 <32 bytes per line>

2.UltraEdit查看数据块

--第0个数据块
--(为了便于和bbed的block一致，称为0比较合适，因为这个块在bbed中看不到)
00000000h: 00 A2 00 00 00 00 C0 FF 00 00 00 00 00 00 00 00 ; .?...?........
00000010h: 66 C8 00 00 00 20 00 00 00 32 00 00 7D 7C 7B 7A ; f?.. ...2..}|{z
00000020h: A0 81 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; 爜..............
00000030h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................
……………………………………………………
00001fa0h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................
00001fb0h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................
00001fc0h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................
00001fd0h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................
00001fe0h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................
00001ff0h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................
--第一个数据块（bbed中的block 1）
00002000h: 0B A2 00 00 01 00 40 01 00 00 00 00 00 00 01 04 ; .?...@.........
00002010h: 38 7A 00 00 00 00 00 00 00 03 20 0B 74 68 4A CD ; 8z........ .thJ?
……………………………………………………………
000021c0h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................
000021d0h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................
--第二个数据块(bbed中的block 2)
00004000h: 1D A2 00 00 02 00 40 01 08 DE E4 00 00 00 02 04 ; .?...@..掬.....
00004010h: F6 B8 00 00 05 00 00 00 08 00 00 00 00 32 00 00 ; 龈...........2..
00004020h: 09 00 00 00 50 00 00 00 FE FF 3F 00 07 00 00 00 ; ....P...??.....
00004030h: 00 32 00 00 7F 00 00 00 FA 01 00 00 07 DE E4 00 ; .2.....?...掬.
00004040h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................
--第100个数据块(bbed中的block 100)
000c8000h: 06 A2 00 00 64 00 40 01 34 3F 09 00 00 00 01 04 ; .?.d.@.4?......
000c8010h: A3 8D 00 00 02 00 20 00 E1 C9 00 00 34 3F 09 00 ; .... .嵘..4?..
000c8020h: 00 00 E8 1F 02 1F 32 00 61 00 40 01 00 00 00 00 ; ..?..2.a.@.....
000c8030h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................
000c8040h: 00 00 00 00 05 00 09 00 2C 01 00 00 52 00 80 00 ; ........,...R.€.
000c8050h: C8 00 59 00 00 80 00 00 A3 35 09 00 00 00 00 00 ; ?Y..€..?......
000c8060h: 00 00 00 00 00 00 80 01 00 00 00 00 6B 00 FA 00 ; ......€.....k.?
000c8070h: 5E 1A 64 19 00 00 00 00 00 00 00 00 00 00 00 00 ; ^.d.............

3.结论:因为2000(16进制)=8192(10进制),而每行又是16，所以UltraEdit定位块的规则是
SELECT to_char(‘2*块数’,’xxxxxxx’) xff FROM dual;(xff*1000)

SQL> SELECT TO_number('2000','xxxxxx') FROM dual;
TO_NUMBER('2000','XXXXXX')
--------------------------
                      8192

二、UltraEdit定位块内内容
1.bbed查看内容

--第一块偏移量为484数据
BBED> set block 1
        BLOCK#          1
BBED> set offset 484
        OFFSET          484
BBED> dump count 128
 File: /opt/oracle/oradata/chf/example01.dbf (0)
 Block: 1                Offsets:  484 to  611           Dba:0x00000000
------------------------------------------------------------------------
 c052e700 00000000 ec82ba2d 01000000 2e060000 39020000 10008984 02000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 <32 bytes per line>
--第一块偏移量为8188数据
BBED> dump offset 8188 count 128
 File: /opt/oracle/oradata/chf/example01.dbf (0)
 Block: 1                Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 010b0000
 <32 bytes per line>

2.UltraEdit中内容

--offset 484(kscnbas)
000021e0h: 00 00 00 00 C0 52 E7 00 00 00 00 00 EC 82 BA 2D ; ....繰?....靷?
000021f0h: 01 00 00 00 2E 06 00 00 39 02 00 00 10 00 89 84 ; ........9.....墑
00002200h: 02 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................
--offset 8188(tailchk)
00003ff0h: 00 00 00 00 00 00 00 00 00 00 00 00 01 0B 00 00 ; ................

3.结论:10进制的便宜量转换为16进制，然后对照你块开始行号+偏移量(16进制),得到对应偏移量开始位置

SQL> SELECT to_char('484','xxxxxxx') FROM dual;
TO_CHAR('484','X
----------------
     1e4
SQL> SELECT to_char('8188','xxxxxxx') FROM dual;
TO_CHAR('8188','
----------------
    1ffc

三.补充说明
1.本实验是拷贝linux下11g数据文件到win上使用UltraEdit操作得出
2.使用UltraEdit,需要熟悉对一些关键数据的偏移量比较清楚

记一次含AND-EQUAL执行计划调优

1.数据库版本

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

2.发现含有AND-EQUAL执行计划

SQL> SELECT
  2  COUNT(TABXNPRESM1_.DETAIL_ID) AS X0_0_
  3    FROM QXTDEV1.TAB_XN_PRESMS TABXNPRESM0_, QXTDEV1.TAB_XN_PRESMS_DETAIL TABXNPRESM1_
  4  WHERE  (TABXNPRESM0_.COMPANY_ID = 346240)
  5     AND (TABXNPRESM0_.EMPLOYEE_ID = 0)
  6     AND (TABXNPRESM0_.PRE_TIME >= TO_DATE('2011/12/25', 'yyyy/mm/dd'))
  7     AND (TABXNPRESM0_.PRE_TIME < TO_DATE('2011/12/26', 'yyyy/mm/dd') + 1)
  8     AND ((TABXNPRESM0_.SEND_TYPE = 1) OR (TABXNPRESM0_.SEND_TYPE = 0))
  9     AND ((TABXNPRESM1_.RESULT = -1) OR (TABXNPRESM1_.RESULT = 0))
 10     AND (TABXNPRESM0_.PRE_ID = TABXNPRESM1_.PRE_ID);
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=1 Bytes=40)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TAB_XN_PRESMS_DETAIL'(Cost=3 Card=2 Bytes=30)
   3    2       NESTED LOOPS (Cost=11 Card=1 Bytes=40)
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'TAB_XN_PRESMS' (Cost=8 Card=1 Bytes=25)
   5    4           AND-EQUAL
   6    5             INDEX (RANGE SCAN) OF 'IDX_XN_PRESMS_2' (NON-UNIQUE)
   7    5             INDEX (RANGE SCAN) OF 'IDX_XN_PRESMS_1' (NON-UNIQUE) (Cost=4 Card=638)
   8    3         INDEX (RANGE SCAN) OF 'IDX_XN_PRESMS_DETAIL' (NON-UNIQUE) (Cost=2 Card=2)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     272188  consistent gets
          0  physical reads
          0  redo size
        375  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

3.关于AND-EQUAL解释
If the WHERE clauses uses columns of many single-column indexes, then Oracle executes the statement by performing a range scan on each index to retrieve the rowids of the rows that satisfy each condition. Oracle then merges the sets of rowids to obtain a set of rowids of rows that satisfy all conditions. Oracle then accesses the table using these rowids.
Oracle can merge up to five indexes. If the WHERE clause uses columns of more than five single-column indexes, then Oracle merges five of them, accesses the table by rowid, and then tests the resulting rows to determine whether they satisfy the remaining conditions before returning them.
大概的意思是当where条件后面含有多个列的单列索引时(不超过5个)，会先得到每个条件的rowid，然后这些rowid进行merges，得到一个rowid的结果集，最后根据这些rowid取表中记录。

4.表/列/索引相关信息

--index和列信息
SQL> SELECT INDEX_NAME, TABLE_NAME, COLUMN_NAME
  2    FROM USER_IND_COLUMNS
  3   WHERE TABLE_NAME IN ('TAB_XN_PRESMS', 'TAB_XN_PRESMS_DETAIL');
INDEX_NAME                     TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------ --------------------
IDX_XN_PRESMS_1                TAB_XN_PRESMS                  COMPANY_ID
IDX_XN_PRESMS_2                TAB_XN_PRESMS                  EMPLOYEE_ID
IDX_XN_PRESMS_3                TAB_XN_PRESMS                  PRE_TIME
PK_TAB_XN_PRESMS               TAB_XN_PRESMS                  PRE_ID
IDX_XN_PRESMS_4                TAB_XN_PRESMS                  SEND_TYPE
IDX_XN_PRESMS_DETAIL           TAB_XN_PRESMS_DETAIL           PRE_ID
IDX_XN_PRESMS_DETAIL_2         TAB_XN_PRESMS_DETAIL           SEND_TIME
PK_TAB_XN_PRESMS_DETAIL        TAB_XN_PRESMS_DETAIL           DETAIL_ID
8 rows selected
--index的统计信息
SQL> SELECT TABLE_NAME,
  2         INDEX_NAME,
  3         TO_CHAR(LAST_ANALYZED, 'yyyy-mm-dd hh24:mi:ss')
  4    FROM USER_INDEXES
  5   WHERE TABLE_NAME IN ('TAB_XN_PRESMS', 'TAB_XN_PRESMS_DETAIL');
TABLE_NAME                     INDEX_NAME                     TO_CHAR(LAST_ANALYZED,'YYYY-MM
------------------------------ ------------------------------ ------------------------------
TAB_XN_PRESMS                  IDX_XN_PRESMS_1                2011-12-29 09:25:32
TAB_XN_PRESMS                  IDX_XN_PRESMS_2                2011-12-29 09:25:35
TAB_XN_PRESMS                  IDX_XN_PRESMS_3                2011-12-29 09:25:39
TAB_XN_PRESMS                  IDX_XN_PRESMS_4                2011-12-29 09:25:21
TAB_XN_PRESMS_DETAIL           IDX_XN_PRESMS_DETAIL           2011-12-29 09:20:03
TAB_XN_PRESMS_DETAIL           IDX_XN_PRESMS_DETAIL_2         2011-12-29 09:20:01
TAB_XN_PRESMS                  PK_TAB_XN_PRESMS               2011-12-29 09:25:46
TAB_XN_PRESMS_DETAIL           PK_TAB_XN_PRESMS_DETAIL        2011-12-29 09:20:02
8 rows selected
--列的唯一度情况
SQL>  SELECT TABLE_NAME, COLUMN_NAME, NUM_DISTINCT
  2     FROM USER_TAB_COLS
  3    WHERE (TABLE_NAME, COLUMN_NAME) IN
  4          (SELECT TABLE_NAME, COLUMN_NAME
  5             FROM USER_IND_COLUMNS
  6            WHERE TABLE_NAME IN ('TAB_XN_PRESMS', 'TAB_XN_PRESMS_DETAIL'))
  7            ORDER BY table_name,NUM_DISTINCT DESC;
TABLE_NAME                     COLUMN_NAME                    NUM_DISTINCT
------------------------------ ------------------------------ ------------
TAB_XN_PRESMS                  PRE_ID                              1999270
TAB_XN_PRESMS                  PRE_TIME                            1342594
TAB_XN_PRESMS                  EMPLOYEE_ID                           10676
TAB_XN_PRESMS                  COMPANY_ID                             3136
TAB_XN_PRESMS                  SEND_TYPE                                10
TAB_XN_PRESMS_DETAIL           DETAIL_ID                           3863184
TAB_XN_PRESMS_DETAIL           PRE_ID                              1996872
TAB_XN_PRESMS_DETAIL           SEND_TIME                            437526
8 rows selected

通过这些信息可以得出：
1)统计信息是最新收集过的
2)因为有多个单列index，数据库为了使得cost最小，可能选择了不合适的index(IDX_XN_PRESMS_2[EMPLOYEE_ID]/IDX_XN_PRESMS_1[COMPANY_ID]),使得出现AND-EQUAL，从而逻辑读偏高。对于这个sql，应该使用唯一度比较高的IDX_XN_PRESMS_3[PRE_TIME]
3)也可以通过修改index，实现程序高效，但是考虑到会影响启动程序，在没有十足的把握之前遵守hint优先原则

5.增加hint提示

SQL> SELECT /*+ index(TABXNPRESM0_ IDX_XN_PRESMS_3) */
  2  COUNT(TABXNPRESM1_.DETAIL_ID) AS X0_0_
  3    FROM QXTDEV1.TAB_XN_PRESMS TABXNPRESM0_, QXTDEV1.TAB_XN_PRESMS_DETAIL TABXNPRESM1_
  4  WHERE  (TABXNPRESM0_.COMPANY_ID = 346240)
  5     AND (TABXNPRESM0_.EMPLOYEE_ID = 0)
  6     AND (TABXNPRESM0_.PRE_TIME >= TO_DATE('2011/12/25', 'yyyy/mm/dd'))
  7     AND (TABXNPRESM0_.PRE_TIME < TO_DATE('2011/12/26', 'yyyy/mm/dd') + 1)
  8     AND ((TABXNPRESM0_.SEND_TYPE = 1) OR (TABXNPRESM0_.SEND_TYPE = 0))
  9     AND ((TABXNPRESM1_.RESULT = -1) OR (TABXNPRESM1_.RESULT = 0))
 10     AND (TABXNPRESM0_.PRE_ID = TABXNPRESM1_.PRE_ID);
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=641 Card=1 Bytes=40)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TAB_XN_PRESMS_DETAIL'(Cost=3 Card=2 Bytes=30)
   3    2       NESTED LOOPS (Cost=641 Card=1 Bytes=40)
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'TAB_XN_PRESMS' (Cost=638 Card=1 Bytes=25)
   5    4           INDEX (RANGE SCAN) OF 'IDX_XN_PRESMS_3' (NON-UNIQUE) (Cost=63 Card=22286)
   6    3         INDEX (RANGE SCAN) OF 'IDX_XN_PRESMS_DETAIL' (NON-UNIQUE) (Cost=2 Card=2)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1422  consistent gets
          0  physical reads
          0  redo size
        375  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

从这里可以看出，程序的逻辑读下降了很多(从272188下降到1422)，得到了优化效果，提高了程序执行效率
结合上篇:BITMAP CONVERSION FROM ROWIDS,总结一个经验，如果同时使用到了一个表的多个index，效率一般情况下不会太高。同时也给各位提个醒，index并非越多越好，有时候会适得其反(建立index时需要考虑情况)

清理表部分数据方法

最近两天，开发要求，清理掉某些表的部分数据，因为不能停业务，不能采用cast+rman方式实现.只能自己写脚本删除，在这些删除数据中，经过总结，共有三种类型:
1.删除单个表数据
删除a表以dealdate为范围的部分数据

DECLARE
  P_SQL   VARCHAR2(300) :=
  'DELETE FROM a WHERE dealdate<to_date('||''''||
'2010-11-01 00:00:00'||''''||','||''''||'yyyy-mm-dd hh24:mi:ss'||''''||')';
  P_COUNT NUMBER := 1000;
BEGIN
  WHILE 1 = 1 LOOP
    EXECUTE IMMEDIATE P_SQL || ' and rownum <= :nu'
      USING P_COUNT;
    IF SQL%NOTFOUND THEN
      EXIT;
    END IF;
    COMMIT;
  END LOOP;
  COMMIT;
END;

2.删除两个关联表数据
tab_a,tab_b两个表通过共有的PRE_ID列关联，然后按照tab_a.ACCEPT_TIME列为条件删除两个表中数据,tab_b表中数据比tab_a多很多

DECLARE
  CURSOR CUR IS
    SELECT B.ROWID BID, A.ROWID AID
      FROM tab_a A, tab_b B
     WHERE A.PRE_ID = B.PRE_ID
       AND A.ACCEPT_TIME <
           TO_DATE('2010-11-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
     ORDER BY B.ROWID;
  V_COUNTER NUMBER;
BEGIN
  V_COUNTER := 0;
  FOR ROW IN CUR LOOP
    DELETE FROM tab_a WHERE ROWID = ROW.AID;
    DELETE FROM tab_b WHERE ROWID = ROW.BID;
    V_COUNTER := V_COUNTER + 1;
    IF (V_COUNTER >= 1000) THEN
      COMMIT;
      V_COUNTER := 0;
    END IF;
  END LOOP;
  COMMIT;
END;

3.删除某个表中重复列
删除tab_a表中的COMPANY_ID/PY_DES/PY_DES_Q/PY_TYPE/RELATE_ID列重复数据

DECLARE
  CURSOR CUR IS
    SELECT A.ROWID AID
      FROM tab_a A
     WHERE ROWID NOT IN (SELECT MAX(B.ROWID)
                           FROM tab_a B
                          WHERE A.COMPANY_ID = B.COMPANY_ID
                            AND A.PY_DES = B.PY_DES
                            AND A.PY_DES_Q = B.PY_DES_Q
                            AND A.PY_TYPE = B.PY_TYPE
                            AND A.RELATE_ID = B.RELATE_ID)
     ORDER BY A.ROWID;
  V_COUNTER NUMBER;
BEGIN
  V_COUNTER := 0;
  FOR ROW IN CUR LOOP
    DELETE FROM tab_a WHERE ROWID = ROW.AID;
    V_COUNTER := V_COUNTER + 1;
    IF (V_COUNTER >= 1000) THEN
      COMMIT;
      V_COUNTER := 0;
    END IF;
  END LOOP;
  COMMIT;
END;

ORA-07445[kslgetl()+120]/ORA-00108错误解决

一.数据库版本

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

二.alert中发现ORA-07445[kslgetl()+120]/ORA-00108错误

Mon Dec 19 09:19:42 2011
found dead dispatcher 'D000', pid = (13, 1)
Mon Dec 19 09:19:42 2011
dispatcher 'D000' encountered error getting listening address
Mon Dec 19 09:19:42 2011
Errors in file /opt/oracle/admin/gaxt/bdump/gaxt_ora_16297.trc:
ORA-07445: exception encountered: core dump [kslgetl()+120] [SIGSEGV] [Address not mapped to object] [0x000000208] [] []
ORA-00108: failed to set up dispatcher to accept connection asynchronously
Mon Dec 19 09:19:45 2011
found dead dispatcher 'D000', pid = (21, 2)
Mon Dec 19 09:19:45 2011
dispatcher 'D000' encountered error getting listening address
Mon Dec 19 09:19:45 2011
Errors in file /opt/oracle/admin/gaxt/bdump/gaxt_ora_16299.trc:
ORA-07445: exception encountered: core dump [kslgetl()+120] [SIGSEGV] [Address not mapped to object] [0x000000208] [] []
ORA-00108: failed to set up dispatcher to accept connection asynchronously

三.trace文件信息

Oracle process number: 15
Unix process pid: 10607, image: oracle@gongantest (D000)
Warning: keltnfy call to ldmInit failed with error 46
*** 2011-12-19 19:21:40.100
network error encountered getting listening address:
  NS Primary Error: TNS-12533: TNS:illegal ADDRESS parameters
  NS Secondary Error: TNS-12560: TNS:protocol adapter error
  NT Generic Error: TNS-00503: Illegal ADDRESS parameters
OPIRIP: Uncaught error 108. Error stack:
ORA-00108: failed to set up dispatcher to accept connection asynchronously
Exception signal: 11 (SIGSEGV), code: 1 (Address not mapped to object), addr: 0x208, PC: [0x7a06b8, kslgetl()+120]
*** 2011-12-19 19:21:40.107
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [kslgetl()+120] [SIGSEGV] [Address not mapped to object] [0x000000208] [] []
ORA-00108: failed to set up dispatcher to accept connection asynchronously
Current SQL information unavailable - no session.
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst()+31          call     ksedst1()            000000000 ? 000000001 ?
                                                   2B91BE8F7D50 ? 2B91BE8F7DB0 ?
                                                   2B91BE8F7CF0 ? 000000000 ?
ksedmp()+610         call     ksedst()             000000000 ? 000000001 ?
                                                   2B91BE8F7D50 ? 2B91BE8F7DB0 ?
                                                   2B91BE8F7CF0 ? 000000000 ?
ssexhd()+629         call     ksedmp()             000000003 ? 000000001 ?
                                                   2B91BE8F7D50 ? 2B91BE8F7DB0 ?
                                                   2B91BE8F7CF0 ? 000000000 ?
__restore_rt()+0     call     ssexhd()             00000000B ? 2B91BE8F8D70 ?
                                                   2B91BE8F8C40 ? 2B91BE8F7DB0 ?
                                                   2B91BE8F7CF0 ? 000000000 ?
kslgetl()+120        signal   __restore_rt()       0600E7560 ? 0000000E8 ?
                                                   09AAE5728 ? 0000009A9 ?
                                                   000003980 ? 09AAE5740 ?
ksfglt()+108         call     kslgetl()            0600E7560 ? 000000001 ?
                                                   09AAE5728 ? 0000009A9 ?
                                                   000003980 ? 09AAE5740 ?

四.在MOS上找到相关文章[ID 1298804.1]

Applies to:
Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.1.0.7 - Release: 11.1 to 11.1
Information in this document applies to any platform.
Symptoms
The following errors are seen in the trace file written by an ORA-7445 [kslgetl]:
network error encountered getting listening address:
NS Primary Error: TNS-12533: TNS:illegal ADDRESS parameters
NS Secondary Error: TNS-12560: TNS:protocol adapter error
NT Generic Error: TNS-00503: Illegal ADDRESS parameters
OPIRIP: Uncaught error 108. Error stack:
ORA-00108: failed to set up dispatcher to accept connection asynchronously
Exception signal: 11 (SIGSEGV), code: 1 (Address not mapped to object), addr: 0x130, PC: [0x82f09dc, kslgetl()+80]
The trace file indicates that there is no session:
Current SQL information unavailable - no session.
The Call Stack Trace in the ORA-7445 trace file contains a function list similar to:
 kslgetl <- PGOSF57_ksfglt
<- kghfre <- kmnsbf <- nsbfr <- nsiofrrg <- nsiocancel
<- nsopen_shutitdown <- nsclose <- nsgblclose <- nsgblTRMHelper <- nsgblRealTerm
<- nlstdstp <- npinlt <- ksuabt <- opidrv <- sou2o
<- opimai_real <- main <- libc_start_main
Cause
The trace file first reports: Warning: keltnfy call to ldmInit failed with error 46
The ORA-7445 is not the starting point here. This exception is just a spin-off from ORA-180 and it is possible that different internal errors may be seen, such as ORA-600 [504], depending on what is happening when the ORA-180 is encountered.
The cause for the ORA-180 is related to the inital message at the beginning of the trace file: "keltnfy call to ldmInit failed with error 46" and this is followed by: "network error encountered getting listening address:"
The error code (here: 46) is the key for solving the issue.
This warning says that ldmInit() returned error 46 which is LDMERR_HOST_NOT_FOUND (host not found).
This error is returned if the OS call gethostbyname() fails with an error. So these appears to be a network specific issue.
Solution
1) Check permission on /etc/hosts
$ ls -l /etc/hosts -rw-r--r-- 2 root root 194 Oct 17 2006 /etc/hosts
Check if /etc/hosts file is correctly configured
<ip address> <fully qualified hostname> <simple or short hostname> <alias, if applicable> ( all of this on one line ).
2) Check the hostname:
$ hostname
$ ping `hostname`
Make sure you are able to ping the hostname
3) Check if /etc/nodename is correctly configured
If you have DNS setup, ping is not a tool to diagnose DNS problem. A better tool to use is nslookup, dnsquery, or dig.
$ nslookup <shortname> $ nslookup <long name> $ nslookup <ip address>
The forward and reverse lookup should succeed and return consistent address/info.
4) Check nsswitch.conf
$ more nsswitch.confhosts: files dnsMake sure host lookup is also done through the /etc/hosts file and not just dns. It is recommended that FILES come first before DNS.
Also, check the resolv.conf. This makes sure that the DNS is working properly.

在这里看到虽然数据库的版本不一样，alert和trace中的错误不完全一致，但是很相似。是由于主机名不能被正常访问导致，所以尝试这从主机名相关部分着手解决。

五.查看主机名相关信息，解决问题

[oracle@gongantest ~]$  ls -l /etc/hosts
-rw-r--r-- 2 root root 176 Dec 16 13:43 /etc/hosts
[oracle@gongantest ~]$ hostname
gongantest
[oracle@gongantest ~]$ ping gongantest
ping: unknown host gongantest
[oracle@gongantest ~]$ more  /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1        localhost.localdomain localhost
#192.168.11.60    gongantest
--发现改主机名的dns被注释掉
[oracle@gongantest ~]$ ping gongantest
PING gongantest (192.168.11.60) 56(84) bytes of data.
From 192.168.9.66 icmp_seq=2 Destination Host Unreachable
From 192.168.9.66 icmp_seq=3 Destination Host Unreachable
From 192.168.9.66 icmp_seq=4 Destination Host Unreachable
--- gongantest ping statistics ---
5 packets transmitted, 0 received, +3 errors, 100% packet loss, time 3999ms, pipe 3
--除掉注释，测试不通
[root@gongantest ~]# ifconfig
eth1      Link encap:Ethernet  HWaddr 00:14:22:10:96:CA
          inet addr:192.168.9.66  Bcast:192.168.11.255  Mask:255.255.252.0
          inet6 addr: fe80::214:22ff:fe10:96ca/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:4207222 errors:0 dropped:0 overruns:0 frame:0
          TX packets:2482964 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:100
          RX bytes:1156547557 (1.0 GiB)  TX bytes:565900103 (539.6 MiB)
--发现该机器的ip为192.168.9.66，修改hosts文件。
--初步确定出现问题的原因是因为机器迁移，使用了新ip，注释掉了hosts文件中错误ip
[root@gongantest ~]# vi /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1        localhost.localdomain localhost
192.168.9.66    gongantest
[oracle@gongantest ~]$ ping gongantest
PING gongantest (192.168.9.66) 56(84) bytes of data.
64 bytes from gongantest (192.168.9.66): icmp_seq=1 ttl=64 time=0.037 ms
64 bytes from gongantest (192.168.9.66): icmp_seq=2 ttl=64 time=0.031 ms
64 bytes from gongantest (192.168.9.66): icmp_seq=3 ttl=64 time=0.033 ms
--- gongantest ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2000ms
rtt min/avg/max/mdev = 0.031/0.033/0.037/0.007 ms
--ping测试正常

继续观察

--观察d000进程是否启动
[oracle@gongantest ~]$ ps -ef|grep ora_
oracle   10180     1  0 13:23 ?        00:00:00 ora_pmon_gaxt
oracle   10182     1  0 13:23 ?        00:00:00 ora_psp0_gaxt
oracle   10184     1  0 13:23 ?        00:00:00 ora_mman_gaxt
oracle   10186     1  0 13:23 ?        00:00:00 ora_dbw0_gaxt
oracle   10188     1  0 13:23 ?        00:00:02 ora_lgwr_gaxt
oracle   10190     1  0 13:23 ?        00:00:00 ora_ckpt_gaxt
oracle   10192     1  0 13:23 ?        00:00:00 ora_smon_gaxt
oracle   10194     1  0 13:23 ?        00:00:00 ora_reco_gaxt
oracle   10196     1  0 13:23 ?        00:00:00 ora_cjq0_gaxt
oracle   10198     1  0 13:23 ?        00:00:00 ora_mmon_gaxt
oracle   10200     1  0 13:23 ?        00:00:00 ora_mmnl_gaxt
oracle   10204     1  0 13:23 ?        00:00:00 ora_s000_gaxt
oracle   10210     1  0 13:23 ?        00:00:00 ora_arc0_gaxt
oracle   10212     1  0 13:23 ?        00:00:00 ora_arc1_gaxt
oracle   10214     1  0 13:23 ?        00:00:00 ora_qmnc_gaxt
oracle   10218     1  0 13:23 ?        00:00:00 ora_j000_gaxt
oracle   10222     1  0 13:24 ?        00:00:00 ora_q000_gaxt
oracle   10234     1  0 13:24 ?        00:00:00 ora_q001_gaxt
oracle   10609     1  0 13:32 ?        00:00:00 ora_d000_gaxt
oracle   10639  9962  0 13:35 pts/0    00:00:00 grep ora_
--观察alert日志未出现该错误
Mon Dec 26 13:32:18 2011
Errors in file /opt/oracle/admin/gaxt/bdump/gaxt_ora_10607.trc:
ORA-07445: exception encountered: core dump [kslgetl()+120] [SIGSEGV] [Address not mapped to object] [0x000000208] [] []
ORA-00108: failed to set up dispatcher to accept connection asynchronously
Mon Dec 26 13:32:21 2011
found dead dispatcher 'D000', pid = (13, 85)
Mon Dec 26 13:36:04 2011
Thread 1 advanced to log sequence 232 (LGWR switch)
  Current log# 1 seq# 232 mem# 0: /opt/oracle/oradata/gaxt/redo01.log

至此ORA-07445[kslgetl()+120]/ORA-00108错误解决

记录一次oer 8102.2处理

1.alert日志

Tue Dec 20 22:09:45 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_m000_27442.trc:
Wed Dec 21 22:10:45 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_m000_32761.trc:
Thu Dec 22 22:11:46 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_m000_5935.trc:
Fri Dec 23 22:12:47 2011
Errors in file /opt/app/oracle/admin/BAS/bdump/bas_m000_11382.trc:

Mnnn performs manageability tasks dispatched to them by MMON. Tasks performed include taking Automatic Workload Repository snapshots and Automatic Database Diagnostic Monitor analysis.
从这个时间点来看，应该是数据库启动GATHER_STATS_JOB收集统计信息时发现这个错误。

2.bas_m000_11382.trc

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /opt/app/oracle/product/10.2.0/db_1
System name:    Linux
Node name:      bas
Release:        2.6.9-78.ELsmp
Version:        #1 SMP Wed Jul 9 15:46:26 EDT 2008
Machine:        x86_64
Instance name: BAS
Redo thread mounted by this instance: 1
Oracle process number: 24
Unix process pid: 11382, image: oracle@bas (m000)
*** ACTION NAME:(Auto-Purge Slave Action) 2011-12-23 22:12:47.074
*** MODULE NAME:(MMON_SLAVE) 2011-12-23 22:12:47.074
*** SERVICE NAME:(SYS$BACKGROUND) 2011-12-23 22:12:47.074
*** SESSION ID:(5465.2033) 2011-12-23 22:12:47.074
oer 8102.2 - obj# 4152, rdba: 0x00401f7c(afn 1, blk# 8060)
kdk key 8102.2:
  ncol: 2, len: 10
  key: (10):  02 c1 0a 06 00 c0 04 dc 00 00
  mask: (4096):
 09 00 00 00 00 fb d1 c0 00 00 00 00 00 70 f8 fe bf 7f 00 00 00 cd 7d 5d 01

oer 8102.<code> - obj# <object id>, rdba: <rdba value>(afn <file#>, blk# <block#>)
kdk key 8102.2:
ncol: <number of columns in the key including the rowid>, len: <key length>
key: (<length>):<hexadecimal value>
obj#:   object_id for the affected index in dba_objects.
rdba:   relative data block address where the key is supposed to be stored in the index.
afn:     absolute file number where the affected index block is stored.
(file_id in dba_data_files, file# in v$datafile).
blk#:   Index block number where the key is supposed to be stored.

出现oer 8102.2的错误，有两种可能:1.坏块,2.表和索引数据不一致

3.找出相关对象

SQL> col object_name for a30
SQL> col owner for a10
SQL> select object_name,owner,object_type
  2  from dba_objects where object_id=4152;
OBJECT_NAME                    OWNER      OBJECT_TYPE
------------------------------ ---------- -------------------
WRI$_SEGADV_OBJLIST_IDX_TS     SYS        INDEX
SQL> select OWNER,TABLE_NAME from dba_indexes
   2 where index_name='WRI$_SEGADV_OBJLIST_IDX_TS';
OWNER      TABLE_NAME
---------- ------------------------------
SYS        WRI$_SEGADV_OBJLIST
SQL> ANALYZE TABLE sys.WRI$_SEGADV_OBJLIST VALIDATE STRUCTURE CASCADE;
ANALYZE TABLE sys.WRI$_SEGADV_OBJLIST VALIDATE STRUCTURE CASCADE
*
ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file

4.分析坏块(逻辑/物理)

SQL> ANALYZE INDEX WRI$_SEGADV_OBJLIST_IDX_TS VALIDATE STRUCTURE;
Index analyzed.
SQL> ANALYZE TABLE WRI$_SEGADV_OBJLIST VALIDATE STRUCTURE;
Table analyzed.
[oracle@bas bdump]$ dbv file=/opt/app/oracle/oradata/BAS/system01.dbf
DBVERIFY: Release 10.2.0.1.0 - Production on Sat Dec 24 21:14:38 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
DBVERIFY - Verification starting : FILE = /opt/app/oracle/oradata/BAS/system01.dbf
DBVERIFY - Verification complete
Total Pages Examined         : 552960
Total Pages Processed (Data) : 360156
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 167596
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 1961
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 23247
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 2890198330 (2750.2890198330)

检测证明，对象以及对象所属的数据文件，无坏块现象

5.分析表和index不一致

--找出index对应列
SQL> SELECT table_name , column_name from dba_ind_columns
  2  WHERE index_name='WRI$_SEGADV_OBJLIST_IDX_TS' order by table_name;
TABLE_NAME                     COLUMN_NAME
------------------------------ --------------------
WRI$_SEGADV_OBJLIST            TS_ID
--确定对应列是否允许为null
SQL> desc WRI$_SEGADV_OBJLIST
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 AUTO_TASKID                                        NUMBER
 TS_ID                                              NUMBER
 OBJN                                               NUMBER
 OBJD                                               NUMBER
 STATUS                                             VARCHAR2(40)
 TASK_ID                                            NUMBER
 REASON                                             VARCHAR2(40)
 REASON_VALUE                                       NUMBER
 CREATION_TIME                                      TIMESTAMP(6)
 PROC_TASKID                                        NUMBER
 END_TIME                                           TIMESTAMP(6)
 SEGMENT_OWNER                                      VARCHAR2(30)
 SEGMENT_NAME                                       VARCHAR2(81)
 PARTITION_NAME                                     VARCHAR2(30)
 SEGMENT_TYPE                                       VARCHAR2(18)
 TABLESPACE_NAME                                    VARCHAR2(30)
--确认在表中对应列是否有空值
SQL> SELECT /*+ FULL(t1) */ count(TS_ID)
  2   FROM WRI$_SEGADV_OBJLIST t1
  3    WHERE t1.TS_ID IS NULL;
COUNT(TS_ID)
------------
           0
--表比index多数据
SQL> SELECT /*+ FULL(t1) */ TS_ID
  2  FROM WRI$_SEGADV_OBJLIST t1
  3  MINUS
  4  SELECT /*+ index(t WRI$_SEGADV_OBJLIST_IDX_TS) */ TS_ID
  5  FROM WRI$_SEGADV_OBJLIST t where  ts_id is not null;
no rows selected
--index中数据条数
SQL> SELECT /*+ index(t WRI$_SEGADV_OBJLIST_IDX_TS) */ count(TS_ID)
  2  FROM WRI$_SEGADV_OBJLIST t
  3  where ts_id is not null;
COUNT(TS_ID)
------------
         901
--表中数据条数
SQL> SELECT /*+ FULL(t1) */ count(TS_ID)
  2  FROM WRI$_SEGADV_OBJLIST t1 ;
COUNT(TS_ID)
------------
         937
--index中不同值数量
SQL> SELECT /*+ index(t WRI$_SEGADV_OBJLIST_IDX_TS) */
  2  COUNT(DISTINCT TS_ID)
  3  FROM WRI$_SEGADV_OBJLIST t WHERE TS_ID IS NOT NULL;
COUNT(DISTINCTTS_ID)
--------------------
                   5
--表中不同值数量
SQL> SELECT /*+ index(t WRI$_SEGADV_OBJLIST_IDX_TS) */ TS_ID
  2  FROM WRI$_SEGADV_OBJLIST t WHERE ts_id IS NOT NULL
  3  MINUS
  4  SELECT /*+ FULL(t1) */ TS_ID
  5  FROM WRI$_SEGADV_OBJLIST t1 ;
     TS_ID
----------
         4
--对比可以知道index中的唯一值比表中，这个也就解释了，为什么表中总条数多，
--但是他们两做减法的时候，记录为空
--索引表比表多数据
SQL> SELECT /*+ index(t WRI$_SEGADV_OBJLIST_IDX_TS) */ TS_ID
  2  FROM WRI$_SEGADV_OBJLIST t WHERE ts_id IS NOT NULL
  3  MINUS
  4  SELECT /*+ FULL(t1) */ TS_ID
  5  FROM WRI$_SEGADV_OBJLIST t1 ;
     TS_ID
----------
         4

上面的检测证明:1.表中有索引中无的数据,2.索引中有表中不存在数据

6.解决问题

SQL> alter index WRI$_SEGADV_OBJLIST_IDX_TS rebuild online;
Index altered.
--测试index中总条数
SQL> SELECT /*+ index(t WRI$_SEGADV_OBJLIST_IDX_TS) */ count(TS_ID)
  2   FROM WRI$_SEGADV_OBJLIST t
  3    where  ts_id is not null;
COUNT(TS_ID)
------------
         937
--无多余index项(以前唯一值为4的记录已经不存在)
SQL> SELECT /*+ index(t WRI$_SEGADV_OBJLIST_IDX_TS) */ TS_ID
  2      FROM WRI$_SEGADV_OBJLIST t WHERE ts_id IS NOT NULL
  3    MINUS
  4      SELECT /*+ FULL(t1) */ TS_ID
  5      FROM WRI$_SEGADV_OBJLIST t1 ;
no rows selected
--通过上述测试，证明表和index不一致问题解决

通过ROWID找回坏块数据

一.准备环境

C:\Users\XIFENFEI>sqlplus chf/xifenfei
SQL*Plus: Release 11.2.0.1.0 Production on 星期五 12月 23 10:49:52 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> create tablespace t_xff datafile 'E:\ORACLE\ORADATA\XFF\t_xff01.dbf'
  2  size 10m autoextend on next 10m maxsize 1g;
表空间已创建。
SQL> create table t_xifenfei tablespace t_xff
  2  as
  3  select * from dba_objects;
表已创建。
SQL> select count(*) from t_xifenfei;
  COUNT(*)
----------
     73286

二.发现坏块
使用ULtraEdit破坏数据(关闭数据库执行)

SQL> select count(*) from t_xifenfei;
select count(*) from t_xifenfei
                     *
第 1 行出现错误:
ORA-01578: ORACLE 数据块损坏 (文件号 13, 块号 373)
ORA-01110: 数据文件 13: 'E:\ORACLE\ORADATA\XFF\T_XFF01.DBF'

三.查询坏块相关信息

The "LOW_RID" is the lowest rowid INSIDE the corrupt block:
SELECT dbms_rowid.rowid_create(1,<DATA_OBJECT_ID>,<RFN>,<BL>,0) LOW_RID
from DUAL;
The "HI_RID" is the first rowid AFTER the corrupt block:
SELECT dbms_rowid.rowid_create(1,<DATA_OBJECT_ID>,<RFN>,<BL>+1,0) HI_RID
from DUAL;
SQL> col tablespace_name for a30
SQL> col segment_type for a5
SQL> col owner for a10
SQL> col segment_name for a20
SQL> SELECT tablespace_name, segment_type, owner, segment_name
  2                 FROM dba_extents
  3                WHERE file_id =13
  4  AND 373 between block_id AND block_id + blocks - 1 ;
TABLESPACE_NAME                SEGME OWNER      SEGMENT_NAME
------------------------------ ----- ---------- --------------------
T_XFF                          TABLE CHF        T_XIFENFEI
SQL> SELECT data_object_id
  2            FROM dba_objects
  3   WHERE object_name = 'T_XIFENFEI'  and owner='CHF';
DATA_OBJECT_ID
--------------
         77759
--坏块的最小rowid
SQL> select dbms_rowid.rowid_create(1, 77759,13,373,0) from dual;
DBMS_ROWID.ROWID_C
------------------
AAAS+/AANAAAAF1AAA
坏块的最大rowid(block+1得到)
SQL> select dbms_rowid.rowid_create(1, 77759,13,374,0) from dual;
DBMS_ROWID.ROWID_C
------------------
AAAS+/AANAAAAF2AAA

四.根据rowid找回数据

SQL> SELECT /*+ ROWID(A) */ COUNT(*) FROM T_XIFENFEI A
  2  WHERE ROWID>='AAAS+/AANAAAAF2AAA';
  COUNT(*)
----------
     55858
SQL> SELECT /*+ ROWID(A) */ COUNT(*) FROM T_XIFENFEI A
  2  WHERE  ROWID<'AAAS+/AANAAAAF1AAA';
  COUNT(*)
----------
     17358
SQL> SELECT 77759-55858-17358 from dual;
77759-55858-17358
-----------------
             4543
SQL> CREATE TABLE T_XIFENFEI_BAK  TABLESPACE T_XFF
  2  AS
  3  SELECT /*+ ROWID(A) */ * FROM T_XIFENFEI A
  4  WHERE ROWID>='AAAS+/AANAAAAF2AAA';
表已创建。
SQL> INSERT INTO  T_XIFENFEI_BAK
  2  SELECT /*+ ROWID(A) */ * FROM T_XIFENFEI A
  3  WHERE  ROWID<'AAAS+/AANAAAAF1AAA';
已创建17358行。
SQL> COMMIT;
提交完成。
SQL> SELECT COUNT(*) FROM T_XIFENFEI_BAK;
  COUNT(*)
----------
     73216

五.和dbms_repair解决坏块对比

SQL> CONN / AS SYSDBA
已连接。
SQL> exec dbms_repair.skip_corrupt_blocks('CHF','T_XIFENFEI');
PL/SQL 过程已成功完成。
SQL>  select skip_corrupt from dba_tables where table_name='T_XIFENFEI';
SKIP_COR
--------
ENABLED
SQL> select count(*) from chf.t_xifenfei;
  COUNT(*)
----------
     73216

通过跳过坏块和rowid功能对比可以看出，两者丢失的数据是相同的，如果有index，同样利用rowid结合index，可能会找回部分数据。当然dbms_repair也提供了类此的功能。两种方法的使用看个人的爱好与习惯。

使用bbed解决ORA-00600[2662]

一、数据库启动报ORA-00600[2662]

[oracle@node1 ora11g]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 22 14:37:00 2011
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size                  2230072 bytes
Variable Size            1493174472 bytes
Database Buffers          637534208 bytes
Redo Buffers                4947968 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [2], [2147510731], [2],
[2164287937], [4194432], [], [], [], [], [], []
Process ID: 16829
Session ID: 96 Serial number: 3

二.alert日志错误显示

Thu Dec 22 14:37:09 2011
ALTER DATABASE OPEN
LGWR: STARTING ARCH PROCESSES
Thu Dec 22 14:37:09 2011
ARC0 started with pid=20, OS id=16831
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Thu Dec 22 14:37:10 2011
ARC1 started with pid=21, OS id=16833
Thu Dec 22 14:37:10 2011
ARC2 started with pid=22, OS id=16835
Thu Dec 22 14:37:10 2011
ARC3 started with pid=23, OS id=16837
ARC1: Archival started
ARC2: Archival started
ARC2: Becoming the 'no FAL' ARCH
ARC2: Becoming the 'no SRL' ARCH
ARC1: Becoming the heartbeat ARCH
Thread 1 opened at log sequence 17
  Current log# 2 seq# 17 mem# 0: /opt/oracle/oradata/ora11g/redo02.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_16829.trc  (incident=36156):
ORA-00600: internal error code, arguments: [2662], [2], [2147510731], [2], [2164287937], [4194432], [], [], [], [], [], []
Incident details in: /opt/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_36156/ora11g_ora_16829_i36156.trc
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_16829.trc  (incident=36157):
ORA-00600: internal error code, arguments: [2662], [2], [2147510731], [2], [2164287937], [4194432], [], [], [], [], [], []
Incident details in: /opt/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_36157/ora11g_ora_16829_i36157.trc
Dumping diagnostic data in directory=[cdmp_20111222143713], requested by (instance=1, osid=16829), summary=[incident=36156].
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Undo initialization errored: err:600 serial:0 start:176607884 end:176611234 diff:3350 (33 seconds)
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_16829.trc:
ORA-00600: internal error code, arguments: [2662], [2], [2147510731], [2], [2164287937], [4194432], [], [], [], [], [], []
Errors in file /opt/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_16829.trc:
ORA-00600: internal error code, arguments: [2662], [2], [2147510731], [2], [2164287937], [4194432], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 16829): terminating the instance due to error 600
Instance terminated by USER, pid = 16829
ORA-1092 signalled during: ALTER DATABASE OPEN...
opiodr aborting process unknown ospid (16829) as a result of ORA-1092
Thu Dec 22 14:37:15 2011
ORA-1092 : opitsk aborting process

三.分析日志
ORA-00600[2662]主要参数说明见:ORA-00600 [2662]
这里补充说明:e表示出现异常问题的数据块的DBA,这里的4194432就是一个数据块的DBA

--通过DBA地址查询数据块和文件号
SQL> select dbms_utility.data_block_address_block(4194432) "blick",
  2    dbms_utility.data_block_address_file(4194432) "file" from dual;
     blick       file
---------- ----------
       128          1
--当前数据库SCN
SQL> select to_char(2147510731,'xxxxxxxxxxx') from dual;
TO_CHAR(2147
------------
    800069cb
--当前数据块SCN
SQL> select to_char(2164287937,'xxxxxxxxxxx') from dual;
TO_CHAR(2164
------------
    810069c1

四.bbed查看相关SCN

[oracle@node1 ora11g]$ bbed
Password:
BBED-00113: Invalid password. Please rerun utility with the correct password.
[oracle@node1 ora11g]$ bbed
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Thu Dec 22 14:49:24 2011
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set filename "/opt/oracle/oradata/ora11g/system01.dbf"
        FILENAME        /opt/oracle/oradata/ora11g/system01.dbf
BBED> set block 1
        BLOCK#          1
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes                   @484
   struct kcvcpscn, 8 bytes                 @484
      ub4 kscnbas                           @484      0x800069c8
      ub2 kscnwrp                           @488      0x0002
   ub4 kcvcptim                             @492      0x2dedee96
   ub2 kcvcpthr                             @496      0x0001
   union u, 12 bytes                        @500
      struct kcvcprba, 12 bytes             @500
         ub4 kcrbaseq                       @500      0x00000011
         ub4 kcrbabno                       @504      0x0000210f
         ub2 kcrbabof                       @508      0x0010
   ub1 kcvcpetb[0]                          @512      0x02
   ub1 kcvcpetb[1]                          @513      0x00
   ub1 kcvcpetb[2]                          @514      0x00
   ub1 kcvcpetb[3]                          @515      0x00
   ub1 kcvcpetb[4]                          @516      0x00
   ub1 kcvcpetb[5]                          @517      0x00
   ub1 kcvcpetb[6]                          @518      0x00
   ub1 kcvcpetb[7]                          @519      0x00
BBED> set block 128
        BLOCK#          128
BBED> p bas_kcbh
ub4 bas_kcbh                                @8        0x810069c1
BBED> p wrp_kcbh
ub2 wrp_kcbh                                @12       0x0002

这里看到的SCN(16进制)和我们在alert日志中看到的有一定的出入原因是在数据库启动的时候，当前SCN增加了，但是因为数据库直接abort，没有写入到数据文件中。导致数据文件头部的SCN比alert中显示的稍微小一点(还有可能，系统当前的scn比system01.dbf的scn大一点)。通过对比数据块和数据文件头部的SCN也可以说明当数据块的SCN>数据块当前SCN导致ORA-00600[2662]

五.bbed修改数据块的SCN

BBED> set offset 8
        OFFSET          8
BBED> m /x c8690080
BBED-00215: editing not allowed in BROWSE mode
BBED> set mode edit
        MODE            Edit
BBED> m /x c8690080
BBED-00209: invalid number (c8690080)
--分开修改，曲线救国策略
BBED> m /x c869
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /opt/oracle/oradata/ora11g/system01.dbf (0)
 Block: 128              Offsets:    8 to  519           Dba:0x00000000
------------------------------------------------------------------------
 c8690081 02000104 2f8f0000 00000000 00000000 00000000 00000000 06000000
 2f000000 20100000 00000000 00000000 07000000 81004000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 06000000 00000000 00000000
 00000040 81004000 07000000 88004000 08000000 10024000 08000000 18024000
 08000000 20024000 08000000 28024000 08000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 <32 bytes per line>
BBED> set offset +2
        OFFSET          10
BBED> m /x 0080
 File: /opt/oracle/oradata/ora11g/system01.dbf (0)
 Block: 128              Offsets:   10 to  521           Dba:0x00000000
------------------------------------------------------------------------
 00800200 01042f8f 00000000 00000000 00000000 00000000 00000600 00002f00
 00002010 00000000 00000000 00000700 00008100 40000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000600 00000000 00000000 00000000
 00408100 40000700 00008800 40000800 00001002 40000800 00001802 40000800
 00002002 40000800 00002802 40000800 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 <32 bytes per line>
BBED> p tailchk
ub4 tailchk                                 @8188     0x69c10e01
BBED> set offset 8188
        OFFSET          8188
BBED> m /x 010ec869
 File: /opt/oracle/oradata/ora11g/system01.dbf (0)
 Block: 128              Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 010ec869
 <32 bytes per line>
BBED> p tailchk
ub4 tailchk                                 @8188     0x69c80e01
BBED> p bas_kcbh
ub4 bas_kcbh                                @8        0x800069c8
BBED> sum apply
Check value for File 0, Block 128:
current = 0x8e2f, required = 0x8e2f
BBED> exit

六.启动数据库

[oracle@node1 ora11g]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 22 14:58:10 2011
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size                  2230072 bytes
Variable Size            1493174472 bytes
Database Buffers          637534208 bytes
Redo Buffers                4947968 bytes
Database mounted.
Database opened.

七.补充说明
一般遇到ORA-00600[2662]都是使用alter session set events ‘10015 trace name adjust_scn level N’;方法处理，但是有时候会遇到ORA-01031错误，那就需要请bbed帮忙处理

OS Pid: 30268 executed alter session set events '10051 trace name adjust_scn level 2'
Thu Dec 22 12:04:07 2011
Errors in file /ora101/diag/rdbms/ora11/ora11/trace/ora11_ora_30268.trc:
ORA-01031: insufficient privileges
Thu Dec 22 12:04:43 2011
Errors in file /ora101/diag/rdbms/ora11/ora11/trace/ora11_ora_846.trc:
ORA-01031: insufficient privileges

通过ZHS16GBK和AL32UTF8字符编码分析exp/imp

一、试验环境和试验准备工作
1.源端

[oracle@node1 ~]$ echo $LANG
zh_CN.gb2312
[oracle@node1 ~]$ sqlplus hr/xifenfei
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Dec 21 14:07:24 2011
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SELECT userenv('language') FROM dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK
SQL> create table xifenfei (name varchar2(6));
Table created.
SQL> insert into xifenfei values('惜分飞');
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> commit;
Commit complete.
SQL> select * from xifenfei;
NAME
------
惜分飞
惜分飞
惜分飞
惜分飞

2.目标端

[oracle@node1 ~]$ echo $LANG
zh_CN.gb2312
[oracle@node1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 21 15:26:18 2011
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, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> SELECT userenv('language') FROM dual;
USERENV('LANGUAGE')
----------------------------------------------------------------
AMERICAN_AMERICA.AL32UTF8

二、导出测试表(xifenfei)
1.ZHS16GBK编码

[oracle@node1 ~]$ echo $NLS_LANG
AMERICAN_AMERICA.ZHS16GBK
[oracle@node1 ~]$ exp hr/xifenfei file=/tmp/xifenfei.dmp \
log=/tmp/xifenfei.log tables=xifenfei
Export: Release 10.2.0.5.0 - Production on Wed Dec 21 14:07:11 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.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
About to export specified tables via Conventional Path ...
. . exporting table                       XIFENFEI          4 rows exported
Export terminated successfully without warnings.

2.AL32UTF8编码

[oracle@node1 ~]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
[oracle@node1 ~]$ exp hr/xifenfei file=/tmp/xifenfei_new.dmp \
> log=/tmp/xifenfei_new.log tables=xifenfei
Export: Release 10.2.0.5.0 - Production on Wed Dec 21 14:38:14 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table                       XIFENFEI          4 rows exported
Export terminated successfully without warnings.

3.dmp文件比较
3.1)文件大小比较

[oracle@node1 ~]$ cd /tmp
[oracle@node1 tmp]$ ll *.dmp
-rw-r--r-- 1 oracle oinstall 16384 12-21 14:07 xifenfei.dmp
-rw-r--r-- 1 oracle oinstall 16384 12-21 14:38 xifenfei_new.dmp
虽然导出客户端设置了不步的编码，但是导出的文件大小相等

3.2)文件头部比较

--xifenfei.dmp
00000000h: 03 03 54 45 58 50 4F 52 54 3A 56 31 30 2E 30 32 ; ..TEXPORT:V10.02
00000010h: 2E 30 31 0A 44 48 52 0A 52 54 41 42 4C 45 53 0A ; .01.DHR.RTABLES.
00000020h: 38 31 39 32 0A 30 0A 33 32 0A 30 0A 03 54 03 54 ; 8192.0.32.0..T.T
00000030h: 07 D0 00 01 00 00 00 00 00 00 00 00 00 11 00 20 ; .?............
00000040h: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 ;
00000050h: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 ;
00000060h: 20 20 20 20 20 20 20 20 20 57 65 64 20 44 65 63 ;          Wed Dec
00000070h: 20 32 31 20 31 34 3A 37 3A 31 32 20 32 30 31 31 ;  21 14:7:12 2011
00000080h: 2F 74 6D 70 2F 78 69 66 65 6E 66 65 69 2E 64 6D ; /tmp/xifenfei.dm
00000090h: 70 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; p...............
--xifenfei_new.dmp
00000000h: 03 03 69 45 58 50 4F 52 54 3A 56 31 30 2E 30 32 ; ..iEXPORT:V10.02
00000010h: 2E 30 31 0A 44 48 52 0A 52 54 41 42 4C 45 53 0A ; .01.DHR.RTABLES.
00000020h: 38 31 39 32 0A 30 0A 33 32 0A 30 0A 03 69 03 54 ; 8192.0.32.0..i.T
00000030h: 07 D0 00 01 00 00 00 00 00 00 00 00 00 15 00 20 ; .?............
00000040h: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 ;
00000050h: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 ;
00000060h: 20 20 20 20 20 20 20 20 57 65 64 20 44 65 63 20 ;         Wed Dec
00000070h: 32 31 20 31 34 3A 33 38 3A 31 35 20 32 30 31 31 ; 21 14:38:15 2011
00000080h: 2F 74 6D 70 2F 78 69 66 65 6E 66 65 69 5F 6E 65 ; /tmp/xifenfei_ne
00000090h: 77 2E 64 6D 70 00 00 00 00 00 00 00 00 00 00 00 ; w.dmp...........
--ZHS16GBK dmp 文件 第2,3字节为 0354
--AL32UTF8 dmp 文件 第2,3字节为 0369

3.3)比较dmp文件内容

--xifenfei.dmp
000020ceh: 2B 30 30 3A 30 30 00 00 04 00 42 59 54 45 06 00 ; +00:00....BYTE..
000020deh: 55 4E 55 53 45 44 01 00 32 0B 00 49 4E 54 45 52 ; UNUSED..2..INTER
000020eeh: 50 52 45 54 45 44 0B 00 44 49 53 41 42 4C 45 3A ; PRETED..DISABLE:
000020feh: 41 4C 4C 00 00 0A 4D 45 54 52 49 43 53 54 0A 54 ; ALL...METRICST.T
0000210eh: 41 42 4C 45 20 22 58 49 46 45 4E 46 45 49 22 0A ; ABLE "XIFENFEI".
0000211eh: 43 52 45 41 54 45 20 54 41 42 4C 45 20 22 58 49 ; CREATE TABLE "XI
0000212eh: 46 45 4E 46 45 49 22 20 28 22 4E 41 4D 45 22 20 ; FENFEI" ("NAME"
0000213eh: 56 41 52 43 48 41 52 32 28 36 29 29 20 20 50 43 ; VARCHAR2(6))  PC
0000214eh: 54 46 52 45 45 20 31 30 20 50 43 54 55 53 45 44 ; TFREE 10 PCTUSED
0000215eh: 20 34 30 20 49 4E 49 54 52 41 4E 53 20 31 20 4D ;  40 INITRANS 1 M
0000216eh: 41 58 54 52 41 4E 53 20 32 35 35 20 53 54 4F 52 ; AXTRANS 255 STOR
0000217eh: 41 47 45 28 49 4E 49 54 49 41 4C 20 36 35 35 33 ; AGE(INITIAL 6553
0000218eh: 36 20 4E 45 58 54 20 31 30 34 38 35 37 36 20 46 ; 6 NEXT 1048576 F
0000219eh: 52 45 45 4C 49 53 54 53 20 31 20 46 52 45 45 4C ; REELISTS 1 FREEL
000021aeh: 49 53 54 20 47 52 4F 55 50 53 20 31 20 42 55 46 ; IST GROUPS 1 BUF
000021beh: 46 45 52 5F 50 4F 4F 4C 20 44 45 46 41 55 4C 54 ; FER_POOL DEFAULT
000021ceh: 29 20 54 41 42 4C 45 53 50 41 43 45 20 22 55 53 ; ) TABLESPACE "US
000021deh: 45 52 53 22 20 4C 4F 47 47 49 4E 47 20 4E 4F 43 ; ERS" LOGGING NOC
000021eeh: 4F 4D 50 52 45 53 53 0A 49 4E 53 45 52 54 20 49 ; OMPRESS.INSERT I
000021feh: 4E 54 4F 20 22 58 49 46 45 4E 46 45 49 22 20 28 ; NTO "XIFENFEI" (
0000220eh: 22 4E 41 4D 45 22 29 20 56 41 4C 55 45 53 20 28 ; "NAME") VALUES (
0000221eh: 3A 31 29 0A 01 00 01 00 06 00 54 03 01 00 00 00 ; :1).......T.....
0000222eh: 00 00 06 00 CF A7 B7 D6 B7 C9 00 00 06 00 CF A7 ; ....惜分飞....惜
0000223eh: B7 D6 B7 C9 00 00 06 00 CF A7 B7 D6 B7 C9 00 00 ; 分飞....惜分飞..
0000224eh: 06 00 CF A7 B7 D6 B7 C9 00 00 FF FF 0A 4D 45 54 ; ..惜分飞...MET
0000225eh: 52 49 43 53 54 72 65 66 65 72 65 6E 74 69 61 6C ; RICSTreferential
0000226eh: 20 69 6E 74 65 67 72 69 74 79 20 63 6F 6E 73 74 ;  integrity const
0000227eh: 72 61 69 6E 74 73 0A 4D 45 54 52 49 43 45 54 20 ; raints.METRICET
0000228eh: 34 0A 4D 45 54 52 49 43 53 54 74 72 69 67 67 65 ; 4.METRICSTtrigge
0000229eh: 72 73 0A 4D 45 54 52 49 43 45 54 20 34 0A 4D 45 ; rs.METRICET 4.ME
000022aeh: 54 52 49 43 53 54 62 69 74 6D 61 70 2C 20 66 75 ; TRICSTbitmap, fu
000022beh: 6E 63 74 69 6F 6E 61 6C 20 61 6E 64 20 65 78 74 ; nctional and ext
000022ceh: 65 6E 73 69 62 6C 65 20 69 6E 64 65 78 65 73 0A ; ensible indexes.
000022deh: 4D 45 54 52 49 43 45 54 20 34 0A 4D 45 54 52 49 ; METRICET 4.METRI
000022eeh: 43 53 54 70 6F 73 74 74 61 62 6C 65 73 20 61 63 ; CSTposttables ac
000022feh: 74 69 6F 6E 73 0A 4D 45 54 52 49 43 45 54 20 34 ; tions.METRICET 4
0000230eh: 0A 4D 45 54 52 49 43 53 54 50 6F 73 74 2D 69 6E ; .METRICSTPost-in
0000231eh: 73 74 20 70 72 6F 63 65 64 75 72 61 6C 20 61 63 ; st procedural ac
0000232eh: 74 69 6F 6E 73 20 0A 4D 45 54 52 49 43 45 54 20 ; tions .METRICET
0000233eh: 34 0A 4D 45 54 52 49 43 45 54 47 30 0A 45 58 49 ; 4.METRICETG0.EXI
0000234eh: 54 0A 45 58 49 54 0A                            ; T.EXIT.
--xifenfei_new.dmp
000020ceh: 2B 30 30 3A 30 30 00 00 04 00 42 59 54 45 06 00 ; +00:00....BYTE..
000020deh: 55 4E 55 53 45 44 01 00 32 0B 00 49 4E 54 45 52 ; UNUSED..2..INTER
000020eeh: 50 52 45 54 45 44 0B 00 44 49 53 41 42 4C 45 3A ; PRETED..DISABLE:
000020feh: 41 4C 4C 00 00 0A 4D 45 54 52 49 43 53 54 0A 54 ; ALL...METRICST.T
0000210eh: 41 42 4C 45 20 22 58 49 46 45 4E 46 45 49 22 0A ; ABLE "XIFENFEI".
0000211eh: 43 52 45 41 54 45 20 54 41 42 4C 45 20 22 58 49 ; CREATE TABLE "XI
0000212eh: 46 45 4E 46 45 49 22 20 28 22 4E 41 4D 45 22 20 ; FENFEI" ("NAME"
0000213eh: 56 41 52 43 48 41 52 32 28 36 29 29 20 20 50 43 ; VARCHAR2(6))  PC
0000214eh: 54 46 52 45 45 20 31 30 20 50 43 54 55 53 45 44 ; TFREE 10 PCTUSED
0000215eh: 20 34 30 20 49 4E 49 54 52 41 4E 53 20 31 20 4D ;  40 INITRANS 1 M
0000216eh: 41 58 54 52 41 4E 53 20 32 35 35 20 53 54 4F 52 ; AXTRANS 255 STOR
0000217eh: 41 47 45 28 49 4E 49 54 49 41 4C 20 36 35 35 33 ; AGE(INITIAL 6553
0000218eh: 36 20 4E 45 58 54 20 31 30 34 38 35 37 36 20 46 ; 6 NEXT 1048576 F
0000219eh: 52 45 45 4C 49 53 54 53 20 31 20 46 52 45 45 4C ; REELISTS 1 FREEL
000021aeh: 49 53 54 20 47 52 4F 55 50 53 20 31 20 42 55 46 ; IST GROUPS 1 BUF
000021beh: 46 45 52 5F 50 4F 4F 4C 20 44 45 46 41 55 4C 54 ; FER_POOL DEFAULT
000021ceh: 29 20 54 41 42 4C 45 53 50 41 43 45 20 22 55 53 ; ) TABLESPACE "US
000021deh: 45 52 53 22 20 4C 4F 47 47 49 4E 47 20 4E 4F 43 ; ERS" LOGGING NOC
000021eeh: 4F 4D 50 52 45 53 53 0A 49 4E 53 45 52 54 20 49 ; OMPRESS.INSERT I
000021feh: 4E 54 4F 20 22 58 49 46 45 4E 46 45 49 22 20 28 ; NTO "XIFENFEI" (
0000220eh: 22 4E 41 4D 45 22 29 20 56 41 4C 55 45 53 20 28 ; "NAME") VALUES (
0000221eh: 3A 31 29 0A 01 00 01 00 06 00 54 03 01 00 00 00 ; :1).......T.....
0000222eh: 00 00 06 00 CF A7 B7 D6 B7 C9 00 00 06 00 CF A7 ; ....惜分飞....惜
0000223eh: B7 D6 B7 C9 00 00 06 00 CF A7 B7 D6 B7 C9 00 00 ; 分飞....惜分飞..
0000224eh: 06 00 CF A7 B7 D6 B7 C9 00 00 FF FF 0A 4D 45 54 ; ..惜分飞...MET
0000225eh: 52 49 43 53 54 72 65 66 65 72 65 6E 74 69 61 6C ; RICSTreferential
0000226eh: 20 69 6E 74 65 67 72 69 74 79 20 63 6F 6E 73 74 ;  integrity const
0000227eh: 72 61 69 6E 74 73 0A 4D 45 54 52 49 43 45 54 20 ; raints.METRICET
0000228eh: 34 0A 4D 45 54 52 49 43 53 54 74 72 69 67 67 65 ; 4.METRICSTtrigge
0000229eh: 72 73 0A 4D 45 54 52 49 43 45 54 20 34 0A 4D 45 ; rs.METRICET 4.ME
000022aeh: 54 52 49 43 53 54 62 69 74 6D 61 70 2C 20 66 75 ; TRICSTbitmap, fu
000022beh: 6E 63 74 69 6F 6E 61 6C 20 61 6E 64 20 65 78 74 ; nctional and ext
000022ceh: 65 6E 73 69 62 6C 65 20 69 6E 64 65 78 65 73 0A ; ensible indexes.
000022deh: 4D 45 54 52 49 43 45 54 20 34 0A 4D 45 54 52 49 ; METRICET 4.METRI
000022eeh: 43 53 54 70 6F 73 74 74 61 62 6C 65 73 20 61 63 ; CSTposttables ac
000022feh: 74 69 6F 6E 73 0A 4D 45 54 52 49 43 45 54 20 34 ; tions.METRICET 4
0000230eh: 0A 4D 45 54 52 49 43 53 54 50 6F 73 74 2D 69 6E ; .METRICSTPost-in
0000231eh: 73 74 20 70 72 6F 63 65 64 75 72 61 6C 20 61 63 ; st procedural ac
0000232eh: 74 69 6F 6E 73 20 0A 4D 45 54 52 49 43 45 54 20 ; tions .METRICET
0000233eh: 34 0A 4D 45 54 52 49 43 45 54 47 30 0A 45 58 49 ; 4.METRICETG0.EXI
0000234eh: 54 0A 45 58 49 54 0A                            ; T.EXIT.
--通过比较知道，不同客户端编码，导出的数据内容相同，
--都是一个汉字对应两个字节(说明是按照服务端编码导出)

三、导入测试表(xifenfei)
1.导入xifenfei.dmp(ZHS16GBK编码导出)
1.1)客户端编码设置ZHS16GBK

[oracle@node1 ~]$ echo $NLS_LANG
AMERICAN_AMERICA.ZHS16GBK
[oracle@node1 ~]$ imp test/xifenfei file=/tmp/xifenfei.dmp \
 log=/tmp/xifenfei_in.log tables=xifenfei fromuser=hr touser=test
Import: Release 11.2.0.3.0 - Production on Wed Dec 21 14:16:27 2011
Copyright (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, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by HR, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing HR's objects into TEST
. . importing table                     "XIFENFEI"
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 惜分飞
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 惜分飞
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 惜分飞
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 惜分飞          0 rows imported
Import terminated successfully with warnings.

1.2)客户端编码设置为AL32UTF8

[oracle@node1 ~]$ echo $NLS_LANG
AMERICAN_AMERICA.AL32UTF8
[oracle@node1 ~]$ imp test/xifenfei file=/tmp/xifenfei.dmp \
log=/tmp/xifenfei_in.log tables=xifenfei fromuser=hr touser=test
Import: Release 11.2.0.3.0 - Production on Wed Dec 21 14:21:41 2011
Copyright (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, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by HR, not by you
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
export client uses ZHS16GBK character set (possible charset conversion)
. importing HR's objects into TEST
. . importing table                     "XIFENFEI"
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 鎯滃垎椋
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 鎯滃垎椋
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 鎯滃垎椋
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 鎯滃垎椋         0 rows imported
Import terminated successfully with warnings.

2.导入xifenfei_new.dmp(AL32UTF8编码导出)
2.1)客户端编码设置ZHS16GBK

[oracle@node1 ~]$ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
[oracle@node1 ~]$ imp test/xifenfei file=/tmp/xifenfei_new.dmp \
> log=/tmp/xifenfei_in.log tables=xifenfei fromuser=hr touser=test
Import: Release 11.2.0.3.0 - Production on Wed Dec 21 14:39:53 2011
Copyright (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, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by HR, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
export client uses AL32UTF8 character set (possible charset conversion)
. importing HR's objects into TEST
. . importing table                     "XIFENFEI"
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 惜分飞
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 惜分飞
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 惜分飞
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 惜分飞          0 rows imported
Import terminated successfully with warnings.

2.2)客户端编码设置为AL32UTF8

[oracle@node1 ~]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
[oracle@node1 ~]$ imp test/xifenfei file=/tmp/xifenfei_new.dmp \
> log=/tmp/xifenfei_in.log tables=xifenfei fromuser=hr touser=test
Import: Release 11.2.0.3.0 - Production on Wed Dec 21 14:41:12 2011
Copyright (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, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by HR, not by you
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing HR's objects into TEST
. . importing table                     "XIFENFEI"
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 鎯滃垎椋
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 鎯滃垎椋
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 鎯滃垎椋
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 鎯滃垎椋         0 rows imported
Import terminated successfully with warnings.

四、修改列长度，导入成功
无论源端客户端使用何种编码导出，目标端使用何种编码导入(仅限我这里说的AL32UTF8和ZHS16GBK),如果客户端编码是ZHS16GBK，验证数据的时候，可以省略掉设置编码的过程。

--导入报错后，登录数据库，修改列长度，因为目标端数据库编码是AL32UTF8，
--1个汉字占用3个字节修改列的程度满足错误提示的最大程度。
SQL> alter table xifenfei modify name varchar2(9);
Table altered.
[oracle@node1 ~]$ imp test/xifenfei file=/tmp/xifenfei.dmp \
log=/tmp/xifenfei_in.log tables=xifenfei fromuser=hr touser=test IGNORE=y
Import: Release 11.2.0.3.0 - Production on Wed Dec 21 14:25:07 2011
Copyright (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, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by HR, not by you
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
export client uses ZHS16GBK character set (possible charset conversion)
. importing HR's objects into TEST
. . importing table                     "XIFENFEI"          4 rows imported
Import terminated successfully without warnings.
--因为目标端客户端编码为AL32UTF8，而linux展示平台编码为gbk，所以出现乱码
--修改客户端编码后，查询乱码问题消失
[oracle@node1 ~]$ sqlplus test/xifenfei
SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 21 14:25:14 2011
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, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> select * from xifenfei;
NAME
---------
鎯滃垎椋
鎯滃垎椋
鎯滃垎椋
鎯滃垎椋
SQL> exit
[oracle@node1 ~]$ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
[oracle@node1 ~]$ sqlplus test/xifenfei
SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 21 14:25:52 2011
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, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> select * from xifenfei;
NAME
------------------
惜分飞
惜分飞
惜分飞
惜分飞

五、试验总结
1.当源端字符编码为ZHS16GBK，目标端编码为AL32UTF8，客户端随便为其中的一种编码，迁移数据不会出现乱码，但是会出现列长度不够现象。反过来不行，因为utf8中的部分字符转换到gbk中肯定会不支持
2.设置了源端客户端编码，仅仅是导出来的dmp文件头部有编码字符标示不一样，存储数据还是按照服务端存储
3.打破神话，exp/imp导入要不乱码，导出和导入的客户端编码要一致

修改oracle数据库字符集

现在有个需求，需要数据库字符集从ZHS16GBK修改为AL32UTF8，因为他们没有子集的关系，所以在转换前，需要先检测库中的数据内容是否全库可以转换为AL32UTF8字符集，检测使用oracle提供的csscan工具实现
一、csscan使用
1.安装csscan相关数据字典

SQL> @?/rdbms/admin/csminst.sql

2.csscan使用说明

[oracle@node1 ~]$ csscan help=y
Character Set Scanner v2.2 : Release 11.2.0.3.0 - Production on Wed Dec 21 12:30:42 2011
Copyright (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.

3.使用csscan检测当前数据库

[oracle@node1 ~]$ csscan userid="'"sys/xifenfei as sysdba"'" full=y \
fromchar=ZHS16GBK tochar=AL32UTF8 log=/tmp/check.log capture=y array=1000000 process=4
Character Set Scanner v2.2 : Release 11.2.0.3.0 - Production on Wed Dec 21 12:36:37 2011
Copyright (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, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Enumerating tables to scan...
. process 1 scanning SYS.SOURCE$[AAAADgAABAAAAXgAAA]
. process 2 scanning XFF.T1[AAATjjAAEAAAC7AAAA]
. process 3 scanning TEST.T_XFF[AAAT+6AAEAAAAIQAAA]
. process 2 scanning SYS.METHOD$[AAAAHvAABAAAA0QAAA]
. process 4 scanning SYS.ATTRIBUTE$[AAAAHvAABAAAA0QAAA]
…………………………………………
. process 4 scanning CTXSYS.DR$INDEX_CDI_COLUMN
. process 2 scanning CTXSYS.DR$SDATA_UPDATE
. process 1 scanning EXFSYS.RLM$RULESETSTCODE
. process 3 scanning EXFSYS.RLM$RULESET
Creating Database Scan Summary Report...
Creating Individual Exception Report...
Scanner terminated successfully.

4.查看csscan检查日志

[oracle@node1 ~]$ ll /tmp/check.log.*
-rw-r--r-- 1 oracle oinstall  14526 12-21 12:37 /tmp/check.log.err
-rw-r--r-- 1 oracle oinstall 100235 12-21 12:37 /tmp/check.log.out
-rw-r--r-- 1 oracle oinstall   8265 12-21 12:37 /tmp/check.log.txt
--err是错误日志
--out是执行过程
--txt是执行结果汇总
[oracle@node1 tmp]$ cat /tmp/check.log.txt
Database Scan Summary Report
Time Started  : 2011-12-21 12:36:37
Time Completed: 2011-12-21 12:37:13
Process ID         Time Started       Time Completed
---------- -------------------- --------------------
         1  2011-12-21 12:36:43  2011-12-21 12:37:12
         2  2011-12-21 12:36:43  2011-12-21 12:37:12
         3  2011-12-21 12:36:43  2011-12-21 12:37:12
         4  2011-12-21 12:36:43  2011-12-21 12:37:12
---------- -------------------- --------------------
[Database Size]
Tablespace                           Used            Free           Total       Expansion
------------------------- --------------- --------------- --------------- ---------------
SYSTEM                            723.63M           6.38M         730.00M            .00K
SYSAUX                            686.56M         103.44M         790.00M            .00K
UNDOTBS1                           18.31M         126.69M         145.00M            .00K
TEMP                                 .00K            .00K            .00K            .00K
USERS                             138.69M          22.56M         161.25M            .00K
EXAMPLE                           310.13M          35.50M         345.63M            .00K
------------------------- --------------- --------------- --------------- ---------------
Total                           1,877.31M         294.56M       2,171.88M            .00K
[Database Scan Parameters]
Parameter                      Value
------------------------------ ------------------------------------------------
CSSCAN Version                 v2.1
Instance Name                  ora11g
Database Version               11.2.0.3.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        1000000
Number of processes            4
Capture convertible data?      YES
------------------------------ ------------------------------------------------
[Scan Summary]
All character type data in the data dictionary are convertible to the new character set
All character type application data remain the same in the new character set
[Data Dictionary Conversion Summary]
Data Dictionary Tables:
Datatype                    Changeless      Convertible       Truncation            Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2                     6,061,403              152                0                0
CHAR                             4,988                0                0                0
LONG                           252,530                0                0                0
VARRAY                          50,812                0                0                0
--------------------- ---------------- ---------------- ---------------- ----------------
Total                        6,369,733              152                0                0
Total in percentage             99.998%           0.002%           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                           711                0                0                0
CHAR                                 0                0                0                0
LONG                                 0                0                0                0
VARRAY                               0                0                0                0
--------------------- ---------------- ---------------- ---------------- ----------------
Total                              711                0                0                0
Total in percentage            100.000%           0.000%           0.000%           0.000%
[Application Data Conversion Summary]
Datatype                    Changeless      Convertible       Truncation            Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2                     8,142,707                0                0                0
CHAR                            63,085                0                0                0
LONG                                 0                0                0                0
VARRAY                             583                0                0                0
--------------------- ---------------- ---------------- ---------------- ----------------
Total                        8,206,375                0                0                0
Total in percentage            100.000%           0.000%           0.000%           0.000%
[Distribution of Convertible, Truncated and Lossy Data by Table]
Data Dictionary Tables:
USER.TABLE                                              Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
CTXSYS.DR$INDEX_VALUE                                            76                0                0
CTXSYS.DR$STOPWORD                                               76                0                0
-------------------------------------------------- ---------------- ---------------- ----------------
XML CSX Dictionary Tables:
USER.TABLE                                              Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------
Application Data:
USER.TABLE                                              Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------
[Distribution of Convertible, Truncated and Lossy Data by Column]
Data Dictionary Tables:
USER.TABLE|COLUMN                                       Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
CTXSYS.DR$INDEX_VALUE|IXV_VALUE                                  76                0                0
CTXSYS.DR$STOPWORD|SPW_WORD                                      76                0                0
-------------------------------------------------- ---------------- ---------------- ----------------
XML CSX Dictionary Tables:
USER.TABLE|COLUMN                                       Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------
Application Data:
USER.TABLE|COLUMN                                       Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------
[Indexes to be Rebuilt]
USER.INDEX on USER.TABLE(COLUMN)
-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
--因为检测结果没有Truncation(截断数据)/Lossy(丢失数据)都不存在记录，所以不用查看错误日志
--如果发现不为0，需要检查err日志，然后先处理丢这些记录，然后再转换

二、修改数据库字符集

[oracle@node1 tmp]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 21 12:59:55 2011
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, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> select value$ from props$ where name='NLS_CHARACTERSET';
VALUE$
------------------------------------------------------
ZHS16GBK
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size                  2230072 bytes
Variable Size            1493174472 bytes
Database Buffers          637534208 bytes
Redo Buffers                4947968 bytes
Database mounted.
SQL> alter system enable restricted session;
System altered.
SQL> alter system set job_queue_processes=0 scope=memory;
System altered.
SQL> alter system set aq_tm_processes=0  scope=memory;
System altered.
SQL> alter database open;
Database altered.
SQL> alter database character set internal_use AL32UTF8;
Database altered.
SQL> shutdown  immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size                  2230072 bytes
Variable Size            1493174472 bytes
Database Buffers          637534208 bytes
Redo Buffers                4947968 bytes
Database mounted.
Database opened.
SQL> select value$ from props$ where name='NLS_CHARACTERSET';
VALUE$
--------------------------------------------------------
AL32UTF8

作者：admin