Solved

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

Posted on 2009-05-11
8
13,123 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
[X]
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
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying 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

Title # Comments Views Activity
Loading flat file data in tables 2 101
loop having error 5 45
error starting form builder in 11g 2 49
Oracle programming for starter 14 76
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Background Still having to process all these year-end "csv" files received from all these sources (including Government entities), sometimes we have the need to examine the contents due to data error, etc... As a "Unix" shop, our only readily …
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

752 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