Automate sql script execution using bash script

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:

 

When Database is up:

 

 

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