Monday, March 9, 2015

Dead Connection in Oracle, ORA-00020

Dead Connection in Oracle


Reason 1

There is an application running and connecting to the database . The connection against the database is failing.
A likely cause is that the application tries a connect using an incorrect username and password.

Failed connections from an application are creating dead processes on the database server.

This might lead to large number of process generated on the server side which would eventually cause
the error ORA-00020 for any new connections.

Further analysis you can see the many processes in the view V$PROCESS may exist without having a related session in the view V$SESSION, so that V$SESSION has much less entries compared to V$PROCESS.

At the  O/S level,  there are numerous processes being generated with a LOCAL=NO description.

Reason 2


When the application is not used for a significant amount of time, the firewall setting between database & application server closes physical connection between application & database.

This will invalidate the connection in connection pool.


Reason 3

These are previously valid connections with the database but the connection between the client and server processes has terminated abnormally.
Examples of a dead connection:
A user reboots/turns-off their machine without logging off or disconnecting from the database.
A network problem prevents communication between the client and the server.


What happens if above situation arise,

This will become major problem as stated below,
     * The database exhausts PROCESSES and gives ORA-20 maximum number of processes exceeded
     * The OS can become exhausted due to the unneeded resources consumed by such oracle processes

SYMPTOMS are,
     * The database view V$PROCESS will have no corresponding V$SESSION entry
ñ  An OS process / thread still exists for the SNIPED session

Immediate solution or Workaround

The solutions to this scenario can are to cleanup the OS processes ... after which the V$PROCESS entries should be removed automatically


Dead Connection Detection (DCD)
Dead Connection Detection (DCD) is a feature of SQL*Net 2.1 and later
DCD detects when a partner in a SQL*Net V2 client/server
or server/server connection has terminated unexpectedly, and flags the dead session
so PMON can release the resources associated with it.
How DCD works,

When DCD is enabled, Net8 (server-side) sends a packet to the client. If the client is active, the packet is discarded. If the client has terminated, the server will receive an error and Net8 (server-side) will end that session.

DCD is well explained on Oracle Metalink Note 151972.1


Solution
Enable Resource Limits & DCD.

See my Note and FAQs how to enable resource Limit.
DCD can be implemented by adding SQLNET.EXPIRE_TIME = to the sqlnet.ora file.

As per Oracle, It is strongly recommended that both DCD and Resource Limits with Profiles be implemented in order to clean up resources at both the database and OS level


FAQs

Q. Why dead connection happens?
Ans. Explained three scenarios above.

Q. What happen when we kill those inactive/dead connections?
Ans. Once kill, process in the database go away. PMON will release all the resources.

Here one thing need to understand that, The blocked session in all cases has to wait for the resources to be freed. If you alter system kill the session, they will be released after the session rolls back.
If the session died an unexpected death, it'll be after pmon does the same.

Q. How to kill session manually if situation arise like ora-00020 ?
Ans. Below are some methods you can use for some immediate remedy,

ALTER SYSTEM DISCONNECT SESSION ',' IMMEDIATE
OR
On Unix/Linux at OS level kill -9
OR
orakill  


Thanks & Regards,
Chandan Tanwani
Oracle Performance Tuning Certified Expert

TAG:- Dead Connection in Oracle, Dead Connection Detection (DCD), ora-00020

Post a Comment