This content has been marked as final. Show 8 replies
Merlin128 wrote:login before limit has been reached
nobody was able to log into the database. when I tried 'sqlplus / as sysdba' I got : ORA-00020: maximum number of processes 1000 exceeded
Number of processes tells how many os(operating system) processes created for session. Keeping 2000 should be better.
At the os level we can see this using ps -ef command.
As stated at OS level you can do a ps -ef|grep oracle<SID>|wc -l
Something like below. It is case SID is mydb
To find out the number of connections (server side processes) do
ps -ef | grep oraclemydb|grep -v grep oracle 10902 1 0 10:55 ? 00:00:00 oraclemydb (LOCAL=NO) oracle 10963 10962 0 10:57 ? 00:00:00 oraclemydb (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
In general if you are running oracle on UNIX, you should have a cron that runs once every 2 minutes etc and does the monitoring of Oracle processes at OS level piping the output to a file with a timestamp. A simple korn shell will do it.
ps -ef | grep oraclemydb|grep -v grep|wc -l 2
Hope this helps,
Instead of bouncing database , we could kill one or two "LOCAL=NO" process from OS then login as sysdba to troubleshoot further.
I've bounced the database and increased the processes...
my question is.. is there a way to tell what the processes were? what machine they were coming from? in a log somewhere..
Since I was unable to log in, I couldn't tell what had happened.. who was causing it..
if it happens again.. is there a way to log it?
also, this machine has DataGuard to a standby system.. is it possible for something to go wrong with dataguard and cause this?
You have only treated the symptom and not fixed the cause. As fellow members said, you could kill some "non-critical" processes and log in to the database.
But you will have to find out why there were so many processes in place. I am sure you might have set the definite number of processes after your planning before you went live. If you suddenly see too many processes, you will have to find out why and where they are coming from. Also check if there are any rogue processes.
To find out if there are any processes which have no sessions attached
select spid from v$process where addr not in (select paddr from v$session);
To answer your question, data guard shouldn't be affected by this. Nor would dataguard cause this issue in primary.
Fix this processes issue before it's too late. Otherwise, there will be a situation when you will have to keep increasing the processes until you hit the OS limit
fyi.. the database is on Windows 2008 x64..
so the unix process commands dont work..
I've been watching procmon and procexp but not sure what I am looking for.. there is only 2 oracle.exe processes..
SELECT MACHINE, COUNT(*) FROM V$SESSION GROUP BY MACHINE;
One, tedious, way to determine culprit would be to parse & count records within listener.log file
to determine from where the greatest number of connections originate.
Most of the time this is caused by some applications which handles the connection pool in a poor manner.
Find out the number of sessions by same username and machine. rather than bouncing the database, ask the application person to stop the apps and check teh sessions again. And kill them off, and you need to make sure there are no java processes sitting idle on the application server.
It's not a one line answer as you need to do the troubleshooting to pinpoint the exact reason!