impdp操作產生大量UNDO的原因及解決方法
在IMPDP Job期間產生大量UNDO的大部分原因都是因為在目標資料庫匯入的表已經存在，並且在這個表上存在一個或多個的索引。在impdp job期間，插入的每一行都需要維護索引。
要解決這個問題，請確保在目標資料庫不存在需要匯入的表，之後讓impdp來建立表。也就是說在執行impdp DataPump job的時候不要使用TABLE_EXISTS_ACTION=APPEND或者TABLE_EXISTS_ACTION=TRUNCATE引數，除非是真正必須要使用。
如果一個大資料量需要匯入一個存在表，你不得不使用TABLE_EXISTS_ACTION=APPEND，那麼請執行以下的步驟：
1).在目標資料庫執行存在表的export，以至於在需要的時候能夠恢復表的資料和相關物件的DDL操作。
2).如果在目標資料庫存在的表上有觸發器，如果可能考慮DROP它們。
3).確保在表上沒有主鍵（索引），（如果你的目的是為了防止重複行，你必須要保留主鍵約束，這將不可避免在相關索引import job期間產生UNDO）。
4).如果在目標資料庫存在的表上包含其它的索引，如果可能考慮DROP它們（注意在執行impdp期間使用EXCLUDE=INDEX不能跳過任何存在索引的維護工作）。
5).將資料匯入目標資料庫存在的表中。
6).重建索引、主鍵和觸發器。
DataPump會選擇最佳的方法確保資料和後設資料以最有效的方法完成匯出和匯入。
預設情況在impdp期間，Impdp將維護索引，如果表和索引已經建立，不要使用direct_path，然而，如果沒有索引強制約束，可以在DataPump匯入命令列指定ACCESS_METHOD=DIRECT_PATH，DataPump能使用直接路徑（direct path)方法實施匯入。
也可以透過禁用主鍵約束來實現直接路徑載入資料（使用ALTER TABLE ... MODIFY CONSTRAINT ... DISABLE NOVALIDATE)，之後在impdp期間使用access_method=direct_path引數載入資料，最後啟用主鍵約束（使用ALTER TABLE ... MODIFY CONSTRAINT ... ENABLE VALIDATE)。
在匯入期間不建立索引消耗UNDO是非常小的，下面是一個很好的資料匯出、匯入案例（案例轉載自：）：
因一次硬體原因導致了伺服器異常停機，防止蝴蝶效應及時進行了資料遷移，一週發現了這個決定正確的。
申請了2小時的停機時間。因資料不大且表空間開始使用不規範決定用expdp資料泵方式遷移。
在新裝置
1).raid10+安裝OS
2).安裝oracle 10.2.0.1(single instance)+
3).升級oracle 10.2.0.5
4).建庫,
5).調整INSTANCE引數,調整temp tbs、redo file size、undo tbs大小
6).建立app schema、建立data\index tablespace
用時1小時左右
stop web app+掛通知
stop listener
sys@TOPBOX>select object_type,count(*) cnt from dba_objects where owner='TOPBOX' GROUP BY OBJECT_TYPE;
OBJECT_TYPE CNT
------------------- --------------------
SEQUENCE 802
PROCEDURE 25
DATABASE LINK 2
LOB 3
TRIGGER 3
MATERIALIZED VIEW 2
TABLE 87
FUNCTION 12
VIEW 41
INDEX 142
sys@TOPBOX>@schemas_space
SCHEMA Object type Space [MB]
----------------------------------- --------------------------------------------- -----------
...
TOPBOX INDEX 34102.25
LOBINDEX .19
LOBSEGMENT .44
TABLE 31132.69
*********************************** -----------
sum 65235.56
...
######################
expdp 用時14分鐘
######################
expdp system/xxxxxx schemas=topbox directory=datapump dumpfile=newtopbox%U.dump filesize=5G parallel=6 logfile=topbox_parallel_expdp.log;
[oracle@topbox datapump]$ du newtopbox0*
829016 newtopbox01.dump
5248012 newtopbox02.dump
5248012 newtopbox03.dump
5248012 newtopbox04.dump
4338316 newtopbox05.dump
3681564 newtopbox06.dump
2029228 newtopbox07.dump
673872 newtopbox08.dump
80164 newtopbox09.dump
[oracle@topbox datapump]$ du newtopbox0*|awk ‘{sum += $1};END {print sum}’
27376196
TIP:
DUMP file only contains of indexes metadata data.
#######################
scp 用時11分鐘
#######################
同為兩臺伺服器DELL 2950 ，找個閒置網路卡埠設定任意同網段IP，如192.168.0.1和192.168.0.2，用平時用的交叉線(不一定要直連線)網線不經交換機直連兩臺機器，發現有自適應很方便。
scp傳輸dump檔案到另一臺機器，速度可達40MB/s.
#########################
IMPDP 用時16分鐘 EXCLUDE=STATISTICS,constraint,index
#########################
impdp system/xxxxxxxx directory=datapump dumpfile=newtopbox%U.dump EXCLUDE=STATISTICS,constraint,index remap_tablespace=users:topbox parallel=6
#########################
CREATE ConstraintS and INDEXS
#########################
impdp username/XXXX directory=xxx DUMPFILE=newtopbox%U.dump SQLFILE=create_index.sql INCLUDE=constraint,index remap_tablespace=users:topbox_idx
TIP:
Edit create_index.sql, Replaced the degree of parallel and nologging options
”
Bug 8604502 – IMPDP creates indexes with parallel degree 1 during import
This issue is fixed in:
12.1 (Future Release)
11.2.0.2 (Server Patch Set)
11.2.0.1 Bundle Patch 7 for Exadata Database
11.1.0.7 Patch 24 on Windows Platforms
”
References MACLEAN‘s post Speed up the index creation.
alter session set workarea_size_policy=MANUAL;
alter session set db_file_multiblock_read_count=512;
alter session set events '10351 trace name context forever, level 128';
alter session set sort_area_size=734003200;
alter session set "_sort_multiblock_read_count"=128;
alter session enable parallel ddl;
alter session set db_file_multiblock_read_count=512;
alter session set db_file_multiblock_read_count=512;
alter session set "_sort_multiblock_read_count"=128;
alter session set "_sort_multiblock_read_count"=128;
spool create_index.log
@create_index.sql
spool off
#######################
Compile invalid objects
#######################
begin
dbms_utility.COMPILE_SCHEMA('TOPBOX');
end
/
#########################
Gather Schema Statistics
#########################
begin
dbms_stats.gather_schema_stats(
ownname => 'TOPBOX',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size 1',
degree => 7
);
end
/
TIP：
METHOD_OPT was ‘FOR ALL COLUMNS SIZE 1′. This basically says to Oracle please only collect basic column statistics (min, max, distinct values etc.), do not collect histograms
on these columns. For columns that are evenly distributed and for columns that are not referenced in SQL statements, this is perfectly adequate. If a column was unevenly
distributed and detrimentally impacted the CBO’s costings of an execution plan, then one could generate histograms for those particular columns separately.
##################
others
##################
Configuration Oracle database automatically with the Linux OS startup and shutdown .
Configuration ORACLE database backup job.
參考文章：
《Import DataPump - How To Limit The Amount Of UNDO Generation of an IMPDP job ? (文件 ID 1670349.1)》
《Run Out Of Space On UNDO Tablespace Using DataPump Import/Export (文件 ID 735366.1)》
--end--
要解決這個問題，請確保在目標資料庫不存在需要匯入的表，之後讓impdp來建立表。也就是說在執行impdp DataPump job的時候不要使用TABLE_EXISTS_ACTION=APPEND或者TABLE_EXISTS_ACTION=TRUNCATE引數，除非是真正必須要使用。
如果一個大資料量需要匯入一個存在表，你不得不使用TABLE_EXISTS_ACTION=APPEND，那麼請執行以下的步驟：
1).在目標資料庫執行存在表的export，以至於在需要的時候能夠恢復表的資料和相關物件的DDL操作。
2).如果在目標資料庫存在的表上有觸發器，如果可能考慮DROP它們。
3).確保在表上沒有主鍵（索引），（如果你的目的是為了防止重複行，你必須要保留主鍵約束，這將不可避免在相關索引import job期間產生UNDO）。
4).如果在目標資料庫存在的表上包含其它的索引，如果可能考慮DROP它們（注意在執行impdp期間使用EXCLUDE=INDEX不能跳過任何存在索引的維護工作）。
5).將資料匯入目標資料庫存在的表中。
6).重建索引、主鍵和觸發器。
DataPump會選擇最佳的方法確保資料和後設資料以最有效的方法完成匯出和匯入。
預設情況在impdp期間，Impdp將維護索引，如果表和索引已經建立，不要使用direct_path，然而，如果沒有索引強制約束，可以在DataPump匯入命令列指定ACCESS_METHOD=DIRECT_PATH，DataPump能使用直接路徑（direct path)方法實施匯入。
也可以透過禁用主鍵約束來實現直接路徑載入資料（使用ALTER TABLE ... MODIFY CONSTRAINT ... DISABLE NOVALIDATE)，之後在impdp期間使用access_method=direct_path引數載入資料，最後啟用主鍵約束（使用ALTER TABLE ... MODIFY CONSTRAINT ... ENABLE VALIDATE)。
在匯入期間不建立索引消耗UNDO是非常小的，下面是一個很好的資料匯出、匯入案例（案例轉載自：）：
因一次硬體原因導致了伺服器異常停機，防止蝴蝶效應及時進行了資料遷移，一週發現了這個決定正確的。
申請了2小時的停機時間。因資料不大且表空間開始使用不規範決定用expdp資料泵方式遷移。
在新裝置
1).raid10+安裝OS
2).安裝oracle 10.2.0.1(single instance)+
3).升級oracle 10.2.0.5
4).建庫,
5).調整INSTANCE引數,調整temp tbs、redo file size、undo tbs大小
6).建立app schema、建立data\index tablespace
用時1小時左右
stop web app+掛通知
stop listener
sys@TOPBOX>select object_type,count(*) cnt from dba_objects where owner='TOPBOX' GROUP BY OBJECT_TYPE;
OBJECT_TYPE CNT
------------------- --------------------
SEQUENCE 802
PROCEDURE 25
DATABASE LINK 2
LOB 3
TRIGGER 3
MATERIALIZED VIEW 2
TABLE 87
FUNCTION 12
VIEW 41
INDEX 142
sys@TOPBOX>@schemas_space
SCHEMA Object type Space [MB]
----------------------------------- --------------------------------------------- -----------
...
TOPBOX INDEX 34102.25
LOBINDEX .19
LOBSEGMENT .44
TABLE 31132.69
*********************************** -----------
sum 65235.56
...
######################
expdp 用時14分鐘
######################
expdp system/xxxxxx schemas=topbox directory=datapump dumpfile=newtopbox%U.dump filesize=5G parallel=6 logfile=topbox_parallel_expdp.log;
[oracle@topbox datapump]$ du newtopbox0*
829016 newtopbox01.dump
5248012 newtopbox02.dump
5248012 newtopbox03.dump
5248012 newtopbox04.dump
4338316 newtopbox05.dump
3681564 newtopbox06.dump
2029228 newtopbox07.dump
673872 newtopbox08.dump
80164 newtopbox09.dump
[oracle@topbox datapump]$ du newtopbox0*|awk ‘{sum += $1};END {print sum}’
27376196
TIP:
DUMP file only contains of indexes metadata data.
#######################
scp 用時11分鐘
#######################
同為兩臺伺服器DELL 2950 ，找個閒置網路卡埠設定任意同網段IP，如192.168.0.1和192.168.0.2，用平時用的交叉線(不一定要直連線)網線不經交換機直連兩臺機器，發現有自適應很方便。
scp傳輸dump檔案到另一臺機器，速度可達40MB/s.
#########################
IMPDP 用時16分鐘 EXCLUDE=STATISTICS,constraint,index
#########################
impdp system/xxxxxxxx directory=datapump dumpfile=newtopbox%U.dump EXCLUDE=STATISTICS,constraint,index remap_tablespace=users:topbox parallel=6
#########################
CREATE ConstraintS and INDEXS
#########################
impdp username/XXXX directory=xxx DUMPFILE=newtopbox%U.dump SQLFILE=create_index.sql INCLUDE=constraint,index remap_tablespace=users:topbox_idx
TIP:
Edit create_index.sql, Replaced the degree of parallel and nologging options
”
Bug 8604502 – IMPDP creates indexes with parallel degree 1 during import
This issue is fixed in:
12.1 (Future Release)
11.2.0.2 (Server Patch Set)
11.2.0.1 Bundle Patch 7 for Exadata Database
11.1.0.7 Patch 24 on Windows Platforms
”
References MACLEAN‘s post Speed up the index creation.
alter session set workarea_size_policy=MANUAL;
alter session set db_file_multiblock_read_count=512;
alter session set events '10351 trace name context forever, level 128';
alter session set sort_area_size=734003200;
alter session set "_sort_multiblock_read_count"=128;
alter session enable parallel ddl;
alter session set db_file_multiblock_read_count=512;
alter session set db_file_multiblock_read_count=512;
alter session set "_sort_multiblock_read_count"=128;
alter session set "_sort_multiblock_read_count"=128;
spool create_index.log
@create_index.sql
spool off
#######################
Compile invalid objects
#######################
begin
dbms_utility.COMPILE_SCHEMA('TOPBOX');
end
/
#########################
Gather Schema Statistics
#########################
begin
dbms_stats.gather_schema_stats(
ownname => 'TOPBOX',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size 1',
degree => 7
);
end
/
TIP：
METHOD_OPT was ‘FOR ALL COLUMNS SIZE 1′. This basically says to Oracle please only collect basic column statistics (min, max, distinct values etc.), do not collect histograms
on these columns. For columns that are evenly distributed and for columns that are not referenced in SQL statements, this is perfectly adequate. If a column was unevenly
distributed and detrimentally impacted the CBO’s costings of an execution plan, then one could generate histograms for those particular columns separately.
##################
others
##################
Configuration Oracle database automatically with the Linux OS startup and shutdown .
Configuration ORACLE database backup job.
參考文章：
《Import DataPump - How To Limit The Amount Of UNDO Generation of an IMPDP job ? (文件 ID 1670349.1)》
《Run Out Of Space On UNDO Tablespace Using DataPump Import/Export (文件 ID 735366.1)》
--end--
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23135684/viewspace-1205538/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Linux中產生zombie的原因詳解及解決方法！Linux
- Windows、Linux下檔案操作(寫、刪除)錯誤的產生原因、及解決方法WindowsLinux
- 深入探討ORA-04031的產生原因及解決方法
- vi/vim配置篇：亂碼產生的原因及解決
- /var/spool/clientmqueue/目錄下存在大量檔案的原因及解決方法clientMQ
- cursor: pin S產生原理及解決方法
- 哪種DML操作產生undo多
- ANR原因及解決方法
- 不同insert操作產生的undo的測試
- ORA-00214 錯誤產生原因與解決方法
- ORA-02020產生的原因及解決辦法
- 關於websphere5.1產生大量heapdump檔案原因Web
- RNN神經網路產生梯度消失和梯度爆炸的原因及解決方案RNN神經網路梯度
- 深度學習中“過擬合”的產生原因和解決方法深度學習
- 生產內網ssh登陸變慢問題原因及解決辦法內網
- Redis擊穿、穿透、雪崩產生原因以及解決思路Redis穿透
- DVR常見故障原因及解決方法VR
- No input file specified 出現的原因及解決方法
- Windows變慢原因分析及解決方法(轉)Windows
- 瞭解下Mysql的間隙鎖及產生的原因MySql
- ORA-29283解決方法（impdp）?
- 網站內頁不收錄的原因及解決方法網站
- DNS故障的幾種常見原因及解決方法DNS
- ORA-00060死鎖的產生及解決
- Linux環境下段錯誤的產生原因及除錯方法小結Linux除錯
- Redo wastage產生的原因AST
- (原)詳解生產線物流規劃的原理及操作方式
- Oracle中undo 如何產生RedoOracle
- 撥號VPS中691的可能原因及解決方法？
- 技術分享 | DNS解析不生效的原因及解決方法DNS
- u盤複製速度慢的原因及解決方法
- 資料庫連線失敗的原因及解決方法資料庫
- 資料庫連線錯誤的原因及解決方法資料庫
- android ANR產生原因和解決辦法Android
- 產生top sql的原因（zt）SQL
- UNDO表空間不足解決方法
- （資料庫十）資料庫中的鎖機制以及死鎖產生的原因及解決辦法資料庫
- undo表空間不能回收的解決方法