Executing sql on all Exadata nodes.

From time to time, I have to run scripts or single commands on all nodes for Exadata. This can take some time.

The problem:

We have a request from our developers to flush the shared pool on all nodes on our UAT Exadata. This is due to a bug we are still experiencing.

The solution:

This is a typical request for my team, were we have to run something on all our nodes. Flushing shared pool can be one of them.

Connecting and executing the same command 8 times, if you have a full rack, can be time-consuming and it is, probably, not the most exciting task to do.

For this reason I came up with the following script. It is something to help me with, so please, test it before using on production.

#!/bin/ksh

###############################
##
## Script to execute sql commands on all Exadata nodes
##
## Owner: David Alejo-Marcos
##
## usage: Exadata_sql_exec.ksh
## i.e.: Exadata_sql_exec.ksh MYTESTDB flush_shared_pool.sql
##
## Example of test.sql:
##
## oracle@hostname1 (MYTESTDB1)$ cat test.sql
## select sysdate from dual;
## select 'this is my test' from dual;
##
## alter system switch logfile;
##
###############################

NO_OF_ARGS=$#

typeset -u DB_SRC=$1
typeset -l DB_SCRIPT1=$2
typeset -l DB_SCRIPT=$2_tmp

if [[ "$NO_OF_ARGS" = "2" ]]; then
echo "Parameters passed: OK"
else
echo "usage: Exadata_sql_exec.ksh"
echo "i.e.: Exadata_sql_exec.ksh MYTESTDB flush_shared_pool.sql"
exit 1
fi
echo "sqlplus -s '/as sysdba'" >/tmp/${DB_SCRIPT}
echo "set linesize 500" >>/tmp/${DB_SCRIPT}
echo "set serveroutput on" >>/tmp/${DB_SCRIPT}
echo "select host_name, instance_name from v\\\$instance;" >>/tmp/${DB_SCRIPT}
echo "@/tmp/${DB_SCRIPT1}" >>/tmp/${DB_SCRIPT}
echo "exit;" >>/tmp/${DB_SCRIPT}
echo "EOF" >>/tmp/${DB_SCRIPT}

# Copy both scripts to all Exadata DB Servers.
dcli -l oracle -g ~/dbs_group -f /tmp/${DB_SCRIPT} -d /tmp
dcli -l oracle -g ~/dbs_group -f ${DB_SCRIPT1} -d /tmp

# Execute command on all Exadata DB
dcli -l oracle -g ~/dbs_group " (chmod 700 /tmp/${DB_SCRIPT} ; /tmp/${DB_SCRIPT} ${DB_SRC} ; rm /tmp/${DB_SCRIPT} ; rm /tmp/${DB_SCRIPT1})"

The sql file to be executed can be anything you would normally run from sqlplus.

Please, bear in mind this script will be executed as many times as nodes you have on your Exadata, so if you create a table of you perform inserts, it will be done 8 times…

Below is an output from a very simple test:

test.sql:

oracle@hostname1 (MYTESTDB1)$ cat test.sql
select sysdate from dual;
select 'this is my test' "ramdom_text" from dual;
alter system switch logfile;

Output:

oracle@hostname1 (MYTESTDB1)$ ./Exadata_sql_exec.ksh MYTESTDB test.sql
Parameters passed: OK
hostname1:
hostname1: Connect to instance MYTESTDB1 on hostname1
hostname1: ORACLE_HOME is set to /apps/oracle/server/product/11.2/dbhome_1
hostname1:
hostname1:
hostname1: HOST_NAME INSTANCE_NAME
hostname1: --------- ----------------
hostname1: hostname1 MYTESTDB1
hostname1:
hostname1:
hostname1: SYSDATE
hostname1: ---------
hostname1: 22-SEP-11
hostname1:
hostname1:
hostname1: ramdom_text
hostname1: ---------------
hostname1: this is my test
hostname1:
hostname1:
hostname1: System altered.
hostname1:
hostname2:
hostname2: Connect to instance MYTESTDB2 on hostname2
hostname2: ORACLE_HOME is set to /apps/oracle/server/product/11.2/dbhome_1
hostname2:
hostname2:
hostname2: HOST_NAME INSTANCE_NAME
hostname2: --------- ----------------
hostname2: hostname2 MYTESTDB2
hostname2:
hostname2:
hostname2: SYSDATE
hostname2: ---------
hostname2: 22-SEP-11
hostname2:
hostname2:
hostname2: ramdom_text
hostname2: ---------------
hostname2: this is my test
hostname2:
hostname2:
hostname2: System altered.
hostname2:
hostname3:
hostname3: Connect to instance MYTESTDB3 on hostname3
hostname3: ORACLE_HOME is set to /apps/oracle/server/product/11.2/dbhome_1
hostname3:
hostname3:
hostname3: HOST_NAME INSTANCE_NAME
hostname3: --------- ----------------
hostname3: hostname3 MYTESTDB3
hostname3:
hostname3:
hostname3: SYSDATE
hostname3: ---------
hostname3: 22-SEP-11
hostname3:
hostname3:
hostname3: ramdom_text
hostname3: ---------------
hostname3: this is my test
hostname3:
hostname3:
hostname3: System altered.
hostname3:
hostname4:
hostname4: Connect to instance MYTESTDB4 on hostname4
hostname4: ORACLE_HOME is set to /apps/oracle/server/product/11.2/dbhome_1
hostname4:
hostname4:
hostname4: HOST_NAME INSTANCE_NAME
hostname4: --------- ----------------
hostname4: hostname4 MYTESTDB4
hostname4:
hostname4:
hostname4: SYSDATE
hostname4: ---------
hostname4: 22-SEP-11
hostname4:
hostname4:
hostname4: ramdom_text
hostname4: ---------------
hostname4: this is my test
hostname4:
hostname4:
hostname4: System altered.
hostname4:
hostname5:
hostname5: Connect to instance MYTESTDB5 on hostname5
hostname5: ORACLE_HOME is set to /apps/oracle/server/product/11.2/dbhome_1
hostname5:
hostname5:
hostname5: HOST_NAME INSTANCE_NAME
hostname5: --------- ----------------
hostname5: hostname5 MYTESTDB5
hostname5:
hostname5:
hostname5: SYSDATE
hostname5: ---------
hostname5: 22-SEP-11
hostname5:
hostname5:
hostname5: ramdom_text
hostname5: ---------------
hostname5: this is my test
hostname5:
hostname5:
hostname5: System altered.
hostname5:
hostname6:
hostname6: Connect to instance MYTESTDB6 on hostname6
hostname6: ORACLE_HOME is set to /apps/oracle/server/product/11.2/dbhome_1
hostname6:
hostname6:
hostname6: HOST_NAME INSTANCE_NAME
hostname6: --------- ----------------
hostname6: hostname6 MYTESTDB6
hostname6:
hostname6:
hostname6: SYSDATE
hostname6: ---------
hostname6: 22-SEP-11
hostname6:
hostname6:
hostname6: ramdom_text
hostname6: ---------------
hostname6: this is my test
hostname6:
hostname6:
hostname6: System altered.
hostname6:
hostname7:
hostname7: Connect to instance MYTESTDB7 on hostname7
hostname7: ORACLE_HOME is set to /apps/oracle/server/product/11.2/dbhome_1
hostname7:
hostname7:
hostname7: HOST_NAME INSTANCE_NAME
hostname7: --------- ----------------
hostname7: hostname7 MYTESTDB7
hostname7:
hostname7:
hostname7: SYSDATE
hostname7: ---------
hostname7: 22-SEP-11
hostname7:
hostname7:
hostname7: ramdom_text
hostname7: ---------------
hostname7: this is my test
hostname7:
hostname7:
hostname7: System altered.
hostname7:
hostname8:
hostname8: Connect to instance MYTESTDB8 on hostname8
hostname8: ORACLE_HOME is set to /apps/oracle/server/product/11.2/dbhome_1
hostname8:
hostname8:
hostname8: HOST_NAME INSTANCE_NAME
hostname8: --------- ----------------
hostname8: hostname8 MYTESTDB8
hostname8:
hostname8:
hostname8: SYSDATE
hostname8: ---------
hostname8: 22-SEP-11
hostname8:
hostname8:
hostname8: ramdom_text
hostname8: ---------------
hostname8: this is my test
hostname8:
hostname8:
hostname8: System altered.
hostname8:

I have been told many times to do not use quotes on the following command:

sqlplus '/as sysdba'

but:

1.- I am too used to it
2.- you only save 2 keystrokes at the end of the day 🙂

As always, comments are welcome.

David Alejo-Marcos.

David Marcos Consulting Ltd.