oracle IMPDP 导入报错“ORA-31623”

111

参考的网文：https://blog.csdn.net/aaron8219/article/details/48133143

[oracle@sisdb:~]tail -100f /oracle/diag/rdbms/siststdb/siststdb/trace/alert_siststdb.log

Mon Aug 31 13:01:37 2015
Errors in file /oracle/diag/rdbms/siststdb/siststdb/trace/siststdb_ora_43843686.trc (incident=36272):
ORA-04031: unable to allocate 56 bytes of shared memory ("streams pool","unknown object","streams pool","fixed allocation callback")
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Mon Aug 31 13:01:40 2015
Sweep [inc][36272]: completed
Mon Aug 31 13:01:51 2015
Errors in file /oracle/diag/rdbms/siststdb/siststdb/trace/siststdb_ora_43843690.trc (incident=48001):
ORA-04031: unable to allocate 56 bytes of shared memory ("streams pool","unknown object","streams pool","fixed allocation callback")
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Mon Aug 31 13:02:20 2015
Errors in file /oracle/diag/rdbms/siststdb/siststdb/trace/siststdb_ora_327938.trc (incident=48002):
ORA-04031: unable to allocate 56 bytes of shared memory ("streams pool","unknown object","streams pool","fixed allocation callback")
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Mon Aug 31 13:02:40 2015
Sweep [inc][48002]: completed
Sweep [inc][48001]: completed
Mon Aug 31 13:10:00 2015
Errors in file /oracle/diag/rdbms/siststdb/siststdb/trace/siststdb_ora_42205274.trc (incident=36281):
ORA-04031: unable to allocate 56 bytes of shared memory ("streams pool","unknown object","streams pool","fixed allocation callback")
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Mon Aug 31 13:10:01 2015
Sweep [inc][36281]: completed



在alert日志中发现，在执行IMPDP导入的时候，数据库报了ORA-04031的错误，我们都知道这是和SGA内存相关的一个报错，而且后面很清楚的提示了是由于SGA中的一个组件“stream pool”出现了内存不足，使用IMPDP竟然还会用到流池，这个是我没有想到的，一直以为只有用sream和AQ技术时才会使用到流池，那么再来看一下流池的大小：



SQL> set line 200 pages 200
SQL> select * from v$sgainfo;

NAME BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size 2263488 No
Redo Buffers 9707520 No
Buffer Cache Size 134217728 Yes
Shared Pool Size 436207616 Yes
Large Pool Size 0 Yes
Java Pool Size 16777216 Yes
Streams Pool Size 0 Yes
Shared IO Pool Size 0 Yes
Granule Size 16777216 No
Maximum SGA Size 8551575552 No
Startup overhead in Shared Pool 363476736 No
Free SGA Memory Available 7952400384



由于这个库安装的时候只设置了MEMORY_MAX_TARGET=9G，而未设置SGA_TARGET（即，SGA_TARGET=0，不采用10g的内存自动管理）和MEMORY_TARGET的值（即，也未采用了11g的ASMM方式），因此流池默认大小也是0



SQL> show parameter memory

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 9G
memory_target big integer 0
shared_memory_address integer 0

SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 8G
sga_target big integer 0

SQL> select name,issys_modifiable from v$parameter where name like '%pool%';

NAME ISSYS_MOD
-------------------------------------------------------------------------------- ---------
shared_pool_size IMMEDIATE
large_pool_size IMMEDIATE
java_pool_size IMMEDIATE
streams_pool_size IMMEDIATE
shared_pool_reserved_size FALSE
buffer_pool_keep FALSE
buffer_pool_recycle FALSE
global_context_pool_size FALSE
olap_page_pool_size DEFERRED

SQL>



可以看到，streams_pool_size的issys_modifiable的值是“IMMEDIATE”，说明是动态参数（无须重启数据库生效），那么就手动分配给它一个值，我给了100M



SQL> alter system set streams_pool_size=100m scope=both;

System altered.

SQL> select * from v$sgainfo;

NAME BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size 2263488 No
Redo Buffers 9707520 No
Buffer Cache Size 134217728 Yes
Shared Pool Size 436207616 Yes
Large Pool Size 0 Yes
Java Pool Size 16777216 Yes
Streams Pool Size 117440512 Yes
Shared IO Pool Size 0 Yes
Granule Size 16777216 No
Maximum SGA Size 8551575552 No
Startup overhead in Shared Pool 363476736 No
Free SGA Memory Available 7834959872

SQL>



现在再来试一下刚才的导入命令，发现已经可以顺利执行下去了