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);

Thursday, February 27, 2014

Oracle "opidcl aborting process unknown ospid (xxx) as a result of ORA-28" error.

If you get following ORA errors in your database alert log:
"opidcl aborting process unknown ospid (xxxx) as a result of ORA-28"

This usually means that some privileged user (dba) has killed sessions from database.

But if you see these errors a lot or all the time then this can be bug. This bug is affected
older (Oracle 11.1.0.6 and 11.1.0.7) versions. With bug error messages look like this:
"ORA-28 : opiodr aborting process unknown ospid (xxxxx_xxxxxxxxxxx) "

NOTE! If some application or user kill several (for example all one user) sessions at the same time there can be several errors in a row in alert log and it is still a normal situation.