Create a folder with the SER number under: /monito_sam/extract_output/Extracts
Copy all required *.sql files into this folder.
Example:
cd /monito_sam/extract_output/Extracts
mkdir SRXXXXTEST
ls -lrt SRXXXXTEST
-rwxrwx--- 1 oracle dba 295 Nov 12 23:04 test_sam.sql
Step 2: Execute the Script
Syntax:
./run_sql.sh <Complete_PATH_OF_SQLS_FOLDER>
To run in background with nohup:
nohup ./run_sql.sh <Complete_PATH_OF_SQLS_FOLDER >/dev/null 2>&1 &
Example:
nohup ./run_sql.sh /monito_sam/extract_output/Extracts/SRXXXXTEST >/dev/null 2>&1
&
Step 3: Post-Execution
After completion: - The script compresses all generated *.csv files into .gz format inside the
same folder. - Grants appropriate access permissions.
Check nohup job status:
jobs -l
Verify output:
cd /monito_sam/extract_output/Extracts/SRXXXXTEST
Step 4: Email Confirmation
Upon successful completion, an email notification will be triggered automatically. We can
update the email address as per the requirements.
Example:
cat run_sql.sh
#!/bin/bash
# If not running in background, re-execute with nohup
if [[ -z "$NOHUP_WRAPPED" ]]; then
export NOHUP_WRAPPED=1
nohup "$0" "$@" > /dev/null 2>&1 &
echo "Process started in background with nohup. PID: $!"
exit 0
fi
# ===========================
# Constants
# ===========================
ORACLE_HOME="/app/oracle/product/19c/db"
EMAIL="SMXXX@karo.sam.com"
CRED_FILE="/home/oracle/admin/.dba_ro_cred"
LOG_DIR="/monito_sam/extract_output/Extracts/LOGS"
SERVICE_NAME="dbservice"
BASE_DIR="/monito_sam/extract_output/Extracts" # Base directory for SQL folders
# ===========================
# Usage check
# ===========================
if [ "$#" -ne 1 ]; then
echo "Usage: $0 <FOLDER_NAME>"
echo "Example: $0 SRXXXXTEST"
exit 1
fi
SQL_DIR="$BASE_DIR/$1"
# Validate directory exists
if [ ! -d "$SQL_DIR" ]; then
echo "Directory $SQL_DIR does not exist!"
exit 1
fi
# ===========================
# Set Oracle environment
# ===========================
export ORACLE_HOME
export PATH=$ORACLE_HOME/bin:$PATH
cd "$SQL_DIR"
# ===========================
# Load credentials
# ===========================
if [ ! -f "$CRED_FILE" ]; then
echo "Credential file not found: $CRED_FILE"
exit 1
fi
USERNAME=$(grep -i '^username=' "$CRED_FILE" | cut -d'=' -f2)
PASSWORD=$(grep -i '^password=' "$CRED_FILE" | cut -d'=' -f2)
if [ -z "$USERNAME" ] || [ -z "$PASSWORD" ]; then
echo "Invalid credentials in $CRED_FILE"
exit 1
fi
# ===========================
# Create log file
# ===========================
LOG_FILE="$LOG_DIR/sql_run_$(date +%Y%m%d_%H%M%S).log"
echo "Starting SQL execution at $(date)" > "$LOG_FILE"
# ===========================
# Execute all .sql files
# ===========================
SQL_SCRIPTS=("$SQL_DIR"/*.sql)
ERROR_OCCURRED=0
for SCRIPT in "${SQL_SCRIPTS[@]}"; do
if [ -f "$SCRIPT" ]; then
echo "Executing $SCRIPT..." | tee -a "$LOG_FILE"
sqlplus -s "${USERNAME}/${PASSWORD}@${SERVICE_NAME}" <<EOF >> "$LOG_FILE" 2>&1
WHENEVER SQLERROR EXIT SQL.SQLCODE
@${SCRIPT}
EXIT;
EOF
if [ $? -ne 0 ]; then
echo "Error executing $SCRIPT. Check log: $LOG_FILE" | tee -a "$LOG_FILE"
ERROR_OCCURRED=1
else
echo "Successfully executed $SCRIPT" | tee -a "$LOG_FILE"
fi
else
echo "No .sql files found in directory: $SQL_DIR" | tee -a "$LOG_FILE"
ERROR_OCCURRED=1
break
fi
done
echo "Execution completed at $(date)" >> "$LOG_FILE"
# ===========================
# Send email notification
# ===========================
if [ "$ERROR_OCCURRED" -eq 1 ]; then
SUBJECT="Oracle SQL Execution Failed on $(hostname) for $SQL_DIR"
else
SUBJECT="Oracle SQL Execution Succeeded on $(hostname) for $SQL_DIR"
fi
mail -s "$SUBJECT" "$EMAIL" < "$LOG_FILE"
echo "Log saved to: $LOG_FILE"
echo "Notification sent to: $EMAIL"
# ===========================
# Compress new .csv files
for csv_file in "$SQL_DIR"/*.csv; do
if [ -f "$csv_file" ]; then
echo "Compressing $csv_file..."
gzip -f "$csv_file" # Force overwrite if .gz exists
chmod 775 "${csv_file}.gz"
echo "Compressed and set permissions for: ${csv_file}.gz"
fi
done
# Set permissions for the SQL directory itself
chmod 775 "$SQL_DIR"
fi
# ===========================
# Compress the log file
# ===========================
gzip "$LOG_FILE"
server*name*/orcl_monitor/extract_output/Extracts>./run_sql.sh SERREXXXX_all
Process started in background with nohup. PID: 24052030
11:19
server*name*/orcl_monitor/extract_output/Extracts>ps -ef|grep 24052030
oracle 24052030 1 0 21:48:32 pts/0 0:00 /bin/bash ./run_sql.sh SERREXXXX_all
oracle 44761554 23331340 0 21:49:24 pts/0 0:00 grep 24052030
oracle 12911108 24052030 0 21:48:33 pts/0 0:00 sqlplus -s
