Friday, August 5, 2011

ORA-00020: maximum number of processes () exceeded ORA-15055: unable to connect to ASM instance ORA-17503: ksfdopn:

If you face following type of error in alert log file and after that database is going to be hang...
and this problem is happen after some days or time

Errors in file /DB01/application1/oracle/admin/halimdb/bdump/halimdb_arc0_69854.trc:
ORA-00313: open failed for members of log group 4 of thread 1
ORA-00312: online log 4 thread 1: '+ASMDISK1/halimdb/redo04.log'
ORA-17503: ksfdopn:2 Failed to open file +ASMDISK1/halimdb/redo04.log
ORA-15055: unable to connect to ASM instance
ORA-00020: maximum number of processes () exceeded
ORA-15055: unable to connect to ASM instance
ORA-00020: maximum number of processes () exceeded
ORA-00312: online log 4 thread 1: '+ASMDISK1/halimdb/redo04.log'
ORA-17503: ksfdopn:2 Failed to open file +ASMDISK1/halimdb/redo04.log
ORA-15055: unable to connect to ASM instance
ORA-00020: maximum number of processes () exceeded
ORA-15055: unable to connect to ASM instance
ORA-00020: maximum number of processes () exceeded

cause is :-
========
The Oracle parameter PROCESSES has been exceeded as a result of multiple database instances connecting to the ASM instance. The default value (often 40) is insufficient to support more than one database connecting to the ASM instance


Then solution is .....
==============
[note:-sometime we do mistake only just checking database processes parameter value,
in this case we have to check ALL ASM instance processes parameter value ]


1) connect to the ASM instance one (if more than one instance and do it for all ASM instance)

2) check the processes parameter value of ASM instance

SQL> show parameter processes

3) check the resource limit of processes

sql> select * from v$resource_limit
where RESOURCE_NAME = 'processes';

if v$resource_limit.MAX_UTILIZATION is reach the processes parameter value then

4) increase the processes parameter value, like

SQL> alter system set processes=300 scope=spfile sid=’+ASM1';

[NOTE : as oracle says......
Processes = 25 + 15n, where n is the number of instances on the box using ASM for their storage.
This formula is for a basic instance and does not accomodate for
* Multiple ARCH processes
* Multiple LGWR processes
Should the ORA-20 occur even after implementing this formula ... add additional for any multiples of these background processes ]


5) restart (bounce) the database and ASM instance as well .

3 comments:

Anonymous said...

Nice Doc...

Regards,
Manish Nashikkar
India

s said...

what is the maximum value limit to increase process for 10.2.0.4

currently i have 1000 process
and 1105 sessions

santhi said...

Hi,

im unable to connect to sqlplus how can I increase the process limit. please suggest workaround.