Data Pump failed with ORA-04031/ORA-4030?

在10g中引入了数据泵Data Pump导入导出工具，DataPump的工作流如下图: 我们在使用Data Pump工具时经常会遇到著名的ORA-04031/ORA-04030错误，主要影响DataPump的内存组件有PGA和SGA中的共享池Shared Pool、流池Streams Pool。Expdp/Impdp对shared Pool的开销主要体现在其运行过程中需要调用一系列的包体PACKGE BODY，它们包括:

PACKAGE_NAME                             TYPE                 SHARABLE_MEM
---------------------------------------- -------------------- ------------
SYS.KUPM$MCP                             PACKAGE BODY               425448
SYS.KUPW$WORKER                          PACKAGE BODY               386000
SYS.DBMS_METADATA_INT                    PACKAGE BODY               325856
SYS.DBMS_REPCAT_UTL                      PACKAGE BODY               269064
SYS.DBMS_METADATA                        PACKAGE BODY               226624
SYS.DBMS_DATAPUMP                        PACKAGE BODY               192888
SYS.DBMS_PRVTAQIS                        PACKAGE BODY               147288
SYS.DBMS_PRVTAQIM                        PACKAGE BODY               142680
SYS.KUPF$FILE                            PACKAGE BODY               142008
SYS.DBMS_METADATA_UTIL                   PACKAGE BODY               115224
SYS.KUPD$DATA                            PACKAGE BODY               109400
SYS.DBMS_LOGREP_EXP                      PACKAGE BODY               102648
SYS.DBMS_SCHED_MAIN_EXPORT               PACKAGE BODY                86816
SYS.DBMS_SYS_SQL                         PACKAGE BODY                72280
SYS.DBMS_AW                              PACKAGE BODY                68128
SYS.DBMS_SQL                             PACKAGE BODY                68064
SYS.DBMS_DM_EXP_INTERNAL                 PACKAGE BODY                57040
SYS.DBMS_AW_EXP                          PACKAGE BODY                52256
SYS.KUPC$QUE_INT                         PACKAGE BODY                52088
SYS.DBMS_CUBE_EXP                        PACKAGE BODY                48432
SYS.KUPV$FT_INT                          PACKAGE BODY                47184
SYS.DBMS_LOGREP_UTIL                     PACKAGE BODY                45856
SYS.DBMS_CDC_EXPDP                       PACKAGE BODY                44616
SYS.DBMS_EXPORT_EXTENSION                PACKAGE BODY                38728
SYS.DBMS_CDC_UTILITY                     PACKAGE BODY                37712
SYS.KUPV$FT                              PACKAGE BODY                34536
SYS.DBMS_DM_MODEL_EXP                    PACKAGE BODY                33904
SYS.DBMS_AQ                              PACKAGE BODY                33512
SYS.DBMS_IJOB                            PACKAGE BODY                33488
SYS.DBMS_AQ_SYS_EXP_INTERNAL             PACKAGE BODY                29464
SYS.DBMS_FILE_GROUP_EXP                  PACKAGE BODY                29440
SYS.KUPD$DATA_INT                        PACKAGE BODY                29424
SYS.DBMS_RULE_EXP_RL_INTERNAL            PACKAGE BODY                29400
SYS.KUPP$PROC                            PACKAGE BODY                25368
SYS.DBMS_AQ_IMPORT_INTERNAL              PACKAGE BODY                25352
SYS.DBMS_AQADM                           PACKAGE BODY                25320
SYS.DBMS_ODCI                            PACKAGE BODY                21200
SYS.UTL_XML                              PACKAGE BODY                21200
SYS.DBMS_CDC_DPUTIL                      PACKAGE BODY                21192
SYS.DBMS_STREAMS_DATAPUMP_UTIL           PACKAGE BODY                21144
SYS.KUPF$FILE_INT                        PACKAGE BODY                17104
SYS.DBMS_SESSION                         PACKAGE BODY                17048
SYS.DBMS_AQ_SYS_EXP_ACTIONS              PACKAGE BODY                17048
SYS.DBMS_STREAMS_DATAPUMP                PACKAGE BODY                17032
SYS.KUPC$QUEUE_INT                       PACKAGE BODY                17032
SYS.DBMS_RULE_ADM                        PACKAGE BODY                17032
SYS.DBMS_LOCK                            PACKAGE BODY                17032
SYS.DBMS_RULEADM_INTERNAL                PACKAGE BODY                12952
SYS.DBMS_TRANSFORM_EXIMP_INTERNAL        PACKAGE BODY                12952
SYS.DBMS_REFRESH_EXP_SITES               PACKAGE BODY                12952
SYS.DBMS_REPCAT_RGT_EXP                  PACKAGE BODY                12936
SYS.UTL_RAW                              PACKAGE BODY                12936
SYS.DBMS_FLASHBACK                       PACKAGE BODY                12936
SYS.DBMS_TRANSFORM_EXIMP                 PACKAGE BODY                12936
SYS.DBMS_SCHED_JOB_EXPORT                PACKAGE BODY                12936
SYS.DBMS_REFRESH_EXP_LWM                 PACKAGE BODY                12936
SYS.DBMS_SCHED_EXPORT_CALLOUTS           PACKAGE BODY                 8856
SYS.DBMS_AQ_EXP_QUEUE_TABLES             PACKAGE BODY                 8856
SYS.DBMS_DEFER_IMPORT_INTERNAL           PACKAGE BODY                 8856
SYS.DBMS_INTERNAL_SAFE_SCN               PACKAGE BODY                 8856
SYS.DBMS_AQ_EXP_INDEX_TABLES             PACKAGE BODY                 8856
SYS.DBMS_AQ_EXP_HISTORY_TABLES           PACKAGE BODY                 8856
SYS.DBMS_AQ_EXP_SIGNATURE_TABLES         PACKAGE BODY                 8856
SYS.DBMS_AQ_EXP_DEQUEUELOG_TABLES        PACKAGE BODY                 8856
SYS.DBMS_AQ_EXP_SUBSCRIBER_TABLES        PACKAGE BODY                 8856
SYS.DBMS_AQ_EXP_CMT_TIME_TABLES          PACKAGE BODY                 8856
SYS.DBMS_AQ_EXP_TIMEMGR_TABLES           PACKAGE BODY                 8856
SYS.DBMS_RULE_EXP_RULES                  PACKAGE BODY                 8840
SYS.DBMS_RULE_EXP_UTLI                   PACKAGE BODY                 8840
SYS.DBMS_AQADM_INV                       PACKAGE BODY                 8840
SYS.DBMS_ZHELP_IR                        PACKAGE BODY                 8840
SYS.KUPC$QUEUE                           PACKAGE BODY                 8840
SYS.DBMS_AQ_EXP_ZECURITY                 PACKAGE BODY                 8840
SYS.KUPU$UTILITIES_INT                   PACKAGE BODY                 8840
SYS.KUPU$UTILITIES                       PACKAGE BODY                 8840
SYS.DBMS_REPCAT_EXP                      PACKAGE BODY                 8840
SYS.DBMS_CDC_EXPVDP                      PACKAGE BODY                 8840

total<<10MB

DataPump内部利用高级队列 Advanced Queue技术，所以也会用到流池Streams Pool，与之相关的Streams Pool组件包括:

POOL         NAME                            BYTES
------------ -------------------------- ----------
streams pool sob_kgqmrec                     19584
streams pool Sender info                     17616
streams pool recov_kgqbtctx                  11904
streams pool kwqbcqini:spilledovermsgs        3168
streams pool kgqbt_alloc_block                2096
streams pool recov_kgqmsub                    1608
streams pool kwqbsinfy:bqg                    1232
streams pool recov_kgqmctx                    1104
streams pool kwqbsinfy:mpr                    1088
streams pool kwqbsinfy:sta                     768
streams pool kgqmsub                           584
streams pool fixed allocation callback         448
streams pool kwqbsinfy:cco                     376
streams pool image handles                     288
streams pool kwqbsinfy:bms                     256
streams pool name_kgqmsub                      256
streams pool spilled:kwqbl                     256
streams pool deqtree_kgqmctx                   144
streams pool substree_kgqmctx                  144
streams pool kgqmdm_fl_1                       144
streams pool time manager index                144
streams pool msgtree_kgqmctx                   144

当Streams Pool分配过小同样可能引发Expdp/Impdp因ORA-04031 ("streams pool", ...)错误而意外终止，详见<EXPDP Fails With ORA-04031 ("streams pool", ...) [ID 457724.1]> 此外DataPump还可能从Large Pool中分配PX msg pool作为并行进程通信池，但是这种内存开销很小。 DataPump对PGA的消耗主要体现在koh-kghu sessi sub-heap上，已知的Bug 10404544(ORA - 4030 DURING EXPDP)、7681160(EXPDP FAILS WITH ORA-4030 WHEN SELECT FROM SYS.KU$_PROCACT_SCHEMA_VIEW)说明该子堆sub-heap(和另一个sub-heap kxs-heap-w)在10.2.0.4上使用expdp时可能引发内存泄露memory-leak。我们来具体看一下DataPump Manager DM00的PGA使用情况:

SQL> oradebug setospid 5278;
Oracle pid: 51, Unix process pid: 5278, image: oracle@rh2.oracle.com (DM00)

SQL> oradebug dump heapdump 536870917;
Statement processed.

SQL> oradebug tracefile_name;
/s01/orabase/diag/rdbms/prod/PROD1/trace/PROD1_dm00_5278.trc

SQL> select pga_alloc_mem / 1024 / 1024, pga_used_mem / 1024 / 1024, pname
  2    from v$process
  3   where addr = '00000000DCBC51F8'
  4  /

PGA_ALLOC_MEM/1024/1024 PGA_USED_MEM/1024/1024 PNAME
----------------------- ---------------------- -----
             8.06555557             7.25846481 DM00

[oracle@rh2 ~]$ egrep "HEAP DUMP heap name|Total heap size|Permanent space" 
/s01/orabase/diag/rdbms/prod/PROD1/trace/PROD1_dm00_5278.trc

HEAP DUMP heap name="session heap"  desc=0x7fae4fc167f8
Total heap size    =  4517512
Permanent space    =   192664
HEAP DUMP heap name="koh-kghu sessi"  desc=0x7fae4f9db150
Total heap size    =   362416
Permanent space    =       80
HEAP DUMP heap name="koh-kghu sessi"  desc=0x7fae4fc47fb0
Total heap size    =   336104
Permanent space    =       80
HEAP DUMP heap name="koh-kghu sessi"  desc=0x7fae4f984660
Total heap size    =   156792
Permanent space    =       80
HEAP DUMP heap name="koh-kghu sessi"  desc=0x7fae4f988c88
Total heap size    =   154912
Permanent space    =       80
HEAP DUMP heap name="Alloc environm"  desc=0x7fae4fc3e090
Total heap size    =   129512
Permanent space    =      416
HEAP DUMP heap name="Alloc statemen"  desc=0x7fae4f8c91f8
Total heap size    =    10264
Permanent space    =      696
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4f9f3650
Total heap size    =    10192
Permanent space    =     1056
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4fc4fed0
Total heap size    =     9912
Permanent space    =     1488
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4f8e4690
Total heap size    =     9080
Permanent space    =      560
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4f8aeda0
Total heap size    =     8992
Permanent space    =      568
HEAP DUMP heap name="pga heap"  desc=0xb7c8ba0
Total heap size    =  2353064
Permanent space    =   654544
HEAP DUMP heap name="KFK_IO_SUBHEAP"  desc=0x7fae4fdfaa98
Total heap size    =   730640
Permanent space    =       80
HEAP DUMP heap name="koh-kghu call "  desc=0x7fae4fb650d0
Total heap size    =   246080
Permanent space    =       80
HEAP DUMP heap name="diag pga"  desc=0x7fae500347e0
Total heap size    =    70816
Permanent space    =    13472
HEAP DUMP heap name="Alloc environm"  desc=0x7fae4fdd15c8
Total heap size    =    68064
Permanent space    =     1336
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4fb50ee0
Total heap size    =    12272
Permanent space    =     2096
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4fb66c00
Total heap size    =    11784
Permanent space    =     1856
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4fc14fc0
Total heap size    =     9816
Permanent space    =     1392
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4fc10068
Total heap size    =     9664
Permanent space    =     1240
HEAP DUMP heap name="PLS PGA hp"  desc=0x7fae4fdeeab8
Total heap size    =    46784
Permanent space    =       80
HEAP DUMP heap name="top call heap"  desc=0xb7ce3c0
Total heap size    =   458584
Permanent space    =     1920
HEAP DUMP heap name="callheap"  desc=0xb7cd578
Total heap size    =   343104
Permanent space    =    65536
HEAP DUMP heap name="callheap"  desc=0xb7cd4c0
Total heap size    =    21616
Permanent space    =      968
HEAP DUMP heap name="kti call subhe"  desc=0x7fae4fdee018
Total heap size    =    20584
Permanent space    =       80
HEAP DUMP heap name="callheap"  desc=0x7fae4f6bd108
Total heap size    =     8952
Permanent space    =      200
HEAP DUMP heap name="callheap"  desc=0x7fae4f6bd030
Total heap size    =     2072
Permanent space    =     1672
HEAP DUMP heap name="top uga heap"  desc=0xb7ce5e0
Total heap size    =  4520496
Permanent space    =       80
HEAP DUMP heap name="session heap"  desc=0x7fae4fc167f8
Total heap size    =  4517512
Permanent space    =   192664
HEAP DUMP heap name="koh-kghu sessi"  desc=0x7fae4f9db150
Total heap size    =   362416
Permanent space    =       80
HEAP DUMP heap name="koh-kghu sessi"  desc=0x7fae4fc47fb0
Total heap size    =   336104
Permanent space    =       80
HEAP DUMP heap name="koh-kghu sessi"  desc=0x7fae4f984660
Total heap size    =   156792
Permanent space    =       80
HEAP DUMP heap name="koh-kghu sessi"  desc=0x7fae4f988c88
Total heap size    =   154912
Permanent space    =       80
HEAP DUMP heap name="Alloc environm"  desc=0x7fae4fc3e090
Total heap size    =   129512
Permanent space    =      416
HEAP DUMP heap name="Alloc statemen"  desc=0x7fae4f8c91f8
Total heap size    =    10264
Permanent space    =      696
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4f9f3650
Total heap size    =    10192
Permanent space    =     1056
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4fc4fed0
Total heap size    =     9912
Permanent space    =     1488
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4f8e4690
Total heap size    =     9080
Permanent space    =      560
HEAP DUMP heap name="Alloc server h"  desc=0x7fae4f8aeda0
Total heap size    =     8992
Permanent space    =      568

一般遇到这类memory leak的问题，Oracle内部会使用一个名叫heap.awk的dump分析工具(类似于ass.awk)来找出问题子堆(problematic sub-heap),我们可以使用图形化的免费工具Membai来替代heap.awk。 总结： DataPump工具Expdp/Impdp需要从PGA和SGA的Shared Pool、Streams Pool和Large Pool分配必要的内存。为了避免Expdp/Impdp出现ORA-04031/ORA-04030错误，我们有必要在自动管理模式下设置合理的pga_aggregate_target和sga_target(抑或者memory_target)内存初始化参数，如果使用手动的SGA管理的话，那么有必要保证shared_pool_size的设置适宜，对于Streams Pool和Large Pool一般设置为150MB大小。