1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

shared memory issues: Correct pfile parameters

Discussion in 'General' started by IgnitedMind, Aug 31, 2012.

  1. IgnitedMind

    IgnitedMind Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi Experts,

    I have recently installed Oracle Database 11.2.0. (fresh Installation so no custom query written yet)
    Single Laptop Machine of 16GB RAM.
    Assigned 13GB to VM.

    After working for few mins I started getting many erros likes

    unable to allocate 104 bytes of shared memory.
    resource pool unavaiable.
    more details on error refer forums.oracle.com/forums/thread.jspa?messageID=10550084#10550084
    dbforums.com/oracle/1684618-ora-04031-unable-allocate-104-bytes-shared-memory.html#post6566206

    after making some changes in pfile i started getting error "Failed to get connection".

    I have been struggling for the solution from last many days & did not find yet :(

    Please find content of my updated pfile(I tried updating all diff parameter but could not succeed) & tell me what I need to set for which parameter, (I have 13GB of RAM)

    orcl.__db_cache_size=822083584
    orcl.__java_pool_size=2777216
    orcl.__large_pool_size=296777216
    orcl.__oracle_base='/home/oracle/app/oracle'#ORACLE_BASE set from environment
    orcl.__pga_aggregate_target=805306368
    orcl.__sga_target=1174405120
    orcl.__shared_io_pool_size=0
    orcl.__shared_pool_size=501989888
    orcl.__streams_pool_size=0
    *.audit_file_dest='/home/oracle/app/oracle/admin/orcl/adump'
    *.audit_trail='db'
    *.compatible='11.2.0.0.0'
    *.control_files='/home/oracle/app/oracle/oradata/orcl/control01.ctl','/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl'
    *.db_block_size=8192
    *.db_domain='localdomain'
    *.db_name='orcl'
    *.db_recovery_file_dest='/home/oracle/app/oracle/flash_recovery_area'
    *.db_recovery_file_dest_size=4070572032
    *.diagnostic_dest='/home/oracle/app/oracle'
    *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
    *.memory_target=501989888
    *.open_cursors=800
    *.processes=500
    *.remote_login_passwordfile='EXCLUSIVE'
    *.undo_tablespace='UNDOTBS1'

    in above pfile i do not have parameter like SHARED_POOL_RESERVED_MIN_ALLOC,SHARED_POOL_RESERVED_SIZE etc if required how to set & what value to set please.

    when databse start up >

    SELECT free_space,avg_free_size,used_space,avg_used_size,request_failures,last_failure_size FROM v$shared_pool_reserved;

    FREE_SPACE AVG_FREE_SIZE USED_SPACE AVG_USED_SIZE REQUEST_FAILURES LAST_FAILURE_SIZE
    ---------- ------------- ---------- ------------- ---------------------------------
    2248456 29979.4133 25532000 340426.667 8 16416


    when issue occur>
    FREE_SPACE1000AVG_FREE_SIZE1000USED_SPACE1000AVG_USED_SIZE1000REQUEST_FAILURES LAST_FAILURE_SIZE
    ----------1000-------------1000----------1000-------------1000---------------- -----------------

    32158961000 31222.29131000 256286001000 248821.3591000 9 3896
     
  2. IgnitedMind

    IgnitedMind Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi Experts,

    I have reverted everything back to original.


    I am still unable to resovle error.

    I have been trying to resolve it from last many days but no way success.

    SQL> SELECT free_space,avg_free_size,used_space,avg_used_size, request_failures,last_failure_size FROM v$shared_pool_reserved;

    FREE_SPACE AVG_FREE_SIZE USED_SPACE AVG_USED_SIZE REQUEST_FAILURES
    ---------- ------------- ---------- ------------- ----------------
    LAST_FAILURE_SIZE
    -----------------
    14420848 277324 26793200 515253.846 0
    0

    SQL> SELECT NAME, SUM(BYTES) FROM V$SGASTAT WHERE POOL='LARGE POOL' GROUP BY ROLLUP (NAME);

    no rows selected.

    but after sometime I get shared mempry error

    SQL> SELECT NAME, SUM(BYTES) FROM V$SGASTAT WHERE POOL='LARGE POOL' GROUP BY ROLLUP (NAME);
    SELECT NAME, SUM(BYTES) FROM V$SGASTAT WHERE POOL='LARGE POOL' GROUP BY ROLLUP (NAME)
    *
    ERROR at line 1:
    ORA-00604: error occurred at recursive SQL level 2
    ORA-04031: unable to allocate 56 bytes of shared memory ("shared pool","select
    obj#,type#,ctime,mtim...","SQLA^e3a2d601","idndef*[]: qkexrPackName")
    ORA-04031: unable to allocate 264 bytes of shared memory ("shared
    pool","unknown object","CCUR^406f53de","kglob")

    Help Appreciated.

    cat initorcl.ora

    orcl.__db_cache_size=822083584
    orcl.__java_pool_size=16777216
    orcl.__large_pool_size=16777216
    orcl.__oracle_base='/home/oracle/app/oracle'#ORACLE_BASE set from environment
    orcl.__pga_aggregate_target=805306368
    orcl.__sga_target=1174405120
    orcl.__shared_io_pool_size=0
    orcl.__shared_pool_size=301989888
    orcl.__streams_pool_size=0
    *.audit_file_dest='/home/oracle/app/oracle/admin/orcl/adump'
    *.audit_trail='db'
    *.compatible='11.2.0.0.0'
    *.control_files='/home/oracle/app/oracle/oradata/orcl/control01.ctl','/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl'
    *.db_block_size=8192
    *.db_domain='localdomain'
    *.db_name='orcl'
    *.db_recovery_file_dest='/home/oracle/app/oracle/flash_recovery_area'
    *.db_recovery_file_dest_size=4070572032
    *.diagnostic_dest='/home/oracle/app/oracle'
    *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
    *.memory_target=0
    *.open_cursors=800
    *.processes=500
    *.remote_login_passwordfile='EXCLUSIVE'
    *.undo_tablespace='UNDOTBS1'

    ================================================
    google says to ++ shared_pool_size, when I do so I do not get shared memeory error but than my two out of only one managed server remain stable.
    I mean after sometime from two managed server, server which runned first it goes down without any error.


    =========================================