#
# dblog is the name of the script
# I am executing it twice here.
oracle@mutation#dblog
-----------------------------------------------------
------------This is before the loop --
-- Each entry in this listing is in tnsnames.ora --
-- None except mutation and lawd is a valid entry --
-- a new file is generated at the end --
-- All counts are updated except valid ones --
-----------------------------------------------------
India 1
Canada 2
USA 3
mutation 4
UK 5
lawd 6
China 7
------------New file --------------------------------
------------end of file --------------------------------
India 2
Canada 3
USA 4
mutation 4
UK 6
lawd 6
China 8

#
# Execute again.
#
oracle@mutation#dblog
-----------------------------------------------------
------------This is before the loop --
-- Each entry in this listing is in tnsnames.ora --
-- None except mutation and lawd is a valid entry --
-- a new file is generated at the end --
-- All counts are updated except valid ones --
-----------------------------------------------------
India 2
Canada 3
USA 4
mutation 4
UK 6
lawd 6
China 8
------------New file --------------------------------
------------end of file --------------------------------
India 3
Canada 4
USA 5
mutation 4
UK 7
lawd 6
China 9
#
#This is the script
#Have your valid tnsentries (as in tnsnames.ora) in a file /tmp/dblist
#The script reads the file everytime and creates a new one.
#
oracle@mutation#cat dblog
#!/usr/bin/bash
echo -----------------------------------------------------
echo ------------This is before the loop                 --
echo -- Each entry in this listing is in tnsnames.ora    --
echo -- None except mutation and lawd  is a valid entry  --
echo -- a new file is generated at the end               --
echo -- All counts are updated except valid ones         --
echo -----------------------------------------------------
cat /tmp/dblist
echo ------------New file --------------------------------
export ORACLE_HOME=/u01/app/oracle/product/9.2.0
cat /tmp/dblist | awk '{print $1}' | while read name
do
err=`sqlplus -s system/manager@$name <<EOF
set echo off
set head off
set feed off
WHENEVER SQLERROR EXIT SQL.SQLCODE
prompt
prompt
exit;
EOF`
if echo $err | grep "ERROR" > /tmp/junk1
then
   awk -F" " '$1 ~/'$name'/ {print  $1" "$2+1 }' /tmp/dblist >> /tmp/dblist.final
else
   awk -F" " '$1 ~/'$name'/ {print  $1" "$2 }' /tmp/dblist   >> /tmp/dblist.final
fi
done
echo ------------end of  file --------------------------------
mv /tmp/dblist.final /tmp/dblist
cat /tmp/dblist