ORA-12516, TNS:listener could not find available handler... 2

[ThatRickGuy]

I am receiving the following error after a while...

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

I had never seen this error before yesterday. I just started testing some new software, everything was going great, then all of a sudden the software started bombing out while trying to connect to the Oracle database. I went to log in to the Oracle web interface, and I get the error there as well.

We have 3 Oracle instances running on the server, and luckily this one is just a development environment so I can bounce the service again with out effecting any of our live systems. But I can't find any really good descriptions of what would cause this issue over a period of time and cause it to persist. I was running the app with no problems all morning, then is just stopped. Just like what happened yesterday.

Anyone know what could cause this?

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[SantaMufasa]

Rick,

Tell us more about your situation:

1) Are you running Oracle's Multi-Threaded Server environment?

2) When you connect to "lsnrctl", what are the results of a "status" check?

3) What are the contents of the "listener.ora" file that "lsnrctl" says that it is using?

4) What are the messages in the "listener.log" that relate to this situation? (i.e., when you attempt to make a connection to the database, what shows up in listener.log?)

5) What has changed (in your recollection) to your environment since things worked properly?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ThatRickGuy]

Mind you, I'm rather a newb to the Oracle interface. I worked with it a lot back in 99-2001, but I'm way out of practice. And I just started here a few months back, so my knowledge of the server environment is a tad limited.

1) No idea. I know that there are 3 instances of Oracle running on a dual processor box. Is there an easy way to determine if it is running threaded?

2) Unfortunately, I have no idea what 'lsnrctl' is, you'll have to give me a few minutes to research that one

3) Here is the Listener.Ora file that appears to be used (all other Listener.Ora files were in sample folders)

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = E:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = htsvxml.ora10g.net)
      (ORACLE_HOME = E:\oracle\product\10.2.0\db_1)
      (SID_NAME = htsvxmb)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = htsdata3)(PORT = 1521))
  )

4) Listener.log appears to be a 1.1 GB file... I think I'll talk to the quasi-DB guy about clearing that or something. In its current state I can't open it with Notepad and Wordpad appears to be choking on it as well. I don't really want to drag a 1gig file over the network to try to open it with TextPad or other improved text editors though. But if it holds the key to figuring this out, I'll see what I can do.

5) Nothing that I know of. I am the only person working in this instance of Oracle, and this is the only application hitting the database. My initial thought was that there were connections getting hung open, but the performance page in the web interface is showing squat for active sessions.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[SantaMufasa]

Rick,

1) Run this code and post the results:

col parameter format a30
col value format a15
select name parameter, value
from v$parameter
where name like ('mts%')
order by name;

2) 'lsnrctl' is the Oracle Listener Control utility. It should be readily available on the machine upon which the Listener (and the Oracle database for which it listens) resides. You should be able to run 'lsnrctl' from the command prompt. Once you run the utility, from the lsnrctl command prompt, issue the command 'status' to see the current settings (if any) for the listener. The results of the 'status' command are what you should post here.

3) Okay, thanks.

4) Yes, the listener.log file can grow large quickly. I recommend that your DBA momentarily 'stop' the listener (from 'lsnrctl'), rename the listener.log file to some archive name (e.g. listener.log.2007mmdd-20070516), then backup, then restart the listener ('start' from 'lsnrctl'). If the large listener.log file resides (even temporarily) on a Unix machine, you can issue the Unix command:

tail -200 listener.log.2007mmdd-20070516

...which displays the last 200 lines of that file.

5) Then we'll need to do more research.

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ThatRickGuy]

1) no rows returned.

2) Here we go. HTSCME is the environment I am having the issues with.

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=htsdata3)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 10.2.0.2.0 - Produ
ction
Start Date                16-MAY-2007 12:15:20
Uptime                    0 days 2 hr. 51 min. 5 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   E:\oracle\product\10.2.0\db_1\network\admin\listener.o
ra
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=htsdata3)(PORT=1521)))
Services Summary...
Service "HTSCMEXDB" has 1 instance(s).
  Instance "htscme", status READY, has 1 handler(s) for this service...
Service "HTSCME_XPT" has 1 instance(s).
  Instance "htscme", status READY, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "htscme" has 1 instance(s).
  Instance "htscme", status READY, has 1 handler(s) for this service...
Service "htsvxml.htsweb.net" has 1 instance(s).
  Instance "htsvxml", status READY, has 2 handler(s) for this service...
Service "htsvxml.ora10g.net" has 1 instance(s).
  Instance "htsvxmb", status UNKNOWN, has 1 handler(s) for this service...
Service "htsvxml_XPT.htsweb.net" has 1 instance(s).
  Instance "htsvxml", status READY, has 2 handler(s) for this service...
Service "htsvxmld.htsweb.net" has 1 instance(s).
  Instance "htsvxmld", status READY, has 2 handler(s) for this service...
Service "htsvxmld_XPT.htsweb.net" has 1 instance(s).
  Instance "htsvxmld", status READY, has 2 handler(s) for this service...
The command completed successfully

4) We manage to stop/restart the database and rename the log file. It looks like everything was going fine, then at one specific point it starts erroring out for only the HTSCME environment. HTSVXML continues responding fine.

This is totally going to kill the formating of the page, but here is the log entries from a few seconds before the issue starts and the first few attempts and errors:

16-MAY-2007 11:03:22 * service_update * htscme * 0
16-MAY-2007 11:03:22 * service_update * htsvxmld * 0
16-MAY-2007 11:03:22 * (CONNECT_DATA=(SERVICE_NAME=HTSCME)(CID=(PROGRAM=c:\windows\system32\inetsrv\w3wp.exe)(HOST=HTSWEB1)(USER=NETWORK?SERVICE))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.108)(PORT=2587)) * establish * HTSCME * 0
16-MAY-2007 11:03:22 * (CONNECT_DATA=(SERVICE_NAME=HTSCME)(CID=(PROGRAM=c:\windows\system32\inetsrv\w3wp.exe)(HOST=HTSWEB1)(USER=NETWORK?SERVICE))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.108)(PORT=2588)) * establish * HTSCME * 0
16-MAY-2007 11:03:22 * (CONNECT_DATA=(SERVICE_NAME=HTSCME)(CID=(PROGRAM=c:\windows\system32\inetsrv\w3wp.exe)(HOST=HTSWEB1)(USER=NETWORK?SERVICE))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.108)(PORT=2589)) * establish * HTSCME * 0
16-MAY-2007 11:03:25 * (CONNECT_DATA=(SERVICE_NAME=HTSCME)(CID=(PROGRAM=c:\windows\system32\inetsrv\w3wp.exe)(HOST=HTSWEB1)(USER=NETWORK?SERVICE))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.108)(PORT=2590)) * establish * HTSCME * 0
16-MAY-2007 11:03:25 * (CONNECT_DATA=(SID=htsvxmld)(CID=(PROGRAM=E:\oracle\product\10.2.0\db_1\perl\5.8.3\bin\MSWin32-x86-multi-thread\perl.exe)(HOST=HTSDATA3)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.125)(PORT=3420)) * establish * htsvxmld * 0
16-MAY-2007 11:03:25 * (CONNECT_DATA=(SERVICE_NAME=HTSCME)(CID=(PROGRAM=c:\windows\system32\inetsrv\w3wp.exe)(HOST=HTSWEB1)(USER=NETWORK?SERVICE))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.108)(PORT=2591)) * establish * HTSCME * 0
16-MAY-2007 11:03:25 * (CONNECT_DATA=(SERVICE_NAME=HTSCME)(CID=(PROGRAM=c:\windows\system32\inetsrv\w3wp.exe)(HOST=HTSWEB1)(USER=NETWORK?SERVICE))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.108)(PORT=2592)) * establish * HTSCME * 0
16-MAY-2007 11:03:25 * (CONNECT_DATA=(SERVICE_NAME=HTSCME)(CID=(PROGRAM=c:\windows\system32\inetsrv\w3wp.exe)(HOST=HTSWEB1)(USER=NETWORK?SERVICE))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.108)(PORT=2593)) * establish * HTSCME * 0
16-MAY-2007 11:03:25 * (CONNECT_DATA=(SERVICE_NAME=HTSCME)(CID=(PROGRAM=c:\windows\system32\inetsrv\w3wp.exe)(HOST=HTSWEB1)(USER=NETWORK?SERVICE))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.108)(PORT=2594)) * establish * HTSCME * 0
16-MAY-2007 11:03:25 * (CONNECT_DATA=(SERVICE_NAME=HTSCME)(CID=(PROGRAM=c:\windows\system32\inetsrv\w3wp.exe)(HOST=HTSWEB1)(USER=NETWORK?SERVICE))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.108)(PORT=2595)) * establish * HTSCME * 0
16-MAY-2007 11:03:25 * service_update * htscme * 0
16-MAY-2007 11:03:25 * (CONNECT_DATA=(SERVICE_NAME=HTSCME)(CID=(PROGRAM=c:\windows\system32\inetsrv\w3wp.exe)(HOST=HTSWEB1)(USER=NETWORK?SERVICE))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.108)(PORT=2596)) * establish * HTSCME * 0
16-MAY-2007 11:03:25 * (CONNECT_DATA=(SERVICE_NAME=HTSCME)(CID=(PROGRAM=c:\windows\system32\inetsrv\w3wp.exe)(HOST=HTSWEB1)(USER=NETWORK?SERVICE))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.108)(PORT=2597)) * establish * HTSCME * 0
16-MAY-2007 11:03:25 * (CONNECT_DATA=(SERVICE_NAME=HTSCME)(CID=(PROGRAM=c:\windows\system32\inetsrv\w3wp.exe)(HOST=HTSWEB1)(USER=NETWORK?SERVICE))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.108)(PORT=2598)) * establish * HTSCME * 0
16-MAY-2007 11:03:25 * (CONNECT_DATA=(SERVICE_NAME=HTSCME)(CID=(PROGRAM=c:\windows\system32\inetsrv\w3wp.exe)(HOST=HTSWEB1)(USER=NETWORK?SERVICE))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.108)(PORT=2599)) * establish * HTSCME * 0
16-MAY-2007 11:03:26 * (CONNECT_DATA=(SERVICE_NAME=HTSCME)(CID=(PROGRAM=c:\windows\system32\inetsrv\w3wp.exe)(HOST=HTSWEB1)(USER=NETWORK?SERVICE))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.108)(PORT=2600)) * establish * HTSCME * 0
16-MAY-2007 11:03:26 * (CONNECT_DATA=(SERVICE_NAME=HTSCME)(CID=(PROGRAM=c:\windows\system32\inetsrv\w3wp.exe)(HOST=HTSWEB1)(USER=NETWORK?SERVICE))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.108)(PORT=2601)) * establish * HTSCME * 12516
TNS-12516: TNS:listener could not find available handler with matching protocol stack
16-MAY-2007 11:03:26 * (CONNECT_DATA=(SERVICE_NAME=HTSCME)(CID=(PROGRAM=c:\windows\system32\inetsrv\w3wp.exe)(HOST=HTSWEB1)(USER=NETWORK?SERVICE))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.108)(PORT=2602)) * establish * HTSCME * 12516
TNS-12516: TNS:listener could not find available handler with matching protocol stack
16-MAY-2007 11:03:26 * (CONNECT_DATA=(SERVICE_NAME=HTSCME)(CID=(PROGRAM=c:\windows\system32\inetsrv\w3wp.exe)(HOST=HTSWEB1)(USER=NETWORK?SERVICE))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.108)(PORT=2603)) * establish * HTSCME * 12516
TNS-12516: TNS:listener could not find available handler with matching protocol stack
16-MAY-2007 11:03:26 * (CONNECT_DATA=(SERVICE_NAME=HTSCME)(CID=(PROGRAM=c:\windows\system32\inetsrv\w3wp.exe)(HOST=HTSWEB1)(USER=NETWORK?SERVICE))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.108)(PORT=2604)) * establish * HTSCME * 12516
TNS-12516: TNS:listener could not find available handler with matching protocol stack
16-MAY-2007 11:03:28 * service_update * htscme * 0
16-MAY-2007 11:03:28 * (CONNECT_DATA=(SID=HTSCME)(CID=(PROGRAM=E:\oracle\product\10.2.0\db_1\bin\emagent.exe)(HOST=HTSDATA3)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.125)(PORT=3425)) * establish * HTSCME * 12519
TNS-12519: TNS:no appropriate service handler found

Thanks again for the help!

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[SantaMufasa]

Rick,

I'm guessing that time is of the essence to you for getting this problem resolved (i.e., this is a business-critical system that needs to be performing properly soon).

Therefore, I suggest that the quickest resolution will come from filing an Service Request (at a high priority) with Oracle Metalink (metalink.oracle.com; 800-223-1711).

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ThatRickGuy]

Nah, it's not that time sensitive. The production systems are working fine, it's just this software I'm working on. I don't think we have to worry about our first contract on it until September or October, and the issue is with all likelihood a problem in my code. I'm just trying to figure out what is happening on the Oracle side so I can figure out what my code is doing incorrectly.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[karluk]

Have you searched for these TNS-12516 and TNS-12519 errors on Metalink? Your problem is quite similar to the situation discussed in note 240710.1. The suggestion in that document is to increase the value of the "processes" startup parameter. That's because the listener knows when a database is getting close to its maximum number of sessions, and will refuse additional connections after it thinks that the limit has been reached. If that's the cause of your errors, simply increasing the maximum number of allowed processes will eliminate them.

 

[ThatRickGuy]

Thanks guys for the help! Karluk, it looks like that is indeed the issue. Thanks for pointing my search in the right direction!

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.