ORACLE 数据泵expdp莫名其妙的报错ORA-31693&ORA-19502&ORA-27063

    最近，测试数据迁移，expdp进行导出的时候，在大数据量的情况下，dumpfile不指定%U时

数据泵导出失败报错：ORA-31693&ORA-19502&ORA-27063，报错信息如下：

Export: Release 11.2.0.4.0 - Production on Sun Mar 17 18:09:26 2019

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,

OLAP, Data Mining and Real Application Testing options

FLASHBACK automatically enabled to preserve database integrity.

Starting "SYS"."SYS_EXPORT_SCHEMA_03":  "/******** AS SYSDBA" directory=zhul SCHEMAS=ZJ_YBDY INCLUDE=TABLE:" IN ('SKC22','KC22','KZA4' ) " dumpfile=expdp_ZJ_YBDY_large_table.dmp logfile=expdp_ZJ_YBDY_large_table.log 

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 799.1 GB

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/TABLE/GRANT/CROSS_SCHEMA/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

 

. . exported "ZJ_YBDY"."SKC22"                           313.8 GB 1711512123 rows

ORA-31693: Table data object "ZJ_YBDY"."KC22" failed to load/unload and is being skipped due to error:

ORA-19502: write error on file "/goldengate/expdp_ZJ_YBDY_large_table.dmp", block number 104043958 (block size=4096)

ORA-27063: number of bytes read/written is incorrect

IBM AIX RISC System/6000 Error: 28: No space left on device

Additional information: -1

Additional information: 262144

ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes

Job "SYS"."SYS_EXPORT_SCHEMA_03" stopped due to fatal error at Sun Mar 17 20:08:30 2019 elapsed 0 01:58:58

    根据提示查看存储情况：

[oracle@zj02]#du -sg *

3.70    dirdat

396.90  expdp_ZJ_YBDY_large_table.dmp

0.00    expdp_ZJ_YBDY_large_table.log

[oracle@zj02]#

[oracle@zj02]#df -g

Filesystem    GB blocks      Free %Used    Iused %Iused Mounted on

/dev/hd4           1.00      0.67   33%    11008     7% /

/dev/hd2           4.00      1.81   55%    48625    11% /usr

/dev/hd9var        2.00      1.39   31%     8288     3% /var

/dev/hd3           4.00      3.08   24%     1601     1% /tmp

/dev/hd1           0.12      0.11   16%       67     1% /home

/dev/hd11admin      0.12      0.12    1%        5     1% /admin

/proc                 -         -    -         -     -  /proc

/dev/hd10opt       2.00      1.70   16%    10530     3% /opt

/dev/livedump      0.25      0.25    1%        4     1% /var/adm/ras/livedump

/dev/oralv       150.00    101.89   33%   190245     1% /oracle

/dev/nbu_lv       10.00      7.63   24%     2088     1% /usr/openv

/dev/fslv00     1000.00    599.24   41%       18     1% /goldengate

[oracle@zj02]#

    到这里，真的不明白oracle闹的是哪一出，存储空间明明是够的，并且没有使用并行，expdp导出竟然报错，还堂而皇之的说存储空间满。

    但是，当dumpfile指定%U的时候，却能成功导出：

[oracle@zj02]#expdp \"/ as sysdba\" directory=zhul SCHEMAS=ZJ_YBDY INCLUDE=TABLE:\" IN \(\'SKC22\',\'KC22\',\'KZA4\' \) \" dumpfile=expdp_ZJ_YBDY_large_table_%U.dmp logfile=expdp_ZJ_YBDY_large_table.log

Export: Release 11.2.0.4.0 - Production on Sun Mar 17 20:12:41 2019

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,

OLAP, Data Mining and Real Application Testing options

FLASHBACK automatically enabled to preserve database integrity.

Starting "SYS"."SYS_EXPORT_SCHEMA_04":  "/******** AS SYSDBA" directory=zhul SCHEMAS=ZJ_YBDY INCLUDE=TABLE:" IN ('SKC22','KC22','KZA4' ) " dumpfile=expdp_ZJ_YBDY_large_table_%U.dmp logfile=expdp_ZJ_YBDY_large_table.log 

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 799.1 GB

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/TABLE/GRANT/CROSS_SCHEMA/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "ZJ_YBDY"."SKC22"                           313.8 GB 1711517503 rows

. . exported "ZJ_YBDY"."KC22"                            261.7 GB 1786268991 rows

. . exported "ZJ_YBDY"."KZA4"                            134.7 GB 6189746 rows

Master table "SYS"."SYS_EXPORT_SCHEMA_04" successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.SYS_EXPORT_SCHEMA_04 is:

  /goldengate/expdp_ZJ_YBDY_large_table_01.dmp

Job "SYS"."SYS_EXPORT_SCHEMA_04" successfully completed at Sun Mar 17 22:52:03 2019 elapsed 0 02:39:16

[oracle@zj02]#

    根据导出成功后，查看dumpfile大小，也不是expdp对单个dumpfile文件大小的限制：

[oracle@zj02]#scp expdp* 130.22.201.185:/goldengate/

oracle@130.22.201.185's password: 

expdp_ZJ_YBDY_large_table.log                                                                      100% 1942     1.9KB/s   00:00    

expdp_ZJ_YBDY_large_table_01.dmp                                                                   100%  710GB  36.0MB/s 5:37:12    

[oracle@zj02]#

    测试，当时服务器的负载比较大，可能是存储响应延时过高导致。