Maximum number of processes exceeded..

This means that you have too many database connections basically. Unfortunately since the demise of svrctl, it also means that you as a DBA cannot connect internal to do something about it!

You need to either ask a couple of users to log out, or you need to kill some sessions at the OS level. Once you managed to connect, see if you can spot anyone making abnormal numbers of connections. If you can't find anything you will need to increase the init parameter 'processes'. Unfortunately, this will require a restart of the database to take effect:

**********------------------------------------ **********

**********------------------------------------ **********

If you are using spfile: -

SQL> show parameter spfile;

SQL> show parameter processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
processes                            integer     150

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

COUNT(*)
----------
149

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

System altered.

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     300 

**********------------------------------------ **********

**********------------------------------------ ********** 

If you use pfile then  create spfile from pfile;

Shutdown db
then startup

then run 
SQL> alter system set PROCESSES= xx scope = spfile;


**********------------------------------------ **********

**********------------------------------------ **********
If you want to use pfile instead of spfile,than go to $ORACLE_HOME/dbs/


cp initSID.ora initSID.ora_org

Now Edit initSID.ora file

vi initSID.ora
Enter "i" (Without Quotes)

PROCESSES=XX
SESSIONS  2*XX

Press ESC

Then Type ":w"  (Without Quotes to save Value in initSID.ora file)

Then ":q!" (Without Quotes to exit from initSID.ora)

You may need to reboot db after change this parameter

SHUTDOWN DB

Start DB 

Note. The maximum allowed processes is one less than the init parameter 'processes'

Note. When connecting via TNS, you may get this error
when the database is out of processes:

ORA-12516: TNS:listener could not find available handler with matching protocol stack

 =========

Frist decide your system open wiht spfile or init file. You can understand it by using below query

show parameter spfile;

If you use spfile then issue:

alter system set PROCESSES= xx scope = spfile;

If you use pfile than 

create spfile from pfile;

Shutdown db

then startup
then run 

alter system set PROCESSES= xx scope = spfile;

If you want to use pfile instead of spfile,than go to $ORACLE_HOME/dbs/

cp initSID.ora initSID.ora_org

vi initSID.ora
PROCESSES=xxx

You may need to reboot db after change this parameter 

=============================

I assume you are using pfile. to check if you are using pfile or spfile , go to sql> / as sysdba show parameter spfile; If it shows none , means you are using pfile. Before making any changes in the init.ora file always make a copy for the same as init.ora.backup. Once that is done , then open your init.ora file , it would be by the name initSID.ora. Once you open that , then there will be a parameter process=150 or any value. Change it there and then , since you are using a pfile , you need to restart the database to bring the changes into effect. go to sql > / as sysdba shutdown immediate <enter> startup < enter> Then the new processes will be reflected in the database. If you are using spfile , then use the following command : alter system set processes=value scope=both; to check the value of the processes parameter : sqlplus "/ as sysdba" show parameter processes; ======== To Increase the number of process Step-1 Create pfile= /oracle/.... from spfile Step 2 Shutdown database Step-3 Amend that pfile and increase the number of processes Step -4 startup the database with new pfile startup pfile='/oracle/....' Step-5 Create spfile from pfile='/otacle/....' So that when you startup database next time it will read spfile (new)

Sukhwinder Singh

Apps DBA