注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

常在心

淡泊明志,人生自在

 
 
 

日志

 
 

日常巡检脚本  

2011-03-28 14:20:04|  分类: 常用脚本查询 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

一、单实例数据库检查

下面二个脚本是合起来一起使用

1)创建检查脚本checklist.sh

vi /home/oracle/dbcheck/checklist.sh

#!/bin/sh
#shell script

# User specific environment and startup program
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/db10g
export ORACLE_SID=test
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin

export PATH
unset USERNAME
# User specific environment and startup program

Curdate=`date +%Y%m%d`
echo > /home/oracle/dbcheck/dbckret$Curdate.log
echo "== 1.Check Oracle BG Process =========================================" >> /home/oracle/dbcheck/dbckret$Curdate.log
ps -ef|grep ora_ |grep -v grep >> /home/oracle/dbcheck/dbckret$Curdate.log
echo >> /home/oracle/dbcheck/dbckret$Curdate.log
echo >> /home/oracle/dbcheck/dbckret$Curdate.log
echo "== 2.Check Listener Status ===========================================" >> /home/oracle/dbcheck/dbckret$Curdate.log
lsnrctl status >> /home/oracle/dbcheck/dbckret$Curdate.log
echo >> /home/oracle/dbcheck/dbckret$Curdate.log
echo >> /home/oracle/dbcheck/dbckret$Curdate.log
echo "== 3.Check Listener Log Size =========================================" >> /home/oracle/dbcheck/dbckret$Curdate.log
ls -lh $ORACLE_HOME/network/log/listener.log >> /home/oracle/dbcheck/dbckret$Curdate.log
echo >> /home/oracle/dbcheck/dbckret$Curdate.log
echo >> /home/oracle/dbcheck/dbckret$Curdate.log
echo "== 4.HarDisk Used Info ===============================================" >> /home/oracle/dbcheck/dbckret$Curdate.log
df -h >> /home/oracle/dbcheck/dbckret$Curdate.log
echo >> /home/oracle/dbcheck/dbckret$Curdate.log
echo >> /home/oracle/dbcheck/dbckret$Curdate.log
echo "== 5.Inode Used Info =================================================" >> /home/oracle/dbcheck/dbckret$Curdate.log
df -i >> /home/oracle/dbcheck/dbckret$Curdate.log

sqlplus /nolog <<EOF
conn / as sysdba
@/home/oracle/dbcheck/DBcheck.sql
exit
EOF

cat /home/oracle/dbcheck/DBcheck.log>>/home/oracle/dbcheck/dbckret$Curdate.log
rm /home/oracle/dbcheck/DBcheck.log

cd $ORACLE_BASE/admin/$ORACLE_SID/bdump/

if [ -f alert_$ORACLE_SID.log ]; then
echo >> /home/oracle/dbcheck/dbckret$Curdate.log
echo "== 10.Check Alert Log ===============================================" >> /home/oracle/dbcheck/dbckret$Curdate.log
cat alert_$ORACLE_SID.log|grep -i ora-  >> /home/oracle/dbcheck/dbckret$Curdate.log
cat alert_$ORACLE_SID.log|grep -i err-  >> /home/oracle/dbcheck/dbckret$Curdate.log
cat alert_$ORACLE_SID.log|grep -i fail- >> /home/oracle/dbcheck/dbckret$Curdate.log

curdate=`date +%Y%m%d%H%I%S`
mv alert_$ORACLE_SID.log alert_$ORACLE_SID_$curdate.log
mv alert_$ORACLE_SID_$curdate.log alert_bak/
fi

2)数据库巡检脚本

cat /home/oracle/dbcheck/DBcheck.sql

SET linesize 132;
set wrap off;
col OWNER FOR a20;
col OBJECT_TYPE FOR a20;
col OBJECT_NAME FOR a50;
col LAST_DDL_TIME FOR a40;
col what for a80;
col TABLESPACE_NAME FOR a50;
col NEXT_DATE FOR a20;
col CONSTRAINT_NAME FOR a30;
col TABLE_NAME FOR a30;
spool /home/oracle/dbcheck/DBcheck.log
prompt
prompt == 6.TableSpace Used Info (MB)=======================================
SELECT A.TABLESPACE_NAME,
       FILENUM,
       TOTAL "TOTAL (MB)",
       F.FREE "FREE (MB)",
       TO_CHAR(ROUND(FREE * 100 / TOTAL, 2), '990.00') "FREE%",
       TO_CHAR(ROUND((TOTAL - FREE) * 100 / TOTAL, 2), '990.00') "USED%",
       ROUND(MAXSIZES, 2) "MAX (MB)"
  FROM (SELECT TABLESPACE_NAME,
               COUNT(FILE_ID) FILENUM,
               SUM(BYTES / (1024 * 1024)) TOTAL,
               SUM(MAXBYTES) / 1024 / 1024 MAXSIZES
          FROM DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME) A,
       (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES / (1024 * 1024))) FREE
          FROM DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F
 WHERE A.TABLESPACE_NAME = F.TABLESPACE_NAME
/


prompt
prompt == 7.Job (BroKen/FAILURES) ===========================================
SELECT JOB, WHAT, NEXT_DATE, BROKEN, FAILURES
  FROM DBA_JOBS
 WHERE BROKEN = 'Y'
    OR FAILURES > 0
/


prompt
prompt == 8.Invalid Objects =================================================
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, LAST_DDL_TIME
  FROM DBA_OBJECTS
 WHERE STATUS = 'INVALID' and LAST_DDL_TIME>to_date('20100101','yyyymmdd')
/


prompt
prompt == 9.Invalid Indexes =================================================
SELECT A.OWNER,
       A.INDEX_NAME,
       A.TABLE_NAME,
       A.STATUS
  FROM DBA_INDEXES A
 WHERE A.STATUS = 'UNUSABLE'
 ORDER BY 1,4
/

spool off

3)在$ORACLE_BASE/admin/SID/bdump下创建目录alert_bak

二、DG备库巡查脚本

cat /home/oracle/dbcheck/checklist.sh

#!/bin/sh
#shell script

# User specific environment and startup program
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/db10g
export ORACLE_SID=blade
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin

export PATH
unset USERNAME
# User specific environment and startup program

Curdate=`date +%Y%m%d`
echo > /home/oracle/dbcheck/dbckret$Curdate.log
echo "== 1.Check Oracle BG Process =========================================" >> /home/oracle/dbcheck/dbckret$Curdate.log
ps -ef|grep ora_ |grep -v grep >> /home/oracle/dbcheck/dbckret$Curdate.log
echo >> /home/oracle/dbcheck/dbckret$Curdate.log
echo >> /home/oracle/dbcheck/dbckret$Curdate.log
echo "== 2.Check Listener Status ===========================================" >> /home/oracle/dbcheck/dbckret$Curdate.log
lsnrctl status >> /home/oracle/dbcheck/dbckret$Curdate.log
echo >> /home/oracle/dbcheck/dbckret$Curdate.log
echo >> /home/oracle/dbcheck/dbckret$Curdate.log
echo "== 3.Check Listener Log Size =========================================" >> /home/oracle/dbcheck/dbckret$Curdate.log
ls -lh $ORACLE_HOME/network/log/listener.log >> /home/oracle/dbcheck/dbckret$Curdate.log
echo >> /home/oracle/dbcheck/dbckret$Curdate.log
echo >> /home/oracle/dbcheck/dbckret$Curdate.log
echo "== 4.HarDisk Used Info ===============================================" >> /home/oracle/dbcheck/dbckret$Curdate.log
df -h >> /home/oracle/dbcheck/dbckret$Curdate.log
echo >> /home/oracle/dbcheck/dbckret$Curdate.log
echo >> /home/oracle/dbcheck/dbckret$Curdate.log
echo "== 5.Inode Used Info =================================================" >> /home/oracle/dbcheck/dbckret$Curdate.log
df -i >> /home/oracle/dbcheck/dbckret$Curdate.log

sqlplus /nolog <<EOF
conn / as sysdba
@/home/oracle/dbcheck/DBcheck.sql
exit
EOF

cat /home/oracle/dbcheck/DBcheck.log>>/home/oracle/dbcheck/dbckret$Curdate.log
rm /home/oracle/dbcheck/DBcheck.log

cd $ORACLE_BASE/admin/$ORACLE_SID/bdump/

if [ -f alert_$ORACLE_SID.log ]; then
echo >> /home/oracle/dbcheck/dbckret$Curdate.log
echo "== 9.Check Alert Log ===============================================" >> /home/oracle/dbcheck/dbckret$Curdate.log
cat alert_$ORACLE_SID.log|grep -i ora-  >> /home/oracle/dbcheck/dbckret$Curdate.log
cat alert_$ORACLE_SID.log|grep -i err-  >> /home/oracle/dbcheck/dbckret$Curdate.log
cat alert_$ORACLE_SID.log|grep -i fail- >> /home/oracle/dbcheck/dbckret$Curdate.log

curdate=`date +%Y%m%d%H%I%S`
mv alert_$ORACLE_SID.log alert_$ORACLE_SID_$curdate.log
mv alert_$ORACLE_SID_$curdate.log alert_bak/
fi

 

cat /home/oracle/dbcheck/DBcheck.sql

SET linesize 132;
set wrap off;
col OWNER FOR a20;
col OBJECT_TYPE FOR a20;
col OBJECT_NAME FOR a50;
col LAST_DDL_TIME FOR a40;
col what for a80;
col TABLESPACE_NAME FOR a50;
col NEXT_DATE FOR a20;
col CONSTRAINT_NAME FOR a30;
col TABLE_NAME FOR a30;
spool /home/oracle/dbcheck/DBcheck.log

prompt
prompt == 6.Database Open_Mode =============================================
SELECT OPEN_MODE FROM v$DATABASE
/


prompt
prompt == 7.Redo Applied Status ============================================
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED
  FROM V$ARCHIVED_LOG
 ORDER BY SEQUENCE#
/


prompt
prompt == 8.MRP Process Status ============================================
SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS
  FROM V$MANAGED_STANDBY
  WHERE PROCESS LIKE 'MRP%';

spool off

  评论这张
 
阅读(388)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017