Remote to a remote oracle server and get running instances?

Question

I need to make a shell script that takes a list of servers and and check the running instances then run sql script in each instance!

now I do not want it to ask me for a password!

it's redhat and centos servers, I can not install any additional packages like sshpass, ...

any ideas?

Answer 1

Agree with first responder. The only way to do what you want, and not 100% sure it is possible, would be to have very high level permissions on all servers, or have special permission settings. Any system admin would be justified in asking for any order to set something like this up in writing. The same goes for the DBAs for the DBs you want to run "sql script in each instance", without user/password signon. My advice is you need to find a way that works with the standard server/DB security, and stop finding a way around them.

Answer 2

First, setup ssh public key authentication to login to remote servers (after setup) without prompt. Do this as the oracle OS user.

http://superuser.com/questions/8077/how-do-i-set-up-ssh-so-i-dont-have-to-type-my-password

Second, run (ymmv):

ps -ef | grep ora_smon | grep -v grep | grep -v awk | awk -F'ora_smon_' '{ print $2 }'

Third, wrap this in a shell for loop:

for sid in `ps -ef | grep ora_smon | grep -v grep | grep -v awk | awk -F'ora_smon_' '{ print $2 }'`; do
  EXECUTE_SCRIPT_BELOW;
done

Fourth, run this script for each instance to setup the run of your SQL script:

PATH=/usr/local/bin:$PATH
ORAENV_ASK=NO
ORACLE_SID=$sid

export PATH
export ORAENV_ASK
export ORACLE_SID

. /usr/local/bin/oraenv

TWO_TASK=

export TWO_TASK

sqlplus / as sysdba @your_sql_script.sql

There are a lot of assumptions here, but if your oracle environment is close to default you should be ok:

"sqlplus / as sysdba" only works if the "dba" os group is assigned to the user you are logged in as via ssh.

It also assumes that a running instance is also "open" for your script, which may not be correct.

It also assumes the oracle environment script is in /usr/local/bin.