[20141011]expdp us7ascii 中文字段 LPX-00216.txt

--生产系统由于历史问题,使用的字符集是AMERICAN_AMERICA.US7ASCII,昨天在使用expdp导出时遇到一个奇怪的问题,
--google竟然没有找到答案,自己解决浪费了一个上午的时间,做一个记录.

--oracle 版本 10.2.0.4 OS:linux 64bit

-- expdp system/xxxxx dumpfile=full_20141011.dmp logfile=full_20141011.log DIRECTORY=dmpdir

--错误提示:
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name "DW01" prematurely terminated
ORA-31671: Worker process DW01 had an unhandled exception.
ORA-24795: Illegal ROLLBACK attempt made
ORA-06512: at "SYS.KUPW$WORKER", line 1352
ORA-24795: Illegal ROLLBACK attempt made
ORA-06512: at "SYS.KUPW$WORKER", line 7073
ORA-24795: Illegal ROLLBACK attempt made
ORA-06512: at "SYS.KUPW$WORKER", line 2829
ORA-24795: Illegal ROLLBACK attempt made
ORA-24795: Illegal ROLLBACK attempt made
ORA-06512: at "SYS.KUPW$WORKER", line 10915
ORA-02354: error in exporting/importing data
ORA-39774: parse of metadata stream failed with the following error:
LPX-00216: invalid character 178 (0xB2)
Job "SYSTEM"."SYS_EXPORT_FULL_04" stopped due to fatal error at 23:52:13
ORA-39014: One or more workers have prematurely exited.

--使用bvi打开文件:(确定最后的位置)
$ bvi -b 106164850688 -s 819200 full_20141011.dmp

18B7FAEFF0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 01 3C 3F 78 6D 6C 20 76 65 72 73 69 6F 6E 3D 22 31 2E 30 22 3F 3E 3C 52 4F 57 53 45 54 3E 3C 52 4F 57 3E 0A 20 20 3C ................... <
18B7FAF028 53 54 52 4D 54 41 42 4C 45 5F 54 3E 3C 56 45 52 53 5F 4D 41 4A 4F 52 3E 31 3C 2F 56 45 52 53 5F 4D 41 4A 4F 52 3E 3C 56 45 52 53 5F 4D 49 4E 4F 52 3E 30 20 3C 2F 56 45 STRMTABLE_T>10 18B7FAF060 52 53 5F 4D 49 4E 4F 52 3E 3C 56 45 52 53 5F 44 50 41 50 49 3E 33 3C 2F 56 45 52 53 5F 44 50 41 50 49 3E 3C 45 4E 44 49 41 4E 4E 45 53 53 3E 32 3C 2F 45 4E 44 49 41 4E RS_MINOR>3218B7FAF098 4E 45 53 53 3E 3C 43 48 41 52 53 45 54 3E 55 53 37 41 53 43 49 49 3C 2F 43 48 41 52 53 45 54 3E 3C 4E 43 48 41 52 53 45 54 3E 41 4C 31 36 55 54 46 31 36 3C 2F 4E 43 48 NESS>US7ASCIIAL16UTF1618B7FAF0D0 41 52 53 45 54 3E 3C 44 42 54 49 4D 45 5A 4F 4E 45 3E 2B 30 38 3A 30 30 3C 2F 44 42 54 49 4D 45 5A 4F 4E 45 3E 3C 46 44 4F 3E 30 30 30 30 30 30 36 30 30 31 32 34 30 46 ARSET>+08:000000006001240F
18B7FAF108 30 35 30 42 30 43 30 33 30 43 30 43 30 35 30 34 30 35 30 44 30 36 30 39 30 37 30 38 30 35 30 35 30 35 30 35 30 35 30 46 30 35 30 35 30 35 30 35 30 35 30 41 30 35 30 35 050B0C030C0C0504050D0609070805050505050F05050505050A0505
18B7FAF140 30 35 30 35 30 35 30 34 30 35 30 36 30 37 30 38 30 38 32 33 34 37 32 33 34 37 30 38 31 31 32 33 30 38 31 31 34 31 42 30 34 37 30 30 38 33 30 30 30 31 30 37 44 30 30 33 05050504050607080823472347081123081141B0470083000107D003
18B7FAF178 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 00000000000000000000000000000000000000000000000000000000
18B7FAF1B0 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 3C 2F 46 44 4F 3E 3C 4F 42 4A 5F 4E 55 4D 3E 33 32 32 34 33 32 3C 2F 4F 42 4A 5F 4E 55 4D 3E 3C 4F 57 4E 45 52 5F 00000000000000000032243218B7FAF1E8 4E 41 4D 45 3E 58 58 58 58 3C 2F 4F 57 4E 45 52 5F 4E 41 4D 45 3E 3C 4E 41 4D 45 3E 54 45 4D 50 5F 4F 50 45 52 41 54 49 4F 4E 5F 56 53 5F 44 49 43 54 3C 2F 4E 41 4D 45 NAME>XXXXTEMP_OPERATION_VS_DICT18B7FAF220 3E 3C 50 52 4F 50 45 52 54 59 3E 35 33 36 38 37 30 39 31 32 3C 2F 50 52 4F 50 45 52 54 59 3E 3C 43 4F 4C 5F 4C 49 53 54 3E 3C 43 4F 4C 5F 4C 49 53 54 5F 49 54 45 4D 3E >536870912
18B7FAF258 3C 4F 42 4A 5F 4E 55 4D 3E 33 32 32 34 33 32 3C 2F 4F 42 4A 5F 4E 55 4D 3E 3C 43 4F 4C 5F 4E 55 4D 3E 31 3C 2F 43 4F 4C 5F 4E 55 4D 3E 3C 49 4E 54 43 4F 4C 5F 4E 55 4D 3224321

--可以看到OBJ_NUM=322432.
SYS@orcl> column OBJECT_NAME format a30
SYS@orcl> select * from dba_objects where object_id=322432 ;
OWNER |OBJECT_NAME |SUBOBJECT_| OBJECT_ID|DATA_OBJECT_ID|OBJECT_TYPE |CREATED |LAST_DDL_TIME |TIMESTAMP |STATUS |T|G|S
------|------------------------------|----------|------------|--------------|-------------------|-------------------|-------------------|-------------------|-------|-|-|-
XXXX |TEMP_OPERATION_VS_DICT | | 322432| 322432|TABLE |2014-06-30 19:54:36|2014-06-30 19:54:36|2014-06-30:19:54:36|VALID |N|N|N

SYS@orcl> column OBJECT_TYPE_PATH format a50
SYS@orcl> column state format a10
SYS@orcl> select process_order,object_type_path,object_type,object_name,processing_status,processing_state state from SYSTEM.SYS_EXPORT_FULL_04 where object_name='TEMP_OPERATION_VS_DICT';
PROCESS_ORDER|OBJECT_TYPE_PATH                                  |OBJECT_TYPE                   |OBJECT_NAME                   |P|STATE
-------------|--------------------------------------------------|------------------------------|------------------------------|-|----------
         1030|DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA           |TABLE_DATA                    |TEMP_OPERATION_VS_DICT        |C|R
        54050|DATABASE_EXPORT/SCHEMA/TABLE/TABLE                |TABLE                         |TEMP_OPERATION_VS_DICT        |C|R

--猜测一下,应该这个已经完成,错误应该是PROCESS_ORDER=1031.

SYS@orcl> select process_order,object_type_path,object_type,object_name,processing_status,processing_state state from SYSTEM.SYS_EXPORT_FULL_04 where PROCESS_ORDER=1031;
PROCESS_ORDER|OBJECT_TYPE_PATH |OBJECT_TYPE |OBJECT_NAME |P|STATE
-------------|--------------------------------------------------|------------------------------|------------------------------|-|----------
1031|DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA |TABLE_DATA |TMP_CYBR |C|E

--确定在表TMP_CYBR吗?

SYS@orcl> select process_order,object_type_path,object_type,object_name,processing_status,processing_state state from SYSTEM.SYS_EXPORT_FULL_04 where PROCESS_ORDER=1031 or object_name='TMP_CYBR';
PROCESS_ORDER|OBJECT_TYPE_PATH                                  |OBJECT_TYPE                   |OBJECT_NAME                   |P|STATE
-------------|--------------------------------------------------|------------------------------|------------------------------|-|----------
          -41|DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA           |                              |TMP_CYBR                      | |
         1031|DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA           |TABLE_DATA                    |TMP_CYBR                      |C|E
        53803|DATABASE_EXPORT/SCHEMA/TABLE/TABLE                |TABLE                         |TMP_CYBR                      |C|R

--乱猜一下,可以确定到导出TMP_CYBR是出错.

SYS@orcl> @desc xxxx.TMP_CYBR
Name                    Null?    Type
----------------------- -------- ----------------
病人ID                           VARCHAR2(32)
住院次数                         NUMBER(18)
...
单位电话                         VARCHAR2(16)
联系电话                         VARCHAR2(16)
ADMISSION_DEPT                   VARCHAR2(10)
DISCHARGE_DEPT                   VARCHAR2(10)

--看到了中文字段,难道是中文字段的引起的吗?

$ echo -n '病人' | xxd -c 16
0000000: b2a1 c8cb ..人

--"病"的ascii码就是b2a1. 正好与提示: LPX-00216: invalid character 178 (0xB2) 对上.

--单独导出这张表看看:

$ expdp system/xxxx dumpfile=cybr.dmp logfile=full.log DIRECTORY=dmpdir tables=xxxx.TMP_CYBR
Export: Release 10.2.0.4.0 - 64bit Production on Saturday, 11 October, 2014 17:25:21
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_05": system/a******* dumpfile=cybr.dmp logfile=full.log DIRECTORY=dmpdir tables=XXXX.TMP_CYBR
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name "DW01" prematurely terminated
ORA-31671: Worker process DW01 had an unhandled exception.
ORA-24795: Illegal ROLLBACK attempt made
ORA-06512: at "SYS.KUPW$WORKER", line 1352
ORA-24795: Illegal ROLLBACK attempt made
ORA-06512: at "SYS.KUPW$WORKER", line 7073
ORA-24795: Illegal ROLLBACK attempt made
ORA-06512: at "SYS.KUPW$WORKER", line 2829
ORA-24795: Illegal ROLLBACK attempt made
ORA-24795: Illegal ROLLBACK attempt made
ORA-06512: at "SYS.KUPW$WORKER", line 10915
ORA-02354: error in exporting/importing data
ORA-39774: parse of metadata stream failed with the following error:
LPX-00216: invalid character 178 (0xB2)

Job "SYSTEM"."SYS_EXPORT_TABLE_05" stopped due to fatal error at 17:25:56

--再次证明,自己的判断正确.修改字段再导出测试:

ALTER TABLE xxxx.TMP_CYBR RENAME COLUMN "病人ID" TO "A人ID";

ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name "DW01" prematurely terminated
ORA-31671: Worker process DW01 had an unhandled exception.
ORA-24795: Illegal ROLLBACK attempt made
ORA-06512: at "SYS.KUPW$WORKER", line 1352
ORA-24795: Illegal ROLLBACK attempt made
ORA-06512: at "SYS.KUPW$WORKER", line 7073
ORA-24795: Illegal ROLLBACK attempt made
ORA-06512: at "SYS.KUPW$WORKER", line 2829
ORA-24795: Illegal ROLLBACK attempt made
ORA-24795: Illegal ROLLBACK attempt made
ORA-06512: at "SYS.KUPW$WORKER", line 10915
ORA-02354: error in exporting/importing data
ORA-39774: parse of metadata stream failed with the following error:
LPX-00216: invalid character 200 (0xC8)

--ascii码变成了0xC8,"人"的ascii="c8cb",这样正好都对上.这些表正常应用不会使用,估计可以删除.

--查询系统看看还有那些是中文字段的表.
SYS@orcl> select distinct owner,table_name from dba_tab_cols where (substr(column_name,1,1))>'z';

OWNER |TABLE_NAME
------|----------
XXXX |TMP_CYBR
XXXX |TMP_CYBR_1

--估计修改没有问题.另外我在中文字符测试,没有这个问题.

ITPub博客

[1011]expdp us7ascii中文字段LPX-00216

最新文章