Windows: Connections to Database are Slow / Hang and Report Errors TNS-12518/ORA-12518

(Doc ID 1346186.1)

Last updated on AUGUST 25, 2017

Applies to:

Oracle Net Services - Version 11.1.0.7 to 11.2.0.3 [Release 11.1 to 11.2]
Microsoft Windows x64 (64-bit)
Microsoft Windows (32-bit)
Known to effect releases of Oracle 11.1.0.7, 11.2.0.1 and 11.2.0.2


Symptoms

Remote and bequeath connections to Windows database hang, Finally reporting the error message TNS-12518: TNS:listener could not hand off client connection reported.
Oracle net client trace of a connection could show similar to the following:

Initial connect packet is sent from client to the TNS listener

[18-JUL-2011 10:59:10:719] nscon: doing connect handshake...
[18-JUL-2011 10:59:10:719] nscon: sending NSPTCN packet


Delay of 1 minute plus, then ORA-12518 reported in a refuse packet, back to the client

[18-JUL-2011 11:00:32:651] nsprecv: 00 83 00 00 04 00 00 00 |........|
[18-JUL-2011 11:00:32:651] nsprecv: 22 00 00 77 28 44 45 53 |"..w(DES|
[18-JUL-2011 11:00:32:651] nsprecv: 43 52 49 50 54 49 4F 4E |CRIPTION|
[18-JUL-2011 11:00:32:651] nsprecv: 3D 28 54 4D 50 3D 29 28 |=(TMP=)(|
[18-JUL-2011 11:00:32:651] nsprecv: 56 53 4E 4E 55 4D 3D 31 |VSNNUM=1|
[18-JUL-2011 11:00:32:651] nsprecv: 38 36 36 34 36 37 38 34 |86646784|
[18-JUL-2011 11:00:32:651] nsprecv: 29 28 45 52 52 3D 31 32 |)(ERR=12|
[18-JUL-2011 11:00:32:651] nsprecv: 35 31 38 29 28 45 52 52 |518)(ERR|
[18-JUL-2011 11:00:32:651] nsprecv: 4F 52 5F 53 54 41 43 4B |OR_STACK|
[18-JUL-2011 11:00:32:651] nsprecv: 3D 28 45 52 52 4F 52 3D |=(ERROR=|
[18-JUL-2011 11:00:32:651] nsprecv: 28 43 4F 44 45 3D 31 32 |(CODE=12|
[18-JUL-2011 11:00:32:651] nsprecv: 35 31 38 29 28 45 4D 46 |518)(EMF|
[18-JUL-2011 11:00:32:651] nsprecv: 49 3D 34 29 29 28 45 52 |I=4))(ER|
[18-JUL-2011 11:00:32:651] nsprecv: 52 4F 52 3D 28 43 4F 44 |ROR=(COD|
[18-JUL-2011 11:00:32:651] nsprecv: 45 3D 31 32 35 36 30 29 |E=12560)|
[18-JUL-2011 11:00:32:651] nsprecv: 28 45 4D 46 49 3D 34 29 |(EMFI=4)|
[18-JUL-2011 11:00:32:651] nsprecv: 29 29 29 |))) |


Alert log for the database shows around the time the errors

Timed out trying to start process m000.
Mon Jul 18 10:55:23 2011
kkjcre1p: unable to spawn jobq slave process
Errors in file c:\app\administrator\diag\rdbms\atndsms\atndsms\trace\atndsms_cjq0_5648.trc:
Mon Jul 18 10:59:34 2011
DDE: Problem Key 'ORA 445' was completely flood controlled (0x6)
Further messages for this problem key will be suppressed for up to 10 minutes
kkjcre1p: unable to spawn jobq slave process
Errors in file c:\app\administrator\diag\rdbms\atndsms\atndsms\trace\atndsms_cjq0_5648.trc:
Mon Jul 18 11:03:45 2011
kkjcre1p: unable to spawn jobq slave process
Errors in file c:\app\administrator\diag\rdbms\atndsms\atndsms\trace\atndsms_cjq0_5648.trc:
Mon Jul 18 11:07:57 2011
kkjcre1p: unable to spawn jobq slave process
Errors in file c:\app\administrator\diag\rdbms\atndsms\atndsms\trace\atndsms_cjq0_5648.trc:
Mon Jul 18 11:08:24 2011
Timed out trying to start process m000.
Mon Jul 18 11:10:30 2011
DDE: Problem Key 'ORA 445' was completely flood controlled (0x6)
Further messages for this problem key will be suppressed for up to 10 minutes
kkjcre1p: unable to spawn jobq slave process


Listener log shows the ORA-12518 for the failing client and also large time gap for PMON updates

18-JUL-2011 11:00:32 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl.sample.com)(INSTANCE_NAME=orcl1)(CID=(PROGRAM=C:\app\Administrator\product\11.2.0\dbhome_1\BIN\sqlplus.exe)(HOST=clientA)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST= XXX.XX.XX.XX)(PORT=62422)) * establish * orcl.sample.com * 12518
TNS-12518: TNS:listener could not hand off client connection
TNS-12560: TNS:protocol adapter error

18-JUL-2011 10:49:27 * service_update * orcl * 0
18-JUL-2011 11:00:32 * service_update * orcl * 0

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms