ORA-12519 & ORA-12505

java.sql.SQLException: Listener refused the connection with the following error:

ORA-12519, TNS:no appropriate service handler found The Connection descriptor used by the client was: localhost:1521:XE

Alright, another Oracle Exception. It says "Listener refused the connection". Is hostname correct? Yes, port? Yes, SID? Yes. Damn me. Must have forgot to start the TNSListener. But wait, if it is saying Listener refused the connection, doesn't it mean that the Listener is working and has refused the connection? Oh, yes. A quick check in the services shows that the Listener is already up and running. Why would Listener refuse the connection.? Wouldn't it refuse if the Oracle service is not running? Yes!! But the Oracle Server is running. Run a quick check and see what it says when the Oracle Service is not running.

Well, it says

java.sql.SQLException: Listener refused the connection with the following error:

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor The Connection descriptor used by the client was: localhost:1521:XE

This clearly states that the SID is not recognized as the service is not running. So the earlier problem was not due to Oracle service not running. Back to square one. Okay, let me see if listener recognizes the Oracle services... What was the command? Oh, Yes. "LSNRCTL.EXE". Do a lsnrctl.exe services

Service "xe" has 1 instance(s).
Instance "xe", status READY, has 1 handler(s) for this service...
Handler(s): "DEDICATED" established:243 refused:0
state:ready LOCAL SERVER

The listener is able to identify that there is a service running. Hmmm. Now why does the listener refuse the connection? Let me do a quick restart on both the Oracle Service and Listener service. No use...

Hmmm. Does it have have anything to do with the username/pwd that I am using. Given the fact that the username/pwd has been created just now...It might be.

Now rerun with the root username/pwd. :-) It still fails. Thank God, I have not misconfigured the username/pwd. Wait, I see that there are some connections already established in the Application Server. Well, if its failing, it should fail for all the connections. Do a restart on the AppServer and check. Its the same thing again.

Okay, lets put up a simple java client which will try to create connections and see. Alright, it gets the connection. Let me try to check how many connections it can establish.

Connection :0
Connection :1
Connection :2
Connection :3
Connection :4
Connection :5
Connection :6
Connection :7
Connection :8
Connection :9
Connection :10
Connection :11
Connection :12
Connection :13
Connection :14
Connection :15
Connection :16
Connection :17
Connection :18
Connection :19
Exception in thread "main" java.sql.SQLException: Listener refused the connection with the following error: ORA-12519, TNS:no appropriate service handler found The Connection descriptor used by the client was: localhost:1521:XE

Aha!!!! Trying to establish more than 20 connections is causing the problem.

Alright, its out of my control now. Googling, finally I land up at:

http://forums.oracle.com/forums/thread.jspa?messageID=1145120

It says, the problem is with the number of process count defined in the init.ora file. Let me check. There is no process count defined. WTH, let me define the process count in init.ora file and restart Oracle

processes=200

It still stays the same. So adding the process count is not recognized. Let me try what was suggested.

ALTER SYSTEM SET PROCESSES=150 SCOPE=SPFILE

Now with this change and restarting the Oracle service, it is able to create upto 409 connections now. Great!