The UNIX and Linux Forums  

Go Back   The UNIX and Linux Forums > Top Forums > Shell Programming and Scripting
Google UNIX.COM


Shell Programming and Scripting Post questions about KSH, CSH, SH, BASH, PERL, PHP, SED, AWK and OTHER shell scripts here.

More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
sqlplus call JohnZ1385 UNIX for Dummies Questions & Answers 1 06-05-2008 07:48 AM
how can i call sqlplus? theodore HP-UX 13 04-25-2008 11:01 AM
help me in sending parameters from sqlplus script to unix shell script Hara Shell Programming and Scripting 2 01-29-2008 11:31 AM
Shell Script And SQLPLUS maco_home UNIX for Dummies Questions & Answers 6 08-25-2007 09:05 AM
running shell script from sqlplus dkr123 Shell Programming and Scripting 9 07-20-2006 07:52 PM

Reply
 
Submit Tools LinkBack Thread Tools Display Modes
  #1 (permalink)  
Old 05-12-2008
Registered User
 

Join Date: May 2008
Posts: 2
Stumble this Post!
Call sqlplus in the shell script

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
Reply With Quote
Forum Sponsor
  #2 (permalink)  
Old 05-12-2008
Cameron's Avatar
Registered User
 

Join Date: Nov 2001
Location: Brisbane, Australia
Posts: 486
Stumble this Post!
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
Reply With Quote
  #3 (permalink)  
Old 05-13-2008
Registered User
 

Join Date: May 2008
Posts: 2
Stumble this Post!
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.
Reply With Quote
  #4 (permalink)  
Old 05-13-2008
Cameron's Avatar
Registered User
 

Join Date: Nov 2001
Location: Brisbane, Australia
Posts: 486
Stumble this Post!
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
Reply With Quote
Google The UNIX and Linux Forums
Reply

Thread Tools
Display Modes




All times are GMT -7. The time now is 05:26 PM.


Powered by: vBulletin, Copyright ©2000 - 2006, Jelsoft Enterprises Limited.
The UNIX and Linux Forums Content Copyright ©1993-2008 The CEP Blog All Rights Reserved -Ad Management by RedTyger Visit The Global Fact Book

Content Relevant URLs by vBSEO 3.2.0