After a lot of work today, i try to get some time to share very short finding which is very useful in order to prevent the max process on ASM instance.

There was an issue with one of the newly created Oracle GI 12c - 12.1.0.2 due to massive migration and ASM instance report error ORA-00020: maximum number of processes. This problem lead to a lot of unnecessary communication for downtime in order to fix ASM instance by adding more processes :)

But the question is.. what is the correct number of processes to set?

Well, as usual, Oracle Metalink is great tools for knowledge sharing and my first thing is to check for the possible formula on how to calculate a correct number of process based on the existing instances.

Here we GO, I find the correct Metalink Note - ORA-00020: Maximum Number Of Processes (500) Exceeded error after upgrade to 12c GI (Doc ID 2240835.1)

The calculation should be done on the instance and node level --> n is the number of database instances connecting to the Oracle ASM instance

Formulas:

For n < 10, PROCESSES = 50*n + 50
For n >= 10, PROCESSES = 10*n + 450

Example

I have 10 instances on one Cluster node

For 10 >= 10, PROCESSES = 10*10 + 450 = 550 PROCESSES

-In order to change the Process parameter you need full downtime of the node... hmm let check also memory allocation on ASM instance!

My strong recommendation is following Metalink Note -> ASM & Shared Pool (ORA-4031) (Doc ID 437924.1)

In a short form:

For ASM release 11.2.0.3/11.2.0.4/12.1 or before upgrade to ASM release 11.2.0.3/11.2.0.4/12.1, please follow the next recommendation:

Log in to ASM:

SQL> show parameter memory_target

If the value is smaller than 1536m, issue the following:

SQL> alter system set memory_max_target=4096m scope=spfile;
SQL> alter system set memory_target=1536m scope=spfile;

All the time make such calculation and ask questions in order to configure your system correctly and to avoid such an issue!

I wish you a great and wonderful night !!!

Please share your feedback