ORA-12519: TNS:no appropriate service handler found
Issuing the following command
lsnrctl services
Showed other stuff and the following important line
Service "xe" has 1 instance(s).
Instance "xe", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:688 refused:0 state:blocked
LOCAL SERVER
Instance "xe", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:688 refused:0 state:blocked
LOCAL SERVER
I googled around a lot, and the best answer I could come up with was on an Oracle forum. It essentially said that the oracle process tells the listener how many connections are available, and then every now and again it tells the listener when connections have been closed. So the problem will manifest itself if a lot of connections are opened before the database tells the listener about the closures. By default in Oracle XE, 40 connections are allowed, which after accounting for background processes leaves 19 client connections.
So the solutions that I could come up with
1) Not use Oracle - moving to MySQL should remove this problem, but may introduce others ;-)
2) The real fix would be to stop the client from creating too many connections via a connection pool. Depending on the application, this may or may not be possible
3) A quick fix to mask the problem would be to alter the number of allowed processes via issuing the following command as sysdba, and the restarting the database and listener
ALTER SYSTEM SET PROCESSES=150 SCOPE=SPFILE
No comments:
Post a Comment