Skip to main content
ExLibris
  • Subscribe by RSS
    • Back
    Aleph
    Ex Libris Knowledge Center

    Error occurred at recursive SQL level 2; unable to allocate 2088 bytes of shared

    1. Last updated
    2. Save as PDF
    • Article Type: General
    • Product: Aleph
    • Product Version: 20

    Description:
    We went up on version 20 over the weekend, receiving ORA errors in the alert log starting at 03:34 this morning:
    Thu Feb 03 03:34:52 2011
    Errors in file /exlibris/app/oracle/admin/aleph1/ddump/diag/rdbms/aleph1/aleph1/trace/aleph1_cjq0_28143.trc:
    ORA-00604: error occurred at recursive SQL level 2
    ORA-04031: unable to allocate 2088 bytes of shared memory ("shared pool","select job, nvl2(last_date, ...","CCursor"
    ,"kgltbsgp")
    Thu Feb 03 03:35:03 2011
    Errors in file /exlibris/app/oracle/admin/aleph1/ddump/diag/rdbms/aleph1/aleph1/trace/aleph1_cjq0_28143.trc:
    ORA-00604: error occurred at recursive SQL level 2
    ORA-04031: unable to allocate 2088 bytes of shared memory ("shared pool","select job, nvl2(last_date, ...","CCursor"
    ,"kgltbsgp")
    Thu Feb 03 03:36:44 2011
    Errors in file /exlibris/app/oracle/admin/aleph1/ddump/diag/rdbms/aleph1/aleph1/trace/aleph1_cjq0_28143.trc:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-04031: unable to allocate 256 bytes of shared memory ("shared pool","unknown object","CCursor","kglob")
    Thu Feb 03 03:36:50 2011
    Errors in file /exlibris/app/oracle/admin/aleph1/ddump/diag/rdbms/aleph1/aleph1/trace/aleph1_j000_18643.trc:
    ORA-12012: error on auto execute of job 1
    ORA-04031: unable to allocate 2088 bytes of shared memory ("shared pool","select u1.user#, u2.user#, u...","CCursor"
    ,"kgltbsgp")
    Thu Feb 03 03:44:01 2011
    PMON failed to delete process, see PMON trace file
    Thu Feb 03 04:04:42 2011
    Errors in file /exlibris/app/oracle/admin/aleph1/ddump/diag/rdbms/aleph1/aleph1/trace/aleph1_reco_16362.trc:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-00018: maximum number of sessions exceeded
    Thu Feb 03 04:16:17 2011
    Errors in file /exlibris/app/oracle/admin/aleph1/ddump/diag/rdbms/aleph1/aleph1/trace/aleph1_w000_2567.trc:
    ORA-00018: maximum number of sessions exceeded
    Thu Feb 03 04:16:32 2011
    Errors in file /exlibris/app/oracle/admin/aleph1/ddump/diag/rdbms/aleph1/aleph1/trace/aleph1_w000_2661.trc:
    ORA-00018: maximum number of sessions exceeded

    Resolution:
    Site increased:

    MEMORY_TARGET from 2 GB to 16 GB
    PGA_AGGREGATE_TARGET from 200 MB to 2 GB

    Oracle was already set to Automatic Memory Management.

    Their machine has 128 GB of memory, so the settings shouldn't bother the machine.

    In addition, Ex Libris asked the site to:

    1) increase thee Oracle processes to 2000 instead of 1300
    2) Set the shared_pool parameters to 1GB

    util a/8 showed hundreds of Analyzed tables/indexes (indicating that Oracle Statistics were not disabled).

    [From site:] The trace file logs have ceased being written to the alert logs since the statistics were disabled. The other errors reported in this ticket were more serious and were resolved by the other changes.

    • Article last edited: 10/8/2013