ORA-12519: TNS:no appropriate service handler found tips

Admin — Thu, 19 Mar 2009 03:30:28 GMT

Users were getting the following ora error messages and I noticed the the listener.log had some entries that showed the state=blocked. Here are my findings and solution.

Users are getting below errors in their app logs connecting to the database.

Error Message: MYPRODDB Io exception: Connection refused (DESCRIPTION= (TMP=) (VSNNUM=153093632)(ERR=12519)(ERROR_STACK=(ERROR=(CODE=12519)

(EMFI=4))))

tabase: java.sql.SQLException: Io exception: Connection refused(DESCRIPTION=(TMP=)(VSNNUM=153093632)(ERR=12519)(ERROR_STACK=(ERROR=(CODE=12519)(EMFI=4))))

2009-03-18 23:04:25,175 ERROR - 18/3/2009 23:4:25 || merx.toolkit.DBWithoutPooling:newConnection():Io exception: Connection refused(DESCRIPTION=(TMP=)(VSNNUM=153093632)(ERR=12519)(ERROR_STACK=(ERROR=(CODE=12519)(EMFI=4))))

2009-03-18 23:04:25,175 ERROR - 18/3/2009 23:4:25 || cl***merx.toolkit.DBWithoutPooling:newStatement()->merx.toolkit.DBException: Cannot connect to Database: java.sql.SQLException: Io exception: Connection refused(DESCRIPTION=(TMP=)(VSNNUM=153093632)(ERR=12519)(ERROR_STACK=(ERROR=(CODE=12519)(EMFI=4))))

2009-03-18 23:04:45,740 ERROR - 18/3/2009 23:4:45 || merx.toolkit.DBWithoutPooling:newConnection():Io exception: Connection refused(DESCRIPTION=(TMP=)(VSNNUM=153093632)(ERR=12519)(ERROR_STACK=(ERROR=(CODE=12519)(EMFI=4))))

2009-03-18 23:04:45,740 ERROR - 18/3/2009 23:4:45 || cl***merx.toolkit.DBWithoutPooling:newStatement()->merx.toolkit.DBException: Cannot connect to Database: java.sql.SQLException: Io exception: Connection refused(DESCRIPTION=(TMP=)(VSNNUM=153093632)(ERR=12519)(ERROR_STACK=(ERROR=(CODE=12519)(EMFI=4))))

2009-03-18 23:04:45,806 ERROR - 18/3/2009 23:4:45 || merx.toolkit.DBWithoutPooling:newConnection():Io exception: Connection refused(DESCRIPTION=(TMP=)(VSNNUM=153093632)(ERR=12519)(ERROR_STACK=(ERROR=(CODE=12519)(EMFI=4))))

I checked the listener.log and this is what it showed:

TNS-12519: TNS:no appropriate service handler found
18-MAR-2009 23:47:45 * (CONNECT_DATA=(SID=MYPRODDB)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=prodserv1)(PORT=42121)) * establish * MYPRODDB * 12519
TNS-12519: TNS:no appropriate service handler found
18-MAR-2009 23:47:45 * (CONNECT_DATA=(SID=MYPRODDB(CID=(PROGRAM=)(HOST=__jdbc__)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=prodserv1)(PORT=42122)) * establish * MYPRODDB * 12519
TNS-12519: TNS:no appropriate service handler found
18-MAR-2009 23:47:45 * (CONNECT_DATA=(SID=MYPRODDB)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=prodserv1)(PORT=42123)) * establish *

I did a status on the listener and this is what showed that the status is blocked:

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

So after doing some research, I found out that the database didn't register with the listener and that the processes parameter needed to be increased. So did the following two things:

SQL> alter system set processes=500 scope=spfile;

SQL> alter system register;

After i issued these two statements and checked the listener status I saw the following:

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

I hope this helps .

Remember, oracle states the following reasons that it maybe in that state:

There are normally 5 reasons for an instance to be blocked:

1.The remote_listener parameter is set in the init/spfile, but the local_listener is not running.
2.The instance is not fully mounted.
3.The instance is shutting down.
4.The maximum number of concurrent sessions has been reached.
5.The node load is at a maximum.

Thanks,

FranklinFaces.com - Oracle & SQL Server Database Forums for all IT Professionals / Oracle Forum / ORA - Oracle Database Error Codes - Search via specific error codes. / ORA-10560 Through ORA-19999 / ORA-12519: TNS:no appropriate service handler found tips / Latest Posts

ORA-12519: TNS:no appropriate service handler found tips