ORA-00020 error on ASM instance

On an Oracle Exadata machine, I found the below error in a trace file:

Tue Oct 09 22:01:09 2012

WARNING: ASM communication error: op 17 state 0x40 (20)

ERROR: slave communication error with ASM

WARNING: ASM communication error: op 18 state 0x40 (20)

ERROR: slave communication error with ASM

NOTE: Deferred communication with ASM instance

Errors in file /u01/app/oracle/diag/rdbms/abcuat/abcuat1/trace/abcuat1_mmon_6564.trc:

ORA-00020: maximum number of processes (100) exceeded

ORA-00020: maximum number of processes (100) exceeded

NOTE: deferred map free for map id 85

ORA-01565: Unable to open Spfile +DATA_EUX/abcuat/spfileabcuat.ora.

System State dumped to trace file /u01/app/oracle/diag/rdbms/abcuat/abcuat1/trace/abcuat1_mmon_6564.trc  ß5.5G  .trc file

Tue Oct 09 22:01:11 2012

WARNING: ASM communication error: op 18 state 0x40 (20)

ERROR: slave communication error with ASM

NOTE: Deferred communication with ASM instance

It is believed that ASM instance is not enough to serve the Oracle instances.  The original processes limit is only 100.  I want to increase it to 200.  The below steps are performed to fix the problem:

1. Login as root
2. run: su grid
3. run: sqlplus / as sysdba   (some page may say using: sqlplus / as sysasm)

[grid@xdb01 root]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Nov 23 20:47:24 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> show parameter processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
processes                            integer     100
SQL> select count(*) from v$process;
(currently, 76 processes are used.)

  COUNT(*)
----------
        76

SQL> alter system set processes=200 scope=spfile;

System altered.

(The below step is for shutdown the ASM.  Since I use Grid, instead shutdown and startup the ASM instance, I reboot each node.)

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

SQL> show parameter processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
processes                            integer     200

On the other node, the spfile should be updated automatically.  We can double check by:

select name, type , value  from V$SPPARAMETER where name = 'processes';

I am using Grid.  After reboot of the first node, I found the +ASM1 on node 1 is using the new parameter (200 processes).  +ASM2 at node 2 remains using 100 processes.

After reboot of node 2, all ASM instance are running using 200 processes.

During reboot of node 2, node 1 has 176 processes.

I have recheck the usage of processes:



SQL> select count(*) from v$process;

  COUNT(*)
----------
        93

SQL> select count(*) from v$process;

  COUNT(*)

----------

       125

To have a more safety margin, I will increase the limit further.

In the meanwhile, it is worth to double check if SESSIONS is of appropriate limit.  Otherwise, we will receive ORA-00018 error.


SQL> show parameter sessions

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sessions                             integer     336


SQL> select count(*) from v$session;

  COUNT(*)
----------
        63

We may refer to this Oracle document ( (1.1 * PROCESSES) + 5) and forum for a rule-of-thumb on how many sessions shall be allocated.

Reference: http://www.shutdownabort.com/errors/ORA-00020.php