Friday, February 28, 2014

Oracle ORA-12564 TNS:connection refused ( shared server )

If you get "ORA-12564: TNS:connection refused" errors from your database connections.
Reason is typically misspelling in tnsnames.ora file.

But if you are using shared server connections these errors can be seen also if you have not enough dispatchers or your dispatcher max session limit is reached.

Usually with ORA-12564 you can also find these kind of errors in your listener logs:
"TNS-12520: TNS:listener could not find available handler for requested type of server"

This way you can check your connections via listener (this shows both dedicated and dispatcher connections):
lsnrctl services

With following sql you can check your dispatcher settings (these settings can be changed with ALTER SYSTEM SET dispatchers= ... commands):
SQL> select * from V$DISPATCHER;
SQL> select * from V$DISPATCHER_CONFIG;


If you does not get these errors in normal usage but only in occasionally then you also might want to check which users are doing most connections when this problem is on. You can do it with this sql (this uses RAC gv$session view so it shows cluster all instances connections. If you want only one instance connections you can use v$session):
SQL>  select INST_ID, USERNAME, count(SID) from gv$session group by USERNAME, INST_ID order by count(SID);

1 comment: