又一次stream_pool不足导致的expdp失败的解决

客户数据库日常的定时脚本在逻辑导出时报错：
-bash-3.00$ expdp \"sys/sys as sysdba\" directory=DUMP_DIR dumpfile=full20140710.dmp schemas= 'PBOSS','UA','RES','MARKET','BASE','PRODUCT','SEC','SO1','SR','CHANNEL','CP','JF','XG','ZG','ZC','REP','ESB_WX','IPCC','WXPORTAL' logfile=full.log COMPRESSION=ALL parallel=6
Export: Release 11.2.0.3.0 - Production on Thu Jul 10 14:03:26 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

UDE-31623: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3326
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4551
ORA-06512: at line 1

单单从导出的报错来看，很难确认为什么导出会报错，不过在导出报错的同时，后台也给出了相关的报错信息：
-bash-3.00$ tail -f alert*
Errors in file /oracle/diag/rdbms/wxboss/wxboss/trace/wxboss_ora_16385.trc  (incident=42273):
ORA-04031: unable to allocate 56 bytes of shared memory ("streams pool","unknown object","streams pool","fixed allocation callback")
Incident details in: /oracle/diag/rdbms/wxboss/wxboss/incident/incdir_42273/wxboss_ora_16385_i42273.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
ORA-04031: unable to allocate 56 bytes of shared memory，一目了然的报错，stream pool 由于无法分配空间导致expdp导出报错
我们知道从expdp数据泵开始oracle通过Advanced Queue高级队列来控制其job作业的启动、停止和重启了，而streams pool的内存不能正常获取使得oracle的高级队列无法正常工作，所以导致expdp报错
具体关于expdp Advanced Queue可以参考博客另一文章：
一次expdp导出hang分析
我们详细去查看告警日志中的dump文件：
Wait State:
  fixed_waits=0 flags=0x21 boundary=0x0/-1
Session Wait History:
    elapsed time of 0.001481 sec since last wait
 0: waited for 'SGA: allocation forcing component growth'
    =0x0, =0x0, =0x0
    wait_id=23 seq_num=224 snap_id=101
    wait times: snap=0.000000 sec, exc=5.008638 sec, total=5.019230 sec
    wait times: max=infinite
    wait counts: calls=100 os=100
    occurred after 0.000000 sec of elapsed time
  1: waited for 'SGA: allocation forcing component growth'
    =0x0, =0x0, =0x0
    wait_id=123 seq_num=223 snap_id=1
    wait times: snap=0.000046 sec, exc=0.000046 sec, total=0.000046 sec
    wait times: max=infinite
    wait counts: calls=1 os=1
    occurred after 0.000000 sec of elapsed time
 2: waited for 'SGA: allocation forcing component growth'
    =0x0, =0x0, =0x0
    wait_id=23 seq_num=222 snap_id=100
    wait times: snap=0.050078 sec, exc=5.008638 sec, total=5.019184 sec
    wait times: max=infinite
    wait counts: calls=100 os=100


从以上的dump文件中我们看到，oracle在不停的等待SGA的resize过程
我们同时检查了v$sga_resize_ops情况：
SQL>select COMPONENT,OPER_TYPE,OPER_MODE,INITIAL_SIZE,FINAL_SIZE,STATUS,START_TIME from v$sga_resize_ops
COMPONENT             OPER_TYPE  OPER_MODE INITIAL_SIZE FINAL_SIZE STATUS    START_TIE
--------------------  ---------- --------- ------------ ---------- --------- ---------
DEFAULT buffer cache   SHRINK     IMMEDIATE   2.6172E+10 2.6038E+10 ERROR     01-JUN-14
DEFAULT buffer cache  SHRINK     IMMEDIATE   2.6172E+10 2.6038E+10 ERROR     01-JUN-14
DEFAULT buffer cache  SHRINK     IMMEDIATE   2.6172E+10 2.6038E+10 ERROR     01-JUN-14
DEFAULT buffer cache  SHRINK     IMMEDIATE   2.6172E+10 2.6038E+10 ERROR     01-JUN-14
DEFAULT buffer cache  SHRINK     IMMEDIATE   2.6172E+10 2.6038E+10 ERROR     01-JUN-14
DEFAULT buffer cache  SHRINK     IMMEDIATE   2.6172E+10 2.6038E+10 ERROR     01-JUN-14
DEFAULT buffer cache  SHRINK     IMMEDIATE   2.6172E+10 2.6038E+10 ERROR     01-JUN-14
DEFAULT buffer cache  SHRINK     IMMEDIATE   2.6172E+10 2.6038E+10 ERROR     01-JUN-14
DEFAULT buffer cache  SHRINK     IMMEDIATE   2.6172E+10 2.6038E+10 ERROR     01-JUN-14
DEFAULT buffer cache  SHRINK     IMMEDIATE   2.6172E+10 2.6038E+10 ERROR     01-JUN-14
DEFAULT buffer cache  SHRINK     IMMEDIATE   2.6172E+10 2.6038E+10 ERROR     01-JUN-14
大量的buffer cache resize 失败的情况，导致stream pool无法正常GROW
原本来说这是一次很简单的处理过程，但是在调整过程中却发现无法正常的分配内存给stream pool：
SQL> alter system set streams_pool_size=1G scope=both;
alter system set streams_pool_size=1G scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool


SQL> alter system set streams_pool_size=1024M scope=both;
alter system set streams_pool_size=1204M scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool
全都报错，无效的数值，后续测试发现该参数通过在线调整不行，但是写入spfile中重启数据库却可以生效，不知道算不算是命中BUG
由于客户的数据库不能随时停机，那么问题来了，我们需要怎么取解决该问题，考虑到该问题，我们在中午业务空闲时段手工resize了db_cache_size的大小通过alter system set db_cache_size=28G 
将db_cache_size的最小值从之前的30Gresize到了28G
此时我们发现 expdp可以正常工作，检查v$sga_resize_ops：
SQL>select COMPONENT,OPER_TYPE,OPER_MODE,INITIAL_SIZE,FINAL_SIZE,STATUS,START_TIME from v$sga_resize_ops                                 
COMPONENT             OPER_TYPE  OPER_MODE INITIAL_SIZE FINAL_SIZE STATUS    START_TIE
--------------------  ---------- --------- ------------ ---------- --------- ---------
DEFAULT buffer cache  SHRINK     IMMEDIATE   2.6172E+10 2.6038E+10 ERROR     01-JUN-14
DEFAULT buffer cache  SHRINK     IMMEDIATE   2.6172E+10 2.6038E+10 ERROR     01-JUN-14
DEFAULT buffer cache  SHRINK     IMMEDIATE   2.6172E+10 2.6038E+10 ERROR     01-JUN-14
DEFAULT buffer cache  SHRINK     IMMEDIATE   2.6172E+10 2.6038E+10 ERROR     01-JUN-14
streams pool          GROW       IMMEDIATE    134217728  268435456 COMPLETE  10-JUL-14
DEFAULT buffer cache  SHRINK     IMMEDIATE   2.6038E+10 2.5904E+10 COMPLETE  10-JUL-14
DEFAULT buffer cache  SHRINK     IMMEDIATE   2.5904E+10 2.5770E+10 COMPLETE  10-JUL-14
shared pool           GROW       IMMEDIATE   1.3824E+10 1.3959E+10 COMPLETE  10-JUL-14
                     
8 rows selected. 
发现stream pool可以正常获取内存


 ------------------------------------------------------------------------------------

原博客地址：http://blog.itpub.net/23732248/
原作者：应以峰 (frank-ying)
-------------------------------------------------------------------------------------

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23732248/viewspace-1455554/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23732248/viewspace-1455554/