ORACLE及OS相關監控指令碼 For AIX6.1

OS check for AIX6.1
-----------------------------
#Collect NMON Data
5 0 * * * /worktmp/nmon/nmon.ksh //透過nmon工具進行系統效能監控

#!/bin/ksh
cd /worktmp/nmon/
nmon -s 300 -c 287 -F /worktmp/nmon/daily_`hostname`_`date +"%Y%m%d"`.nmon -f -d -t
exit 0

[@more@]

0,15,30,45 * * * * /root_cmd/checkdiskspace.sh > /dev/null 2>&1 //監控磁碟的使用
#!/bin/sh
LOG_FILE=/tmp/d_log`date +%Y%m%d`
echo >>$LOG_FILE
echo "***************************************** check disk space *****************************************">>$LOG_FILE
df -m >$LOG_FILE
df -m |grep -v proc |grep -v Filesystem |awk '{x=1*$4}{print $1","$2","$3","$4","$5","$6","$7}'>$TEMP_PATH/disk_info
cat $TEMP_PATH/disk_info | grep -v '^#' | while read line
do
item1=$(echo $line | awk -F ',' '{print $1}')
item2=$(echo $line | awk -F ',' '{print $2}')
item3=$(echo $line | awk -F ',' '{print $3}')
item4=$(echo $line | awk -F ',' '{print $4}' |awk -F '%' '{print $1}')
item5=$(echo $line | awk -F ',' '{print $5}')
item6=$(echo $line | awk -F ',' '{print $6}')
item7=$(echo $line | awk -F ',' '{print $7}')
if [ "$item4" -gt "85" ]; then
mail -s "DISK-Warnning: `date +%Y'-'%m'-'%d' '%H':'%M':'%S`, $item7 is not have enough space ,please check."
< $LOG_FILE
else
echo " The space of disk $item7 is OK!!" >>$LOG_FILE
fi
done

50 23 * * * /ora102/scripts/cksyslog.sh 2>&1 //監控系統log
#!/bin/ksh
ORACLE_BASE=/ora102;export ORACLE_BASE
ORACLE_HOME=/ora102/10.2;export ORACLE_HOME
ORACLE_SID=STCSMES;export ORACLE_SID
PATH=$PATH:$ORACLE_HOME/bin;export PATH
DATE=`date +"%m%d"`;export DATE
cd /tmp
errpt|grep $DATE > sys.err
if [ `cat sys.err|wc -l` -gt 0 ]
then
mail -s "System ERRORS on `hostname`" < sys.err
fi
--------------------------------------
Oracle 日常維護指令碼 For AIX6.1
--------------------------------------

0,5,15,20,25,30,35,40,45,50,55 * * * * /ora102/scripts/session_count.sh 2>&1 //使用者SESSION統計
#!/bin/sh
ORACLE_HOME=/ora102/10.2;export ORACLE_HOME
ORACLE_SID=STCSMES;export ORACLE_SID
PATH=$PATH:$ORACLE_HOME/bin;export PATH
sqlplus -s ' / as sysdba' <insert into smp.session_count(current_time,session_count) select sysdate,count(*) from v$session;
commit;
EOF
exit

0,5,15,20,25,30,35,40,45,50,55 * * * * /ora102/scripts/cklsnr.sh 2>&1 //Listener 狀態監控
####################################################################
## cklsnr.sh ##
####################################################################
#!/bin/sh
ORACLE_HOME=/ora102/10.2;export ORACLE_HOME
ORACLE_SID=STCSMES;export ORACLE_SID
PATH=$PATH:$ORACLE_HOME/bin;export PATH
cd /tmp
lsnrctl status STCSMES|grep -i "no listener" > lsnr.status
if [ -s lsnr.status ]
then
mail -s "Listener 'STCSMES' on `hostname` is down" < lsnr.status
fi

0,5,15,20,25,30,35,40,45,50,55 * * * * /ora102/scripts/ckinstance.sh 2>&1 // Instance狀態監控
####################################################################
## ckinstance.sh ##
####################################################################
ORACLE_BASE=/ora102;export ORACLE_BASE
ORACLE_HOME=/ora102/10.2;export ORACLE_HOME
ORACLE_SID=STCSMES;export ORACLE_SID
PATH=$PATH:$ORACLE_HOME/bin;export PATH
for SID in `cat $ORACLE_HOME/sidlist`
do
cd /tmp
ps -ef|grep ora_|grep -v grep > instance.status
if [ `cat instance.status | grep _${SID} | grep -v grep | wc -l` -eq 0 ]
then
echo "DB Down" | mail -s "Instance ${SID} on `hostname` is down"
fi
done

0,15,30,45 * * * * /ora102/scripts/ckalertlog_real.sh 2>&1 //Oracle alert log監控
####################################################################
## ckalertlog_real.sh ##
####################################################################
#!/bin/ksh
ORACLE_BASE=/ora102;export ORACLE_BASE
ORACLE_HOME=/ora102/10.2;export ORACLE_HOME
ORACLE_SID=STCSMES;export ORACLE_SID
PATH=$PATH:$ORACLE_HOME/bin;export PATH
for SID in `cat $ORACLE_HOME/sidlist`
do
cd $ORACLE_BASE/admin/$SID/bdump
if [ -f alert_${SID}.log ]
then
tail alert_${SID}.log | grep -i -E 'ORA-|error|warn|start|shut' > alert_real.err
fi
if [ `cat alert_real.err|wc -l` -gt 0 ]
then
mail -s "${SID} ORACLE ALERT ERRORS" < alert_real.err
fi
done

0-59 * * * * /ora102/scripts/killsniped.sh 2>&1 //Kill ORACLE 中狀態為sniped的程式
#!/bin/sh
ORACLE_HOME=/ora102/10.2;export ORACLE_HOME
ORACLE_SID=STCSMES;export ORACLE_SID
PATH=$PATH:$ORACLE_HOME/bin;export PATH
tmpfile=/tmp/tmp.$$
sqlplus -s ' / as sysdba' <set pagesize 0;
set linesize 20;
set head off;
set feedback off;
spool $tmpfile
select trim(p.spid) from v$process p,v$session s
where s.paddr=p.addr
and s.status='SNIPED' and s.type='USER';
spool off
EOF
for x in `cat $tmpfile | grep "^[0123456789]"`
do
kill -9 $x
done
rm $tmpfile

0-59 * * * * /ora102/scripts/killuser.sh 2>&1 //Kill 特定的使用者連線
#!/bin/sh
ORACLE_HOME=/ora102/10.2;export ORACLE_HOME
ORACLE_SID=STCSMES;export ORACLE_SID
PATH=$PATH:$ORACLE_HOME/bin;export PATH
tmfile=/tmp/tm.$$
sqlplus -s ' / as sysdba' <set pagesize 0;
set linesize 20;
set head off;
set feedback off;
spool $tmfile
select trim(p.spid) from v$process p,v$session s where s.paddr=p.addr
and (s.osuser in ('Sam_Yin','johnson','dashan_zhang') or s.terminal in ('MIS-SAM_YIN','JOHNSON2009','MIS-DASHAN_ZHAN'));
spool off
EOF
for x in `cat $tmfile | grep "^[0123456789]"`
do
kill -9 $x
done
rm $tmfile

0-59 * * * * /ora102/scripts/killbossview.sh 2>&1 //Kill特定的程式連線
#!/bin/sh
ORACLE_HOME=/orahome/10.2;export ORACLE_HOME
ORACLE_SID=STCSMES;export ORACLE_SID
PATH=$PATH:$ORACLE_HOME/bin;export PATH
tmfile=/tmp/tm.$$
sqlplus -s ' / as sysdba' <set pagesize 0;
set linesize 20;
set head off;
set feedback off;
spool $tmfile
select trim(p.spid) from v$process p,v$session s where s.paddr=p.addr and s.type='USER' and s.program='BOSSVIEW.EXE';
spool off
EOF
for x in `cat $tmfile | grep "^[0123456789]"`
do
kill -9 $x
done
rm $tmfile

30 23 * * * /ora102/scripts/ckalertlog.sh 2>&1 //Oracle alert log監控及日誌備份
####################################################################
## ckalertlog.sh ##
####################################################################
#!/bin/ksh
ORACLE_BASE=/ora102;export ORACLE_BASE
ORACLE_HOME=/ora102/10.2;export ORACLE_HOME
ORACLE_SID=STCSMES;export ORACLE_SID
PATH=$PATH:$ORACLE_HOME/bin;export PATH
DATE=`date +"%m%d"`;export DATE
for SID in `cat $ORACLE_HOME/sidlist`
do
cd $ORACLE_BASE/admin/$SID/bdump
if [ -f alert_${SID}.log ]
then
mv alert_${SID}.log alert_${SID}.log.$DATE
touch alert_${SID}.log
grep -i -E 'ORA-|error|alter|warn' alert_${SID}.log.$DATE > alert.err
fi
if [ `cat alert.err|wc -l` -gt 0 ]
then
mail -s "${SID} ORACLE ALERT ERRORS" < alert.err
fi
done

40 23 * * * /ora102/scripts/cleanlog.sh 2>&1 //備份tns log，並清空
#!/bin/sh
ORACLE_BASE=/ora102;export ORACLE_BASE
ORACLE_HOME=/ora102/10.2;export ORACLE_HOME
ORACLE_SID=STCSMES;export ORACLE_SID
PATH=$PATH:$ORACLE_HOME/bin;export PATH
DATE=`date +"%m%d"`;export DATE
cd /ora102/10.2/network/log
cp stcsmes.log stcsmes.log.$DATE
cat /dev/null > stcsmes.log
touch stcsmes.log

00 14 * * * /ora102/scripts/session_monitor_full.sh //使用者SESSION監控，輸出儲存為excel檔
#!/bin/sh
ORACLE_BASE=/ora102;export ORACLE_BASE
ORACLE_HOME=/ora102/10.2;export ORACLE_HOME
ORACLE_SID=STCSMES;export ORACLE_SID
PATH=$PATH:$ORACLE_HOME/bin;export PATH
DATE=`date +"%m%d"`;export DATE
cd /ora102/10.2/network/log
cp stcsmes.log stcsmes.log.$DATE
cat /dev/null > stcsmes.log
touch stcsmes.log
stcsmesfab2:ora102:/ora102 >more /ora102/scripts/session_monitor_full.sh
#!/bin/sh
ORACLE_BASE=/ora102;export ORACLE_BASE
ORACLE_HOME=/ora102/10.2;export ORACLE_HOME
ORACLE_SID=STCSMES;export ORACLE_SID
PATH=$PATH:$ORACLE_HOME/bin;export PATH
log=/tmp/session_monitor_`date +%y%m%d`.xls
cd /ora102/scripts
rm session_monitor.xls
sqlplus -s ' / as sysdba' @session_monitor.sql
echo "######################################################################################" >> $log
cat session_monitor.xls >> $log
SUBJ="`hostname` Session monitor !"
uuencode /ora102/scripts/session_monitor.xls session_monitor.xls | mail -s "$SUBJ" "$1"
#mail -s "$SUBJ" "$1" < /ora102/scripts/session_monitor.xls
exit

30 14 * * * /ora102/scripts/dc.sh 2>&1 //OS系統及Oracle相關資訊監控
#!/bin/sh
ORACLE_HOME=/ora102/10.2;export ORACLE_HOME
ORACLE_SID=STCSMES;export ORACLE_SID
PATH=$PATH:$ORACLE_HOME/bin;export PATH
file=/tmp/daily_check.log
lsconf|grep Processor > $file
lsattr -El mem0 >> $file
lsps -s >> $file
sqlplus -s ' / as sysdba' <spool d_ck.log
--share pool size--
show parameters shared_pool_size
--share pool free--
select pool,name,bytes/1024/1024 m from v$sgastat where name='free memory' and pool='shared pool';
--db cache--
show parameters db_cache_size
--pga size--
show parameters pga_aggregate_target
--share pool hit radio--
SELECT(1- ROUND(BYTES /(4096*1024*1024),2))*100|| '%' "Share Pool Hit Ratio"
FROM v$SGASTAT
WHERE NAME= 'free memory'
AND POOL = 'shared pool';
--buffer cache hit ratio
select 1 - ((physical.value - direct.value - lobs.value) / logical.value)
"Buffer Cache Hit Ratio"
from v$sysstat physical,v$sysstat direct,v$sysstat lobs,v$sysstat logical
where physical.name = 'physical reads'
and direct.name='physical reads direct'
and lobs.name='physical reads direct (lob)'
and logical.name='session logical reads';
--library cache hit ratio
select (ROUND(sum(pins-reloads)/sum(pins),2))*100||'%' "Librarycache Hit Ratio" FROM V$LIBRARYCACHE;
--pga hit ratio
SELECT name "NAME For PGA",VALUE FROM v$PGASTAT WHERE NAME='cache hit percentage';
--tbs free
select tablespace_name,round(sum(bytes)/1024/1024,2) "FREE_MB" from dba_free_space group by tablespace_name;
spool off
EOF
cat d_ck.log >> $file
du -gs /ora102/oradata/* >> $file
rm d_ck.log
mail -s "Daily Check log on `hostname`" < $file

---------------------------------
Oracle 多個Instance監控
---------------------------------
0,20,40 * * * * /ora104/scripts/del_arc.sh > /tmp/del_arc.log //定時清除logical standby自身產生的archivelog
$ cat $ORACLE_HOME/sidlist //sidlist檔案內容
FAB1STB
FAB2STB
APPSTB

#!/bin/sh
export ORACLE_HOME=/ora104/10.2
for SID in `cat $ORACLE_HOME/sidlist`
do
export ORACLE_SID=${SID}
$ORACLE_HOME/bin/rman <connect target /
crosscheck archivelog all;
delete noprompt expired archivelog all;
delete noprompt archivelog all;
exit;
EOF
done

0,5,15,20,25,30,35,40,45,50,55 * * * * /ora104/scripts/ckinstance.sh 2>&1 //監控多個instance
####################################################################
## ckinstance.sh ##
####################################################################
#!/bin/sh
ORACLE_BASE=/ora104;export ORACLE_BASE
ORACLE_HOME=/ora104/10.2;export ORACLE_HOME
ORACLE_SID=FAB2STB;export ORACLE_SID
PATH=$PATH:$ORACLE_HOME/bin;export PATH
for SID in `cat $ORACLE_HOME/sidlist`
do
cd /tmp
ps -ef|grep ora_|grep -v grep > instance.status
if [ `cat instance.status | grep _${SID} | grep -v grep | wc -l` -eq 0 ]
then
echo "DB Down" | mail -s "Instance ${SID} on `hostname` is down"
fi
done

58 23 * * * * /ora104/scripts/ckalertlog.sh 2>&1 //監控多個alter log
ORACLE_BASE=/ora104;export ORACLE_BASE
ORACLE_HOME=/ora104/10.2;export ORACLE_HOME
ORACLE_SID=FAB1STB;export ORACLE_SID
PATH=$PATH:$ORACLE_HOME/bin;export PATH
DATE=`date +"%m%d"`;export DATE
for SID in `cat $ORACLE_HOME/sidlist`
do
cd $ORACLE_BASE/admin/$SID/bdump
if [ -f alert_${SID}.log ]
then
mv alert_${SID}.log alert_${SID}.log.$DATE
touch alert_${SID}.log
grep ORA- alert_${SID}.log.$DATE > alert.err
fi
if [ `cat alert.err|wc -l` -gt 0 ]
then
mail -s "${SID} ORACLE ALERT ERRORS" < alert.err
fi
done