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.
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.
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 ...
.... Or ...
Both examples are extracts from larger scripts.
Hope it helps in some way.
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.
Hi All,
Please let me know what i am missing in the following code (part of my script)
Schemas=(AWQM WFCONTROLLER PROVCO PRISM)
for s in "${Schemas}"
do
sch="${s}_$tol"
if || ;then
echo "This is AD or TD region"
sqlplus -s $sch/$tpwd@$ttns <<EOF... (7 Replies)
Hi All,
How i will use sqlplus in shell script?
Can any one provide sample code which explain following:
1. Connect to oracle DB
2. Exceute select * from tablename
3. Release connection to the DB
4. Append output in file everytime when query executes.
Thanks in advance (1 Reply)
Hi
I want to connect to the Oracle database using a username/password and get back the query result(a numeric value) in a variable, which I can then compare using a conditional.
Can anybody help me with this.
Thanks
Gaurav (4 Replies)
Hi,
I need help urgently for following issue. Pls help me to resolve this issue.
I am calling sql script file(file1.sql) from UNIX Shell Script(script1.ksh) using sql plus and trying to create flat file that contains all records returned from SQL query in SQL script(file1.sql)
I given... (6 Replies)
I have a .sh file which acts as an all set for a couple of oracle scripts i want to call. Basically it has 3 variables, the user Id, the password, and the oracle instance to connect to.. the problem is I cant seem to get the connection string format properly.. the program essentially is this:
... (1 Reply)
i am new working with hp-ux but i had to because of my job.
so i want to execute some sql scripts but
when i call sqlplus to run them it responds
/sbin/sh: sqlplus not found.
i have oracle 8.1.7 installed.
what should i do
sorry if this sounds too easy but i am now learning.
The same... (13 Replies)
Can anybody help me out in sending parameters from sql*plus script to unix shell script without using flat files..
Initially in a shell script i will call sql*plus and after getting some value from some tables, i want that variable value in unix shell script. How can i do this?
Please tell me... (2 Replies)
i'm having real problems retrieving the returncode of my sqlplus-call. I found a lot of informations on the net, but havn't been able to get it running so far, so now i ask for some help ;)
I do start the sqlplus out of my shell script with the parameters stored in the proc_clips.sql, which is... (6 Replies)
I have a script which connects to different database servers using sqlplus. Is there a way by which I can run a shell command on that host from sqlplus?
I know about 'host' command but it runs script on the local machine where the original script is running. Is there a way to run command on the... (9 Replies)