Solved

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

Posted on 2009-05-11
8
12,164 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
Comment Utility
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
Comment Utility
.... 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
Comment Utility
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
 
LVL 1

Accepted Solution

by:
rbodepudi earned 150 total points
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:sunhux
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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

772 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now