Validate weather the oracle DBs you manage are in sync with your catalogue or inventory

Objective: Validate weather the oracle DBs you manage are in sync with your catalogue or inventory. Here our ask is to ensure that all the DBs or instance listed under that server are really there in oratab & running @ the os level.

Assumption: You manage your oracle DB catalogue or inventory in excel or in DB as a table. You dump the information as you see below.

hostname, RAC, os-user,#of instance,instance_names
host1,    N,    oracle,    2,    orainst1;orainst2;
host2,    N,    oracle,    2,    orainst3;orainst4;

Step 1: Ensure you have passwordless ssh setup from the server your going to run this validation.

ssh-keygen -t rsa
ssh-copy-id oracle@host2
ssh host2  << shouldnt prompt for password

Step 2: Processs the csv file.

while read line
do
srvnm=`echo $line|cut -d ',' -f 1`
srvusr=`echo $line|cut -d ',' -f 3`
invcnt=`echo $line|cut -d ',' -f 4`
inslst1=`echo $line|cut -d ',' -f 5`
inslst2=`echo $inslst1|tr ";" "\n"`

Step 3: Remote connect to the target server & do necessary validations

#!/bin/ksh
while read line
do
srvnm=`echo $line|cut -d ',' -f 1`
srvusr=`echo $line|cut -d ',' -f 3`
invcnt=`echo $line|cut -d ',' -f 4`
inslst1=`echo $line|cut -d ',' -f 5`
inslst2=`echo $inslst1|tr ";" "\n"`
srvrsp=$(ssh -o StrictHostKeyChecking=no $srvusr@$srvnm /bin/sh -s $inslst2 <<"EOF"
hstnm=`uname -n|cut -d '.' -f 1`
echo $hstnm
typeset -i j
j=0
typeset -i k
k=0
for i in "$@"
do
k=`cat /etc/oratab|grep -v ASM|grep -v MGMTDB|grep -v "^$"|grep -v "^#"|cut -d ":" -f 1|grep -iw $i|wc -l`
if [ $k -eq 1 ]
then
j=j+1
fi
done
echo $j
j=0
k=0
for i in "$@"
do
k=`ps -ef|grep -iw ora_pmon_$i|grep -v grep|wc -l`
if [ $k -eq 1 ]
then
j=j+1
fi
done
echo $j
EOF
)
rtc=$?
if [ $rtc -eq 0 ]
then
oracnt=`echo $srvrsp|cut -d ' ' -f 2`
runcnt=`echo $srvrsp|cut -d ' ' -f 3`
        if [ $invcnt -eq $oracnt ]
        then
                if [ $invcnt -eq $runcnt ]
                then
                echo "$srvnm, Conn_OK, Oratab_OK, Pmon_OK"
                else
                echo "$srvnm, Conn_OK, Oratab_OK, Pmon_NOT"
                fi
        else
                if [ $invcnt -eq $runcnt ]
                then
                echo "$srvnm, Conn_OK, Oratab_NOT, Pmon_OK"
                else
                echo "$srvnm, Conn_OK, Oratab_NOT, Pmon_NOT"
                fi
        fi
else
echo "$srvnm, conn_NOT, Oratab_NA, Pmon_NA"
fi
done < catalogue.input

./invVAL_v1.sh
host1, Conn_OK, Oratab_OK, Pmon_OK
host2, Conn_OK, Oratab_OK, Pmon_NOT

so in host2, I have only 1 instance alive, so pmon_NOT is reported (a _NOT means we arent in sync with our catalogue), which is fine.

Thanks