ITPub博客

首页 > 数据库 > Oracle > ORACLE 数据泵expdp莫名其妙的报错ORA-31693&ORA-19502&ORA-27063

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

原创 Oracle 作者:清风艾艾 时间:2019-03-18 08:25:33 0 删除 编辑

    最近,测试数据迁移,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]#

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29357786/viewspace-2638616/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
个人喜欢IT行业,目前从事数据库工作,包括Oracle、mysql、mongodb、sqlserver等数据库的维护,喜欢专研开发技术,尤其对java程序的开发感兴趣。工作经历上,在中国联通系统集成公司、中公网医疗信息技术有限公司做过数据库技术支持;目前在海量数据,负责华东区oracle、mysql、mongodb的维护工作。

注册时间:2015-01-30

  • 博文量
    222
  • 访问量
    390587