We help IT Professionals succeed at work.

Shell / sql script to check Oracle current number of processes used : ORA-00020 00018 exceeded

sunhux
sunhux asked
on
17,682 Views
Last Modified: 2013-12-21
I recently ran into the following 2 issues :
ORA-00020: maximum number of processes (%s) exceeded
ORA-00018: maximum number of sessions (%s) exceeded

Following link described the problem :
http://ora-00020.ora-code.com/msg/40800.html

I can't recall what one of my DBA colleague taught me to do over the phone (I'm no DBA) :
alter ... spfile=500  ( to increase from current 300 processes to 500 )  & then followed by
command below (perhaps I'm missing one more command)  to verify the value have been
changed :
    show parameter session

My management asks : how can I monitor if we'll run close to exceeding these resource/parameters again;  so I'll need a way to monitor if the number of SGA
processes and sessions came close to being exceeded again.

So I'll need a script that I can put into crontab (to run every minute) to do the following :
select count(*) from v$process;
if the above hits 80% of the current set max limit, email to notify me
select count(*) from v$session;
if the above hits 80% of the current set max limit, email to notify me

I can't figure out how to embed  the above sql commands in a Shell
script.  I'm running on HP-UX B11.13 on PA-RISC
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks rbode, wmp.


Hi wmp,

Can you help look into the following problem when I tried out your script
(has this got to do with me using "sysdba" as Db login id that has no password?) :

$ ksh -v chkoraprocsesn.sh
#!/bin/ksh
SESSWARN=65
PROCWARN=65
MAILTO=victor_lim@sla.gov.sg,goh_pit_ong@sla.gov.sg
TEMPFILE=/tmp/spwarn.$$
USER="sysdba"
PASS=""
$ORACLE_HOME/bin/sqlplus $USER/$PASS@$ORACLE_SID << EOF > /dev/null
set serveroutput off echo off term off wrap off
set head off pagesize 0 linesize 0 feedback off
spool $TEMPFILE
select '@@sessions', count(*) from v\$session;
select '@@processes', count(*) from v\$process;
select '@@maxsess', value from v\$parameter where name='sessions';
select '@@maxproc', value from v\$parameter where name='processes';
quit
EOF

/usr/bin/cat $TEMPFILE | /usr/bin/grep @@ | while read line
  do
   set $line
   [ $1 == "@@sessions" ] && sessions=$2
   [ $1 == "@@processes" ] && processes=$2
   [ $1 == "@@maxsess" ] && maxsess=$2
   [ $1 == "@@maxproc" ] && maxproc=$2
  done
cat: Cannot open /tmp/spwarn.14764: No such file or directory

[ $(( sessions * 100 / maxsess )) -gt $SESSWARN ] && echo "$ORACLE_SID Sessions Warning" | /usr/bin/mailx -s "DB Alert" $MAILTO
chkoraprocsesn.sh[28]:  sessions * 100 / maxsess : bad number

Author

Commented:
I've managed to address that sqlplus login issue as follows :
 # $ORACLE_HOME/bin/sqlplus $USER/$PASS@$ORACLE_SID << EOF > /dev/null
  sqlplus '/ as sysdba' << EOF > /dev/null     <== this one managed to login


Now got a different error :

$ ksh -v chkoraprocsesn.sh
#!/bin/ksh
SESSWARN=65
PROCWARN=65
MAILTO=victor_lim@sla.gov.sg,goh_pit_ong@sla.gov.sg
TEMPFILE=/tmp/spwarn.$$
USER="sysdba"
PASS=""
sqlplus '/ as sysdba' << EOF > /dev/null
# $ORACLE_HOME/bin/sqlplus $USER/$PASS@$ORACLE_SID << EOF > /dev/null
set serveroutput off echo off term off wrap off
set head off pagesize 0 linesize 0 feedback off
spool $TEMPFILE
select '@@sessions', count(*) from v\$session;
select '@@processes', count(*) from v\$process;
select '@@maxsess', value from v\$parameter where name='sessions';
select '@@maxproc', value from v\$parameter where name='processes';
quit
EOF

/usr/bin/cat $TEMPFILE | /usr/bin/grep @@ | while read line
  do
   set $line
   [ $1 == "@@sessions" ] && sessions=$2
   [ $1 == "@@processes" ] && processes=$2
   [ $1 == "@@maxsess" ] && maxsess=$2
   [ $1 == "@@maxproc" ] && maxproc=$2
  done
chkoraprocsesn.sh[23]: ==: unknown test operator
chkoraprocsesn.sh[24]: ==: unknown test operator
chkoraprocsesn.sh[25]: ==: unknown test operator
chkoraprocsesn.sh[26]: ==: unknown test operator
chkoraprocsesn.sh[23]: ==: unknown test operator
chkoraprocsesn.sh[24]: ==: unknown test operator
chkoraprocsesn.sh[25]: ==: unknown test operator
chkoraprocsesn.sh[26]: ==: unknown test operator
chkoraprocsesn.sh[23]: ==: unknown test operator
chkoraprocsesn.sh[24]: ==: unknown test operator
chkoraprocsesn.sh[25]: ==: unknown test operator
chkoraprocsesn.sh[26]: ==: unknown test operator
chkoraprocsesn.sh[23]: ==: unknown test operator
chkoraprocsesn.sh[24]: ==: unknown test operator
chkoraprocsesn.sh[25]: ==: unknown test operator
chkoraprocsesn.sh[26]: ==: unknown test operator
chkoraprocsesn.sh[23]: ==: unknown test operator
chkoraprocsesn.sh[24]: ==: unknown test operator
chkoraprocsesn.sh[25]: ==: unknown test operator
chkoraprocsesn.sh[26]: ==: unknown test operator
chkoraprocsesn.sh[23]: ==: unknown test operator
chkoraprocsesn.sh[24]: ==: unknown test operator
chkoraprocsesn.sh[25]: ==: unknown test operator
chkoraprocsesn.sh[26]: ==: unknown test operator
chkoraprocsesn.sh[23]: ==: unknown test operator
chkoraprocsesn.sh[24]: ==: unknown test operator
chkoraprocsesn.sh[25]: ==: unknown test operator
chkoraprocsesn.sh[26]: ==: unknown test operator
chkoraprocsesn.sh[23]: ==: unknown test operator
chkoraprocsesn.sh[24]: ==: unknown test operator
chkoraprocsesn.sh[25]: ==: unknown test operator
chkoraprocsesn.sh[26]: ==: unknown test operator

[ $(( sessions * 100 / maxsess )) -gt $SESSWARN ] && echo "$ORACLE_SID Sessions Warning" | /usr/bin/mailx -s "DB Alert" $MAILTO
chkoraprocsesn.sh[29]:  sessions * 100 / maxsess : bad number
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.