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!

Ora-12516

Discussion in 'Server Administration and Options' started by 13478, Jan 26, 2017.

  1. 13478

    13478 Active Member

    Messages:
    41
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Louisana
    Hello,

    We created a shared db link, it works to connect to another database.

    The problem is, when client opened many sessions at same time, we get error:

    ORA-12516:TNS:listener could not find available handler with matching protocol stack

    database is 12.1.0.2 in windows server.
    any idea?

    Thank you very much!
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,639
    Likes Received:
    368
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Which release of Windows are you running? 32-bit or 64-bit? How much RAM is installed? How much of that RAM is dedicated to your shared memory configuration for the database? Such errors are usually memory-related so it's likely that a memory handle has been lost or a semaphore can't be created to handle the work. TCP communication uses memory structures to hold the necessary connection information; you need to check your memory configuration for that server; it's likely that database services are consuming more resources than necessary which causes other memory to run short. This post doesn't address your problem directly but you might benefit from the script described in it:

    https://dfitzjarrell.wordpress.com/2016/01/26/heap-of-trouble/

    That script will show you where the shared memory is being used and how much of it is actually free, which can assist you in adjusting the memory settings for your database.
     
    13478 likes this.
  3. 13478

    13478 Active Member

    Messages:
    41
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Louisana

    Hello Zargon,

    yeah, that is inspiring.
    I found some problem that our database memory_target is set too small (I am not the person who create this db).

    now memory_target is set to 9G.
    now performance is better.

    let us observe for a while.

    Thank you very much !!!
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,639
    Likes Received:
    368
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Your observations should include regular queries of V$MEMORY_TARGET_ADVICE as it will show you where you are (your current setting) as well as what changes to the current setting may give you in performance improvement. You need to read this post:

    https://dfitzjarrell.wordpress.com/2015/03/30/sound-advice/

    to understand how to use the advisors to your and your database's advantage.