Database 维护脚本

#!/bin/bash

echo "#####################################################"
echo "#########Check Database Instance Status##############"
echo "#####################################################"

ORATAB=/etc/oratab
echo "`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; do
	echo "$pslist" | grep "ora_pmon_$i" > /dev/null
	if(($?));then
		echo "Oracle Instance - $i : Down."
	else
		echo "Oracle Instance - $i : Up."
	fi
done


echo "#####################################################"
echo "#########Check Database Listener Status##############"
echo "#####################################################"

cd /home/oracle/dba_shscript
rm -f lsnr.exist
ps -ef | grep -i Listener | grep -v grep > /home/oracle/dba_shscript/lsnr.exist

if [ -s lsnr.exist ] 
then
	echo "`lsnrctl status`"
else
	echo "`lsnrctl start`"
fi


echo "#####################################################"
echo "#########Check Database Alert Log####################"
echo "#####################################################"

cd $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace
echo "`pwd`"
rm -f /home/oracle/dba_shscript/alert_$ORACLE_SID.log
if [ `cat alert_$ORACLE_SID.log | wc -l` -gt 0 ]
then
	grep ORA- alert_$ORACLE_SID.log > /home/oracle/dba_shscript/alert_$ORACLE_SID.log
fi

echo "Please check /home/oracle/dba_shscript/alert_$ORACLE_SID.log"

echo "#####################################################"
echo "#########Analyze Table###############################"
echo "#####################################################"
source /home/oracle/.bash_profile
rm -f /home/oracle/dba_shscript/analyze_table.sql
sqlplus -S "xinhailun/xinhailun" << EOF
set heading off
set feed off
set pagesize 200
set linesize 100
spool /home/oracle/dba_shscript/analyze_table.sql
select 'ANALYZE TABLE ' || owner || '.' || segment_name ||' ESTIMATE STATISTICS SAMPLE 10 PERCENT;'
  from dba_segments
 where segment_type = 'TABLE'
   and owner = 'XINHAILUN';
spool off
exit
!
EOF
sqlplus -S "xinhailun/xinhailun" << EOF
@/home/oracle/dba_shscript/analyze_table.sql
exit
!
EOF

echo "#####################################################"
echo "#########Monitor Tablespace##########################"
echo "#####################################################"

sqlplus -S "/ as sysdba" << EOF
set feed off
set linesize 100
set pagesize 200
spool /home/oracle/dba_shscript/tablespace.alert
SELECT 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 off
exit
!
EOF
if [ `cat /home/oracle/dba_shscript/tablespace.alert|wc -l` -gt 0 ]
then
echo "Please check /home/oracle/dba_shscript/tablespace.alert"
fi


echo "#####################################################"
echo "#########Check Invalid Objects#######################"
echo "#####################################################"

sqlplus -S "/ as sysdba" << EOF
set feed off 
set heading off 
col object_name format a30 
col owner format a15
col object_type format a15
spool /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 
! 
EOF
if [ `cat /home/oracle/dba_shscript/invalid_object.alert|wc -l` -gt 0 ] 
then 
echo "Please check /home/oracle/dba_shscript/invalid_object.alert"
fi