Home » Infrastructure » Unix » very simple Oracle monitoring shell script (not replacement for Grid Control) (Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit)
very simple Oracle monitoring shell script (not replacement for Grid Control) [message #509216] Thu, 26 May 2011 09:12 Go to next message
oranooob
Messages: 88
Registered: May 2009
Member
Hi

Our Oracle DBA's are monitoring Oracle with Grid Control. Sadly they look at the alerts too late.

I am not a DBA and wish to implement a very simple shell script for basic DB monitoring. At the moment I am doing a simple SQL statement and if there is an Oracle error it will generate a mail.

How can I improve this script? What basic additional checks are reasonable?

The DB is on a remote host where I have no access (only unprivileged DB user with SQL*Plus)

check_ora ()
{
sqlplus -S $USER/$PASSWD@$ORACLE_SID <<EOF > $BASE_P/out/state.P43LL
 
set echo Off
set term On
set pages 0
set head off
set ver off
set feed off
set trims on
set linesize 200
 
SELECT COUNT (*)
  FROM audit_trail;
 
exit
EOF
 
grep 'ORA-.....' $PFAD_MON/out/state.P43LL
	if [ $? = 0 ]; then
	cat $BASE_P/body.txt | mailx -r xxx@xxx.xx -s "DB Error, please make a session with SQL*Plus" xxx@xxx.xx xxx@xxx.xx xxx@xxx.xx
	fi
}

Re: very simple Oracle monitoring shell script (not replacement for Grid Control) [message #509218 is a reply to message #509216] Thu, 26 May 2011 09:40 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10626
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
To do anything more in DB level ( not just in your schema), you need more privs. And it depends on what exactly you want to monitor.
The posted script will check for availability of database (testing the listener, instance and check if the database is open to public).
I myself use a similar script that will just ping many databases through a cronjob. Works reliably for as long as I can remember Smile

Edit:
And I would not check audit trail. Just connect and select something a custom table or dual.
If it works, the database is up and available.


[Updated on: Thu, 26 May 2011 09:43]

Report message to a moderator

Re: very simple Oracle monitoring shell script (not replacement for Grid Control) [message #509238 is a reply to message #509218] Thu, 26 May 2011 10:26 Go to previous messageGo to next message
Michel Cadot
Messages: 59145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
...And you will find many of this kind of scripts on the web.

Regards
Michel
Re: very simple Oracle monitoring shell script (not replacement for Grid Control) [message #509246 is a reply to message #509238] Thu, 26 May 2011 10:57 Go to previous message
BlackSwan
Messages: 22790
Registered: January 2009
Senior Member
Or just look on this site

http://www.orafaq.com/wiki/Scripts
Previous Topic: Command to find out a text in a file
Next Topic: Problems with sysdba and env params
Goto Forum:
  


Current Time: Sat Sep 20 05:08:21 CDT 2014

Total time taken to generate the page: 1.04511 seconds