How to increase number of connections in Oracle
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: How to increase number of connections in Oracle

  1. #1
    Join Date
    Oct 2008
    Posts
    5

    How to increase number of connections in Oracle

    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

  2. #2
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  3. #3
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    Is there any other way to force shutdown oracle in such a situation ?
    Shutdown abort.
    http://www.perf-engg.com
    A performance engineering forum

  4. #4
    Join Date
    Oct 2008
    Posts
    5
    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.

  5. #5
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    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.
    http://www.perf-engg.com
    A performance engineering forum

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by 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.
    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.
    Last edited by PAVB; 10-29-2008 at 06:52 AM.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  7. #7
    Join Date
    Oct 2008
    Posts
    5
    Okay. Thanks for all the replies.

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

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by bittus
    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?

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

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  9. #9
    Join Date
    Oct 2008
    Posts
    5
    Code:
    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]

  10. #10
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    ... that means you are not relying on init.ora file so, no reason to change it.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width