Database 维护脚本

#!/bin/bashecho "#####################################################"echo "#########Check Database Instance Status##############"echo "#####################################################"ORATAB=/etc/oratabecho "`date`"echo "Oracle Database(s) Status: `hostname`"db=`egrep -i ":Y|:N" $ORATAB | cut -d ":" -f1 | grep -v "\#" | grep -v "\*"`pslist="`ps -ef | grep pmon`"for i in $db; doecho "$pslist" | grep "ora_pmon_$i" > /dev/nullif(($?));thenecho "Oracle Instance - $i : Down."elseecho "Oracle Instance - $i : Up."fidoneecho "#####################################################"echo "#########Check Database Listener Status##############"echo "#####################################################"cd /home/oracle/dba_shscriptrm -f lsnr.existps -ef | grep -i Listener | grep -v grep > /home/oracle/dba_shscript/lsnr.existif [ -s lsnr.exist ] thenecho "`lsnrctl status`"elseecho "`lsnrctl start`"fiecho "#####################################################"echo "#########Check Database Alert Log####################"echo "#####################################################"cd $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/traceecho "`pwd`"rm -f /home/oracle/dba_shscript/alert_$ORACLE_SID.logif [ `cat alert_$ORACLE_SID.log | wc -l` -gt 0 ]thengrep ORA- alert_$ORACLE_SID.log > /home/oracle/dba_shscript/alert_$ORACLE_SID.logfiecho "Please check /home/oracle/dba_shscript/alert_$ORACLE_SID.log"echo "#####################################################"echo "#########Analyze Table###############################"echo "#####################################################"source /home/oracle/.bash_profilerm -f /home/oracle/dba_shscript/analyze_table.sqlsqlplus -S "xinhailun/xinhailun" << EOFset heading offset feed offset pagesize 200set linesize 100spool /home/oracle/dba_shscript/analyze_table.sqlselect 'ANALYZE TABLE ' || owner || '.' || segment_name ||' ESTIMATE STATISTICS SAMPLE 10 PERCENT;'  from dba_segments where segment_type = 'TABLE'   and owner = 'XINHAILUN';spool offexit!EOFsqlplus -S "xinhailun/xinhailun" << EOF@/home/oracle/dba_shscript/analyze_table.sqlexit!EOFecho "#####################################################"echo "#########Monitor Tablespace##########################"echo "#####################################################"sqlplus -S "/ as sysdba" << EOFset feed offset linesize 100set pagesize 200spool /home/oracle/dba_shscript/tablespace.alertSELECT F.TABLESPACE_NAME,       TO_CHAR((T.TOTAL_SPACE - F.FREE_SPACE), '999,999,999') "USED (MB)",       TO_CHAR(F.FREE_SPACE, '999,999,999') "FREE (MB)",       TO_CHAR(T.TOTAL_SPACE, '999,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)) < 70;spool offexit!EOFif [ `cat /home/oracle/dba_shscript/tablespace.alert|wc -l` -gt 0 ]thenecho "Please check /home/oracle/dba_shscript/tablespace.alert"fiecho "#####################################################"echo "#########Check Invalid Objects#######################"echo "#####################################################"sqlplus -S "/ as sysdba" << EOFset feed off set heading off col object_name format a30 col owner format a15col object_type format a15spool /home/oracle/dba_shscript/invalid_object.alert SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS WHERE STATUS = 'INVALID' ORDER BY OWNER, OBJECT_TYPE, OBJECT_NAME;spool off exit ! EOFif [ `cat /home/oracle/dba_shscript/invalid_object.alert|wc -l` -gt 0 ] then echo "Please check /home/oracle/dba_shscript/invalid_object.alert"fi