Shell Programming and Scripting

Hi,

I am writing a script to test database connection. If the first try fails, it will wait for 1 minutes and then try again. The script is as following:

........
for i in $ORACLE_SID
do
$ORACLE_HOME/bin/sqlplus $username/$password@$i <<! >/dev/null
select * from tab;
exit

if [ $? -ne 0 ]; then
sleep 60
$ORACLE_HOME/bin/sqlplus $username/$password@$i <<! >/u01/app/oracle/local/check_$i.ora
select * from tab;
exit
fi
!
done

#********************************
# If not, exist and email
#********************************
for i in $ORACLE_SID
do
if [ -s "/u01/app/oracle/local/check_$i.ora" ]; then
check_stat=`cat /u01/app/oracle/local/check_$i.ora|grep -i ERROR\wc -l`;
if [ $check_stat -ne 0 ];
echo ATG/DBA $i database is down >> /u01/app/oracle/local/dbdown_err
fi
fi
done

When I test it and shut down the database, the output file check_orcl.ora is not generated. Please help and where it is not correct.

Thanks

Not sure if I'll hit the mark here as I only do a little sql.
But (i think) you may need to ensure that you space things out.
And it makes for easier reading.

Code:

$ORACLE_HOME/bin/sqlplus $username/$password @$i << ! >/dev/null
                                            ^      ^ [suggested spaces]

Also, is $i a script ?? From your code, you are trying to execute the $ORACLE_SID.
Suggestively, forget about >/dev/null until you've gotten the script working.

An example that I use ...

Code:

        sqlplus $MDREAD @$MD_BATCH/DBU301.sql >> $LOG
        RESULT=$?
        if [ $RESULT != 0 ]
        then
                echo 'Error in DBU301' >> $LOG
                echo '**** DBU301 process completed' `date` '****' >> $LOG
        fi

.... Or ...

Code:

        sqlplus $MDMAN << !---- 
        @$MD_HOME/rdbms/install/first_message.sql;
        @$MD_HOME/rdbms/install/database_changes.sql;
        !----

Both examples are extracts from larger scripts.
Hope it helps in some way.

Cheers,
Cameron

How can I call sqlplus twice to test connection? When I call the sqlplus the second time, the log file has not been generated.
Thanks for your help.

Create yourself an actual .sql job and execute it.
Ensure the first line of the .sql job is a spool statement to the log file you require.
Don't really need to test it a second time.
You're unix script does not contain a test resulting in a start of the db instance if connectivity isn't available. And I think you might be able to contain everything within one loop.

Hope that's been of some help for you.
Enjoy your trip to discovery working on your solution.

Cheers,
Cameron