Reflection on ORA-00020

Reflection on ORA-00020

Check the alert Log in the production environment today and find the following error. This error does not need to be explained too much. Obviously, this is because of the classic problem of session leak.

ORA-00020: maximum number of processes 5000 exceeded
ORA-20 errors will not be written to the alert log
The next minute. Please look at trace files to see all
The ORA-20 errors.
Fri Sep 19 12:39:38 2014
Process W001 submission faile

The analysis of this problem is to view the corresponding awr report. You can get some information.
If you can locate some obvious problems, it will be smooth. If not, you have to narrow down the time range. See the ash report to find more information.

Snap Id Snap Time Sessions Curs/Sess
---------------------------------------------
Begin Snap: 14866 19-Sep-14 12:00:13 3,926 3.6
End Snap: 14867 19-Sep-14 13:00:17 2,693 6.7
Elapsed: 60.07 (mins)
DB Time: 4,202.97 (mins)

Although the ash Report provides a lot of effective troubleshooting ideas, it is powerless to monitor sessions.
In addition, some information about the active session is obtained in ASH. For example, if the application has a problem and the connection is not released in time, these sessions are in inactive state and will not be reflected in the ash report.
In the awr report, if the time of two snapshots is short enough, the session information may be detailed. However, the session information changes in real time, and the interval between snapshots is too short.
In this case, it is recommended that you write your own scripts to monitor sessions. Although a lot of historical information can be found in Oracle, oracle cannot meet many of our requirements as needed.
Some Molecular archives are also complementary to oracle, and can be used to troubleshoot problems more quickly and effectively.
For example, the above ora error is usually followed by several questions from a general perspective.
What is the general session situation in the production environment.
The frequency at which this problem occurs.
How to prevent it.

Without some data support, the leaders are most afraid to ask this question. However, once you have your own archive and are not fully dependent on the database, you will get a lot of extra gains.
At this time, a simple and clear icon can make the leaders clear at a glance.

The above is a simple part, and the data is clear at a glance. The number of sessions in a few days is very low because of the upgrade. Then the number of sessions starts to shake. The problem can be detected in a timely manner.
We have to coordinate with the Development Department for how to prevent the attacks, but from the dba perspective, we can provide sufficient information and support.