Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2009-05-11
8
12,809 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
0
Comment
Question by:sunhux
  • 5
  • 2
8 Comments
 
LVL 68

Assisted Solution

by:woolmilkporc
woolmilkporc earned 240 total points
ID: 24356952
Just to give you an idea how it could be done:

#!/bin/ksh
sqlplus -s /nolog <<EOF 1> /tmp/sesspro.$$
      connect user/pass@database
      set serveroutput on
      set hea off pagesize 0 feedback off
      select "processes", count(*) from v$process;
      select "sessions", count(*) from v$session;
      quit
EOF

cat  /tmp/sesspro.$$  | while read line
  do
    set $line
    [ $1 == "processes" -a $2 -gt 80 ] && echo "Too much processes" | mail -s "DB alert" recipient@domain.tld
    [ $1 == "sessions" -a $2 -gt 80 ] && echo "Too much sessions" | mail -s "DB alert" recipient@domain.tld
  done

rm /tmp/sesspro.$$
 
 exit

I've not the time to test it right now, so please be careful!

wmp

 

0
 
LVL 68

Assisted Solution

by:woolmilkporc
woolmilkporc earned 240 total points
ID: 24357014
.... sorry, didn't see that you are talking about 80 % , not 80 sessions/processes! Will try to work it out later.
0
 
LVL 68

Assisted Solution

by:woolmilkporc
woolmilkporc earned 240 total points
ID: 24358233
OK, I'll attach a more serious try now.

Note that a normal ksh has only integer arithmetics, so results are rounded.

Note further that, if run every minute, you will receive lots of mails once the limit gets hit.
The other day I wrote a script here at EE which dealt with such a problem. Here is the link:

http://www.experts-exchange.com/Database/Oracle/Q_24248161.html#23939343

Maybe you can incorporate it by yourself.

I didn't include very meaningful email messages. You should tailor that according to your needs.

As for cron, to get a reliable environment, the script should be started from root's crontab with a 'su -' to the appropriate DB userid whose profile contains the required settings.

* * * * * su - dbuser -c /path/to/script >/dev/null 2>&1

Should you decide to run it from dbuser's crontab, you must set all needed ORACLE variables, such as ORACLE_HOME . ORACLE_SID etc. in the script.

Escaping the '$' signs in the 'select' statements ('\$') is very important. Leave as is!

Good luck!

wmp

#!/bin/ksh
SESSWARN=80
PROCWARN=80
MAILTO=recipient@domain.tld
TEMPFILE=/tmp/spwarn.$$
USER=dbuser
PASS=dbpass
$ORACLE_HOME/bin/sqlplus $USER/$PASS@$ORACLE_SID << EOF > /dev/null
set serveroutput off echo off term off wrap off
set hea 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
 
[ $(( sessions * 100 / maxsess )) -gt $SESSWARN ] && echo "$ORACLE_SID Sessions Warning" | /usr/bin/mailx -s "DB Alert" $MAILTO
[ $(( processes * 100 / maxproc )) -gt $PROCWARN ] && echo "$ORACLE_SID Processes Warning" | /usr/bin/mailx -s "DB Alert" $MAILTO
 
/usr/bin/rm $TEMPFILE
 
exit

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Accepted Solution

by:
rbodepudi earned 150 total points
ID: 24418513
You can use this script to get the processes grouped by username...

select qcsid SID, username, totl_par_procs_in_use, reqstd_degree, obtaind_degree, par_proc_cnt_for_sessn,
       currtime, sqltext
from (
select qcsid, username, totl_par_procs_in_use, reqstd_degree, obtaind_degree, par_proc_cnt_for_sessn,
       currtime, num_active,qnum_active, sqltext
from (
select s.qcsid,ses.username,sys.value totl_par_procs_in_use,s.req_degree reqstd_degree,s.degree Obtaind_degree,
       count(*) par_proc_cnt_for_sessn,sum(decode(ses.status,'ACTIVE',1,0)) num_active,
sum(decode(qses.status,'ACTIVE',1,0)) qnum_active,
       to_char(sysdate,'HH24:MI:SS') currtime,coalesce(sql.sql_text,sqlt.sql_text,ses.action) sqltext
from v$px_session s
  inner join v$px_process_sysstat sys
      on s.qcsid <> s.sid
      and sys.statistic like 'Servers In Use%'
  inner join v$session ses
      on s.sid=ses.sid
      and s.serial#=ses.serial#
  inner join v$px_process p
      on p.sid = ses.sid
      and p.serial# = ses.serial#
  left outer join v$sql sql
      on ses.sql_address = sql.address
      and ses.sql_hash_value = sql.hash_value
      and sql.child_number=0
  left outer join v$session qses
      on s.qcsid=qses.sid
      and s.qcserial#=qses.serial#
  left outer join v$sqltext sqlt
      on qses.sql_address = sqlt.address
      and qses.sql_hash_value = sqlt.hash_value
      and sqlt.piece=0
group by s.qcsid,s.degree,s.req_degree,coalesce(sql.sql_text,sqlt.sql_text,ses.action),sys.value,
ses.username
) AA
union all
select a.SID,a.username,null,null,null,0,to_char(sysdate,'HH24:MI:SS'),null,null,
  coalesce(b.sql_text, a.action, a.module) SQL_TEXT
from v$session a,v$sql b
where a.status='ACTIVE' and a.sql_address=b.address and a.sql_hash_value=b.hash_value
and a.sid not in (select sid from v$px_session)
--and b.piece=0
) BB order by qcsid

0
 

Author Comment

by:sunhux
ID: 24449009
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
0
 

Author Comment

by:sunhux
ID: 24449122
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
0
 
LVL 68

Assisted Solution

by:woolmilkporc
woolmilkporc earned 240 total points
ID: 24449141
Hi again,
yes, you are on the right track.
To login as sysdba, the sqlplus call should look like this -

USER="sysdba"
$ORACLE_HOME/bin/sqlplus / as $USER << EOF > /dev/null  
Note that in this case you'll have to be logged in on UNIX using a userid authorized to issue "sqlplus / as sysdba".

I guess you did something like the following already -
for testing you should comment out the 'mailx' lines and echo the values instead, maybe like this -

#[ $(( sessions * 100 / maxsess )) -gt $SESSWARN ] && echo "$ORACLE_SID Sessions Warning" | mailx -s "DB Alert" $MAILTO
#[ $(( processes * 100 / maxproc )) -gt $PROCWARN ] && echo "$ORACLE_SID Processes Warning" | mailx -s "DB Alert" $MAILTO
echo "% sessions used" $(( sessions * 100 / maxsess ))
echo "% processes used" $(( processes * 100 / maxproc ))

wmp
0
 
LVL 68

Assisted Solution

by:woolmilkporc
woolmilkporc earned 240 total points
ID: 24449178
Uh, ksh and ksh aren't always the same, it seems.
Use single '=' instead of double '=='.
As I'm a bit used to C programming, I prefer the '==' (where allowed, of course).
wmp
 
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question