| Need Help - processes exceeding [message #58587] |
Thu, 18 September 2003 21:17  |
S.Rajesh Kannan
Messages: 26
Registered: September 2003
|
Junior Member |
|
|
Hi all,
I have encountered "ORA-00020: maximum number of processes (150) exceeded" error message while connecting oracle. More than 150 oracle process are running like "(DESCRIPTION=(LOCAL= YES)(ADDRESS=(PROTOCOL=beq)))" in my Solaris OS. I increased 30% of values on shared_pool_size and log_buffer. Whenever i restart oracle, it was working fine. after 1 hour the oracle processes are keep on increasing. Please help me.
Thanks in advance,
S.Rajesh Kannan
|
|
|
|
| Re: Need Help - processes exceeding [message #58588 is a reply to message #58587] |
Fri, 19 September 2003 02:37  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Rajesh,
To buy you some time,You could increase PROCESSES init.ora parameter of this instance to a much higher value (say 500) ,assuming you have sufficient SEMMNS and SEMMSL settings.
You dont have to increase Shared_pool_size or Log_buffer unnecessarily,although process_array does reside in shared_pool.
Are you using Dedicated Servers or Shared Servers ?
What is your peak 'select count(*) from v$session;' ?
Did you check your cronjobs to make sure there are no scripts that keep spawning connections to the local database without disconnecting or abnormally terminating?
You say it keeps on increasing !? Check the maximum number of open file handles ( lsof ) and also open sockets ( netstat). Most likely your application/scripts locally (or remote even if it says LOCAL=YES and PROTOCOL=beq) are not closing the connections gracefully and they need to be fixed for a permanent soln. I have seen this happening more with Windows clients(PCs) that get shutdown abruptly.
Also try configuring DCD(ie Dead connection detection) at your Server side by setting
sqlnet.expire_time=10
in your $ORACLE_HOME/network/admin/sqlnet.ora .This makes Oracle to send a network packet every 10 minute to the client and when there is no response, the connection is deemed invalid and is terminated to free up resources.
If you are sure that these are not local connections, set automatic_ipc=off in all your client sqlnet.ora files and confirm with listener.log . If you are not going somewhere with troubleshooting ,enable sqlnet tracing to figure out whats going on.
Hope this helps
Thiru
|
|
|
|
| Re: Need Help - processes exceeding [message #58590 is a reply to message #58588] |
Fri, 19 September 2003 03:22 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Also do a ,
Select username,machine,program,count(*) from v$session group by username,machine,program;
at the peak traffic to show you who/what is contributing to the max number of connections.
If your V$session looks fine(ie count(*) well below 150) but your count(*) from v$process = 150 AND IF and ONLY IF you are using Dedicated servers, you can kill THOSE Server processes at Unix (again use this at your own risk), to avoid bouncing the instance.
Once you figure out what is constituting the abnormal number of connections(processes), you will be fixing it for a permanent soln.
Thiru
|
|
|
|
| Re: Need Help - processes exceeding [message #58666 is a reply to message #58590] |
Wed, 24 September 2003 22:10 |
S.Rajesh Kannan
Messages: 26
Registered: September 2003
|
Junior Member |
|
|
Great Thanks Thiru,
I am executing a perl script every minute through cron. I will make min 1 to max 10 oracle connection through $ORACLE_HOME/bin/sqlplus -s username/pwd < sqlscript. Is it correct way to execute sqlscript?. I think this is the problem. Please advice me.
Thanks
S.Rajesh Kannan
|
|
|
|
| Re: Need Help - processes exceeding [message #58670 is a reply to message #58666] |
Thu, 25 September 2003 07:02 |
Kartik Nair
Messages: 15
Registered: August 2003
|
Junior Member |
|
|
Ditto. I agree with Thiru ...
I had the same problem with some of the Java code running hay-wire. Increasing the number of processes in the init.ora file is only a temp solution.
So had the developers look at the mosntrous code ...
Good luck!
Kartik.
|
|
|
|
| Re: Need Help - processes exceeding [message #58672 is a reply to message #58666] |
Thu, 25 September 2003 07:37 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
No problem Rajesh. I remember making a typo in the crontab about 7 years back,that resulted in full database exports every minute !!
Executing a sql script every minute is not a problem as long as you make sure you terminate the session normally.
just curious, why are you making 10 connections in your cronjob ?
-Thiru
|
|
|
|
| Re: Need Help - processes exceeding [message #58694 is a reply to message #58672] |
Fri, 26 September 2003 02:01 |
S.Rajesh Kannan
Messages: 26
Registered: September 2003
|
Junior Member |
|
|
I am executing a sqlsctipt thru
" cat $SQL_PATH/script1.sql|$ORACLE_HOME/bin/sqlplus -s sys/syspasswd " in perl script. After getting the result i am storing in a text file. I am forking the processes up to 10 connections. Now also i am getting the same error after 1 hour after restarting oracle database. Earlier i used PERL DBI, DBD for oracle. The same error i received.
If i increased number of process in init.ora, i think i will get the same error after 1 hours.
Thanks
|
|
|
|
| Re: Need Help - processes exceeding [message #58704 is a reply to message #58694] |
Fri, 26 September 2003 08:06 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Try $ORACLE_HOME/bin/sqlplus -s sys/password@connect_string @$SQL_PATH/script1.sql
cat | is not the standard way of executing sql scripts.
Why are you forking processes for 10 connections ?
How are you getting the results? ie are you spooling and exiting ?
Increasing processes is just a temp soln I suggested.Permanent fix is to close the connections gracefully.
=Thiru
|
|
|
|
| Re: Need Help - processes exceeding [message #58736 is a reply to message #58704] |
Mon, 29 September 2003 04:07 |
S.Rajesh Kannan
Messages: 26
Registered: September 2003
|
Junior Member |
|
|
Hi Thiru,
I have observed the following error in the alert_sid.log in bdump directory.
Because of this, i am unable to enter sqlplus. The processes was keep on increasing.
start time of database : Mon Sep 29 12:15:30 2003
Mon Sep 29 13:41:30 2003
ORACLE Instance SID - Can not allocate log, archival required
Mon Sep 29 13:41:30 2003
ARCH: Connecting to console port...
Thread 1 cannot allocate new log, sequence 235
All online logs needed archiving
Current log# 1 seq# 234 mem# 0: REDO_LOG_PATH/redo03.log
How to rectify this?.
Thanks in Advance,
S.Rajesh Kannan.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
