How to Automating Run SQL Script Execution on Remote Servers with Bash

This Bash Run SQL Script connects to multiple remote servers, runs a specified SQL script, and records the outcome. This is particularly useful for performing regular maintenance tasks across several database servers.

Objective :

• Automate running SQL scripts on many remote servers.
• Help database admins manage maintenance tasks efficiently.
• Reduce manual work with automated script runs.
• Centralize script execution and logging.

Prerequisites :

• Access to a Linux-based server with SSH enabled.
• Prepare your SQL script (rman_backup.sql) and place it at /home/oracle/sql file/rman_backup.sql
• Basic knowledge of Bash scripting.

Note :

• Navigate to the directory where the script is located.
• you can change the user name to your requirement 
• Customization: Modify the username (oracle) and ip’s (“192.168.220.179” “192.168.220.180” “192.168.220.181”) to match your environment.
• Understand each command before executing the script to ensure it fits your environment and requirements.

ALSO READ: 

• How to Remote Linux Server Management with Bash Scripts
• How to Set Up SSH Passwordless Connection

Example output :

Script :

#!/bin/bash
#################################################################
# Date: 30-June-2024
# Author: Krishna Tummeti
# Website: Tech Base Hub
# Purpose: Automating Run SQL Script Execution on Remote Servers with Bash
#################################################################
# List of remote server IP addresses
servers=("192.168.220.179" "192.168.220.180" "192.168.220.181")
# Base directory for storing logs
base_dir="/home/oracle/krishna"
# Path to the SQL script
sql_file="/home/oracle/sqlfiles/rman_backup.sql"
# Loop through each server and execute the SQL script
for server in "${servers[@]}"; do
    echo "Connecting to the remote server $server"
    ssh oracle@$server << EOF > $base_dir/${server}_destination_server_data.txt 2>&1
    sqlplus -s / as sysdba << SQL
    @$sql_file
    exit
    SQL
    exit
EOF
done
echo "Script is completed"

Thank You