Cron tab Scripts 

Concurret managers

Name  :   conc_mgr_chk.sh

a=`ps -ef | grep -c FNDLIBR`

if [ $a -le 12 ]; then

  echo  "Conc Mgrs are down. Please check. " |   mail  -s "Alert:Concurrent managers are down on Rackspace 8005 Instance"  sma@gmail.com

else

  echo  "conc Mngrs are up ."|  mail  -s "Alert:Concurrent managers are up"  fdgd@gm.com

tablespace check  :

email if tablespace reaches above 90%

tablespace.sh

sqlplus '/ as sysdba' << EOF

set feedback off echo off

set linesize 100

set pagesize 200

@/home/oracle/scripts/tablespace1.sql

spool off

exit

EOF

if [ `cat /home/oracle/scripts/tablespace.log|wc -l` -gt 0 ]

then

cat  /home/oracle/scripts/tablespace.log >> /home/oracle/scripts/mailcontent

cat /home/oracle/scripts/tablespace.log |mailx -s "PRODLIVE() : Tablespace with less than 10% free space"  internal_dba_notify@enrich.com

#rm -rf /u2/scripts/tablespace.log

#rm -rf /u2/scripts/mailcontent

fi

tablespace1.sql

Spool /home/oracle/scripts/tablespace.log

SELECT F.TABLESPACE_NAME,

TO_CHAR ((T.TOTAL_SPACE-F.FREE_SPACE),'999,999') "USED(MB)",

TO_CHAR (F.FREE_SPACE, '999,999') "FREE(MB)",

TO_CHAR (T.TOTAL_SPACE, '999,999') "TOTAL(MB)",

TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),'999')||'%' PER_FREE

FROM   (

SELECT       TABLESPACE_NAME,

ROUND (SUM (BLOCKS*(SELECT VALUE/1024

FROM V$PARAMETER

WHERE NAME = 'db_block_size')/1024)

) FREE_SPACE

FROM DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME

) F,

(

SELECT TABLESPACE_NAME,

ROUND (SUM (BYTES/1048576)) TOTAL_SPACE

FROM DBA_DATA_FILES

GROUP BY TABLESPACE_NAME

) T

WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME

AND (ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)) < 10;

spool off

invalids

inavlids.sh

for i in 6

do

RC=3

RC=`sqlplus -s <<EOF

system/manager

set heading off

set feedback off

set pagesize 0

set trimspool on

select count(*) from dba_objects where status='INVALID';

exit

EOF`

echo "$RC"

if [ $RC -ge $i ]

then

  echo  "INVALID objects  HAVE BEEN INCREASED. " |   mail -s "Alert:Inavlids have been increased in BTPROD Instance"  smanoj516@gmail.com

else

  echo  "INVALID objects ARE NORMAL."|  mail  -s "Alert:INVALID  OBJECTS STATUS OF BT PROD " smanoj516@gmail.com

fi

done

database status (dbstatus.sh):

for i in DEV

do

RC=FAILURE

RC=`sqlplus -s <<EOF

system/manager@"$i"

set heading off

set feedback off

set pagesize 0

set trimspool on

select instance_name from v\\$instance;

exit

EOF`

if [ "$RC" !=  "$i" ]

then

 echo "Instance $i is down" |  mail -s "ALERT: Instance $i is down " smanoj516@gmail.com

else

 echo "Instance $i is up" |  mail -s "ALERT: Instance $i is up and running " smanoj516@gmail.com

fi

done

Note : here DEV is the name of the instance,please provide proper userid and password

Disk  space in server 

ALERT=90

df -H | grep -vE '^Filesystem|tmpfs|cdrom' | awk '{ print $5 " " $1 }' | while read output;

do

  #echo $output

  usep=$(echo $output | awk '{ print $1}' | cut -d'%' -f1  )

  partition=$(echo $output | awk '{ print $2 }' )

  if [ $usep -ge $ALERT ]; then

    echo "BT PROD is Running out of space \"$partition ($usep%)\" on $(hostname) as on $(date)" |

     mail -s "Alert:BT PROD is runnig Almost out of disk space $usep" smanoj516@gmail.com

 fi

done

  echo "Disk space on BT PROD is normal" | mail -s "Alert:disk space on BT PROD" smanoj516@gmail.com

APPS user Status :( (APPSOPEN.sh)

for i in OPEN

do

RC=FAILURE

RC=`sqlplus -s <<EOF

system/manager

set heading off

set feedback off

set pagesize 0

set trimspool on

select ACCOUNT_STATUS from dba_users where username='APPS';

exit

EOF`

if [ "$RC" !=  "$i" ]

then

 echo "APPS user is locked" |  mail -s "ALERT: BT Prod APPS USER is LOCKED" smanoj516@gmail.com

else

 echo "APPS USER STATUS is OPEN" |  mail -s "ALERT: BT PROD APPS USER STATUS" smanoj516@gmail.com

fi

done

listener status in databaseL (lsnrctl.sh)

lsnrctl status deiv

if [ $? == 0 ]

then

echo "BT PROD Listener is UP and Running" |  mail -s "ALERT: BT PROD DB listener " smanoj516@gmail.com

else

 echo "Listener is down" |  mail -s "ALERT: BT Prod DB listener is Down " smanoj516@gmail.com

fi

Alert log script :

Alertlog.sh

cd /u01/oracle/PROD/db/tech_st/11.1.0/admin/DEV_ip-10-80-166-222/diag/rdbms/dev/DEV/trace/

tail -300 alert_DEV.log > mtcheck.log

grep ORA-  mtcheck.log > report.log

if [ `cat report.log|wc -l` -gt 0 ]

then

cat report.log | mail -s  "Alert: ORA errors occurred in BT PROD" smanoj516@gmail.com

else

echo  "No ORA errors in BT Prod" | mail -s  "Status: ORA errors status in BT PROD" smanoj516@gmail.com

fi

database locks /blocking sessions

(locks.h)

sqlplus '/ as sysdba'<<EOF

set heading off

set feedback on

set pagesize 0

set trimspool on

spool check.log

@check.sql

exit

EOF

if [ `cat check.log | wc -l` -gt 5 ]

then

 echo "LOCKS  IN  DATABASE" |  mail -s "ALERT: BT PROD instance contains locks" smanoj516@gmail.com

else

 echo "NO LOCKS IN DATABAE" |  mail -s "ALERT: BT PROD instance locks status" smanoj516@gmail.com

fi

(check.sql) select /*+ RULE */ lpad(' ',decode(a.request,0,0,4))||a.sid ssid,

       s.osuser,

       s.username,

       decode(a.lmode,

        0, 'None',           /* Mon Lock equivalent */

        1, 'Null',           /* N */

        2, 'Row-S (SS)',     /* L */

        3, 'Row-X (SX)',     /* R */

        4, 'Share',          /* S */

        5, 'S/Row-X (SSX)',  /* C */

        6, 'Exclusive',      /* X */

        to_char(a.lmode)) mode_held,

      decode(a.request,

        0, 'None',           /* Mon Lock equivalent */

        1, 'Null',           /* N */

        2, 'Row-S (SS)',     /* L */

        3, 'Row-X (SX)',     /* R */

        4, 'Share',          /* S */

        5, 'S/Row-X (SSX)',  /* C */

        6, 'Exclusive',      /* X */

        to_char(a.request)) mode_requested,

      c.name

 from sys.obj$ c,v$lock b,v$lock a,  v$session s

where a.id1 in ( select id1 from v$lock where lmode = 0 )

 and a.sid = b.sid

 and a.sid = s.sid

 and c.obj# = b.id1

 and b.type = 'TM'

order by a.id1,a.request,b.sid,c.name;

spool off