[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 405
  • Last Modified:

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

i am planning to write an unix script:
1.if the number of sessions for a user(say king) goes more than 8 : email DBA
2.in a particular table, if I dont see any transactions say for 1 hr: email DBA

can any body help me on this ?  
0
ramavenkatesa
Asked:
ramavenkatesa
  • 21
  • 16
  • 4
1 Solution
 
slightwv (䄆 Netminder) Commented:
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.
0
 
slightwv (䄆 Netminder) Commented:
#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.
0
 
ramavenkatesaAuthor Commented:
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.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
slightwv (䄆 Netminder) Commented:
What is the version of Oracle (please include all 4 numbers like 10.2.0.4)?
0
 
ramavenkatesaAuthor Commented:
10.2.0.3.0 -- oracle version
0
 
slightwv (䄆 Netminder) Commented:
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

Open in new window

0
 
johnsmith1962Commented:
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.
0
 
ramavenkatesaAuthor Commented:
can u please post the script?
0
 
ramavenkatesaAuthor Commented:
can u please post the unix script? I would like to see that one too.

Thanks a lot slightwv
0
 
johnsmith1962Commented:
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
0
 
ramavenkatesaAuthor Commented:
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 ?
0
 
slightwv (䄆 Netminder) Commented:
Do you have auditing turned on for the tables or any triggers to capture DML for them?

http://www.experts-exchange.com/Database/Oracle/Q_20628334.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
0
 
johnsmith1962Commented:
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.
0
 
ramavenkatesaAuthor Commented:
my boss wants unix script only. can anybody help me on this ?
0
 
johnsmith1962Commented:
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.
0
 
ramavenkatesaAuthor Commented:
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
0
 
slightwv (䄆 Netminder) Commented:
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
/


0
 
ramavenkatesaAuthor Commented:

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.
0
 
slightwv (䄆 Netminder) Commented:
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
/
0
 
ramavenkatesaAuthor Commented:
i modified the unix script to suit my environment, can u please modify or correct ??
script-please-correct.txt
0
 
slightwv (䄆 Netminder) Commented:
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.
0
 
ramavenkatesaAuthor Commented:
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 ???
0
 
ramavenkatesaAuthor Commented:
thanks a lot slightwv: . i appreciate ur help.
0
 
slightwv (䄆 Netminder) Commented:
>>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;
0
 
ramavenkatesaAuthor Commented:

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
0
 
ramavenkatesaAuthor Commented:
got it.. thanks.
sorry, i did not see ur previous mail.
0
 
ramavenkatesaAuthor Commented:
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` ??  

0
 
slightwv (䄆 Netminder) Commented:
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
...
0
 
ramavenkatesaAuthor Commented:
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
0
 
slightwv (䄆 Netminder) Commented:
What is the contents of:
/oracle/ALPD/admin/log/RTRN_USER_8_alert.log
0
 
ramavenkatesaAuthor Commented:
cat RTRN_USER_8_alert.log
Checking if # of sessions for user gt 8 or not
0
 
ramavenkatesaAuthor Commented:
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?
0
 
slightwv (䄆 Netminder) Commented:
>>echo "Checking if # of sessions for user gt 8 or not " > $FILE

Sorry. I missed this line.

>>I think $FILE gets appended…

Nope.  a single redirect '>' truncates.

>>export DBLOG=`cat $FILE`

Think about what this is doing.  It is setting the variable DBFILE equal to the contents of $FILE.

Then  "if [[ $DBLOG -gt 0 ]]; then" checks if the value of DBLOG is greater than 0.

A string "Checking..." cannot be compard to a number.


Get rid of:
echo "Checking if # of sessions for user gt 8 or not " > $FILE

and make the following change:
$ORACLE_HOME/bin/sqlplus -s -L $DBUID << !EOF > $FILE
0
 
ramavenkatesaAuthor Commented:
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..
0
 
slightwv (䄆 Netminder) Commented:
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 [[ ...
0
 
slightwv (䄆 Netminder) Commented:
I also don't see where ${DBA_EMAIL_LIST} is being set.

If that an environment variable created somewhere else?
0
 
ramavenkatesaAuthor Commented:
./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
0
 
ramavenkatesaAuthor Commented:
cat .SETADMINLIST   -- this is in one folder ..
DBA_EMAIL_LIST="DBATeamList@gmail.com"; export DBA_EMAIL_LIST
0
 
slightwv (䄆 Netminder) Commented:
>>./test1.sh[28]: 4: is not an identifier

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

export DBLOG=`cat $FILE`

0
 
ramavenkatesaAuthor Commented:
I am done. Thanks a lot. slightwv:

can u please see this question: http://www.experts-exchange.com/Database/Oracle/Q_25650042.html?fromWizard=true
0
 
slightwv (䄆 Netminder) Commented:
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.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 21
  • 16
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now