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, a Georgia Tech graduate Senior Database Engineer/Data Architect based in Atlanta, USA, is an Oracle OCP DBA and Developer, Certified Cloud Architect Professional, and OCI Autonomous Database Specialist. With extensive expertise in database design, configuration, tuning, capacity planning, RAC, DG, scripting, Python, APEX, and PL/SQL, he combines technical mastery with a passion for innovation. Notably, Halim secured 16th place worldwide in PL/SQL Challenge Cup Playoff on the year 2010.
Friday, August 5, 2011
ORA-00020: maximum number of processes () exceeded ORA-15055: unable to connect to ASM instance ORA-17503: ksfdopn:
Labels:
ora
Subscribe to:
Post Comments (Atom)
My Blog List
-
-
-
ASSM states3 weeks ago
-
UKOUG Discover 20241 month ago
-
-
-
-
-
-
-
-
Moving Sideways8 years ago
-
-
Upcoming Events...11 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