Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186

    Question Unanswered: Data Pump Export issue - no streams pool created and cannot automatically create one

    SOLVED, see my reply

    I am trying to use data pump on a 10.2.0.1 database that has vlm enabled and getting the following error :

    Export: Release 10.2.0.1.0 - Production on Tuesday, 20 April, 2010 10:52:08

    Connected to: Oracle Database 10g Release 10.2.0.1.0 - Production
    ORA-31626: job does not exist
    ORA-31637: cannot create job SYS_EXPORT_TABLE_01 for user E_AGENT_SITE
    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_20100420105208" and "KUPC$S_1_20100420105208" for Data Pump job
    ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
    ORA-06512: at "SYS.KUPC$QUE_INT", line 1555
    ORA-00832: no streams pool created and cannot automatically create one


    This is my script (that I currently use on other non vlm databases successfully):
    expdp e_agent_site/<password>@orcl parfile=d:\DailySitePump.par

    this is my parameter file :
    DUMPFILE=site_pump&#37;U.dmp
    PARALLEL=1
    LOGFILE=site_pump.log
    STATUS=300
    DIRECTORY=DATA_DUMP
    QUERY=wwv_document$:"where last_updated > sysdate-18"
    EXCLUDE=CONSTRAINT
    EXCLUDE=INDEX
    EXCLUDE=GRANT
    TABLES=wwv_document$
    FILESIZE=2000M

    My oracle directory is created and the user has rights

    googling the issue says that the shared pool is too small or streams_pool_size needs setting. shared_pool_size = 1200M and when I query v$parameter it shows that streams_pool_size = 0

    I've tried alter system set streams_pool_size=1M; but I just get :
    ORA-02097: parameter cannot be modified because specified value is invalid
    ORA-04033: Insufficient memory to grow pool

    The server is a windows enterprise box with 16GB ram and VLM enabled, pfile memory parameters listed below:

    # resource
    processes = 1250
    job_queue_processes = 10
    open_cursors = 1000 # no overhead if set too high

    # sga
    shared_pool_size = 1200M
    large_pool_size = 150M
    java_pool_size = 50M

    # pga
    pga_aggregate_target = 850M # custom


    # System Managed Undo and Rollback Segments
    undo_management=AUTO
    undo_tablespace=UNDOTBS1

    # vlm support
    USE_INDIRECT_DATA_BUFFERS = TRUE
    DB_BLOCK_BUFFERS = 1500000


    Any ideas why I cannot run data pump? I am assuming that I just need to set streams_pool_size but I don't understand why I cannot increase the size of it on this db. It is set to 0 on other databases that work fine and I can set it which is why I am possibly linking the issue to vlm

    thanks
    Robert
    Last edited by robert xr4x4; 04-20-10 at 07:48. Reason: problem solved
    There are 10 types of people in the world, those that know Binary and those that don't.

  2. #2
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186

    Solved

    Managed so solve the issue by lower another memory parameter, runs fine now

    Code:
    alter system set java_pool_size = 35M;
     
    alter system set streams_pool_size = 15M;
    There are 10 types of people in the world, those that know Binary and those that don't.

  3. #3
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    Well, you ARE quite at the limit of your server.

    Do you really need 12G db_block_buffers (assuming your db_block_size is 8K) ?

    What's your db_writer_processes value ? (should be close to the number of processors, otherwise checkpoints will take pretty long ...)
    Last edited by magicwand; 04-20-10 at 18:24.
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    why not just use db_cache_size and unset db_block_buffers?
    this is 10g not 8i
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    Quote Originally Posted by magicwand View Post
    Well, you ARE quite at the limit of your server.

    Do you really need 12G db_block_buffers (assuming your db_block_size is 8K) ?

    What's your db_writer_processes value ? (should be close to the number of processors, otherwise checkpoints will take pretty long ...)
    I am maxed out on sga and pga and I want to get as much of the database into the rest of the available ram as possible, not much point having unused ram on the server. (yes, block size is 8k)

    db_writer_processes is 1. The box has a single quad core cpu
    There are 10 types of people in the world, those that know Binary and those that don't.

  6. #6
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    Quote Originally Posted by The_Duck View Post
    why not just use db_cache_size and unset db_block_buffers?
    this is 10g not 8i
    All the documentation I've read on VLM says you have to set that, you can only use the older paraemters with VLM
    There are 10 types of people in the world, those that know Binary and those that don't.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •