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
