Hi,
We have Oracle 11G x64 installed on the server. 32bit client is installed on client PCs. Clients claim that they lose connection to the database, and recovers after a while. Also, restarting listener and database instance services fixes immediately.
Clients get ORA-12520 error. But I see 12514 and 12528 errors in the logs. I have read a lot about the errors, got confused couldn't address the issue in my case.
Is there anything wrong with my settings or the environment?
Configuration files and listener logs are below.
Thanks in advance.
Caner
-------------------------------------------------
C:\Users\Administrator>lsnrctl status
LSNRCTL for 64-bit Windows: Version 11.2.0.4.0 - Production on 24-APR-2015 07:46
:29
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 11.2.0.4.0 - Produ
ction
Start Date 24-APR-2015 06:39:41
Uptime 0 days 1 hr. 6 min. 48 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\app\Administrator\product\11.2.0\dbhome_1\network\a
dmin\listener.ora
Listener Log File D:\app\Administrator\diag\tnslsnr\WINDOWS-6IUI40U\list
ener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=WINDOWS-6IUI40U)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "VIZRTDBXDB" has 1 instance(s).
Instance "vizrtdb", status READY, has 1 handler(s) for this service...
Service "vizrtdb" has 1 instance(s).
Instance "vizrtdb", status READY, has 1 handler(s) for this service...
The command completed successfully
-----------------------------------------------
C:\Users\Administrator>lsnrctl service
LSNRCTL for 64-bit Windows: Version 11.2.0.4.0 - Production on 24-APR-2015 07:49
:06
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "VIZRTDBXDB" has 1 instance(s).
Instance "vizrtdb", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: WINDOWS-6IUI40U, pid: 2676>
(ADDRESS=(PROTOCOL=tcp)(HOST=WINDOWS-6IUI40U)(PORT=55766))
Service "vizrtdb" has 1 instance(s).
Instance "vizrtdb", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:60 refused:0 state:ready
LOCAL SERVER
The command completed successfully
--------------------------------------
# listener.ora Network Configuration File: D:\app\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:D:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = WINDOWS-6IUI40U)(PORT = 1521))
)
)
ADR_BASE_LISTENER = D:\app\Administrator
---------------------------------------
# tnsnames.ora Network Configuration File: D:\app\Administrator\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
VIZRTDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = WINDOWS-6IUI40U)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = VIZRTDB)
)
)
ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)
--------------------------------------------
# sqlnet.ora Network Configuration File: D:\app\Administrator\product\11.2.0\dbhome_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.
# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
--------------------------------------------
From listener logs.
24-APR-2015 06:39:29 * (CONNECT_DATA=(SERVICE_NAME=VIZRTDB)(CID=(PROGRAM=C:\Program?Files??x86?\Vizrt\Media?Sequencer\scheduler.exe)(HOST=CISTTVINGB21317)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.23.52.194)(PORT=51828)) * establish * VIZRTDB * 12516
TNS-12516: TNS:listener could not find available handler with matching protocol stack
24-APR-2015 06:39:29 * (CONNECT_DATA=(SERVICE_NAME=VIZRTDB)(CID=(PROGRAM=C:\Program?Files??x86?\Vizrt\Media?Sequencer\scheduler.exe)(HOST=CISTTVINGB21317)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.23.52.194)(PORT=51829)) * establish * VIZRTDB * 12516
TNS-12516: TNS:listener could not find available handler with matching protocol stack
Fri Apr 24 06:39:32 2015
24-APR-2015 06:39:32 * (CONNECT_DATA=(SERVICE_NAME=VIZRTDB)(CID=(PROGRAM=C:\Program?Files??x86?\Vizrt\Media?Sequencer\scheduler.exe)(HOST=CISTTVINGB21317)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.23.52.194)(PORT=51830)) * establish * VIZRTDB * 12516
TNS-12516: TNS:listener could not find available handler with matching protocol stack
24-APR-2015 06:39:52 * (CONNECT_DATA=(SERVICE_NAME=VIZRTDB)(CID=(PROGRAM=C:\Program?Files??x86?\Vizrt\Media?Sequencer\scheduler.exe)(HOST=CISTTVINGB21223)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.23.53.7)(PORT=53197)) * establish * VIZRTDB * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
24-APR-2015 06:39:52 * (CONNECT_DATA=(SERVICE_NAME=VIZRTDB)(CID=(PROGRAM=C:\Program?Files??x86?\Vizrt\Media?Sequencer\scheduler.exe)(HOST=CISTTVINGB21223)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.23.53.7)(PORT=53198)) * establish * VIZRTDB * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
24-APR-2015 06:39:52 * (CONNECT_DATA=(SERVICE_NAME=VIZRTDB)(CID=(PROGRAM=C:\Program?Files??x86?\Vizrt\Media?Sequencer\scheduler.exe)(HOST=CISTTVINGB21326)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.23.53.23)(PORT=50850)) * establish * VIZRTDB * 12514
24-APR-2015 06:39:58 * (CONNECT_DATA=(SERVICE_NAME=VIZRTDB)(CID=(PROGRAM=C:\Program?Files??x86?\Vizrt\Media?Sequencer\scheduler.exe)(HOST=CISTTVINGB21223)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.23.53.7)(PORT=53209)) * establish * VIZRTDB * 12528
TNS-12528: TNS:listener: all appropriate instances are blocking new connections
24-APR-2015 06:39:58 * (CONNECT_DATA=(SERVICE_NAME=VIZRTDB)(CID=(PROGRAM=C:\Program?Files??x86?\Vizrt\Media?Sequencer\scheduler.exe)(HOST=CISTTVINGB21328)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.23.53.10)(PORT=51916)) * establish * VIZRTDB * 12528
TNS-12528: TNS:listener: all appropriate instances are blocking new connections
24-APR-2015 06:39:58 * (CONNECT_DATA=(SERVICE_NAME=VIZRTDB)(CID=(PROGRAM=C:\Program?Files??x86?\Vizrt\Media?Sequencer\scheduler.exe)(HOST=CISTTVINGB21229)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.23.53.6)(PORT=52773)) * establish * VIZRTDB * 12528
TNS-12528: TNS:listener: all appropriate instances are blocking new connections