Automate sql script execution using bash script

Tutorials

Many times as a system administrator you will be requiring the need to check few db details time to time or need to update,insert or simply execute a sql file automatically. For all these requirements a simple solution is to script the execution of sql files. here in this example we will see how to run sql files from a folder to oracle DB using bash shell script.

For this pre-requisites is sqlplus client shall already be installed on the machine and shall be available. you can just run below command to make sure sqlplus client is installed. “sqlplus /nolog”. Make sure your tns entry is setup correctly and you are able to login to the database with the username and password.

Having said that below are the script details:

In this script we have kept all the sql files in a folder i.e. /home/oracle/sqls

Here we have used  a sample sql file which outputs the DB version details. you can just drop as many as sqls you wanted to run in the above specified folder.

In the script we will check the DB status before running the scripts, if the DB status found down it will exit without running the script. autosql.log is the log file it will generate post running the script which can be referred for checking the script execution status.

Below are the variables which needs be filled up before executing the script.

DB_HostName="mydb.mycompany.com"
DB_Port="1521"
DB_SID="db11203"
DB_UserName="system"
DB_Password="welcome"
DIR_SqlFiles="/home/oracle/sqls"

Script Name: autosql.sh

Language: Bash Shell Script

#!/bin/bash
# Shell script to run sql files from command line.
# Pre-Req: sqlplus client shall be installed already.
###########################################################
# Variables Section (DB Details)
###########################################################
DB_HostName="mydb.mycompany.com"
DB_Port="1521"
DB_SID="db11203"
DB_UserName="system"
DB_Password="welcome"
DIR_SqlFiles="/home/oracle/sqlfiles"
##########################################################
# All Script Functions Goes Here
##########################################################
db_statuscheck() {
echo "`date` :Checking DB connectivity...";
echo "`date` :Trying to connect "${DB_UserName}"/"${DB_Password}"@"${DB_SID}" ..."
echo "exit" | sqlplus "${DB_UserName}/${DB_Password}@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=${DB_HostName})(PORT=${DB_Port})))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=${DB_SID})))" | grep -q "Connected to:" > /dev/null
if [ $? -eq 0 ]
then
DB_STATUS="UP"
export DB_STATUS
echo "`date` :Status: ${DB_STATUS}. Able to Connect..."
else
DB_STATUS="DOWN"
export DB_STATUS
echo "`date` :Status: DOWN . Not able to Connect."
echo "`date` :Not able to connect to database with Username: "${DB_UserName}" Password: "${DB_Password}" DB HostName: "${DB_HostName}" DB Port: "${DB_Port}" SID: "${DB_SID}"."
echo "`date` :Exiting Script Run..."
exit
fi
}

runsqls() {
echo "`date` :Checking DB status..."
db_statuscheck
if [[ "$DB_STATUS" == "DOWN" ]] ; then
echo "`date` :DB status check failed..."
echo "`date` :Skipping to run extra sqls and exiting..."
exit
fi
echo "`date` :DB status check completed"
echo "`date` :Connecting To ${DB_UserName}/******@${DB_SID}";
if [[ "$DB_STATUS" == "UP" ]] ; then
for file in `dir -d $DIR_SqlFiles/*` ; do
#for file in `cat extrasqlslist.txt` ;do
echo "`date` :Executing file $file..."
echo "`date` :__________________________________________";
echo "`date` :SQL OUTPUT:";
echo "`date` :__________________________________________";
sqlplus -s ""${DB_UserName}"/"${DB_Password}"@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST="${DB_HostName}")(PORT="${DB_Port}")))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME="${DB_SID}")))" <<EOF
@$file;
commit;
quit;
echo "`date` :__________________________________________";
EOF
done
echo "`date` :completed running all extra sqls to create DM violations table"
else
echo "`date` :Either the DB is down or the exit status returned by script shows ERROR."
echo "`date` :Exiting ..."
exit
fi

}

Main() {
echo "`date` :Starting Sql auto run script."
runsqls
echo "`date` :Sql auto run script execution completed."
}

Main | tee autosql.log

 

 

Sample Output:

When Database is Down:

Auto Sql Script Output When DB is down

 

When Database is up:

sql shell script run output

 

 

In case of any ©Copyright or missing credits issue please check CopyRights page for faster resolutions.

4 Responses

  1. California says:

    Great explanation. One question:
    What does this statement do:
    grep -q “Connected to:” > /dev/null
    if [ $? -eq 0 ]

    So if the output of grep -q “Connected to:” > /dev/null is 0 then the server is up, how does it work?

    • Grep -q means

      -q, –quiet, –silent
      Quiet; do not write anything to standard output. Exit immediately with zero status if any match is found, even if an error was detected. So if “Connected to:” match found means the sqlplus got connected and it will return zero else non-zero.

  2. Sreekanth Reddy Lingamdinne says:

    Thank you http://www.techpaste.com for making this helpful. I have implemented this bash script in my environment and it is working very fine.i will give 3.5 star. i will give 5 star if you provide process stop if any script has error.

  3. Dennys says:

    Hi,

    How can I pass different set of variables. I want to run the script above multiple times but for different variables.

    So instead of having

    DB_HostName=”mydb.mycompany.com”
    DB_Port=”1521″
    DB_SID=”db11203″
    DB_UserName=”system”
    DB_Password=”welcome”
    DIR_SqlFiles=”/home/oracle/sqls”

    can I have multiple files

    1.cfg
    DB_HostName=”mydb1.mycompany.com”
    DB_Port=”1521″
    DB_SID=”1db11203″
    DB_UserName=”system”
    DB_Password=”welcome”
    DIR_SqlFiles=”/home/oracle/sqls”

    2.cfg
    DB_HostName=”mydb2.mycompany.com”
    DB_Port=”1521″
    DB_SID=”2db11203″
    DB_UserName=”system”
    DB_Password=”welcome”
    DIR_SqlFiles=”/home/oracle/sqls”

    Something like a loop and read the above from a file.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.