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
No comments:
Post a Comment