Oracle Databases – Connecting to Multiple Databases with Common Username and Different Passwords

bashoracle

Environment:

We have ~100 databases hosted on multiple machines. All databases have common username but password is different on every database.

Issue:

How can I read the password and TNS (Service name) from an external file one by one and connect to the each database and run the sql query and pass the output to a logfile.

What is done so far:

  1. Created pass file that contains password and Service_Name and delimiter ":"
cat pass
Loufr#123:PROD
Brinj#6523:TEST
  1. Another file dblist is created with all the dbnames (that matches the service names) in step 1
cat dblist
PROD
TEST
DEV
QA
Quality

Goal:

read name of the database from dblist and find the password from pass file for that database.

Use dbname and password to connect to database in following string

sqlplus -s dbsnmp@${TNS}/${Password}
spool output.log
@query.sql
exit

Best Answer

This script should do the trick:

while read TNS; do
    Password="$(sed -n "s/\(.*\):${TNS}$/\1/p" pass)"
    [ "$Password" ] && sqlplus -s dbsnmp@${TNS}/${Password} << EOF
whenever sqlerror exit sql.sqlcode;
set echo off 
set heading off

spool output.log
@query.sql

exit;
EOF
done < dblist

If you just ask for a script parsing the files containing the credentials. You can see also: Connect to sqlplus in a shell script and run SQL scripts

Related Question