Script: Shell Script to connect to all databases in a server and change a parameter

Hi,

When you need to run something like a check of parameter in all databases in a server and if you have more databases, manually doing will be cumbersome.

Here is the script that helps to connect to all databases in a server by reading /etc/oratab (linux) and then change a parameter streams pool to 50M and then change in the database too.

#!/bin/ksh
ORACLE_HOME=$ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
LD_LIBRARY_PATH=${SAVE_LLP}:${ORACLE_HOME}/lib
export TNS_ADMIN ORACLE_HOME PATH LD_LIBRARY_PATH
for INSTANCE in `cat /etc/oratab|grep -v "^#"|cut -f1 -d: -s`
do
export ORACLE_SID=$INSTANCE
# grep the streams pool size value in the parameter file (my pfile location is same for all database except the SID of database)
sed -i "s/`grep STREAMS_POOL_SIZE /u01/$ORACLE_SID/pfile/init.ora 
    | cut -d= -f2`/ 50M/g" /u01/$ORACLE_SID/pfile/init.ora
#Then connect to database and change in spfile as well
sqlplus '/ as sysdba' << eof
alter system set streams_pool_size = 50M scope=spfile;
exit;
eof
done

Further, if you have more than one servers and copying this script to others server and run there will be again problem, for that
connect to one server and use ssh with 'bash -s' option

For example log in test1 and copy the above script to ch.sh, and run, this runs the script on remote server from locally and change the required stuff :-

test1:/home/oracle> ssh test2 'bash -s' < ch.sh
test1:/home/oracle> ssh test3 'bash -s' < ch.sh
test1:/home/oracle> ssh test4 'bash -s' < ch.sh

Hope this helps.

-Geek DBA