Home » Infrastructure » Linux » Not able to execute sql command in shell script (Redhat linux,Database version:10.2.0.1.0)
Not able to execute sql command in shell script [message #355060] Wed, 22 October 2008 07:27 Go to next message
ranvijaidba
Messages: 66
Registered: May 2008
Location: Bangalore
Member
I 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 .I am not comfortable in shell scripting

This is the script:
#!/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 < EOF >$BASE_DIR/dblog/db.log
   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
   echo "$db database closes normall"
   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: Only for testing i am passing "select sysdate from dual"
Error is:
[oracle@ssipl-srvr-027 ~]$ ./shutdbs.sh
./shutdbs.sh: line 16: syntax error near unexpected token `from'
./shutdbs.sh: line 16: ` select sysdate from dual;'

[EDITED by LF: added [code] tags]

[Updated on: Mon, 03 November 2008 04:17] by Moderator

Report message to a moderator

Re: Not able to execute sql command in shell script [message #355101 is a reply to message #355060] Wed, 22 October 2008 11:54 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
There should be a space after sqlplus in sqlplus/nolog .
Re: Not able to execute sql command in shell script [message #355242 is a reply to message #355101] Thu, 23 October 2008 03:54 Go to previous messageGo to next message
ranvijaidba
Messages: 66
Registered: May 2008
Location: Bangalore
Member
I correct it but getting error...

I think some problem in for loop because it is also not showing message that i want to print.

I simplified script without sending output to any file.
#!/bin/sh
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
   connect sys/ski4now as sysdba
   SET LINESIZE 100
   SET PAGESIZE 50
   SET  heading off feedback off verify off
   select sysdate from dual;
   exit
   EOF
   echo "$db database closes normall"
done
##########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*******"
 fi
exit


but getting error:

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

[EDITED by LF: added [code] tags]

[Updated on: Mon, 03 November 2008 04:17] by Moderator

Report message to a moderator

Re: Not able to execute sql command in shell script [message #355274 is a reply to message #355242] Thu, 23 October 2008 05:49 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Is this the full script ? If so, how does a "select sysdate from dual" shutdown a database ?

Please use code tags for future postings.
Re: Not able to execute sql command in shell script [message #355289 is a reply to message #355274] Thu, 23 October 2008 06:19 Go to previous messageGo to next message
ranvijaidba
Messages: 66
Registered: May 2008
Location: Bangalore
Member
Hi ebrian,

Thanks i am only checking with select command.if it recognize select command that there is no problem in executing shutdown command.

This is the code after replacing select command with shutdown immediate.Getting same error in both case.
#!/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 <<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"
done
##########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*******"
 fi
exit

Error:

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

Thanks for prompt reply.

[EDITED by LF: added [code] tags]

[Updated on: Mon, 03 November 2008 04:16] by Moderator

Report message to a moderator

Re: Not able to execute sql command in shell script [message #355324 is a reply to message #355289] Thu, 23 October 2008 11:46 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Nothing jumps out at me. Try to run your script in debug mode:

sh -x ./shutdbs.sh
Re: Not able to execute sql command in shell script [message #355440 is a reply to message #355324] Fri, 24 October 2008 01:38 Go to previous messageGo to next message
ranvijaidba
Messages: 66
Registered: May 2008
Location: Bangalore
Member
I run the script in debug mode as you told me.This is the output.

[oracle@ssipl-srvr-027 ~]$ sh -x ./shutdbs.sh
+ BASE_DIR=/home/oracle
+ export BASE_DIR
+ cd /home/oracle
./shutdbs.sh: line 29: syntax error: unexpected end of file


Thanks
Re: Not able to execute sql command in shell script [message #355510 is a reply to message #355440] Fri, 24 October 2008 10:10 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
I ran this on my system and it executed without error.

Can you open the file in vi binary mode and check for any special characters ?

vi -b shutdbs.sh
Re: Not able to execute sql command in shell script [message #356878 is a reply to message #355510] Sun, 02 November 2008 23:53 Go to previous messageGo to next message
ranvijaidba
Messages: 66
Registered: May 2008
Location: Bangalore
Member
Thanks ebrian Now script is running.Actually problem is in every line i put extra space for code clearity.i remove all the space.Now it is working.



Re: Not able to execute sql command in shell script [message #356920 is a reply to message #356878] Mon, 03 November 2008 03:06 Go to previous messageGo to next message
ThomasG
Messages: 3103
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
ranvijaidba wrote on Mon, 03 November 2008 06:53
Actually problem is in every line i put extra space for code clearity


Which we of course would have seen and could have told you in about 4.2 seconds, if you had formatted your code with CODE tags. Confused
Re: Not able to execute sql command in shell script [message #356926 is a reply to message #356920] Mon, 03 November 2008 04:20 Go to previous messageGo to next message
Littlefoot
Messages: 19711
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So I added [code] tags to all his messages, just to make sure you'd be able to see it. Code seems to be nicely formatted; what "extra space" is Ranvijaidba talking about? (note that i don't know Unix shell scripting, so - it looks perfect to me).
Re: Not able to execute sql command in shell script [message #356936 is a reply to message #356926] Mon, 03 November 2008 04:46 Go to previous messageGo to next message
ThomasG
Messages: 3103
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
When using this kind of "<<EOF" piping in Unix-ish OSes, the closing EOF *must* be on it's own line without any blanks before or after it.

Re: Not able to execute sql command in shell script [message #356967 is a reply to message #356936] Mon, 03 November 2008 06:32 Go to previous messageGo to next message
Littlefoot
Messages: 19711
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So, this
   sqlplus /nolog <<EOF             --> EOF here ...
   bla bla
   exit
   EOF                              --> ... and EOF here
   echo "$db database closes normal"
should have been
   sqlplus /nolog <<EOF        
   bla bla
   exit
EOF                                 --> no blanks around this EOF?
   echo "$db database closes normal"

Did I get it right?
Re: Not able to execute sql command in shell script [message #356981 is a reply to message #356967] Mon, 03 November 2008 07:07 Go to previous messageGo to next message
ThomasG
Messages: 3103
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Yep, that's right, Littlefoot.

Thanks for wishing me a nice Christmas, btw. Very Happy.

If all goes well, I will just have to fire off the conversion scripts in the morning, and then just keep a tail on the logs open at my parent's. Thank god they finally got Internet this year, so I can keep an eye on the jobs from the family Christmas party.

The REAL problems will of course only surface when the users start working again after the holidays.

Re: Not able to execute sql command in shell script [message #356989 is a reply to message #356981] Mon, 03 November 2008 07:30 Go to previous message
Littlefoot
Messages: 19711
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
[chat mode on]

./fa/5238/0/

[/chat mode off]

./fa/1587/0/
  • Attachment: mouse.jpg
    (Size: 29.28KB, Downloaded 1003 times)
Previous Topic: permission denied while running oraInstaller
Next Topic: Oracle Putty SSH Tunnel
Goto Forum:
  


Current Time: Thu Oct 30 18:25:27 CDT 2014

Total time taken to generate the page: 0.26401 seconds