日常监控一
最近一直在写脚本.分享下自己的几个脚本,互相交流学习
#/bin/bash
LOC=/home/oracle/script
su - oracle -c
su - oracle -c
echo "This is a database moniter!"
source ~/.bash_profile
sqlplus "/ as sysdba" << EOF
source ~/.bash_profile
sqlplus "/ as sysdba" << EOF
spool '/home/oracle/spool.txt';
set echo off;
set verify off;
set feed off;
set line 10000;
select '##################DB moniter start######################'from dual;
set echo off;
set verify off;
set feed off;
set line 10000;
select '##################DB moniter start######################'from dual;
select tablespace_name,sum(bytes)/(1024*1024) as total_size from dba_data_files group by tablespace_name;
select file_name,bytes/(1024*1024) as Size_M from dba_data_files;
SELECT tablespace_name, trunc ( sum ( bytes ) / (1024*1024) ) as free_size,sum ( blocks ) as free_block ,max ( bytes ) / (1024) as big_chunk_k, count (*) as num_chunks FROM dba_free_space GROUP BY tablespace_name ;
SELECT tablespace_name, largest_free_chunk, nr_free_chunks, sum_alloc_blocks, sum_free_blocks, to_char(100*sum_free_blocks/sum_alloc_blocks, '09.99') ||'%' AS pct_free FROM ( SELECT tablespace_name, sum(blocks) AS sum_alloc_blocks FROM dba_data_files GROUP BY tablespace_name),( SELECT tablespace_name AS fs_ts_name, max(blocks) AS largest_free_chunk, count(blocks) AS nr_free_chunks, sum(blocks) AS sum_free_blocks FROM dba_free_space GROUP BY tablespace_name) WHERE tablespace_name = fs_ts_name;
SELECT a.table_name, a.next_extent, a.tablespace_name FROM all_tables a,( SELECT tablespace_name, max(bytes) as big_chunk FROM dba_free_space GROUP BY tablespace_name ) f WHERE f.tablespace_name = a.tablespace_name AND a.next_extent > f.big_chunk ;
SELECT a.table_name, a.next_extent, a.tablespace_name FROM all_tables a,( SELECT tablespace_name, max(bytes) as big_chunk FROM dba_free_space GROUP BY tablespace_name ) f WHERE f.tablespace_name = a.tablespace_name AND a.next_extent > f.big_chunk ;
select '#################Log switch map#####################' from dual;
select * FROM (SELECT * FROM (SELECT TO_CHAR(FIRST_TIME, 'DD/MM') AS "DAY" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '00', 1, 0)), '99') "00:00" ,TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '01', 1, 0)), '99') "01:00", TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '02', 1, 0)), '99') "02:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '03', 1, 0)), '99') "03:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '04', 1, 0)), '99') "04:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '05', 1, 0)), '99') "05:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '06', 1, 0)), '99') "06:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '07', 1, 0)), '99') "07:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '08', 1, 0)), '99') "08:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '09', 1, 0)), '99') "09:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '10', 1, 0)), '99') "10:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '11', 1, 0)), '99') "11:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '12', 1, 0)), '99') "12:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '13', 1, 0)), '99') "13:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '14', 1, 0)), '99') "14:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '15', 1, 0)), '99') "15:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '16', 1, 0)), '99') "16:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '17', 1, 0)), '99') "17:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '18', 1, 0)), '99') "18:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'),'19', 1, 0)), '99') "19:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '20', 1, 0)), '99') "20:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '21', 1, 0)), '99') "21:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '22', 1, 0)), '99') "22:00" , TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '23', 1, 0)), '99') "23:00" FROM v\$log_history WHERE extract(year FROM FIRST_TIME) = extract(year FROM sysdate) GROUP BY TO_CHAR(FIRST_TIME, 'DD/MM') )ORDER BY TO_DATE(extract(year FROM sysdate) || DAY, 'YYYY DD/MM') DESC)WHERE ROWNUM <8;
spool off;
spool off;
EOF
echo "********Now Set enviorment Variable"
Fi='/home/oracle/spool.txt'
FI='/home/oracle/spool.lst'
TR_SID=$(echo $ORACLE_SID |tr "[a-z]" "[A-Z]")
ARC_CNT=$(ls $ORACLE_BASE/ora_arc |wc -l)
echo "********Database of daily moniter is finished!" >>$Fi
echo "...............................................................................">>$Fi
#########Disk moniter
echo "********This is OS level moniter!">>$Fi
Fi='/home/oracle/spool.txt'
FI='/home/oracle/spool.lst'
TR_SID=$(echo $ORACLE_SID |tr "[a-z]" "[A-Z]")
ARC_CNT=$(ls $ORACLE_BASE/ora_arc |wc -l)
echo "********Database of daily moniter is finished!" >>$Fi
echo "...............................................................................">>$Fi
#########Disk moniter
echo "********This is OS level moniter!">>$Fi
df -H >>$Fi
DSK=$(df -H |sed '1d' |sed 's/\%//g' |awk '{$5>90}{print $5}')
for i in $DSK
do
if [ $i -gt 90 ];
then echo "Warring,Disk is nearly full!">>$Fi
echo "========Disk usage is $i%">>$Fi
fi
done
#########DB datfile moniter
cd $ORACLE_BASE/ora_data/$TR_SID/datafile
DSK=$(df -H |sed '1d' |sed 's/\%//g' |awk '{$5>90}{print $5}')
for i in $DSK
do
if [ $i -gt 90 ];
then echo "Warring,Disk is nearly full!">>$Fi
echo "========Disk usage is $i%">>$Fi
fi
done
#########DB datfile moniter
cd $ORACLE_BASE/ora_data/$TR_SID/datafile
echo "********This is datfile details">>$Fi
du -H * >>$Fi
########DB archivelog moniter
cd /opt/ora_arc
echo "********This is total number of archivelogs!">>$Fi
ls |wc -l >>$Fi
if [ $ARC_CNT -gt 200 ];
then echo "!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!Waring,Archive log is too large,That is $ARC_CNT">>$Fi
echo "Now I will clear the archivelogs!">>$Fi
find $ORACLE_BASE/ora_arc -type f -atime +30 -exec rm -rf {} \ ; >/dev/null
if [ $? -ne 0 ];
then echo "Clear obsolete archivelogs fialed,Please check it!">>$Fi
else echo "Clear obsoleter archivelogs succeed!">>$Fi
fi
fi
du -H * >>$Fi
########DB archivelog moniter
cd /opt/ora_arc
echo "********This is total number of archivelogs!">>$Fi
ls |wc -l >>$Fi
if [ $ARC_CNT -gt 200 ];
then echo "!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!Waring,Archive log is too large,That is $ARC_CNT">>$Fi
echo "Now I will clear the archivelogs!">>$Fi
find $ORACLE_BASE/ora_arc -type f -atime +30 -exec rm -rf {} \ ; >/dev/null
if [ $? -ne 0 ];
then echo "Clear obsolete archivelogs fialed,Please check it!">>$Fi
else echo "Clear obsoleter archivelogs succeed!">>$Fi
fi
fi
echo "********This is the total size of archivelogs!">>$Fi
du -H /opt/ora_arc >>$Fi
##########DB bgprocess moniter
du -H /opt/ora_arc >>$Fi
##########DB bgprocess moniter
echo "********This is the bgprocess of ORACLE!">>$Fi
ps -ef | awk '$8~/^ora/ {print $0}' >>$Fi
echo "********This is the listener">>$Fi
ps -ef |grep LI >>$Fi
ps -ef | awk '$8~/^ora/ {print $0}' >>$Fi
echo "********This is the listener">>$Fi
ps -ef |grep LI >>$Fi
tnsping $ORACLE_SID >/dev/null
if [ $? -ne 0 ];
then echo "!!!!!!!!!Waring,listener is not properly,Now restart it!">>$Fi
lsnrctl realod >/dev/null
if [ $? -ne 0 ];
then echo "Restart listener failed,Please check it">>$Fi
fi
fi
if [ $? -ne 0 ];
then echo "!!!!!!!!!Waring,listener is not properly,Now restart it!">>$Fi
lsnrctl realod >/dev/null
if [ $? -ne 0 ];
then echo "Restart listener failed,Please check it">>$Fi
fi
fi
##########OS mem,io,cpu moniter
echo "********This vmstaT to moniter io,cpu,mem of OS">>$Fi
vmstat 5 5 >>$Fi
##########OS iostat
echo "********This is iostat!">>$Fi
iostat 5 2 >>$Fi
#########DB alterlog
echo "********This is the alterlog of $ORACLE_SID!">>$Fi
tail -n 30 $ORACLE_BASE/admin/$ORACLE_SID/bdump/alert_$ORACLE_SID.log >>$Fi
echo "********This vmstaT to moniter io,cpu,mem of OS">>$Fi
vmstat 5 5 >>$Fi
##########OS iostat
echo "********This is iostat!">>$Fi
iostat 5 2 >>$Fi
#########DB alterlog
echo "********This is the alterlog of $ORACLE_SID!">>$Fi
tail -n 30 $ORACLE_BASE/admin/$ORACLE_SID/bdump/alert_$ORACLE_SID.log >>$Fi
#mail -s "A daily mail of monitering db" oracle
spool '$LOC/rman.log';
select bak.bs_completion_time,bak.pkey,bak.backup_type,bak.file_type,bak.fname,bak.tag,bak.device_type,bak.completion_time,bak.obsolete,bak.bytes/(1024*1024),bak.bs_type,bak.bs_incr_type,bak.df_tablespace,bak.bs_bytes/(1024*1024) as from v\$backup_files bak where bak.bs_completion_time>=sysdate -1;
more $Fi |sed '/SQL>/d'|sed '/^$/d' >>$FI && rm -rf $Fi
if [ $? -ne 0 ]
then
echo "Script. $Fi not tranform. to $FI,Please check it" >>$Fi
fi
if [ $? -ne 0 ]
then
echo "Script. $Fi not tranform. to $FI,Please check it" >>$Fi
fi
more $FI >> /share/$ORACLE_SID/daily_$(date +'%F-%X') && rm -rf $FI
if [ $? -eq 0 ];
then echo "++Remove $FI succeed!">>/share/$ORACLE_SID/daily_$(date +'%F-%X')
fi
if [ $? -eq 0 ];
then echo "++Remove $FI succeed!">>/share/$ORACLE_SID/daily_$(date +'%F-%X')
fi
echo "********This is backup of rman details!">>/share/$ORACLE_SID/daily_$(date +'%F-%X')
find $LOC -name "*log" -exec rm -rf {} \;
sqlplus / as sysdba<set echo off;
set verify off;
set line 400;
column peky format a20;
column backup_type format a20;
column file_type format a30;
column fname format a80;
column tag format a20;
column device_type format a20;
column completion_time format a20;
column obsolete format a10;
column size_m format a50;
column bs_type format a20;
column bs_incr_type format a10;
column bs_completion_time format a30;
column df_tablespace format a20;
column bs_size_m format a50;
find $LOC -name "*log" -exec rm -rf {} \;
sqlplus / as sysdba<
set verify off;
set line 400;
column peky format a20;
column backup_type format a20;
column file_type format a30;
column fname format a80;
column tag format a20;
column device_type format a20;
column completion_time format a20;
column obsolete format a10;
column size_m format a50;
column bs_type format a20;
column bs_incr_type format a10;
column bs_completion_time format a30;
column df_tablespace format a20;
column bs_size_m format a50;
spool '$LOC/rman.log';
select bak.bs_completion_time,bak.pkey,bak.backup_type,bak.file_type,bak.fname,bak.tag,bak.device_type,bak.completion_time,bak.obsolete,bak.bytes/(1024*1024),bak.bs_type,bak.bs_incr_type,bak.df_tablespace,bak.bs_bytes/(1024*1024) as from v\$backup_files bak where bak.bs_completion_time>=sysdate -1;
spool off;
EOF
EOF
more $LOC/rman.log |sed '/SQL/d' >>/share/$ORACLE_SID/daily_$(date +'%F-%X')
if [ $? -ne 0 ];
then echo "********Moniter rman backup Done!">>/share/$ORACLE_SID/daily_$(date +'%F-%X')
else echo "********Moniter rman backup Failed,Please check it!">>/share/$ORACLE_SID/daily_$(date +'%F-%X')
fi
then echo "********Moniter rman backup Done!">>/share/$ORACLE_SID/daily_$(date +'%F-%X')
else echo "********Moniter rman backup Failed,Please check it!">>/share/$ORACLE_SID/daily_$(date +'%F-%X')
fi
echo "######################Script. End!######################################">>/share/$ORACLE_SID/daily_$(date +'%F-%X')
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/15072844/viewspace-622008/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
Oracle 表对象HKW监控shell
下一篇：
日常监控二
请登录后发表评论
登录
全部评论
北京盛拓优讯信息技术有限公司. 版权所有 京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区
广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员
违法和不良信息举报电话:18101376593