ORACLE 資料泵expdp莫名其妙的報錯ORA-31693&ORA-19502&ORA-27063

清風艾艾發表於2019-03-18

    最近,測試資料遷移,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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章