sunhux
asked on
Shell / sql script to check Oracle current number of processes used : ORA-00020 00018 exceeded
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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
# $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.
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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