LinuxQuestions.org
Review your favorite Linux distribution.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 10-22-2008, 07:07 AM   #1
Ranvijaidba
LQ Newbie
 
Registered: Oct 2008
Location: Mumbai(India)
Posts: 2

Rep: Reputation: 0
Not able to execute Oracle Sql command in shell script


I have made one script to shutdown all oracle database that is running on server.I am checking Oracle process for this.But when i am running this script it is not recognizing sql command.Please tell me where i am wrong.

This is the script:

#!/bin/sh
############################# DATABASE SHUTDOWN #########################
BASE_DIR=/home/oracle
export BASE_DIR
cd $BASE_DIR
for db in `ps -ef|grep pmon|grep -v grep|awk '{print $8}'|sed -e 's/ora_pmon_//g'|grep -v sed`
do
echo "Loading $db database profile..........."
. $BASE_DIR/$db
echo "Shutting $db database..."
sqlplus/nolog < EOF >$BASE_DIR/dblog/db.log
connect sys/ski4now as sysdba
SET LINESIZE 100
SET PAGESIZE 50
SET heading off feedback off verify off
shutdown immediate
exit
echo "$db database closes normal"
EOF
done
exit
##########Checking for any running database####################
if [ `ps -ef|grep pmon |wc -l` -ge 1 ]
then
echo " Database is running Please Check error log file for Shutdown error"
else
echo "***All Database is closed*******"
exit 1
fi
exit


Note: not recognising "shutdown immediate" command
 
Old 10-22-2008, 01:47 PM   #2
jan61
Member
 
Registered: Jun 2008
Posts: 235

Rep: Reputation: 46
Moin,

I think, you use the here document in a wrong syntax. Try it this way (untested, I have no Oracle on my machine here):
Code:
( sqlplus/nolog <<EOF
connect sys/ski4now as sysdba
SET LINESIZE 100
SET PAGESIZE 50
SET heading off feedback off verify off
shutdown immediate
exit
EOF
echo "$db database closes normal"
) >$BASE_DIR/dblog/db.log
The echo is not a sqlplus command, you should place it outside the here document, the command sequence should start with "<<", not "<" and an output redirection IMHO does not work when starting such a document. Use a subshell (the braces) to redirect output.

Jan

EDIT: The output redirection is also possible without a subshell:
Code:
sqlplus/nolog >$BASE_DIR/dblog/db.log <<EOF
connect sys/ski4now as sysdba
SET LINESIZE 100
SET PAGESIZE 50
SET heading off feedback off verify off
shutdown immediate
exit
EOF
echo "$db database closes normal" >>$BASE_DIR/dblog/db.log

Last edited by jan61; 10-22-2008 at 01:51 PM.
 
Old 10-23-2008, 01:14 AM   #3
Ranvijaidba
LQ Newbie
 
Registered: Oct 2008
Location: Mumbai(India)
Posts: 2

Original Poster
Rep: Reputation: 0
I edit the code but again i am getting error

Now codes looks like:
#!/bin/sh
############################# ARMANI DATABASE SHUTDOWN #########################
BASE_DIR=/home/oracle
export BASE_DIR
cd $BASE_DIR
for db in `ps -ef|grep pmon|grep -v grep|awk '{print $8}'|sed -e 's/ora_pmon_//g'|grep -v sed`
do
echo "Loading $db database profile..........."
. $BASE_DIR/$db
echo "Shutting $db database..."
sqlplus/nolog >$BASE_DIR/dblog/db.log <<EOF
connect sys/ski4now as sysdba
SET LINESIZE 100
SET PAGESIZE 50
SET heading off feedback off verify off
select sysdate from dual;
#shutdown immediate;#
exit
EOF
echo "$db database closes normall" >>$BASE_DIR/dblog/db.log
done
exit
##########Checking for any running database####################
if [ `ps -ef|grep pmon |wc -l` -ge 1 ]
then
echo " Database is running Please Check error log file for Shutdown error"
else
echo "***All Database is closed*******"
exit 1
fi
exit

The error is:

[oracle@ssipl-srvr-027 ~]$ ./shutdbs.sh
./shutdbs.sh: line 32: syntax error: unexpected end of file
[oracle@ssipl-srvr-027 ~]$

Last edited by Ranvijaidba; 10-23-2008 at 01:16 AM.
 
Old 10-23-2008, 03:45 PM   #4
jan61
Member
 
Registered: Jun 2008
Posts: 235

Rep: Reputation: 46
Moin,

please use the code tag ("#" at the editor's menu) to show your code. One can't see clearly, if you've made a syntax error.

Some points I would check:
- at line 17 you use "#" to comment out a sql command within your sqlplus here document. That's not a valid sql comment, you should use "--" instead.
- I don't know, how you formatted the lines between the "<<EOF" and "EOF" lines. Take account of the fact, that the closing "EOF" has to stand at the very beginning of a line (no leading spaces are allowed) and no other characters are allowed after the "EOF".
- at line 22 there is an exit - the following lines are never executed. This is not the reason for the syntax error, but you should be aware of it.
- do you really call sqlplus/nolog (without a space)? The shell interprets this as a path, not as a command and tries to execute nolog in the directory sqlplus. Do you probably want to execute sqlplus -nolog?

A hint more to your convenience: You don't have to grep -v, if you use the grep like this: grep [p]mon. In this case the grep itself will not appear in the ps output, but all pmon processes are displayed.

Jan
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to execute Source command in a shell script UltraSoul Linux - General 3 09-23-2010 10:13 AM
execute a unix shell script from a database trigger(oracle 10g) nayant.m Linux - Newbie 1 03-28-2008 07:54 AM
Execute a php or sql script by button click stranger_6_7 Linux - General 3 02-02-2007 08:24 PM
How to execute a command line in a shell script orgazmo Programming 5 05-09-2005 09:16 AM
cannot execute 'cd /' shell command from PL/SQL.... samyakm Linux - Newbie 1 01-03-2005 10:10 AM


All times are GMT -5. The time now is 06:54 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration