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]#
測試,當時伺服器的負載比較大,可能是儲存響應延時過高導致。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29357786/viewspace-2638616/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle expdp資料泵遠端匯出Oracle
- Oracle資料泵匯出匯入(expdp/impdp)Oracle
- Oracle資料庫的邏輯備份工具-expdp資料泵Oracle資料庫
- Oracle使用資料泵expdp,impdp進行資料匯出匯入Oracle
- 【Datapump】Oracle資料泵遷移資料命令參考(expdp/impdp說明)Oracle
- 使用資料泵(expdp、impdp)遷移資料庫流程資料庫
- 使用impdp,expdp資料泵進入海量資料遷移
- oracle資料庫的impdp,expdpOracle資料庫
- ORACLE 資料泵impdp匯入報錯之ORA-31693 ORA-04098Oracle
- 資料泵datapump(expdp/impdp)的parfile用法,避免跳脫字元字元
- Java程式呼叫expdp資料泵實現自動邏輯備份Oracle資料庫的方案設計JavaOracle資料庫
- 【DB筆試面試164】在Oracle中,如何徹底停止expdp資料泵程式?筆試面試Oracle
- oracle10g expdp資料泵的bug,按schema匯出,匯入impdp時無jobOracle
- 【資料泵】EXPDP匯出表結構(真實案例)
- Oracle用資料泵匯入資料包12899的錯誤碼解決方法Oracle
- 通過dblink,資料泵expdp遠端跨版本導庫
- 【ASK_ORACLE】重灌Oracle資料泵(Datapump)工具的方法Oracle
- Oracle資料泵(Oracle Data Pump) 19cOracle
- Oracle資料泵的匯入和匯出Oracle
- Oracle資料庫(資料泵)遷移方案(上)Oracle資料庫
- Oracle資料庫(資料泵)遷移方案(下)Oracle資料庫
- oracle邏輯備份之--資料泵Oracle
- expdp報錯ORA-39181
- oracle按照表條件expdp匯出資料Oracle
- 史上最全Oracle資料泵常用命令Oracle
- Oracle Data Pump 11G 資料泵元件Oracle元件
- Linux下執行資料泵expdp和impdp命令,字元轉義案例兩則Linux字元
- expdp匯出報錯ORA-39127
- oracle12c還原資料庫遇到的問題-將一個11.2.0.1的資料泵匯出檔案匯入12.1.0.2版本報錯Oracle資料庫
- 資料泵expdp匯出遇到ORA-01555和ORA-22924問題的分析和處理
- 【BUILD_ORACLE】使用Oracle資料泵線上不停機克隆/遷移PDBUIOracle
- 【北亞資料恢復】異常斷電導致Oracle資料庫報錯的oracle資料恢復資料恢復Oracle資料庫
- 【DATAPUMP】Oracle資料泵定時備份刪除指令碼Oracle指令碼
- Oracle 12C新特性-資料泵新引數(LOGTIME)Oracle
- 殺停資料泵
- oracle 11g expdp匯出報ORA-24001Oracle
- Oracle 12C新特性-資料泵新引數(VIEWS_AS_TABLES)OracleView
- 資料庫升級之-資料泵資料庫