This script will loop all running db instances and run rman validate database
#!/bin/sh
#created on March, 2020 by Jiulu Sun
#use the following two commands to manually generate the instance list and create oratab_host file to loop
# ps -ef|grep ckpt |awk '{print $8}'|awk -F "_" '{if (NF==3) print $3;if (NF==4) print $3"_"$4}'|grep -v "ASM"|grep -v '^_' > dblist_vdclracdev01
# for inst in $(<dblist_vdclracdev01)^Jdo^Jgrep -i ${inst}":" /etc/oratab|awk -F ":" '{print $1":"$2}'^Jdone > oratab_vdclracdev01
#now the oratab_host file is created, validate all of them.
LOG_LOCATION=/u01/dba/jsun
timestamp=`date +%Y_%m_%d_%H_%M_%S`
hostname=`hostname`
RMANLOG_FILE=$LOG_LOCATION/RmanValidate_$hostname_$timestamp.log
SQLPLUS_LOGFILE=$LOG_LOCATION/sqlplus_logfile_$hostname_$timestamp.log
ORATAB=$LOG_LOCATION/oratab_vdclracdev01
HOSTNAME=`hostname`
export NLS_DATE_FORMAT="dd-month-yyyy hh:mi:ss"
cat $ORATAB | while read LINE
do
case $LINE in
\#*) ;; #comment-line in oratab
*)
ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
export ORACLE_SID
ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`
export ORACLE_HOME
echo "The following databases will be checked"
echo $ORACLE_SID
echo $ORACLE_HOME
if [ "$ORACLE_SID" == '*' ] ; then
# same as NULL SID - ignore this entry
ORACLE_SID=""
continue
fi
ORAENV_ASK=NO
. oraenv
echo "checking ${ORACLE_SID} :" >> $RMANLOG_FILE
echo "checking ${ORACLE_SID} :" >> $SQLPLUS_LOGFILE
$ORACLE_HOME/bin/rman target / <<EOF
spool log to /u01/dba/jsun/tmp.out
CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
backup validate check logical database;
quit
EOF
cat /u01/dba/jsun/tmp.out >> $RMANLOG_FILE
$ORACLE_HOME/bin/sqlplus /nolog <<EOF
spool /u01/dba/jsun/tmp.out
connect / as sysdba;
select * from v\$DATABASE_BLOCK_CORRUPTION;
quit
EOF
cat /u01/dba/jsun/tmp.out >> $SQLPLUS_LOGFILE
;;
esac
done
#mail -s "check patch status for all databases running under $ORACLE_HOME " $To_EMAIL_ADDR < $RMANLOG_FILE
exit
#!/bin/sh
#created on March, 2020 by Jiulu Sun
#use the following two commands to manually generate the instance list and create oratab_host file to loop
# ps -ef|grep ckpt |awk '{print $8}'|awk -F "_" '{if (NF==3) print $3;if (NF==4) print $3"_"$4}'|grep -v "ASM"|grep -v '^_' > dblist_vdclracdev01
# for inst in $(<dblist_vdclracdev01)^Jdo^Jgrep -i ${inst}":" /etc/oratab|awk -F ":" '{print $1":"$2}'^Jdone > oratab_vdclracdev01
#now the oratab_host file is created, validate all of them.
LOG_LOCATION=/u01/dba/jsun
timestamp=`date +%Y_%m_%d_%H_%M_%S`
hostname=`hostname`
RMANLOG_FILE=$LOG_LOCATION/RmanValidate_$hostname_$timestamp.log
SQLPLUS_LOGFILE=$LOG_LOCATION/sqlplus_logfile_$hostname_$timestamp.log
ORATAB=$LOG_LOCATION/oratab_vdclracdev01
HOSTNAME=`hostname`
export NLS_DATE_FORMAT="dd-month-yyyy hh:mi:ss"
cat $ORATAB | while read LINE
do
case $LINE in
\#*) ;; #comment-line in oratab
*)
ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
export ORACLE_SID
ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`
export ORACLE_HOME
echo "The following databases will be checked"
echo $ORACLE_SID
echo $ORACLE_HOME
if [ "$ORACLE_SID" == '*' ] ; then
# same as NULL SID - ignore this entry
ORACLE_SID=""
continue
fi
ORAENV_ASK=NO
. oraenv
echo "checking ${ORACLE_SID} :" >> $RMANLOG_FILE
echo "checking ${ORACLE_SID} :" >> $SQLPLUS_LOGFILE
$ORACLE_HOME/bin/rman target / <<EOF
spool log to /u01/dba/jsun/tmp.out
CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
backup validate check logical database;
quit
EOF
cat /u01/dba/jsun/tmp.out >> $RMANLOG_FILE
$ORACLE_HOME/bin/sqlplus /nolog <<EOF
spool /u01/dba/jsun/tmp.out
connect / as sysdba;
select * from v\$DATABASE_BLOCK_CORRUPTION;
quit
EOF
cat /u01/dba/jsun/tmp.out >> $SQLPLUS_LOGFILE
;;
esac
done
#mail -s "check patch status for all databases running under $ORACLE_HOME " $To_EMAIL_ADDR < $RMANLOG_FILE
exit
Comments
Post a Comment