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

    Unanswered: out of process memory / TNS:internal limit restriction exceeded

    This is a production system.

    We have a backend Oracle 9.2.0.1 database with a front end Oracle Apache server (Apache/1.3.22).

    We keep getting errors when getting Oracle to resize images using Intermedia. we have over 50
    databases using exactly the same code but this probably has the most users at around few hundred users. The
    server is rarely at 100% cpu so the system seems to be able to cope with the load fine. The application is
    an estate agent front of house and back office accounting system so there are no large queries running or
    large updates of data.

    Once we start getting the errors we get them all over the place just running normal small query or update statements,
    if I log onto sqlplus as sys sysdba sometimes it will not log me in, giving TNS:listener failed to start a dedicated server process
    and if I do get in and do a shutdown abort it never seems to shutdown. I have to kill the oracle.exe process in windows before I can
    restart the database which is not nice!


    There also seem to be loads and loads of TNS-12500 TNS:listener failed to start a dedicated server process in the Apache log file


    And from Database Alert Log
    ---------------------------
    Tue Aug 01 14:48:50 2006
    Errors in file c:\oracle\admin\orcl\udump\orcl_ora_3468.trc:
    ORA-04030: out of process memory when trying to allocate 524320 bytes (joxcx callheap,ioc_allocate ufree)
    ORA-04030: out of process memory when trying to allocate 1048608 bytes (joxcx callheap,ioc_allocate ufree)
    ORA-04030: out of process memory when trying to allocate 2097184 bytes (joxcx callheap,ioc_allocate ufree)
    ORA-04030: out of process memory when trying to allocate 4194336 bytes (joxcx callheap,ioc_allocate ufree)



    Looking in the listener.log file there are these errors
    -------------------------------------------------------
    01-AUG-2006 14:53:05 * (CONNECT_DATA=(SERVICE_NAME=orcl)(CID=(PROGRAM=e:\ oracle\ora92\Apache\Apache\apache.exe)(HOST=GOADSB Y-T75ASEX)(USER=asp3_admin))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.2)(PORT=496 4)) * establish * orcl * 12500
    TNS-12500: TNS:listener failed to start a dedicated server process
    TNS-12540: TNS:internal limit restriction exceeded
    TNS-12560: TNSrotocol adapter error
    TNS-00510: Internal limit restriction exceeded
    32-bit Windows Error: 8: Exec format error



    I have allocated the following resources and memory in the init.ora
    processes = 500
    job_queue_processes = 10
    open_cursors = 1000

    shared_pool_size = 400M
    db_cache_size = 1000M
    java_pool_size = 250M
    sga_max_size = 1600M
    pga_aggregate_target = 2500M

    If I try and set the memory resources any higher then the database will not start even though there is nearly 2 gig of
    RAM available in the machine.


    It looks like I have a couple of problems. For some reason the listener is refusing to create new sessions, I can't see
    where I can up the limit or even find out what the limit is. I guess I need to change something to allow more connections
    but I can't see where.


    I think need to allocate more memory to Oracle, I don't understand why I am getting the ORA-04030: out of process memory when
    trying to allocate 524320 bytes (joxcx callheap,ioc_allocate ufree). As I said there is nearly 2 GIG of free RAM on the server.
    There are probably around 400,000 names and addresses with varting degrees of property details, accounts transaction records
    etc etc so the database is not huge. Last nights data dump was 2 gig (zipped up to 370MB) Any suggestions would be greatfully
    recieved for why I can't increase the memory, or what other parameters I should be setting in the init.ora file.






    LISTENER.ORA
    -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = goadsby-t75osex)(PORT = 1521))
    )
    )
    )

    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = PLSExtProc)
    (ENVS = "EXTPROC_DLLS=ANY")
    (ORACLE_HOME = c:\oracle\ora92)
    (PROGRAM = extproc)
    )
    (SID_DESC =
    (GLOBAL_DBNAME = orcl)
    (ORACLE_HOME = c:\oracle\ora92)
    (SID_NAME = orcl)
    )
    )
    -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=




    INITORCL.ORA
    -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
    # misc
    db_name = orcl
    db_block_size = 8192
    compatible = 9.2.0.0.0
    utl_file_dir = *
    max_dump_file_size = 5M
    remote_login_passwordfile = exclusive

    optimizer_index_caching = 70 # temp fix for non hint queries 0-100 higher promotes nested loops
    query_rewrite_enabled = true # needed for user defined function based indexes

    # locations
    control_files = (e:\oracle\oradata\orcl\control01.ctl, c:\oracle\oradata\orcl\control02.ctl)
    background_dump_dest = C:\oracle\admin\orcl\bdump
    user_dump_dest = C:\oracle\admin\orcl\udump
    log_archive_dest = C:\oracle\oradata\orcl\arch
    log_archive_format = orcl_%S.arc
    log_archive_start = true

    # resource
    processes = 500
    job_queue_processes = 10
    open_cursors = 1000

    shared_pool_size = 400M
    db_cache_size = 1000M
    java_pool_size = 250M
    sga_max_size = 1600M

    pga_aggregate_target = 2500M
    -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
    There are 10 types of people in the world, those that know Binary and those that don't.

  2. #2
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    I'd lower most of your parameters you listed for the SGA. Particularly, I'd adjust the SGA_MAX_SIZE and the PGA_AGGREGATE_TARGET. What I see right off is you say that you have "nearly 2 gig" of RAM on the machine, yet, your PGA_AGGREGATE_TARGET size is set to 2.4GB...

    However, if you are saying you have 2GB available to Oracle: I believe in Win2K server and previous, Windows had a hard limit of 2GB of memory space available to each process. If you push this limit, Windoze will choke. I think in Win2K3, this limit has been changed... not 100% about this, as I'm a Redhat/Oracle admin.

    Hope this helps.
    JoeB
    save disk space, use smaller fonts

  3. #3
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    sorry, I wasnt quite clear in my original message, there is 4gig in the machine and nearly 2gig that is not being used at all.

    I have looked up the 2gig limit and found some documentation and will follow that, thanks for the pointer

    cheers
    Robert
    There are 10 types of people in the world, those that know Binary and those that don't.

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Joe is correct in that windows 32 bit only allows 2GB per process (and that includes memory fragmentation). If you use the /3GB you can get 3GB per process but then the kernal has 1Gb less so you have to monitor some of the windows memory pool values. Of course if you go to windows 64 bit (with 64 bit Oracle) you dont have these restrictions.

    In the meantime try adjusting your memory settings for example

    shared_pool_size = 500M
    db_cache_size = 500M
    java_pool_size = 50M (assuming you dont use much java in the database itself)
    sga_max_size = 1000M
    pga_aggregate_target = 400M

    You will have to fine tune it by monitoring the v$ views to see how much your actually using.

    Alan

Posting Permissions

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