ORA-04031: unable to allocate 4200 bytes of shared memory
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: ORA-04031: unable to allocate 4200 bytes of shared memory

  1. #1
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818

    ORA-04031: unable to allocate 4200 bytes of shared memory

    I've been away for 2 weeks vacation and have returned to a situation where a server blue-screened and was rebuilt by IT and a consultant (and guess what - they have no docs or record of how they did it!). Anyway, they restored one of our DBs which keeps crashing now.

    The application that uses it 'records' errors as they occur, but these do not appear in the alert log or any trace files so I'm completely stumped as to 'where' they're coming from.

    The shared memory issue? We never had problems before with the Shared Pool size (ie no errors indicating a memory error). I'm loathe to just upping the SHARED_POOL_SIZE without knowing what the real cause of this problem is. Any ideas?

    ORA-04031: unable to allocate 4200 bytes of shared memory ('shared pool','unknown object','sga heap','state objects')

    ORA-00604: error occurred at recursive SQL level %s

    ORA-00604: error occurred at recursive SQL level %s

    ORA-00604: error occurred at recursive SQL level %s

    ORA-00604: error occurred at recursive SQL level %s

    ORA-00604: error occurred at recursive SQL level 1

    ORA-04031: unable to allocate 4176 bytes of shared memory ('shared pool','STREAM_PERMISSIONS_VW','sga heap','library cache')

    ORA-04031: unable to allocate 4200 bytes of shared memory ('shared pool','SELECT * FROM LABEL_VERSION_...','sga heap','library cache')

    ORA-04031: unable to allocate 4200 bytes of shared memory ('shared pool','unknown object','sga heap','state objects')

    ORA-04031: unable to allocate 4200 bytes of shared memory ('shared pool','unknown object','sga heap','state objects')

    ORA-00604: error occurred at recursive SQL level %s

    ORA-00604: error occurred at recursive SQL level 2

    ORA-04031: unable to allocate 4168 bytes of shared memory ('shared pool','SYN$','sga heap','state objects')

    ORA-00604: error occurred at recursive SQL level %s

    ORA-00604: error occurred at recursive SQL level %s

    ORA-00604: error occurred at recursive SQL level %s

    ORA-00604: error occurred at recursive SQL level %s

    ORA-00604: error occurred at recursive SQL level %s

    ORA-04031: unable to allocate 4176 bytes of shared memory ('shared pool','SELECT * FROM LABEL_DETAILS_...','sga heap','library cache')

    ORA-04031: unable to allocate 4200 bytes of shared memory ('shared pool','SELECT * FROM LABEL_DETAILS_...','sga heap','library cache')

    ORA-00604: error occurred at recursive SQL level %s

    ORA-00604: error occurred at recursive SQL level %s

    ORA-00604: error occurred at recursive SQL level %s

    ORA-04031: unable to allocate %s bytes of shared memory ('%s','%s','%s','%s')

    ORA-00604: error occurred at recursive SQL level %s

    ORA-00604: error occurred at recursive SQL level 1

    ORA-04031: unable to allocate 4176 bytes of shared memory ('shared pool','select type#,blocks,extents,...','sga heap','library cache')

    ORA-00604: error occurred at recursive SQL level 1

    ORA-04031: unable to allocate 4176 bytes of shared memory ('shared pool','DOCUMENT','sga heap','library cache')

    ORA-04031: unable to allocate 4096 bytes of shared memory ('shared pool','STANDARD','PL/SQL MPCODE','BAMIMA: Bam Buffer')

    ORA-06508: PL/SQL: could not find program unit being called

    ORA-06512: at 'ALPLADMIN.ERROR_LOG_SEQ_TRG', line 6

    ORA-04088: error during execution of trigger 'ALPLADMIN.ERROR_LOG_SEQ_TRG'

    ORA-00604: error occurred at recursive SQL level %s

    ORA-04031: unable to allocate 4200 bytes of shared memory ('shared pool','unknown object','sga heap','state objects')

    ORA-00604: error occurred at recursive SQL level 2

    ORA-04031: unable to allocate 4168 bytes of shared memory ('shared pool','VIEW$','sga heap','state objects')

    ORA-04031: unable to allocate 4176 bytes of shared memory ('shared pool','SELECT * FROM LABEL_DETAILS_...','sga heap','library cache')

    ORA-04031: unable to allocate 4176 bytes of shared memory ('shared pool','SELECT * FROM LABEL_DETAILS_...','sga heap','library cache')

    ORA-04031: unable to allocate 4200 bytes of shared memory ('shared pool','SELECT * FROM LABEL_DETAILS_...','sga heap','library cache')

    ORA-04031: unable to allocate 4200 bytes of shared memory ('shared pool','SELECT * FROM STREAM_PERMISS...','sga heap','library cache')

    ORA-00604: error occurred at recursive SQL level %s

    ORA-00604: error occurred at recursive SQL level %s

    ORA-00604: error occurred at recursive SQL level %s

    ORA-00604: error occurred at recursive SQL level %s

    ORA-00604: error occurred at recursive SQL level 1

    ORA-04031: unable to allocate 4176 bytes of shared memory ('shared pool','LABEL_DETAILS_VW','sga heap','library cache')

    ORA-04031: unable to allocate 4200 bytes of shared memory ('shared pool','unknown object','sga heap','state objects')

    ORA-00604: error occurred at recursive SQL level %s

    ORA-00604: error occurred at recursive SQL level 2

    ORA-04031: unable to allocate 4168 bytes of shared memory ('shared pool','OBJAUTH$','sga heap','state objects')

    ORA-04031: unable to allocate 4176 bytes of shared memory ('shared pool','SELECT * FROM LABEL_DETAILS_...','sga heap','library cache')

    ORA-04031: unable to allocate 4176 bytes of shared memory ('shared pool','SELECT * FROM LABEL_DETAILS_...','sga heap','library cache')

    ORA-04031: unable to allocate 4200 bytes of shared memory ('shared pool','SELECT * FROM LABEL_DETAILS_...','sga heap','library cache')

    ORA-00604: error occurred at recursive SQL level %s

    ORA-00604: error occurred at recursive SQL level %s

    ORA-00604: error occurred at recursive SQL level 2

    ORA-04031: unable to allocate 4168 bytes of shared memory ('shared pool','VIEW$','sga heap','state objects')

    ORA-00604: error occurred at recursive SQL level %s

    ORA-00604: error occurred at recursive SQL level 1

    ORA-04031: unable to allocate 4176 bytes of shared memory ('shared pool','LABEL_DETAILS_VW','sga heap','library cache')

    ORA-04031: unable to allocate 4176 bytes of shared memory ('shared pool','SELECT * FROM LABEL_DETAILS_...','sga heap','library cache')

  2. #2
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    Jmac I thought you of all people would be the first one to research on Metalink before coming here. Tsk tsk...

    direct HIT here.
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  3. #3
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    I'm back in just 10 minutes after 2 weeks lazing by a pool in the Canary Islands to find they've rebuilt a server and there are problems by the dozen all over the place! I've been dealing with the serious errors and hoping that my chums at DBASupport could help me out and save me a bit of typing!

    Thanks amigo.

    P.S. The pool was heated, the weather scorching and the beer very, very cold.

  4. #4
    Join Date
    Nov 2004
    Location
    Chennai
    Posts
    38
    if u r running 8.1.7.0 then its a bug
    the metalink suggest to set the parameter _db_handles_cached=0

  5. #5
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    Originally posted by JMac
    I'm back in just 10 minutes after 2 weeks lazing by a pool in the Canary Islands to find they've rebuilt a server and there are problems by the dozen all over the place! I've been dealing with the serious errors and hoping that my chums at DBASupport could help me out and save me a bit of typing!

    Thanks amigo.

    P.S. The pool was heated, the weather scorching and the beer very, very cold.
    Ok, understandable. I'll let you slide but be careful Davey and MH might not be so sypathetic
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    Originally posted by OracleDoc
    Ok, understandable. I'll let you slide but be careful Davey and MH might not be so sypathetic
    us brits got to stick together

  7. #7
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Hmmmm curious this ...

    The ORA error messages posted above were captured by the Application and recorded in an Error_log table in the schema. Hey - a developer doing something sensible! However, I can't find any ORA errors at all in the alert log. This database (call it 'c') links to DB 'b' and DB 'a'.

    A - is the master database.
    B - has a DB link and uses Views to replicate data at B based on data at A.
    C - has a DB link and uses Views to replicate data at C based on data at B. Some queries against C will pull data from A via B via these views.

    Make sense?

    I can't find any ORA-04031 or ORA-00604 errors in the alert logs of DBs A, B or C.

    So where can they be coming from. The Developer doesn't know - he says he just catches them! Is it possible that somehow one of these DBs has a messaging level that doesn't allow the messages to appear in the alert logs? (I very much doubt this).

    Or can the messages be written somewhere else than the udump or udump destinations?

    To further complicate things:
    DB A is Ora 9.2.0.4

    DB B is Ora 9.2.0.5
    DB C is Ora 8.1.7

    A is on a Windows 2003 server.

    B and C share an NT4 server, and the contractors installed multiple Oracle homes and migrated each Db up from 8.1.5 in order to get some of the functionality between B and C working. Why they stopped upgrading C at 8.1.7 I don't know.

    The errors are timed at 20 minute intervals. At almost the exact time as the system appears to archive redo logs (10Mb logs - the original DB had 1Mb logs and were sufficient for this fairly small system)


    Anyone have any clues??????

  8. #8
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,014
    Or can the messages be written somewhere else than the udump or udump destinations?
    Are you sure that you don't need to look in bdump?
    Also did you check to see where bdump is pointing?

    Code:
    SQL> select name, value 
    from v$parameter where name like '%dump%';
    
    NAME                   VALUE
    ---------------------- --------------------------------
    shadow_core_dump       partial
    background_core_dump   partial
    background_dump_dest   /u01/app/oracle/admin/t31a/bdump
    user_dump_dest         /u01/app/oracle/admin/t31a/udump
    max_dump_file_size     10240
    core_dump_dest         /u01/app/oracle/admin/t31a/cdump
    this space intentionally left blank

  9. #9
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Akila's comment looks promising:

    Metalink says: "This bug exists in 8.1.7 only. If _db_handles_cached is non-zero (5 by default) and you are seeing ORA-4031s and after setting _db_handles_cached to 0 the ORA-4031s stop, you may be running into this problem. "

    As this Db is 8.1.7 and we do get ORA-4031a after ORA-00604 then it might be a bug. (But as we upgraded from 8.1.5 to 8.1.7 shouldn't it have been a problem before?).

    I'll monitor the situation after setting the undcumented parameter and let you know.

  10. #10
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Originally posted by gandolf989
    Are you sure that you don't need to look in bdump?
    Also did you check to see where bdump is pointing?

    Code:
    SQL> select name, value 
    from v$parameter where name like '%dump%';
    
    NAME                   VALUE
    ---------------------- --------------------------------
    shadow_core_dump       partial
    background_core_dump   partial
    background_dump_dest   /u01/app/oracle/admin/t31a/bdump
    user_dump_dest         /u01/app/oracle/admin/t31a/udump
    max_dump_file_size     10240
    core_dump_dest         /u01/app/oracle/admin/t31a/cdump
    Aye sorry - it was a typo. I meant to say bdump as well, and yup! I've checked the parameters to show me where the dump dests are.

    Thanks Gandolf.

    This is a puzzling one.

Posting Permissions

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



Click Here to Expand Forum to Full Width