又一次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)
-------------------------------------------------------------------------------------