Passing sqlplus output to shell variable

Shell Programming and Scripting
1

Hi ,

I am using below code :

for i in `ps -ef|grep pmon|awk {' print $8 '}|cut -f3 -d'_'|grep -v '^grep'`
do
ORACLE_SID=$i
export ORACLE_SID;
dest=`sqlplus  "/ as sysdba" <<EOF
set heading off feedback on verify off
select DESTINATION from v\\$archive_dest where target in ('REMOTE','STANDBY');
exit
EOF`
echo $dest;
done

Below is the output:

c456veq {/home/oracle}: ./pwdcopy.sh
SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 21 06:23:01 2016 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> SQL> no rows selected SQL> ora426b 1 row selected. SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 21 06:23:01 2016 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> SQL> no rows selected SQL> ora427b 1 row selected. SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

I am looking for the red colored output only.

Best regards,
Vishal

2

Hello admin_db,

Could you please try following and let me know if this helps.

./your_script.ksh | awk '{match($0,/rows selected SQL>.*row selected. SQL>/);if(substr($0,RSTART+19,RLENGTH-40)){print substr($0,RSTART+19,RLENGTH-40)}}'

Hope this helps.

Thanks,
R. Singh

1 Like
3

My ORACLE is a bit rusty, but I'd think by setting a few options correctly you can get rid of most of the undesired output. Did you try setting feedback off ?

4

Most of the undesired output will be surpressed when you call sqlplus with the -s option.
Setting feedback off will get rid of the no rows selected message - btw. your output shows you executed 2 queries (one returning no rows, one returning the archive-destination) where your script only shows one.

for i in `ps -ef|grep pmon|awk {' print $8 '}|cut -f3 -d'_'|grep -v '^grep'`
do
   ORACLE_SID=$i
   export ORACLE_SID;
   dest=`sqlplus -s "/ as sysdba" <<EOF
      set heading off feedback off verify off
      select DESTINATION from v\\$archive_dest where target in ('REMOTE','STANDBY');
      exit
EOF`
   echo $dest;
done
1 Like
5

And, for the for loop, try

for i in $(ps -ef | awk '/pmon/ {split ($8, T, "_"); print T[3]}')
1 Like
6

Thanks !

Now all is good and I want to copy password file of primary to standby -I am using something like below and looks like this is not working.

for i in `ps -ef|grep pmon|awk {' print $8 '}|cut -f3 -d'_'|grep -v '^grep'`
do
   ORACLE_SID=$i
   export ORACLE_SID;
   dest=`sqlplus -s "/ as sysdba" <<EOF
      set heading off feedback off verify off
      select DESTINATION from v\\$archive_dest where target in ('REMOTE','STANDBY');
      exit
EOF`
echo $dest;
Ht=`tnsping $dest|sed -n 's/.*[Hh][Oo][Ss][Tt] *= *\([^)]*\).*/\1/p'`
echo "Hostname " $Ht;
scp -i ~/.ssh/dg_key $ORACLE_HOME/dbs/orapw"$ORACLE_SID"* oracle@"$Ht":$ORACLE_HOME/dbs/orapw"$dest"*
done

Best regards,
Vishal