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
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
