ORA-00020 and Impact on database on increasing processes values


[oracle@server1 ~]$ sqlplus "sys/xxx as sysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Fri May 24 13:36:53 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
ERROR:
ORA-00020: maximum number of processes (30) exceeded

I usually check my alert logfile for any oracle errors and find following info .

Alert logfile
Fri May 24 13:38:30 2013
ORA-00020: No more process state objects available
ORA-20 errors will not be written to the alert log for
the next minute. Please look at trace files to see all
the ORA-20 errors.

According to oracle docs :

Error: ORA 20 
Text: maximum number of processes <num> exceeded
-------------------------------------------------------------------------------
Cause : An operation requested a resource that was unavailable. The maximum number of processes is specified by the initialization parameter PROCESSES. When this maximum is reached, no more requests are processed.

Action : Try the operation again in a few minutes. If this message occurs often, shut down Oracle, increase the PROCESSES parameter in the initialization parameter file, and restart Oracle.

Finally , i have decided to increase the no. of processes but didn't find any exact formula or any optimal value to set this parameter . So i have set it to 200 for now . Another issue here with us to connect with oracle, since we getting error while connecting with oracle . Here is one trick to create a session by using "Prelim" option . Interesting things about this option is that we can only use the "shut abort" command nothing else (AFAIK). Here are the steps to set the processes value :
[oracle@server1 ~]$ sqlplus -prelim "sys/xxxx as sysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Fri May 24 13:38:55 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.

SQL> shut immediate
ORA-01012: not logged on

SQL> shut abort
ORACLE instance shut down.

SQL> exit

Once the instance is down we can easily increase the process value at mount stage .

[oracle@server1 ~]$ sqlplus "sys/sys as sysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Fri May 24 13:51:40 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 418484224 bytes
Fixed Size 1336932 bytes
Variable Size 310380956 bytes
Database Buffers 100663296 bytes
Redo Buffers 6103040 bytes
Database mounted.

SQL> alter system set processes=200 scope=spfile;
System altered.

SQL> shut immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 418484224 bytes
Fixed Size 1336932 bytes
Variable Size 310380956 bytes
Database Buffers 100663296 bytes
Redo Buffers 6103040 bytes
Database mounted.
Database opened.

SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
ORCL READ WRITE
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: