Call sqlplus in the shell script

Shell Programming and Scripting
1

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

2

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. :slight_smile:

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

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

        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

3

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.

4

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. :wink:

Cheers,
Cameron