write a unix script in oracle for monitoring -- for # of sessions & minitoring table

Any particular reason you want a script to do this?  Oracle has the ability to send emails directly from the database.

You can create a stored procedure and have it execute every X minutes/hours/etc??? and let you know.

My databases email me everything I consider important every morning before I get up.

#2 might be a little difficult.  Unless you have auditing or a trigger on the table I'm thinking you would need to use something like logminer to get this information.

can u please tell me ,how can i do that by stored procedure ?

i need not write only by unix script.. i can write that in db also.

What is the version of Oracle (please include all 4 numbers like 10.2.0.4)?

10.2.0.3.0 -- oracle version

If the database server can see your systems mail server set the init paramter:

alter system set smtp_out_server=your.mail.server;

Then create the utl_mail package (as SYS):
@?/rdbms/admin/utlmail.sql
@?/rdbms/admin/prvtmail.plb
grant execute on utl_mail to myDatabaseUser;


Then it's just PL/SQL code to capture what is important to you and use dbms_schedular to kick it off as often as you wish.

Here's a hacked up example of what I use.  I think I have the user connection count correct but haven't tested it.

create or replace procedure check_db as

	sender_v		varchar2(50) := 'databaseServer@someServer.com';
	recipient_v	varchar2(50) := 'myusername@someServer.com';
	subj_v		varchar2(100);
	dbName		varchar2(50);
	difference	number;

	crlf		varchar2(2):= CHR( 13 ) || CHR( 10 );
	tmpStr		varchar2(4000);
	message_v		varchar2(32000);

begin

	select name into dbName from
	(
		select substr(global_name,0,instr(global_name,'.')-1) name from global_name where global_name like '%.%'
		union
		select global_name from global_name where global_name not like '%.%'
	) tempTab
	where name is not null;

	subj_v := subj_v || dbName || ' ';

	-- get bad users (count > 8)
	tmpStr := null;
	for i in ( select username,count(*) user_count from v$session where username is not null group by username having count(*) > 8) loop
		tmpSTR := tmpStr || i.username || ' - ' || i.user_count || crlf;
	end loop;
	if tmpStr is not null then
		tmpStr := 'Problem with users' || crlf || tmpStr;
	end if;
	message_v := message_v || tmpStr || crlf || crlf;
	tmpStr := null;

	-- set subject line
	subj_v := subj_v  || to_char(sysdate,'MM/DD/YYYY') || case instr(message_v,'PROBLEM') when 0 then ': OK' else ': PROBLEM' end;

	-- send it
	utl_mail.send(
		sender => sender_v,
		recipients => recipient_v,
		subject => subj_v,
		message => message_v
	);

	exception
	when others then
		utl_mail.send(
			sender => sender_v,
			recipients => recipient_v,
			subject => 'ERROR: ' || SQLCODE,
			message => SQLERRM
		);
end;
/

show errors

Select allOpen in new window

I have something like this for unix, if you want to use a shell script rather than a stored procedure let me know and I will post the script.

can u please post the script?

can u please post the unix script? I would like to see that one too.

Thanks a lot slightwv

This is the session count.  I will have to look around for the other one.  It uses a sql script and a shell script:

sql script:


connect / as sysdba
set heading off
set verify off
set feedback off
spool usercount.txt
select 'Sessions: '||count(1) from v$session;
spool off
exit;


SHell Script:


#!/usr/bin/ksh
cd /usr/oracle
export ORACLE_SID=orcl
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH
cd /home/oracle/bin
sqlplus /nolog @/home/oracle/bin/countprocs.sql
sessions=`grep : /home/oracle/bin/usercount.txt`
Numsessions=`echo "$sessions" | awk  '{print $2}'| tr -d ' '`
if [ Numsessions -gt 40 ]
then
  echo ' ' >> /tmp/usercount.txt
  echo 'Sessions on orcl are increasing.  Please check orcl load.' >> /tmp/usercount.txt
  echo $Numsessions >> /tmp/usercount.txt
  sendmail myname@gmail.com < /tmp/usercount.txt
  rm /tmp/usercount.txt
fi

in the stored procedure, i see

1.if the number of sessions for a user(say king) goes more than 8 : email DBA  -- this is done
2.in a particular table, if I dont see any transactions say for 1 hr: email DBA  --
can i please get this one ?

Do you have auditing turned on for the tables or any triggers to capture DML for them?

https://www.experts-exchange.com/questions/20628334/Last-DML-Time.html

There are some other ways but I don't think this will be what you want.  For example:

ALL_TAB_MODIFICATIONS

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_2097.htm#i1591024

As I noted, in my text, I will have to try and track down the one for transactions.  As Slightwv notes, you will have to have auditing turned on in some form to catch transactions, unless what you actually want is an idle timeout, which can be determined using v$process and v$session.

my boss wants unix script only. can anybody help me on this ?

As we said, we can identify idle sessions, but we cannot id sessions that have run transactions (ie, uid) separately from non transactional sessions unless auditing in some form is turned on.  We also need the Oracle version because the format of this information changes.

10.2.0.3.0 -- oracle version

i have attached our current script: basically this is using # Check sample_db database availability and email DBA if database is down
 
what my boss says is that .. just modify the current script to include the  
1.if the number of sessions for a user(say king) goes more than 8 : email DBA : --
NOTE : can i do something like ps -ef | grep <username> and count these at OS level.
in our db .. mostly only 1 or 2 users will be there not many. . we want to monitor the sessions of a user .. or else can the stored proc be included in cron or not ??

2.in a particular table, if I dont see any transactions say for 1 hr: email DBA   -- NOTE: this table has a column of sysdate( timestamp).. can i do anything say (sysdate - 1hr) to find .. whether table returns data or not ??  if the answer is no .. email DBA

THANKS A LOT FOR UR HELP ..FOR BOTH OF U.
current-script.txt

1:  We have already provided the SQL for that.  You just need to add it to the script.

>>NOTE: this table has a column of sysdate( timestamp)

Is that column updated if the row is updated or only populated on insert?

Is it a timestamp or date (the match to get hours between changes).

If the column is a timestamp then try this SQL:

select 'No records in last hour'
from (
      select max(col1) max_timestamp from tab1
)
where max_timestamp < systimestamp - interval '1' hour
/

SQL> desc trans_table
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------

 STORE_NUM                                 NOT NULL NUMBER(5)
 REGISTER_NUM                              NOT NULL CHAR(3)
 TRANS_NUM                                 NOT NULL NUMBER(38)
 TRANSACTION_TIME                          NOT NULL DATE      
 PREF_CUST_NUM                                      CHAR(20)
 TRANS_STATUS_CODE                                  NUMBER(38)
 TRANS_TOTAL                                        NUMBER(13,2)
 TRANS_TLOG                                         BLOB
 TRANS_EJ                                           BLOB
 TRANS_ADJ                                          BLOB
 DIVISION_ID                               NOT NULL VARCHAR2(10)

transaction time gives me the exact date at what time the transaction happened... this will get updated everytime a row is inserted or deleted.

OK, then just change my SQL to a date instead of timestamp:

select 'No records in last hour'
from (
      select max(col1) max_timestamp from tab1
)
where max_timestamp < sysdate - interval '1' hour
/

i modified the unix script to suit my environment, can u please modify or correct ??
script-please-correct.txt

Sorry but I don't have time to write your script for you.

You don't need a seperate SQL script file.  The original script you posted uses what is called a HERE script in UNIX ( the "<< !EOF" code).

I assume you need to create a new shell script using the one you posted as a sample.

I suggest you make the following changes:
$ORACLE_HOME/bin/sqlplus -s -L $DBUID << !EOF >> $FILE
set pagesize 0
set timing off
set feedback off
select username,count(*) user_count from v$session where username is not null group by username having count(*) > 8;
!EOF

#then check the contents of $FILE using something like wc -l
#if I remembered all the 'set' commands properly there should be nothing
#in $FILE if no rows are found.

where can i keep the above sql stmt given by u in the script..
i tested the sql stmt : i had some bad data in the table.. max(transaction_time) is giving date of

SQL> select max(TRANSACTION_TIME) max_timestamp from RTRNVAL.RETURNS_VAL_TRANS

MAX_TIMESTAMP
-------------------
12/08/6630 15:44:00

can we change the above sql stmt ???

thanks a lot slightwv: . i appreciate ur help.

>>can we change the above sql stmt ???

You can change it to whatever you want.  You just need to define the requirement.  Say, 'valid' data cannot be greater than NOW:

select max(TRANSACTION_TIME) max_timestamp from RTRNVAL.RETURNS_VAL_TRANS where transaction_time <= sysdate;

06/06/2100 11:29:00
04/21/2891 18:22:00
04/10/6001 16:13:00
06/06/6260 11:25:00
06/07/6440 17:01:00
06/07/6440 17:01:00
12/08/6630 15:44:00

these are the bad data in the table..how can i eliminate these results

got it.. thanks.
sorry, i did not see ur previous mail.

i am sending the output to a file,  may return data or "no rows selected" -- how can i complete this step
export DBLOG=`cat $FILE|egrep -i "ORA-|Errors|WARNING|date|time|"|wc -l` ??  

My unix is rusty but it depends on how you want to go.

If you want to check for "no rows found" it should go something like:

...
export DBLOG=`cat $FILE|egrep -i "no rows found"|wc -l`
if [[ $DBLOG -ne 1 ]]; then
...

Thu Apr  1 13:46:32 EDT 2010
./RTRN_USER_8.sh[31]: Checking if # of sessions for user gt 8 or not : syntax error

can u please look what is the error ... ??

v1-users-gt-8-dev.txt

What is the contents of:
/oracle/ALPD/admin/log/RTRN_USER_8_alert.log

cat RTRN_USER_8_alert.log
Checking if # of sessions for user gt 8 or not

I think $FILE gets appended… we may end up having values subsequently unless you clean it up every time…-- can u add a line in the script to clean that file?

if count(*) greater than 8 - no errors. and nothing returns to log also.. This is fine.
if I change the condition in the sql query to..  

select count(*) user_count from v\$session where username='RTRNVAL' having count(*) > 2;

 ./test.sh
Thu Apr  1 14:48:06 EDT 2010
./test.sh[28]: 4: is not an identifier

i see a number of 4 in the log.. also, as the number is4 . i did not get mail..

What is line 28?

add the following line after the export and before the if to check the value of the variable DBLOG.

export DBLOG= ...
echo I got $DBLOG
if [[ ...

I also don't see where ${DBA_EMAIL_LIST} is being set.

If that an environment variable created somewhere else?

./test1.sh
Thu Apr  1 15:00:14 EDT 2010
./test1.sh[28]: 4: is not an identifier
in the log . i see number 4 .. and the problem is i did not get mail..why is it so?

i attached the script i used just now..
current-script-0301pm.txt

cat .SETADMINLIST   -- this is in one folder ..
DBA_EMAIL_LIST="DBATeamList@gmail.com"; export DBA_EMAIL_LIST

>>./test1.sh[28]: 4: is not an identifier

You can't have a space before or after the '='.

export DBLOG=`cat $FILE`

I am done. Thanks a lot. slightwv:

can u please see this question: https://www.experts-exchange.com/questions/25650042/restore-prod-backup-in-dev-server-using-RMAN.html?fromWizard=true

Glad to help.

>>can u please see this question:

Sorry, I'm not an RMAN expert.  There are numerous links on how to clone using RMAN.  Just Google around.