Monday, July 19, 2010

Shell script to get the information from multiple Oracle databases

I will demonstrate in the following example, how to get the informaiton from the many oracle databases quickly and easly using the Unix/Linux shell script.

Example 1: You want to get the information about the version of the oracle database for many(100s of oracle databases in very quick and efficient manner using shell script, assuming that use have common user id with same passwor in all the databases i.e. scott/tiger.
You will need i. DB list: which will be input to your shell script ii. SQL file containing oracle query iii. Shell script ,iv. Log file, which is output of the execution of the shell script.
Input file 1: db_list.txt: which will contain list of the databses i.e
$cat db_list.txt
PLNTD1.US.COM
PTDBD1.US.COM
PTDBD2.US.COM


Input file 2: db_version.sql: which will contain SQL query i.e.
$cat db_version.sql
set feedback off
set line 200
set pagesize 0
set echo off
set heading off

select d.global_name, v.versionfrom global_name d, product_component_version v where product like 'Oracle Database%';


File 3: Shell Script:get_dbs_info.sh - Main Korn shell script i.e
$>cat get_dbs_info.sh
#!/bin/ksh
DB_INPUT=db_list.txt
LOG=Db_version_info_`date +"%m%d%y%H%M%S"`.log
echo "Log File Name->"${PWD}${LOG} >$LOG
cat $DB_INPUT while read line do
#echo $line >>$LOG
sqlplus -s
mailto:gems_read/gems_passw0rd@$line <<"EOC">> ./$LOG
@db_version.sql
"EOC"
echo $?
done


Now lets execute the shell script, -x option is to run the script with debug option.
$>ksh -x get_dbs_info.sh
will generate the following output show in File4:

File 4: O/P or Log file: Will give you the list of database with oracle version, when you execute the shell script.
$>cat Db_version_info_071910175552.log
Log File Name->/balvant/exp/Db_version_info_071910175552.log
PLNTD1.US.COM
10.2.0.4.0
PTDBD1.US.COM
10.2.0.4.0
PTDBD2.US.COM
10.2.0.4.0