How to increase number of connections in Oracle

[bittus]

Hi,

Today morning my application (connected to oracle thru Jrun) stopped responding and I diagnosed the problem is with Oracle. I went into the SQL prompt and tried to stop the database, when I found the mesage :

ORA-00020: maximum number of processes (150) exceeded

Tried to stop oracle for so long and I failed. Since the situation went critical, I was forced to restart the whole system. I got an idea of the problem as the number of connections has exceeded the specified limit.

Oracle was not responding to my "shutdown" command too. I don't know what to be done on such a situation and hence I restarted the whole system, even though I knew that it shouldn't be done. Is there any other way to force shutdown oracle in such a situation ?

I googled and found some solutions to rectify this limitation - to increase the max. number of connections. I found a SQL statement that will increase the number of connections.

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

In fact, I dont know the consequences of this command. Also I dont know the consequences of increasing the number of connections. Can someone help me on this ?

Thanks

[vnktummala]

check the metalink document ...

https://metalink2.oracle.com/metalink/plsql/f?p=130:14:1398747120235482178:::14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,30794.1,1,1,1,he lvetica

Thanks,

[malay_biswal]

Is there any other way to force shutdown oracle in such a situation ?

Shutdown abort.

[bittus]

Sorry to say that I am not able to understand wat it is. I tried the link where it is asking for an IP address. I tried giving my db server's ip which it is not accepting.

[malay_biswal]

That link is a metalink site (oracle support). Anyway by increasing the value to 200 from 150 i do not see any issue. How ever you should also increase the session and transaction values proportionally.

[PAVB]

Sorry to say that I am not able to understand wat it is. I tried the link where it is asking for an IP address. I tried giving my db server's ip which it is not accepting.

IP Address?
Link points to Metalink and should be asking you for your User name and Password, which you should have providing you are running a licensed copy of Oracle.

Now... summarizing other posters:

1- Command would allow as many as 200 connections to your database.
2- If you are getting ORA-00020 you can do shutdown abort after login into the system as sysdba like...
sqlplus /nolog
conn sys as sysdba
shutdown abort
...if you don't want to go there you can always stop your listeners, kill all active sessions and do a normal shutdown.

[bittus]

Okay. Thanks for all the replies.

Is there any problem if i increase the value of "processes" entry in init.ora file ?

[PAVB]

Is there any problem if i increase the value of "processes" entry in init.ora file ?

Because of your initial post we supposed you were using spfile but, if you are using pfile you have to alter init.ora.

Could you please check your initialization method by running query below?

SELECT  instance_name,
        DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type" 
FROM    sys.v_$parameter ,
        v$instance
WHERE   name = 'spfile';

[bittus]

SELECT  instance_name,
        DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type" 
FROM    sys.v_$parameter ,
        v$instance
WHERE   name = 'spfile';

INSTANCE_NAME Init F
---------------- ------
gstar SPFILE

[/QUOTE]

[PAVB]

... that means you are not relying on init.ora file so, no reason to change it.