代码改变世界

Expdp 导数错误 ORA-00832

2013-09-03 23:40  潇湘隐者  阅读(3719)  评论(0)  编辑  收藏  举报

问题实验环境

      操作系统：Red Hat Enterprise Linux Server release 5.7 (Tikanga)

      数据库  ：Oracle Database 10g Release 10.2.0.4.0 - Production

错误再现分析

在使用数据泵导数据时，遇到下面错误：

[oracle@gsp db_expd_bak]$ expdp system/xxxx   directory=dump_dir dumpfile=dm.dmp tablespaces=dm content=all;

Export: Release 10.2.0.4.0 - Production on Thursday, 29 August, 2013 21:38:44

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Release 10.2.0.4.0 - Production

ORA-31626: job does not exist

ORA-31637: cannot create job SYS_EXPORT_TABLESPACE_01 for user SYSTEM

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95

ORA-06512: at "SYS.KUPV$FT_INT", line 600

ORA-39080: failed to create queues "KUPC$C_1_20130829213845" and "KUPC$S_1_20130829213845" for Data Pump job

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95

ORA-06512: at "SYS.KUPC$QUE_INT", line 1606

ORA-00832: no streams pool created and cannot automatically create one

clip_image002

造成ORA-00832：no streams pool created and cannot automatically create one错误的原因一般是streams_pool_size太小或没有定义streams_pool_size

A database feature which needs STREAMS SGA was being used, however, the streams_pool_size parameter was not defined and the value of db_cache_size was too small to permit an automatic transfer of SGA to the streams pool from the buffer cache.

Action: Please set the parameter streams_pool_size or set sga_target

SQL> show parameter streams_pool_size

NAME                                 TYPE        VALUE

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

streams_pool_size                    big integer 0

SQL>

由于STREAMS_POOL_SIZE一般在ASSM中是动态分配的，所以参数streams_pool_size一直为0，要查看streams_pool_size的真实大小就必须通过下面脚本来查询：

Code Snippet
  1. epps> col name for a36;
  2.  
  3. epps> col value for a10;
  4.  
  5. epps> col idfefault for a10;
  6.  
  7. epps> col ismod for a10;
  8.  
  9. epps> col isadj for a10;
  10.  
  11. epps>SELECT X.ksppinm      name       ,
  12.   2         Y.ksppstvl     value      ,
  13.   3         Y.ksppstdf     idfefault  ,
  14.   4         DECODE(bitand(Y.ksppstvf,7), 1, 'MODIFIED', 4, 'SYSTEM_MOD', 'FALSE')  ismod,
  15.   5         DECODE(bitand(Y.ksppstvf,2), 2, 'TRUE', 'FALSE')  isadj
  16.   6  FROM sys.x$ksppi  X,
  17.   7       sys.x$ksppcv Y
  18.   8  WHERE X.inst_id = userenv('Instance') AND
  19.   9        Y.inst_id = userenv('Instance') AND
  20.  10        X.indx    = Y.indx              AND
  21.  11        X.ksppinm LIKE '%_streams%'
  22.  12  ORDER BY translate(X.ksppinm, '_', '');
  23.  
  24.       NAME                                 VALUE      IDFEFAULT  ISMODISADJ
  25.  
  26. ------------------------------------ ---------- ---------- ---------- ----------
  27.  
  28.       __streams_pool_size                  0          TRUE       FALSEFALSE
  29.  
  30.       _memory_broker_shrink_streams_pool   900        TRUE       FALSEFALSE
  31.  
  32.       _disable_streams_pool_auto_tuning    FALSE      TRUE       FALSEFALSE
  33.  
  34.       _streams_pool_max_size               0          TRUE       FALSEFALSE

clip_image004

epps> show parameter sga

NAME                                 TYPE        VALUE

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

lock_sga                             boolean     TRUE

pre_page_sga                         boolean     FALSE

sga_max_size                         big integer 3424M

sga_target                           big integer 0

epps>

查看streams_pool_size的实际大小，发现其大小为0，更让我吃惊的却在后面：sga_target 为0，也就是说数据库没有启动自动共享内存管理（Automatic Shared Memory Management ASMM）。真是绕了一大圈。所以必须手动调整streams_pool_size的大小：

epps> alter system set streams_pool_size=100M scope=memory;

alter system set streams_pool_size=100M scope=memory

*

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-04033: Insufficient memory to grow pool

epps> alter system set streams_pool_size=1M scope=memory;

alter system set streams_pool_size=1M scope=memory

*

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-04033: Insufficient memory to grow pool

因为SGA采用老的分配方式，没有采用ASSM管理SGA，需要从其它内存中释放一些内存出来分配给streams_pool_size，结合分析，最后从

shared_pool_size中分配32M出来给streams_pool_size。问题解决，另外一个解决方法就是讲sga_target设为非零。让SGA动态给streams_pool_size分配内存。

clip_image006

 

参考资料：

https://forums.oracle.com/thread/1062498?start=0&tstart=0

http://blog.itpub.net/post/2333/409664

http://blog.csdn.net/xiaofan23z/article/details/6767396