Subscribe to Posts by Email

Subscriber Count

    696

Disclaimer

All information is offered in good faith and in the hope that it may be of use for educational purpose and for Database community purpose, but is not guaranteed to be correct, up to date or suitable for any particular purpose. db.geeksinsight.com accepts no liability in respect of this information or its use. This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content and if notify any such I am happy to remove. Product and company names mentioned in this website may be the trademarks of their respective owners and published here for informational purpose only. This is my personal blog. The views expressed on these pages are mine and learnt from other blogs and bloggers and to enhance and support the DBA community and this web blog does not represent the thoughts, intentions, plans or strategies of my current employer nor the Oracle and its affiliates or any other companies. And this website does not offer or take profit for providing these content and this is purely non-profit and for educational purpose only. If you see any issues with Content and copy write issues, I am happy to remove if you notify me. Contact Geek DBA Team, via geeksinsights@gmail.com

Pages

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

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

  • thank you sharing a nice script, used to change a parameter across all databases in a server.
    I modified the script so that one call the script with arguements.No need to go into the script and change the parameter name and value.

    #!/bin/ksh
    # call this script as ch.sh STREAMS_POOL_SIZE 50M
    # check the number of parameters
    if [ $# -lt 2 ] || [ $# -gt 2 ]; then
    echo “invalid number of arguements .Should give only two arguements”
    exit 1;
    fi
    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
    # converting the Lower case to Upper case
    par=”${1^^}”
    val=${2}
    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 ${par} /u01/$ORACLE_SID/pfile/init.ora
    | cut -d= -f2`/ ${val}/g” /u01/$ORACLE_SID/pfile/init.ora
    #Then connect to database and change in spfile as well
    sqlplus ‘/ as sysdba’ << eof
    alter system set ${par} = ${val} scope=spfile;
    exit;
    eof
    done

    ————–
    regards