Issue with running multiple commands withing su command

RHEL 6.2/Bash shell

root user will be executing the below script. It switches to oracle user and expect to do the following things

A. Source the environment variables for BATGPRD Database (the file used for sourcing is shown below after the script)
B. Shutdown the DB from sqlplus -- The section between EOF takes care of this
C. execute ps command and grep for the string pmon
D. Execute srvctl start command to start the database

Commands for A,B,C and D and enclosed in a single quote delimited by colons.

Issue description:
DB is shutdown succesfully from sqlplus (A & B ). But ps command and srvctl commands shown in red are not executed.
The exit command shown below (in green below) is actually the exit from sqlplus. I don't if this affecting the execution of remaining
sections of the script.

# cat test1.sh
su - oracle -c 'source /home/oracle/BATGPRD1_oracle_env;sqlplus -s / as sysdba <<EOF
spool /home/oracle/SCRIPTS/Clone/mytestSQL.log
set echo off
set heading off

select sysdate from dual;
shutdown immediate;
select name, open_mode from v$database;
exit
EOF; ps -ef | grep pmon ; srvctl start database -d BATGPRD'
#
#

#
The file which I am sourcing above

# cat /home/oracle/BATGPRD1_oracle_env
export ORACLE_SID=BATGPRD1
export ORACLE_HOME=/db/product/oracle/11.2
export PATH=$PATH:$ORACLE_HOME/bin
#

#

### Script execution

# ./test1.sh
-bash: line 9: warning: here-document at line 0 delimited by end-of-file (wanted `EOF')

24/03/2014

Database closed.
Database dismounted.
ORACLE instance shut down.
select name, open_mode from v
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 71641
Session ID: 2243 Serial number: 5

EOF needs to be on it's own, try this:

EOF
ps -ef | grep pmon ; srvctl start database -d BATGPRD'

You can do it like this:

su - oracle -c "source /home/oracle/BATGPRD1_oracle_env;sqlplus -s / as sysdba <<EOF ; ps -ef | grep pmon ; srvctl start database -d BATGPRD
spool /home/oracle/SCRIPTS/Clone/mytestSQL.log
set echo off
set heading off

select sysdate from dual;
shutdown immediate;
select name, open_mode from v$database;
exit
EOF
"

Can I suggest putting the code in another script (like /usr/local/bin/o_info) and pass $database in as a parameter you can the call with su like this:

su - oracle -c "/usr/local/bin/o_info $database"

Thank you Subbeh. Your fix worked.

Hi Chubler,
Your idea of passing DB name as a parameter is great. Hope it won't treat $database as a literal because of the double quotes. Let me try to modify the script as per suggestion. If it works, it will make my script much more flexible. Thank you ChublerXL

Regarding Chubler's example

su - oracle -c "/usr/local/bin/o_info $database"

Is double quotes used when you need to pass a parameter ?

-c is for command.. that means a string, so to pass more than 1 string you will use double quotes making what is within considered as a single...

Hi vbe,
As you can see above, OP used just single quotes to enclose multiple commands. So double quotes are not actually needed when you have multiple commands. Right ?

Passing DB name as a parameter was not very succesfull.
Just to let you know that ORACLE_SID should ${DB_NAME}1 since this is RAC. Hence I am using $11 .eg BATGPRD1

As shown below, the execution didn't go well as the PATH variable didn't get set correctly and the script cannot find sqlplus and srvctl binaries.

# cat test3.sh
su - oracle -c "export ORACLE_SID=$11;export ORACLE_HOME=/oracle/product/oracle/11.2.0;export PATH=$PATH:$ORACLE_HOME/bin
sqlplus -s / as sysdba <<EOF
spool /home/oracle/SCRIPTS/Clone/mytestSQL.log
set echo off
set heading off

select sysdate from dual;
shutdown immediate;
select name, open_mode from v$database;
exit
EOF
ps -ef | grep pmon|grep -v source ; srvctl start database -d $1"
#

### Executing . Pass BATGPRD as Parameter1

# ./test3.sh BATGPRD
-bash: line 1: sqlplus: command not found
oracle   18475     1  0 23:00 ?        00:00:00 ora_pmon_BATGPRD1
grid     31520     1  0 Feb16 ?        00:15:33 asm_pmon_+ASM1
-bash: line 11: srvctl: command not found
#

Uh, no. The shell parser on the receiving end of su -c has to know this is one command.

su - someuser  -c 'ls; grep foo bar'
su - someuser  -c "ls; grep foo bar"

Either of these will work.

A probably ridiculous example:

su - someuser  -c 'su - anotheruser  -c "ls; grep foo bar" '

You might do this because of how /etc/sudoers is set up.

Thanks Jim. Thanks everyone.

Any idea why passing the DB name as the parameter doesn't work ? Code and its execution shown in my last post. Just reminding that I execute the script as root.

Yes.
You could try this:

su -c "echo $UID;whoami;echo $USER"

EDIT -- happens when one answers at the end of page one... --

Does the o_info script (as in Chubler's example) check for $1 ?

If you SOURCE the file, it will use the same variable name as the caller script: $database
If you EXECUTE the file, it will as well parse its arguments, starting with $1 which would be the value of $database.

Hope this helps

Thank you Sea. So, how can I pass the database name into the su command from the caller script ?

The problem I think is that some of your $ variables are being expanded before the su command is run, try escaping them like this, I don't really like $11 either, can I suggest ${1}1 for more readability:

# cat test3.sh
su - oracle -c "export ORACLE_SID=${1}1;export ORACLE_HOME=/oracle/product/oracle/11.2.0;export PATH=\$PATH:\$ORACLE_HOME/bin
sqlplus -s / as sysdba <<EOF
spool /home/oracle/SCRIPTS/Clone/mytestSQL.log
set echo off
set heading off

select sysdate from dual;
shutdown immediate;
select name, open_mode from v$database;
exit
EOF
ps -ef | grep pmon|grep -v source ; srvctl start database -d $1"
#

sample_cmd:

printf "My location is: $0
My arguments are: $*
Database: $database"

Your script:

database=myDBname
echo EXEC:
sh /path/to/../sample_cmd $database
echo SOURCE
. /path/to/../sample_cmd someWHAT

Saying:
Inside the script, you can just pass an argument like you would do beeing in the shell.
Whethere there is full path required or not, depens on the scripts location.

$database is only availabe within the caller script, however, calling another script passing $database inside the caller script, calls the 2nd script with 'expanded' $database, so the 2nd script gets "myDBname" as argument.

In your script call it like:

su -c "/path/to/o_info $database"

hope this helps