For running a single SQL Command via sqlplus on multiple servers, we have to first do a few pre-requisites
1. Oracle client should be installed on the Unix/Linux Server
2. Create Local TNS entry in the tnsnames.ora file for the databases where you will run the commands
3. All databases should have one common user with a common password (this method is not the safest method
since the password will be in plain text in your Shell script)
Now let us proceed to first create a text file called dbnodes.txt which will have the TNSNAMES for the
database we will connect to..
Above is the entries in the dbnodes.txt file, these are the databases where we will loop the sql commands
Create a sql script with the commands you have to run on all the databases, lets call the file script.sql
set echo on
set linesize 200
set pages 0
select sysdate from dual;
select user from dual;
select instance_name,host_name from gv$instance;
alter system set cpu_count=8 scope=both sid='*';
show parameter cpu_count;
alter session set nls_date_format='DD/MM/YYYY';
I do this to dynamically to cap the cpu_count on some of our databases to contain any resouce hogging.
Now the final shell script which will call the dbnodes.txt and script.sql to loop the above sql commands
through all databases. The shell script is called dbloop.sh
cat dbnodes.txt | while read line
sqlplus -s user/user123@$line @/u03/scripts/script.sql
Copy all 3 files dbnodes.txt, script.sql and dbloop.sh to one directiry and run it like below; to collect the log of the SQL commands.
$--> dbloop.sh > dbrun.log
This will spool the output to a logfile.
So there you see it, one of the easiest ,method to run a set ofcommon sql commands on hundreds of server. I use this script to do basics like checking a certain parameters on all our production databases, to check dataguard status for multiple production DB’s etc. The use cases of this script is unlimited. Hope you enjoyed it 🙂 Keep it Easy and Keep It Oracle !