ls_heartbeat - liamlamth/blog GitHub Wiki

#DEFINE REGULAR VARIABLES
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export PATH=/usr/sbin:/usr/local/bin:$ORACLE_HOME/bin:$PATH
LOG=/operation/log/`basename $0|sed 's/.ksh/.log/'`
NOW=$(expr `date +%H%M` + 0)

#DEFINE TNSNAMES
PRD_SERVER1="dba_user/dba_password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myserver1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=PRD_SERVER1.local)))"
PRD_SERVER2="dba_user/dba_password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myserver2)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=PRD_SERVER2.local)))"

AUDIT_SERVER1="dba_user/dba_password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myserver3)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=AUDIT_SERVER1.local)))"

#DEFINE ARRAY
#grep all defined tnsnames variable name
DBNAME_AR=$(cat "`realpath $0`" | grep -v "DBNAME_AR" | awk '/DEFINE TNSNAMES/{f=1;next} /DEFINE ARRAY/{f=0} f' | sed -r '/^\s*$/d' | sed 's/=".*//')

#RUN SCRIPT
ACTIVE_DB=`date +"%Y-%m-%d %H:%M Active Database: "`
for eachDBNAME in $DBNAME_AR
do
        declare DBNAME="$eachDBNAME"
        if [ $(echo -e "SELECT '${DBNAME}' DB_NAME, STATUS INST_STATUS, DATABASE_STATUS DB_STATUS FROM V\$INSTANCE;" | sqlplus -s ${!DBNAME} | grep -c "^${DBNAME}.*OPEN.*ACTIVE$") == 1 ]; then
                ACTIVE_DB+="${DBNAME},"
        else
                ACTIVE_DB+="$(printf '_%.0s' $(seq ${#DBNAME})),"
                if [[ $DBNAME =~ ^(AUDIT_SERVER1|AUDIT_SERVER2)$ ]] && [ $NOW -lt 830 -o $NOW -gt 1930 ]; then                               ### daily clone database
                        :
                elif [[ $(date +%Y%m%d) == '20220101' ]] && [[ $DBNAME =~ (AUDIT_SERVER1|AUDIT_SERVER2) ]]; then                             ### temporarily ignore
                        :
                else
                        ABNORMAL_DB+="${DBNAME},"
                fi
        fi
done

#ADDITIONAL HANDLING
if [ $(ssh 192.168.2.1 "ssh 192.168.3.1 ps -ef | grep -c pmon_AUDIT_SERVER3") == 1 ]; then                                                   ### jump host
        ACTIVE_DB+="AUDIT_SERVER3,"
else
        ACTIVE_DB+="$(printf ' %.0s' $(seq ${#DBNAME})),"
        ABNORMAL_DB+="AUDIT_SERVER3,"
fi

#output result and make action
echo ${ACTIVE_DB::-1} >> $LOG
if [ ! -z "$ABNORMAL_DB" ]; then
        echo -e "**ABNORMAL DATABASE: ${ABNORMAL_DB::-1}" | /usr/bin/mutt -s "ERROR: Database Services Abnormal" -- db_adm
fi