Skip to main content

RMAN validate all databases on host shell script

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

Comments

Popular posts from this blog

non-existent process lock port on windows server

I have a database link created between oracle and sqlserver using oracle tg4odbc, the product is installed on windows server and run as service "OracleOraGtw11g_home1TNSListener", but sometime the service cannot started, the root cause of this problem is that the port number 1521 is used by an non-existent process. The first step is to use netstat -bano|find "1521" to get the process id, in my case it's 5844, which shows the connection is from my oracle server 10.8.0.169 H:\>netstat -bano|find "1521"   TCP    0.0.0.0:1521           0.0.0.0:0              LISTENING       5844   TCP    10.14.45.33:1521       10.8.0.169:42987       ESTABLISHED     5844 however the process id does not show in either task manager or process explorer. The next step is to run tcpview, which shows non-existent under process column, there are three rows, two show status as "listening", the other one shows status "established", right click and k

Opatch apply/lsinventory error: oneoff is corrupted or does not exist

I am applying the quarterly patch for 19c RDBMS, I tried using napply but failed, but somehow it corrupted the inventory though nothing applied. further apply and lsinventory command ran into error like this: $ ./OPatch/opatch lsinventory Oracle Interim Patch Installer version 12.2.0.1.21 Copyright (c) 2020, Oracle Corporation.  All rights reserved. Oracle Home       : /u02/app/oracle/19.0.0 Central Inventory : /u01/app/oraInventory    from           : /u02/app/oracle/19.0.0/oraInst.loc OPatch version    : 12.2.0.1.21 OUI version       : 12.2.0.7.0 Log file location : /u02/app/oracle/19.0.0/cfgtoollogs/opatch/opatch2020-09-08_13-35-59PM_1.log Lsinventory Output file location : /u02/app/oracle/19.0.0/cfgtoollogs/opatch/lsinv/lsinventory2020-09-08_13-35-59PM.txt -------------------------------------------------------------------------------- Inventory load failed... OPatch cannot load inventory for the given Oracle Home. LsInventorySession failed: Unable to create patchObject Possible ca

shell script to clean up oracle dumpfile

https://github.com/iacosta/personal/blob/master/shells/cleanhouse.sh #!/bin/ksh # # Script used to cleanup any Oracle environment. # # Cleans:      audit_file_dest #              background_dump_dest #              core_dump_dest #              user_dump_dest #              Clusterware logs # # Rotates:     Alert Logs #              Listener Logs # # Scheduling:  00 00 * * * /networkdrive/dba/scripts/purge/cleanup.sh -d 7 > /u01/dba/bin/cleanup.log 2>&1 # # Created By:  Lei Dao # # # RM="rm -f" RMDIR="rm -rf" LS="ls -l" MV="mv" TOUCH="touch" TESTTOUCH="echo touch" TESTMV="echo mv" TESTRM=$LS TESTRMDIR=$LS SUCCESS=0 FAILURE=1 TEST=0 HOSTNAME=`hostname` ORAENV="oraenv" TODAY=`date +%Y%m%d` ORIGPATH=/usr/local/bin:$PATH ORIGLD=$LD_LIBRARY_PATH export PATH=$ORIGPATH # Usage function. f_usage(){   echo "Usage: `basename $0` -d DAYS [-a DAYS] [-b DAYS] [