Here's the script for the lazy ones. After script I have explained how the script actually works.
This script requires connection as sqlplus / as sysdba without giving password
#
# 1.0 17.09.2015 Mika Heino
#
# List Oracle SIDs in server in human readable output
# and connect SQL plus as sysdba
#
db=`ps -ef | grep [p]mon | awk {'print $8'} | sed -e 's,ora_pmon_,,' | sed -e s,asm_pmon_,,`
for i in $db ; do
if [ "$i" == "sed" ] || [ "$i" == *ASM* ]
then
continue
else
export ORAENV_ASK=NO
export ORACLE_SID=$i
. oraenv
export ORAENV_ASK=YES
sqlplus -S / as sysdba @your_sql_script
fi
done
What it actually does?
ps -ef | grep [p]mon -- List all active pmon processes. Use [] to avoid grep coming into the result list
awk {'print $8'} -- print from output only the 8th column, which is the process name
sed -e 's,ora_pmon_,,' -- remove 'ora_pmon_' text from output
sed -e s,asm_pmon_,, -- remove 'asm_pmon_' text from output
Final output looks like this. It will still output 'sed' text two times, but this will be removed in the clumsy if -clause
[oracle@server ~]$ ps -ef | grep [p]mon | awk {'print $8'} | sed -e 's,ora_pmon_,,' | sed -e s,asm_pmon_,,
+ASM1
INSTANCE2
INSTANCE3
INSTANCE4
sed
sed
This output is then inserted into variable $db.
db=`ps -ef | grep [p]mon | awk {'print $8'} | sed -e 's,ora_pmon_,,' | sed -e s,asm_pmon_,,`
Do for loop for all values within the variable
for i in $db ; do
Skip and continue if the value is 'sed' or 'ASM'
if [ "$i" == "sed" ] || [ "$i" == *ASM* ]
then
continue
else
Otherwise export the Oracle environment, use . oraenv and execute the desired SQL script.
export ORAENV_ASK=NO
export ORACLE_SID=$i
. oraenv
export ORAENV_ASK=YES
If your environment has passwords, use them right here using variables.
sqlplus -S / as sysdba @your_sql_script
fi
done
