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,