資料庫監控指令碼

資料庫自動化監控，傳送監控報告，省事省心。

發來和大家共享,本指令碼監控資料庫例項負載情況，表空間使用情況，磁碟組空間情況，表鎖情況，監聽情況，報錯資訊，備份資訊。：

[oracle@lls-db2 monitor]$ more moni.sh
#/bin/sh
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=/opt/oracle/product/10.2.0/db
export ORACLE_SID=lls

export ORACLE_TERM=xterm                                           
export NLS_LANG=AMERICAN_AMERICA.zhs16gbk                          
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data               
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib
export PATH=$ORACLE_HOME/bin:$PATH:$HOME/bin
DATE=`date '+%Y_%m_%d_%H'`
HOST=lls

HOSTDATE=/home/oracle/monitor/$HOST$DATE
MONTH=`date '+%h'`
YEAR=`date '+%Y'`
FILENAME=lls_$DATE.lst
sqlplus / as sysdba<col OBJECT_NAME format a15
COL WHAT FORMAT A50
spool $HOSTDATE
 select username,count(*) from v\$session group by username;
select machine,count(*) from v\$session group by machine order by 1;
select fs.tablespace_name "Tablespace",
    (df.totalspace - fs.freespace) "Used MB",
    fs.freespace "Free MB",
    df.totalspace "Total MB",
    round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from
    (select tablespace_name,
        round(sum(bytes) / 1048576) TotalSpace
      from dba_data_files
      group by tablespace_name) df,
    (select tablespace_name,
        round(sum(bytes) / 1048576) FreeSpace
      from dba_free_space
      group by tablespace_name) fs
where df.tablespace_name = fs.tablespace_name
;
select name,total_mb,free_mb from v\$asm_diskgroup;

select sum(bytes)/1024/1024 SIZE_IN_MB  from dba_segments where wner='HLMP';

select /*+ rule */ uo.owner,s.sid,s.sql_id,s.row_wait_obj#,uo.object_name,s.blocking_session,s.event
from v\$session s,v\$locked_object lo,dba_objects uo
where s.sid=lo.session_id
and lo.object_id=uo.object_id;

spool off
exit;
!

echo "====$HOST $DATE==========" >>$HOSTDATE.lst
date >>$HOSTDATE.lst
echo "====COMMAND IS UPTIME====" >>$HOSTDATE.lst
uptime >>$HOSTDATE.lst
echo "====COMMAND IS NETSTAT====" >>$HOSTDATE.lst
netstat -antp|grep LIST >>$HOSTDATE.lst
echo "====COMMAND IS VMSTAT====" >>$HOSTDATE.lst
vmstat 1 5 >>$HOSTDATE.lst
echo "====COMMAND IS DF====" >>$HOSTDATE.lst
df -h >>$HOSTDATE.lst
echo "====PROCESSES OF RACLE====" >>$HOSTDATE.lst

ps -ef|grep ora|grep NO|wc -l >>$HOSTDATE.lst
echo "====ORA- ERRORS====" >>$HOSTDATE.lst

cat /opt/oracle/admin/hqmss/bdump/alert_hqmss2.log|grep -A3 ORA-|grep -B1 $MONTH|grep -B1 $YEAR|tail -30>>$HOSTDATE.lst

echo "====RMAN BACKUP ERRORS=====" >>$HOSTDATE.lst
cat /tol/backup/rman_backup/log/rman_tol24_`date '+%Y%m%d'`.log |grep -B4 ORA->>$HOSTDATE.lst

echo "====listener service====" >>$HOSTDATE.lst
lsnrctl service>>$HOSTDATE.lst