Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Aug 2012
    Posts
    10

    Unanswered: ORA-04031: unable to allocate 104 bytes of shared memory

    Dear All,

    • Let me put my current Environment Info.
    Single Laptop Machine of 16GB RAM.
    Assigned 13GB to VM.

    I have installed Oracle Database 11g. 11.2.00

    When I try to access my application many times I received below error

    • Error:
    “”Caused by: java.sql.SQLException: Could not retrieve datasource via JNDI url 'jdbc/oimJMSStoreDS' weblogic.jdbc.extensions.ConnectionDeadSQLExceptio n: weblogic.common.resourcepool.ResourceDeadException : Could not create pool connection. The DBMS driver exception was: ORA-00604: error occurred at recursive SQL level 3
    ORA-04031: unable to allocate 352 bytes of shared memory ("shared pool","select /*+ rule */ bucket_cn...","SQLA^337fc737","qertbs:qertbIAlloca te")

    at org.quartz.utils.JNDIConnectionProvider.getConnect ion(JNDIConnectionProvider.java:166)
    at org.quartz.utils.DBConnectionManager.getConnection (DBConnectionManager.java:112)
    at org.quartz.impl.jdbcjobstore.JobStoreCMT.getNonMan agedTXConnection(JobStoreCMT.java:164)
    ... 3 more””
    Some time it says oimJMSStoreDS,SOADATAStore.

    • Even when above error occur I have 7GB free JVM so JVM is not an Issue.

    •I have updated Max Capacity of oimOperationsDB =100,oimOperdationDB, oimJMSStoreDS=80,SOADATAStore =80. But still I have error
    • ERROR::
    • ““Aug 30, 2012 3:24:10 PM EST> <Warning> <oracle.integration.platform.blocks.event.saq> <SOA-31013> <Error handling message (rolling back).
    java.sql.SQLException: ORA-04031: unable to allocate 352 bytes of shared memory ("shared pool","select /*+ rule */ bucket_cn...","SQLA^337fc737","qertbs:qertbIAlloca te")
    ORA-06512: at "SYS.DBMS_AQ", line 335
    ORA-06512: at "DEV_SOAINFRA.EDN_DEQUEUE_OAOO_DELIVERY", line 14”
    • “<Aug 30, 2012 3:28:58 PM EST> <Error> <JDBC> <BEA-001112> <Test "SELECT 1 FROM DUAL" set up for pool "SOADataSource" failed with exception: "java.sql.SQLException: ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","SELECT 1 FROM DUAL","SQLA","tm”
    • ug 30, 2012 3:46:58 PM EST> <Error> <JDBC> <BEA-001112> <Test "SELECT 1 FROM DUAL" set up for pool "oimOperationsDB" failed with exception: "java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 2
    ORA-04031: unable to allocate 104 bytes of shared memory ("shared pool","select /*+ rule */ bucket, e...","SQLA^bbcee4f7","opiprwd : opitca")
    ".>
    • MDS-01376: Unable to get database connection from data source "mds" configured with JNDI name "jdbc/mds/MDS_REPOS".
    weblogic.common.resourcepool.ResourceDisabledExcep tion: Pool mds-oim is Suspended, cannot allocate resources to applications..

    at oracle.mds.config.PConfig.loadFromBean(PConfig.jav a:959)
    at oracle.mds.config.PConfig.<init>(PConfig.java:758)
    at oracle.mds.config.MDSConfig.loadFromBean(MDSConfig .java:787)
    at oracle.mds.config.MDSConfig.loadFromElement(MDSCon fig.java:848)
    at oracle.mds.config.MDSConfig.<init>(MDSConfig.java: 491)
    Truncated. see log file for complete stacktrace
    Caused By: oracle.mds.exception.MDSExceptionList: MDS-01329: unable to load element "persistence-config"
    MDS-01370: MetadataStore configuration for metadata-store-usage "mdsRepos" is invalid.
    MDS-01376: Unable to get database connection from data source "mds" configured with JNDI name "jdbc/mds/MDS_REPOS".
    weblogic.common.resourcepool.ResourceDisabledExcep tion: Pool mds-oim is Suspended, cannot allocate resources to applications..
    MDS-01370: MetadataStore configuration for metadata-store-usage "mdsReposOIM" is invalid.
    MDS-01376: Unable to get database connection from data source "mds" configured with JNDI name "jdbc/mds/MDS_REPOS".
    weblogic.common.resourcepool.ResourceDisabledExcep tion: Pool mds-oim is Suspended, cannot allocate resources to applications..

    •I have modified Shared_pool_size,large_pool_size. Content of my mofified Pfile is as below.
    o orcl.__db_cache_size=822083584
    o orcl.__java_pool_size=2777216
    o orcl.__large_pool_size=56777216
    o orcl.__oracle_base='/home/oracle/app/oracle'#ORACLE_BASE set from environment
    o orcl.__pga_aggregate_target=805306368
    o orcl.__sga_target=1174405120
    o orcl.__shared_io_pool_size=0
    o orcl.__shared_pool_size=601989888
    o orcl.__streams_pool_size=0

    What’s value of parameter I need to set for datastore, pfile ? (I have 13GB of memory).

    Help Appreciated.

  2. #2
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    Errors regarding shared pool are mostly caused by not using bind variables. Enable SQL trace for your application and investigate all SQL executed by the app.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    bcm@bcm-laptop:~$ oerr ora 4031
    04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"
    // *Cause:  More shared memory is needed than was allocated in the shared
    //          pool.
    // *Action: If the shared pool is out of memory, either use the
    //          DBMS_SHARED_POOL package to pin large packages,
    //          reduce your use of shared memory, or increase the amount of
    //          available shared memory by increasing the value of the
    //          initialization parameters SHARED_POOL_RESERVED_SIZE and 
    //          SHARED_POOL_SIZE.
    //          If the large pool is out of memory, increase the initialization
    //          parameter LARGE_POOL_SIZE.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Aug 2012
    Posts
    10
    Quote Originally Posted by dayneo View Post
    Errors regarding shared pool are mostly caused by not using bind variables. Enable SQL trace for your application and investigate all SQL executed by the app.
    It is the fresh Installation so no custamized query has been written, hence i think there should not be bind variable Issue, thoughts ?

  5. #5
    Join Date
    Aug 2012
    Posts
    10
    Quote Originally Posted by anacedent View Post
    Code:
    bcm@bcm-laptop:~$ oerr ora 4031
    04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"
    // *Cause:  More shared memory is needed than was allocated in the shared
    //          pool.
    // *Action: If the shared pool is out of memory, either use the
    //          DBMS_SHARED_POOL package to pin large packages,
    //          reduce your use of shared memory, or increase the amount of
    //          available shared memory by increasing the value of the
    //          initialization parameters SHARED_POOL_RESERVED_SIZE and 
    //          SHARED_POOL_SIZE.
    //          If the large pool is out of memory, increase the initialization
    //          parameter LARGE_POOL_SIZE.
    Hi,

    I can not see SHARED_POOL_RESERVED_SIZE in my pfile.
    what it does & how much I can set it now ? (other parameter like large_pool size etc all are 5GB) ?

    Help Appreciated

  6. #6
    Join Date
    Aug 2012
    Posts
    10
    Hi,

    I have updated my pfile as below but than after some time I am failed to get connection.
    " Failed to get connection" error appear.

    orcl.__db_cache_size=822083584
    orcl.__java_pool_size=2777216
    orcl.__large_pool_size=496777216
    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'
    ~



    when error occure, result of below query

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

    FREE_SPACE1000AVG_FREE_SIZE1000USED_SPACE1000AVG_U SED_SIZE1000REQUEST_FAILURES
    ----------1000-------------1000----------1000-------------1000----------------
    LAST_FAILURE_SIZE
    -----------------
    32158961000 31222.29131000 256286001000 248821.3591000 9
    3896

    Help Appreciated.

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >" Failed to get connection" error appear.
    GOOGLE indicates above is NOT an Oracle error but from other software.

    It appears you have multiple problems.
    When was last time Oracle worked without error?
    What changed since then?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Aug 2012
    Posts
    10
    Quote Originally Posted by anacedent View Post
    >" Failed to get connection" error appear.
    GOOGLE indicates above is NOT an Oracle error but from other software.

    It appears you have multiple problems.
    When was last time Oracle worked without error?
    What changed since then?
    Anacedent,

    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.


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

  9. #9
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    @IgnitedMind
    First things first.... stop messing with pfiles. 11g actually uses spfile by default. The spfile is a binary file that you can't edit through a text editor. So I am not quite sure how you are "editing" your pfile. All system parameter changes should be done through sqlplus as sysdba (in my opinion).

    Secondly, you should be using automatic memory management. That would be the databases default. Can't really make out if you are using it from what you have sent.

    Thirdly, you say
    When I try to access my application many times I received below error
    . Assuming you used the default setup of the database, then shared pool errors will be as a result of external applications executing SQL against it. I see shared pool errors every day, and every single time, it is as a result of custom written applications that do not execute their SQL efficiently. That is most often reguarding bind variables.

  10. #10
    Join Date
    Aug 2012
    Posts
    10
    Hi Dayneo,

    Thanks for the update.

    Reply to your Queries...

    First, While starting I pass my pfile as its working better than my default spfile. (better means using my pfile I do not get issues which I get using my spfile(only one managed server run at a time: to avoide this i m using pfile)

    startup pfile='....';

    Secondly, Yes I am using default installation & i suppose AMM support by default, correcct.

    Thirdly, I have done fresh installation so do not have any custome code/query written yet.

    Help Appreciated.

    content of my spfile for your reference

    orcl.__java_pool_size=67108864
    orcl.__large_pool_size=67108864
    orcl.__oracle_base='/home/oracle/app/oracle'#ORACLE_BASE set from environment
    orcl.__pga_aggregate_target=872415232
    orcl.__sga_target=1275068416
    orcl.__shared_io_pool_size=0
    orcl.__shared_pool_size=402653184
    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^AC^@^@C"^@^@^C^@^@^@^@^@^@^@^@^@^A^DtL^@^@/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_max_target=6442450944
    *.memory_target=2147483648
    *.open_cursors=800
    *.pga_aggregate_target=0
    *.processes=500

  11. #11
    Join Date
    Aug 2012
    Posts
    10
    I have also runned diagnosrtic tool & it has given below Info

    Evidence Details:
    Unexpectedly High Allocation in all Subpools of Shared Pool
    ** In your trace file, Top 5 Allocation in all subpools:
    AllocationName........Size
    free memory........12777912 bytes
    private strands........11440128 bytes
    event statistics per sess........9906304 bytes
    ksunfy : SSO free list........9177536 bytes
    CCUR........8752200 bytes

  12. #12
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    Hmmmm... Ok, you are specifying the pfile at startup.
    You said something about "one managed server". What exactly do you mean by "managed server"?
    Please post output from
    Code:
    show parameters sga
    and
    Code:
    SHOW PARAMETERS pool
    Then run this in SQL*PLUS and post result:
    Code:
    compute sum of bytes on pool
    break on pool skip 1
    select pool, name, bytes from v$sgastat order by pool, name
    /

  13. #13
    Join Date
    Aug 2012
    Posts
    10
    Dayno Thanks a lot for providing your valuable time.

    Actaully I have observed running DB with pfile is faster than with spfile.

    find below info as request. PFA

    SQL> startup pfile='/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora';
    ORACLE instance started.

    Total System Global Area 217157632 bytes
    Fixed Size 2211928 bytes
    Variable Size 163581864 bytes
    Database Buffers 46137344 bytes
    Redo Buffers 5226496 bytes
    Database mounted.
    Database opened.
    SQL> show parameters sga

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    lock_sga boolean FALSE
    pre_page_sga boolean FALSE
    sga_max_size big integer 208M
    sga_target big integer 0
    SQL> SHOW PARAMETERS pool;

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    buffer_pool_keep string
    buffer_pool_recycle string
    global_context_pool_size string
    java_pool_size big integer 24M
    large_pool_size big integer 0
    olap_page_pool_size big integer 0
    shared_pool_reserved_size big integer 6710886
    shared_pool_size big integer 136M
    streams_pool_size big integer 0

    ===========================
    I can also create new spfile/pfile according to your suggestion if required ?
    Attached Files Attached Files

  14. #14
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    the pfileQuery.txt doesn't contain the full result of the query, so I can't see what your instance is actually using.
    Code:
    compute sum of bytes on pool
    break on pool skip 1
    select pool, name, bytes from v$sgastat order by pool, name
    /
    sga_max_size big integer 208M
    sga_target big integer 0
    That is just totally wrong! Should look like this instead:
    Code:
    dayneo@RMSD> show parameter sga
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- -----
    lock_sga                             boolean     FALSE
    pre_page_sga                         boolean     FALSE
    sga_max_size                         big integer 512M
    sga_target                           big integer 512M
    I would say your problem is that pfile you are using. I would say you should revert back to your spfile and figure out why it is "slow".

  15. #15
    Join Date
    Aug 2012
    Posts
    10
    Hi,

    Thanks to your for your help & support.
    I did not find any luck from last 10 days

    I have updated the memory_target & other parameter.But after ++ memory it be too slow, not even able to login

    Updated pfile is as below.

    *.memory_target=6G
    *.memory_max_target=6G
    *.open_cursors=800
    *.shared_pool_size=375809638
    *.shared_servers=4
    *.sga_target=5G

    Again pasting result as you requested.
    SQL> startup pfile='/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora';
    ORACLE instance started.

    Total System Global Area 6413680640 bytes
    Fixed Size 2213776 bytes
    Variable Size 1677723760 bytes
    Database Buffers 4697620480 bytes
    Redo Buffers 36122624 bytes
    Database mounted.
    Database opened.
    SQL> SHOW PARAMETERS pool;

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    buffer_pool_keep string
    buffer_pool_recycle string
    global_context_pool_size string
    java_pool_size big integer 0
    large_pool_size big integer 0
    olap_page_pool_size big integer 0
    shared_pool_reserved_size big integer 20132659
    shared_pool_size big integer 384M
    streams_pool_size big integer 0

    SQL> show parameter sga



    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    lock_sga boolean FALSE
    pre_page_sga boolean FALSE
    sga_max_size big integer 6G
    sga_target big integer 5G
    ===============

    Do you want me to create new spfile (i have already tried though), please share steps if possible to run.

    Help Appreciated.,
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

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