How to check invalid objects and broken job in multiple database

Recently i have to do release support. I need to check the invalid objects and broken jobs in nine environment, which is very tedious and time consuming.  Thus, i wrote a shell script to do such thing.

 

#!/bin/bash


#Env part

##TEST Env--vqa2
CUSTOMER_Q2="***/***"
SECURITY_Q2="***/***"
KIOSK_Q2="***/***"
AQ_Q2="***/***"
AQ_FULFILLMENT_Q2="***/***"
AQ_FULFILLMENT_M_Q2="***/***"
AQ_FULFILLMENT_PUT_Q2="***/***"
OMS_FULFILLMENT_Q2="***/***"
OMS_FULFILLMENT_M_Q2="***/***"
OMS_FULFILLMENT_PUT_Q2="***/***"
LOGISTICS_Q2="***/***"

##STAGE
CUSTOMER_STAGE="***/***"
SECURITY_STAGE="***/***"
KIOSK_STAGE="***/***"
AQ_STAGE=""***/***"
AQ_FULFILLMENT_STAGE="***/***"
OMS_FULFILLMENT_STAGE="***/***"
LOGISTICS="***/***"


##PROD
CUSTOMER_PROD="***/***"
SECURITY_PROD="***/***"
KIOSK_PROD="***/***"
AQ_PROD="***/***"
AQ_FULFILLMENT_PROD="***/***"
AQ_FULFILLMNET_MPROD="***/***"
AQ_FULFILLMENT_PUTPROD="***/***"
OMS_FULFILLMENT_PROD="***/***"
OMS_FULFILLMENT_MPROD="***/***"
OMS_FULFILLMENT_PUTPROD="***/***"
LOGISTICS_PROD="***/***"

##ZDRUSER
CUSTOMER_PROD_ZDR="***/***"
SECURITY_PROD_ZDR="***/***"
KIOSK_PROD_ZDR="***/***"
AQ_PROD_ZDR="***/***"
AQ_FULFILLMENT_PROD_ZDR="***/***"
AQ_FULFILLMNET_MPROD_ZDR="***/***"
AQ_FULFILLMENT_PUTPROD_ZDR="***/***"
OMS_FULFILLMENT_PROD_ZDR="***/***"
OMS_FULFILLMENT_MPROD_ZDR="***/***"
OMS_FULFILLMENT_PUTPROD_ZDR="***/***"
LOGISTICS_PROD_ZDR="***/***"

LOG_DIR=/home/rxyu/logs
LOG_FILE=$LOG_DIR/monitor/monitor_release_`date '+%Y_%m_%d'`.log
REPORT_FILE=$LOG_DIR/reports/report_release_`date '+%Y_%m_%d'`.lst
CUR_DATE=`date '+%Y_%m_%d %H:%M:%S'`

ENV=$(echo $1|tr 'a-z' 'A-Z')

if [ $ENV == "STAGE" ]; then
  env_list=($CUSTOMER_STAGE $SECURITY_STAGE $KIOSK_STAGE $AQ_STAGE $AQ_FULFILLMENT_STAGE "" "" $OMS_FULFILLMENT_STAGE "" "" $LOGISTICS)
elif [ $ENV == "PROD" ]; then
  env_list=($CUSTOMER_PROD $SECURITY_PROD $KIOSK_PROD $AQ_PROD $AQ_FULFILLMENT_PROD $AQ_FULFILLMNET_MPROD $AQ_FULFILLMENT_PUTPROD $OMS_FULFILLMENT_PROD $OMS_
FULFILLMENT_MPROD $OMS_FULFILLMENT_PUTPROD $LOGISTICS_PROD)
elif [ $ENV == "ZDR" ]; then
  env_list=($CUSTOMER_PROD_ZDR $SECURITY_PROD_ZDR $KIOSK_PROD_ZDR $AQ_PROD_ZDR $AQ_FULFILLMENT_PROD_ZDR $AQ_FULFILLMNET_MPROD_ZDR $AQ_FULFILLMENT_PUTPROD_ZDR
 $OMS_FULFILLMENT_PROD_ZDR $OMS_FULFILLMENT_MPROD_ZDR $OMS_FULFILLMENT_PUTPROD_ZDR $LOGISTICS_PROD_ZDR)
elif [ $ENV == "Q2" ]; then
  env_list=($CUSTOMER_Q2 $SECURITY_Q2 $KIOSK_Q2 $AQ_Q2 $AQ_FULFILLMENT_Q2 $AQ_FULFILLMENT_M_Q2 $AQ_FULFILLMENT_PUT_Q2 $OMS_FULFILLMENT_Q2 $OMS_FULFILLMENT_M_
Q2 $OMS_FULFILLMENT_PUT_Q2 $LOGISTICS_Q2)
else
  echo "Unknown Environment"
  exit
fi

#app body
echo "">>$LOG_FILE
echo "">>$LOG_FILE
echo "">>$LOG_FILE
echo "******************************************************************Start of the Round of DB Validation************************************">>$LOG_FILE
echo "Start one round Release suport at $CUR_DATE for Environment "$ENV" ">>$LOG_FILE


for CONNECT_STR in ${env_list[@]}
do
 temp_str="$(echo ${CONNECT_STR:0:5} | tr 'a-z' 'A-Z')"
 echo $temp_str
if [ $temp_str == "CUSTO" -o $temp_str == "OMS_F" ]; then
  echo "Connecting database "$CONNECT_STR"......">>$LOG_FILE
 
  #to connect database
sqlplus -s $CONNECT_STR <<EEEOF >>$LOG_FILE
set serveroutput on
set feedback on
set linesize 180
col interval format A50
select owner,object_name,object_type,last_ddl_time,status from all_objects o where o.status='INVALID'
/
select j.job,j.LOG_USER,j.last_date,j.interval from dba_jobs j where j.BROKEN='Y'
/
exit;
EEEOF
 
else
  echo "Connecting database "$CONNECT_STR"  without Invalid jobs......">>$LOG_FILE

#to conect database
sqlplus -s $CONNECT_STR <<EOF >>$LOG_FILE
set serveroutput on
set feedback on
set linesize 180
col interval format A50
select owner,object_name,object_type,last_ddl_time,status from all_objects o where o.status='INVALID'
/
exit;
EOF
fi
done



echo "******************************************************************End of the Round of DB Validation************************************">>$LOG_FILE