Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2009-05-11
8
Medium Priority
?
13,793 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 960 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 960 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 960 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Accepted Solution

by:
rbodepudi earned 600 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 960 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 960 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

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…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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

730 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