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 .
Halim is a Sr. Database Engineer/Data Architect (in Atlanta, USA) who is an Oracle certified (OCP) DBA, (OCP) Developer, Certified Cloud Architect Professional as well as OCI Autonomous DB specialist with extensive expertise in Database design, configuration, tuning, capacity planning, RAC, DG, Scripting, Python, PL/SQL etc. He achieved 16th position in worldwide first-ever PL/SQL Challenge cup playoff- http://plsql-challenge.blogspot.com/2010/07/winners-of-first-plsql-challenge.html
Subscribe to:
Post Comments (Atom)
My Blog List
-
-
-
4096 Columns1 week ago
-
-
-
-
-
-
-
Oracle Cloud & Third party tools4 years ago
-
-
-
Moving Sideways8 years ago
-
Upcoming Events...10 years ago
-
3 comments:
Nice Doc...
Regards,
Manish Nashikkar
India
what is the maximum value limit to increase process for 10.2.0.4
currently i have 1000 process
and 1105 sessions
Hi,
im unable to connect to sqlplus how can I increase the process limit. please suggest workaround.
Post a Comment